1 / 11

Chapter 20 Other Memory Management Topics

This chapter delves into various memory management strategies in Oracle, focusing on Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM) introduced in Oracle 10g and 11g. It examines components like Shared Pool, Large Pool, and Redo Buffer, emphasizing optimization techniques to enhance efficiency and performance. Readers will learn how to monitor memory allocations using dynamic views, set relevant parameters, and manage memory-related issues effectively to prevent memory thrashing and starvation situations. Key insights on result caching and overall memory optimization strategies are also provided.

leann
Télécharger la présentation

Chapter 20 Other Memory Management Topics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 20Other Memory Management Topics

  2. Overview • Memory management in Oracle • Oracle 10G: ASMM (Dynamic SGA) • Oracle 11G: AMM (Dynamic SGA & PGA) • Result Cache • Optimization of other memory areas • Shared Pool • Large Pool • Redo Buffer

  3. Optimizing Overall Memory • AMM (Automatic Memory Management) • Oracle 11G feature • Let’s Oracle decide how to allocate between pools • Includes between SGA components and PGA • ASMM (Automatic Shared Memory Management) • Oracle 10g feature • Let’s Oracle decide how to allocate for SGA only • PGA still separate entity managed on it’s own

  4. IO Wait Times / Memory Optimization • Memory optimization helps reduce IO • Categories of Oracle IO include: • Buffered datafile IO • Temporary segment IO waits • Direct path reads • System IO • See “Active Sessions Waiting” chart in OEM

  5. Using Advisories • For PGA • In Oracle 11g • Look at V$PGA_TARGET_ADVICE • Includes elapsed times • Compare directly with V$DB_CACHE_ADVICE • In Oracle 10g • Look at V$PGA_TARGET_ADVICE • Need to calculate elapsed times outside of V$PGA_TARGET_ADVICE • Determine average time and block counts • Use averages to convert byte counts • Combine times with buffer cache advisory

  6. AMM • Enable by setting parameters • MEMORY_MAX_TARGET • MEMORY_TARGET • Optionally set (minimum sizes for) • SGA_TARGET • PGA_AGGREGATE_TARGET • Individual pool parameters, such as LARGE_POOL • Can also configure in OEM • Go to Advisor Central • Then to Memory Advisors

  7. AMM (cont.) • Monitoring Memory Allocations • Can monitor using several dynamic views • V$MEMORY_DYNAMIC_COMPONENTS • V$MEMORY_RESIZE_OPS • V$MEMORY_TARGET_ADVICE • Views show useful statistics • Current memory allocations • Minimum and maximum sizes • Details on each resize operation • Affect on performance for potential resize operations

  8. AMM Issues & Considerations • Set minimum sizes for memory components • Helps avoid memory “thrashing” • Helps avoid “memory starvation” • Not all memory components are part of AMM • Nondefault buffer pools • AMM incompatible with “Linux HugePages” • Cannot lock SGA when using AMM (LOCK_SGA)

  9. Result Set Cache • Entire result sets stored in shared memory • Can help avoid • Parse time • Logical reads • Physical reads • Cache contention (e.g. latches) • Effective only in certain scenarios • Read only or nearly read only data • Small result sets • Needs to be used sparingly to avoid result cache latch contention • Expensive queries that meet above criteria • Can store PL/SQL Functions in cache in certain situations

  10. Configuring the Result Cache • Set with parameters • RESULT_CACHE_MODE={OFF|MANUAL|FORCE} • RESULE_CACHE_MAX_SIZE (size of cache) • RESULT_CACHE_MAX_RESULT (maximum size of one result) • Monitor with V$RESULT_CACHE_STATISTICS • Monitor contention by looking for “latch free” waits • Use with caution: • RESULT_CACHE_MODE=FORCE • The RESULT_CACHE table property

  11. Other Memory Optimizations • Sizing the Shared Pool • Moderately sized pool generally sufficient • See V$SHARED_POOL_ADVICE for help • Using bind variables important for efficiency • If not used, size increases usually not helpful • Review setting of CURSOR_SHARING parameter • Large Pool Sizing • Optional SGA component • Reduces shared pool fragmentation • Used for shared server configuraton • Used for parallel processes • Redo Log Buffer is generally small and self sufficient • Locking the SGA in memory helpful in certain scenarios

More Related