Optimizing Buffer Cache Management: Principles and Techniques
This chapter discusses key principles of buffer cache tuning, focusing on the LRU (Least Recently Used) algorithm. It explains how the cache manages blocks based on access frequency, differentiating between “hot” and “cold” blocks. Additionally, it covers techniques for handling table scans, buffer cache hit ratios, and utilizing system views like V$BH for performance monitoring. The document highlights multiple buffer caches, configuration parameters, sizing considerations, and the impact of Automatic Shared Memory Management (ASMM) on performance, providing strategies to prevent starvation and enhance memory management.
Optimizing Buffer Cache Management: Principles and Techniques
E N D
Presentation Transcript
Buffer Cache Principles • The LRU List / LRU Algorithm • Keeps information on how often blocks are accessed • “Warm” blocks have been recently accessed • On the “MRU” – most recently used end of LRU list • “Cold” blocks have not been recently accessed • On the “LRU” end of the LRU list • Coldest blocks removed from cache to make way for new blocks
Table Scans and the Buffer Cache • Blocks from table scans treated differently • Done to prevent flooding the cache • Table scan blocks put immediately at LRU end of list • Blocks read using direct IO bypass buffer cache • Blocks from index lookups placed at MRU end of the LRU list • CACHE table parameter can modify behavior • Will treat scans the same as index lookups
Buffer Cache Configuration • Monitor buffer cache using V$BH • One row for each block in buffer cache • Shows which segments in buffer cache • Buffer cache hit ratio • Means percentage of block requests already in cache • Historically used to measure good performance • Look at V$BUFFER_POOL_STATISTICS • View with caution, may not tell the whole story • Operations with direct IO not included
Multiple Buffer Caches • Seven buffer caches in all • Default • Keep • Smaller tables, frequently accessed • Recycle • Larger tables, accessed via table scans • Less relevant with 11g based on cache being bypassed • Blocksize specific (2K to 32K) • Configured generally for performance, or • If transportable tablespace feature used between databases
Sizing the Buffer Cache • Relationship between buffer cache and physical dependent on application workload • Use the effective buffer cache advisory • Oracle keeps second, larger LRU list just for advisory statistics • See V$DB_CACHE_ADVICE
ASMM and AMM • Automatic Shared Memory Management (10g) • Used with versions 10g forward • Allows certain SGA components to be auto sized • Use parameter SGA_TARGET (10g) • See V$SGA_TARGET_ADVICE • Can still set individual parameters to specify minimum settings • Automatic Memory Management (11g) • Use parameter MEMORY_TARGET (11g) • Simplifies memory management in Oracle
ASMM Tuning • Can still set individual parameters • Including db_cache_size, shared_pool, large_pool • specifies minimum settings • Can prevent ASMM “over shrinking” a pool • Can stabilize overall memory in the SGA • ASMM doesn’t manage non-default pools • Memory thrashing can occur with ASMM • With variable workloads • Look for “SGA: allocation forcing component growth” • Set minimum values for individual pools to avoid thrashing • If persistent problem, disable ASMM
ASMM Tuning (cont.) • Buffer cache starvation can occur if • Shared servers are used • Global PL/SQL variables used • In-memory collections are used • Poor cursor management • General taxing of large or shared pools • Avoid by: • Disabling ASMM • Setting buffer cache parameter individually
