Chapter 20 Other Memory Management Topics

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