Transcript Buffer Cache Tuning

Chapter 18
Buffer Cache Tuning
1
Buffer Cache Principles

The LRU List / LRU Algorithm
–
–
Keeps information on how often blocks are accessed
“Warm” blocks have been recently accessed

–
“Cold” blocks have not been recently accessed

–
2
On the “MRU” – most recently used end of LRU list
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
–
–
–


Blocks from index lookups placed at MRU end
of the LRU list
CACHE table parameter can modify behavior
–
3
Done to prevent flooding the cache
Table scan blocks put immediately at LRU end of list
Blocks read using direct IO bypass buffer cache
Will treat scans the same as index lookups
Buffer Cache Configuration

Monitor buffer cache using V$BH
–
–

Buffer cache hit ratio
–
–
–
–
4
One row for each block in buffer cache
Shows which segments in buffer cache
–
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

–
Recycle


–
Larger tables, accessed via table scans
Less relevant with 11g based on cache being bypassed
Blocksize specific (2K to 32K)

5
Smaller tables, frequently accessed

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
–
–
6
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)
7  Simplifies memory management in Oracle
ASMM Tuning

Can still set individual parameters
–
–
–
–


ASMM doesn’t manage non-default pools
Memory thrashing can occur with ASMM
–
–
–
–
8
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
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
–
–
–
–
–

Avoid by:
–
–
9
Shared servers are used
Global PL/SQL variables used
In-memory collections are used
Poor cursor management
General taxing of large or shared pools
Disabling ASMM
Setting buffer cache parameter individually