Buffer Cache Management

MANAGING BUFFER CACHE

Sizing the Buffer Cache
• Describe how the buffer cache is used by different Oracle processes
• List the tuning issues related to the buffer cache
• Monitor the use of buffer cache, and the different pools within buffer cache
• Implement dynamic SGA allocation
• Set the DB_CACHE_ADVICE parameter
• Create and size multiple buffer pools
• Detect and resolve free list contention

Buffer Cache Characteristics
The buffer cache holds copies of the data blocks from the data files. Because the buffer cache is a part of the SGA, these blocks can be shared by all users. The server processes read data from the data files into the buffer cache.

The buffer caches can be individually sized with the following parameters:
• DB_CACHE_SIZE specifies the size of default buffer pool in bytes.
• DBA_KEEP_CACHE_SIZE specifies the size of keep buffer pool in bytes.
• DBA_RECYCLE_CACHE_SIZE specifies the size of recycle buffer pool in bytes.

The buffer pools can be resized dynamically.

The blocks in the buffer cache are managed using two lists:
– The least recently used (LRU) list is used to keep the most recently accessed
blocks in memory. The blocks on the list are organized from the most recently
used (MRU) to the least recently used.

– The dirty list points to blocks in the buffer cache that have been modified but not
written to disk.

• Blocks in the buffer cache can be in one of three states:
– Free buffers are blocks that have the same image on disk and in memory. These
blocks are available for reuse.
– Dirty blocks are blocks with a different image in memory than the image on disk.
These blocks must be written to disk before they can be reused.
– Pinned buffers are memory blocks that are currently being accessed.

Dynamic SGA

The dynamic SGA feature implements an infrastructure to allow the server to change its SGA configuration without shutting down the instance.
• With a dynamic SGA, the Oracle server can modify its physical address space use to respond to the operating system’s use of physical memory.
• A dynamic SGA provides an SGA that will grow and shrink in response to a DBA command.




GRANULES
In the dynamic SGA model, a new unit of allocation called a granule has been created.
• SGA memory is tracked in granules by SGA components.
• Use V$BUFFER_POOL to monitor granule allocation and de-allocation in the buffer cache.
The SGA components are allocated and deallocated in units of contiguous memory called granules.
• A granule is a unit of contiguous virtual memory allocation.
• The size of a granule depends on the estimated total SGA:
– - 4 MB if estimated SGA size is less than 128 MB
– – 16 MB otherwise

Allocating Granules at Startup
• At instance startup, the Oracle server allocates granule entries, one for each granule to support SGA_MAX_SIZE bytes of address space.
• As startup continues, each component acquires as many granules as it requires.
• The minimum SGA configuration is three granules:
– One granule for fixed SGA (includes redo buffers)
– One granule for the buffer cache
– One granule for the shared pool

Adding Granules to Components
• A DBA can dynamically increase memory allocation to a component by issuing an ALTER SYSTEM command.
• Increase of the memory use of a component succeeds only if there are enough free granules to satisfy the request.
• Memory granules are not freed automatically from another component in order to satisfy the increase.

SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------- ----------- ------------------
db_cache_size big integer 4194304

SQL> alter system set db_cache_size=8M;
System altered.

SQL> show parameter db_cache_size;
NAME TYPE VALUE
------------------------------- ----------- ------------------
db_cache_size big integer 8388608


Example: Increasing the Size of an SGA Component
• Initial parameter values:
– SGA_MAX_SIZE = 128M, DB_CACHE_SIZE = 96M
– SHARED_POOL_SIZE = 32M

• ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
Error message indicating insufficient memory

• ALTER SYSTEM SET DB_CACHE_SIZE = 64M;

• ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
Error message indicating insufficient memory. Check scoreboard to
see if shrink has completed.

• ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
The statement is now processed.

Deprecated Buffer Cache Parameters
• Three parameters have been deprecated and will be maintained for backward compatibility.
– DB_BLOCK_BUFFERS
– BUFFER_POOL_KEEP
– BUFFER_POOL_RECYCLE
• These parameters cannot be combined with the dynamic size parameters.
Deprecated Buffer Cache Parameters

Dynamic Buffer Cache Advisory Parameter
• The buffer cache advisory feature enables and disables statistics gathering for predicting behavior with different cache sizes.
• The information provided by these statistics can help DBAs size the buffer cache optimally for a given workload.
• The buffer cache advisory is enabled by means of the DB_CACHE_ADVICE initialization parameter:
– This parameter is dynamic, and can be changed using ALTER SYSTEM.
– Three values are allowed: OFF, ON, and READY.

OFF: Advisory is turned off and the memory for the advisory is not allocated
• READY: Advisory is turned off, but the memory for the advisory remains allocated.
• ON: Advisory is turned on and both CPU and memory overhead is incurred.

Dynamic Views to Support Buffer Cache Advisory

• Buffer cache advisory information is collected in the V$DB_CACHE_ADVICE view.
• The view contains different rows that predict the estimated number of physical reads for different cache sizes.
• The rows also compute a physical read factor, which is the ratio of the number of estimated reads to the number of reads actually performed during the measurement interval by the real buffer cache.

Using V$DB_CACHE_ADVICE
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT‘
AND block_size = (
SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND advice_status = 'ON';

Using V$DB_CACHE_ADVICE View
The following output shows that if the cache was 212MB, rather than the current size of
304MB, the estimated number of physical reads would be 17 million (17,850,847). Increasing the cache size beyond its current size would not provide a significant benefit.
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
(10%) 30 3,802 18.70 192,317,943
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
(Current) 304 38,020 1.00 10,282,475
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
(150%) 456 57,030 .76 7,824,764

Tuning Goals
Because physical I/O takes significant time and increases CPU demand, Oracle server
performance is improved when the servers find most of the blocks that they need in memory.
The statistic that measures the performance of the database buffer cache is the cache hit ratio.

Diagnostic Measures
To effectively monitor the usage of the buffer cache, you can use the following mechanisms:
• Measure the cache hit ratio: Use the V$SYSSTAT view, the utlbstat.sql and utlestat.sql scripts, or the Stats Pack utility (available in Oracle8i and later versions)
• Use the V$DB_CACHE_ADVICE view (available in Oracle9i)

Tuning Techniques
The DBA monitors the buffer cache by calculating the cache hit ratio from statistics collected by the Oracle server.

To improve the cache hit ratio, the DBA can:
• Increase the size of buffer cache
• Use multiple buffer pools to separate blocks by access characteristics
• Configure the tables to be cached in memory
• Configure to bypass the buffer cache for sorting and parallel reads if possible.

Diagnostic Tools
Description of the Views
• The V$SYSSTAT and V$SESSTAT views contain the statistics used to calculate the
cache hit ratio:
SQL> SELECT name, value FROM v$sysstat
2> WHERE
name in (‘session logical reads‘,
3> ‘physical reads`,
4> ‘physical reads direct’
5> ‘physical reads direct (lob)’);
• V$BUFFER_POOL: Describes multiple buffer pools, and V$BUFFER_POOL
STATISTICS shows information on individual pool. You can also use monitor buffer
pools with the query:

SQL> SELECT name, physical_reads, db_block_gets, consistent_gets FROM v$buffer_pool_statistics;

• V$BH: Describes blocks held in the buffer cache

From the STATSPACK report:
Statistic Total Per Per
Trans Logon Second
---------------------- ------ --------- ---------
physical reads 15,238 13.0 15,238.0
physical reads direct 863 0.7 863.0
Physical reads direct(lob) 0 0 0
session logical reads 119,376 101.8 119,376.0

Measuring the Cache Hit Ratio
The server collects statistics on data access and stores them in the dynamic performance table V$SYSSTAT. You measure the cache hit ratio using the following system statistics:
• physical reads: Number of blocks read from disk
• physical reads direct: Number of direct reads, does not require the cache
• physical reads direct (lob): Number of direct reads of large binary objects
• session logical reads: Number of logical read requests
Calculate the hit ratio for the buffer cache with this formula:
Hit Ratio = 1 – (physical reads – physical reads direct - physical reads direct (lob) ) / session logical reads
Session logical reads gives the total number of read requests for data. This value includes requests satisfied by access to buffers in memory and requests that cause a physical I/O. You can multiply the ratio by 100 to convert it to a percentage.
Because these statistics are collected since the instance startup time, query them during
normal working loads but not immediately after startup. Because the buffer cache is empty when the instance starts, there are more physical reads after startup.

SQL> SELECT 1 - (phy.value – lob.value – dir.value) / ses.value "CACHE HIT RATIO"
FROM v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy WHERE ses.name = 'session logical reads' AND dir.name = ‘physical reads direct' AND lob.name = 'physical reads direct (lob)' AND phy.name = 'physical reads';

Guidelines for Using the Cache Hit Ratio
Hit ratio is affected by data access methods:
• Full table scans
• Data or application design
• Large table with random access
• Uneven distribution of cache hits

Increase the cache size ratio under the following conditions:
• The cache hit ratio is less than 0.9 on OLTP system.
• There is no undue page faulting.
• If the previous increase was effective.

Increasing the Cache Hit Ratio by Reducing Buffer Cache Misses
If your hit ratio is low, you may want to increase the number of buffers in the cache to
improve performance.

To make the buffer cache larger:
• Allocate more memory to the buffer cache by decreasing the size of any other component of SGA with unused memory.
• If there is room for SGA to grow (that is, if SGA_MAX_SIZE is not reached), use
ALTER SYSTEM to increase the value of DB_CACHE_SIZE,
(DB_KEEP_CACHE_SIZE, or DB_RECYCLE_CACHE_SIZE)
• Increase the value of the DB_BLOCK_BUFFERS initialization parameter for oracle8i
databases. This parameter is static.

Removing Unnecessary Buffers when Cache Hit Ratio Is High
If your hit ratio is high, your cache is probably large enough to hold your most frequently
accessed data. In this case, you can reduce the cache size and still maintain good
performance.

Evaluating the Cache Hit Ratio
• Do not continue increasing DB_BLOCK_BUFFERS if the last increase made no significant difference in the cache hit ratio. This may be because of the way that you are
accessing your data, or there may be other operations that do not even use the buffer
pool.
For example, the Oracle server bypasses the buffer cache for sorting and parallel
reads.

• Also, when looking at the cache hit ratio, bear in mind that blocks encountered during a
full table scan are not put to the head of the LRU list; therefore, repeated scanning does
not cause the blocks to be cached.

• The solution lies at the design or implementation level, in that repeated scanning of the
same large table is rarely the most efficient solution to the problem. It may be better to
perform all of the processing in a single pass or add appropriate indexes to the table.
• In large databases running an OLTP application, most rows are accessed either one or
zero times in any given unit of time. On this basis there is little point in keeping the row
(or the block that contains it) in memory for very long after its use.
• Finally, the relationship between cache hit ratio and number of buffers is far from a
smooth distribution. When tuning the buffer pool, avoid the use of additional buffers
that contribute little or nothing to the cache hit ratio.

Increasing DB_BLOCK_BUFFERS
As a general rule, increase DB_BLOCK_BUFFERS under the following conditions:
• The cache hit ratio is less than 90%.
• There is adequate memory for other processes, as measured by the amount of page
faults.
• The previous increase of DB_BLOCK_BUFFERS was effective.

Multiple Buffer Pools
The DBA may be able to improve the performance of the database buffer cache by creating multiple buffer pools. Objects are assigned to a buffer pool depending on how the objects are accessed. There are three buffer pools:
• KEEP: This pool is used to retain objects in memory that are likely to be reused.
Keeping these objects in memory reduces I/O operations.
• RECYCLE: This pool is used to eliminate blocks from memory that have little chance
of being reused. Flushing these blocks from memory enables you to allocate the space
that would be used by their cache buffers to other objects.
• DEFAULT: The pool always exists. It is equivalent to the single buffer cache.

Individual pools have their own size defined by
DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, and DB_RECYCLE_CACHE_SIZE.
– These parameters are dynamic.
– Latches are automatically allocated by Oracle RDBMS.

Example : Enabling Multiple Buffer Pools
SQL>CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL KEEP …);

SQL>ALTER TABLE customer
STORAGE (BUFFER_POOL RECYCLE);

SQL> ALTER INDEX cust_name_idx
STORAGE (BUFFER_POOL KEEP);

KEEP Buffer Pool Guidelines
• Tuning goal: Keeping blocks in memory
• Size: Holds all or nearly all blocks
• Tool: ANALYZE ... ESTIMATE STATISTICS

SQL> ANALYZE TABLE hr.countries ESTIMATE STATISTICS;
SQL> SELECT table_name, blocks FROM dba_tables WHERE owner = ’HR’
4 AND table_name = ’COUNTRIES’;
TABLE_NAME BLOCKS
---------- ----------
COUNTRIES 14


RECYCLE Buffer Pool Guidelines
• Tuning goal: Eliminating blocks from memory
when transactions are completed
• Size: Holds only active blocks
• Tool: V$CACHE
...
SQL> SELECT owner#, name, count(*) blocks FROM v$cache
GROUP BY owner#, name;


OWNER# NAME BLOCKS
------ ---------- ----------
5 CUSTOMER 147
...

Using V$CACHE to Find Blocks in the Buffer Pool
The DBA can also monitor the number of buffer pool blocks by object using V$CACHE.
V$CACHE is created by the catparr.sql script.

To determine the number of blocks required for objects in the RECYCLE pool:
• Tune the buffer cache with the RECYCLE pool disabled.
• Run catparr.sql to set up and populate V$CACHE.
• During peak running times, use the following query to calculate how many blocks are
used by each object:
SQL> SELECT owner#, name, count(*) blocks FROM v$cache GROUP BY owner#, name;
• Sum the blocks for all objects that will be used in the RECYCLE buffer pool and divide
by four to get RECYCLE pool size. You divide by four because it is assumed that onefourth
of the blocks targeted for the RECYCLE pool are active; the other three-fourths
are waiting to be aged out of the cache.

Tool: V$SESS_IO
SQL> SELECT s.username, io.block_gets, io.consistent_gets, io.physical_reads
FROM v$sess_io io, v$session s WHERE io.sid = s.sid ;

BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
---------- --------------- --------------
2187 23271 1344

Example : Calculating the Hit Ratio for Multiple Pools

SQL> SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO" FROM sys.v$buffer_pool_statistics WHERE db_block_gets + consistent_gets > 0;

NAME HIT_RATIO
------------------ ----------
KEEP .983520845
RECYCLE .503866235
DEFAULT . 790350047

SQL> SELECT * FROM v$buffer_pool;
ID NAME LO_SETID HI_SETID SET_COUNT BUFFERS LO_BNUM HI_BNUM
-- ------- -------- -------- --------- ------- ------- -------
1 KEEP 3 3 1 14000 0 13999
2 RECYCLE 4 6 3 2000 14000 15999
3 DEFAULT 1 2 2 4000 16000 19999



Dictionary Views
These dictionary views have a BUFFER_POOL column that indicates the default buffer pool for the given object:
• USER_SEGMENTS, DBA_SEGMENTS
• USER_CLUSTERS, ALL_CLUSTERS, DBA_CLUSTERS
• USER_INDEXES, ALL_INDEXES, DBA_INDEXES
• USER_TABLES, ALL_TABLES, DBA_TABLES
• USER_OBJECT_TABLES, ALL_OBJECT_TABLES, DBA_OBJECT_TABLES
• USER_ALL_TABLES, ALL_ALL_TABLES, DBA_ALL_TABLES

The V$BUFFER_POOL view describes the buffer pools allocated. The columns from
V$BUFFER_POOL show:
• The number and range of LRU latches allocated to the buffer (sets)
• The number and range of blocks allocated to the buffer

SQL> SELECT * FROM v$buffer_pool;
ID NAME LO_SETID HI_SETID SET_COUNT BUFFERS LO_BNUM HI_BNUM
-- ------- -------- -------- --------- ------- ------- -------
1 KEEP 3 3 1 14000 0 13999
2 RECYCLE 4 6 3 2000 14000 15999
3 DEFAULT 1 2 2 4000 16000 19999

Caching Tables
• Enable caching during full table scans by:
– Creating the table with the CACHE clause
– Altering the table with the CACHE clause
– Using the CACHE hint in a query
• Guideline: Do not overcrowd the cache.

Performance Indicators FOR Buffer Cache
The buffer cache hit ratio is by far the most useful measure of buffer cache performance.
However, there are some other indicators.

Wait Statistics
You should consider increasing the buffer cache size if there are high or increasing values for the Free Buffer Inspected system statistic. This statistic is the number of buffers skipped to find a free buffer. Buffers are skipped because they are dirty or pinned.

Wait Events
You can find out whether there have been waits for buffers from V$SYSTEM_EVENT or
V$SESSION_WAIT. If there are no waits, the event has not yet occurred. There are three main events to look out for:

a) Buffer Busy Waits
• This wait indicates that there are some buffers in the buffer cache that multiple
processes are attempting to access concurrently. Query V$WAITSTAT for the wait
statistics for each class of buffer.



Common buffer classes that have buffer busy waits include
1) data block
2) segment header
3) undo header
4)undo block.

1. data block - if the contention is on tables or indexes (not the segment header):
• Check for SQL statements using unselective indexes.
• Check for right-hand-indexes (that is, indexes that are inserted at the same point by many processes; for example, those which use sequence number generators for the key values).
• Consider using automatic segment-space management, or increasing free lists to avoid multiple processes attempting to insert into the same block

2. undo header
Displays contention on rollback segment header: If you are not using automatic
undo management, then add more rollback segments.
3.undo block
Displays contention on rollback segment block: If you are not using automatic undo management, consider making rollback segment sizes larger.

b) Free Buffer Inspected
This is a measure of how many buffers on the LRU list are inspected by a process looking for a free buffer (writing a new block) before triggering DB writer to flush the dirty buffers to disk.

c) Free Buffer Waits
This wait event indicates that a server process was unable to find a free buffer and has
posted the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk.
In order to resolve the contention, DBWR has to make blocks available faster for overwriting. To achieve this, examine ways of speeding up the write process. This event is also an indication that the buffer cache is too small. Examine the hit ratios for the buffer cache in order to determine if the cache should be resized.

Free Lists
• A free list for an object maintains a list of blocks that are available for inserts.
• The number of free lists for an object can be set dynamically.
• Single-CPU systems do not benefit greatly from multiple free lists.
• The tuning goal is to ensure that an object has sufficient free lists to minimize contention.
• Using Automatic Free Space Management eliminates the need for free lists, thus reducing contention on the database.

Diagnosing Free List Contention
Dynamic Performance Views
The V$SESSION_WAIT, V$WAITSTAT, and V$SYSTEM_EVENT dynamic performance
views are used to diagnose free list contention problems.
The DBA_SEGMENTS data dictionary view is used to identify the objects that need to be modified to increase the number of free lists.

Initialization Parameters
There are no initialization parameters to set for minimizing free list contention. The
FREELISTS keyword is used at the segment level. This value cannot be set dynamically; an object must be dropped and re-created in order to change it.

Modifying Free List
The number of free lists in a free list group can be changed by an alter table statement. Note that you cannot alter the free list storage parameter for segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT.

Diagnostic Criteria
You can query V$WAITSTAT and V$SYSTEM_EVENT to determine whether there is free list contention. If high numbers are returned, you must identify the object or objects.

• To query V$WAITSTAT:
SQL>SELECT class, count, time FROM v$waitstat WHERE class = ’segment header’;

• To query V$SYSTEM_EVENT:
SQL> SELECT event, total_waits FROM v$system_event WHERE event = ’buffer busy waits’;

To reduce buffer busy waits on:
• Data blocks: Change PCTFREE and/or PCTUSED; check for right-hand indexes
(indexes that are inserted into at the same point by many processes); increase
INITRANS; reduce the number of rows per block
• Segment headers: Use free lists or increase the number of free lists; use free list groups
(This can make a difference even in a single instance environment.)
• Free list blocks: Add more free lists (in the case of Oracle Parallel Server, making sure
that each instance has its own free list group)

Resolving Free List Contention
• Query V$SESSION_WAIT.
• Identify the object and get free lists for the segment from DBA_SEGMENTS.

• Either:
– Change the object in question, or
– Move into an auto-managed tablespace.

Example : Identifying the Objects with Free list contention
Determine the File, Block, and ID for which free list contention is occurring by querying
V$SESSION_WAIT.
• Identify the segment and determine the number of free lists that currently exist for the
segment identified by querying DBA_SEGMENTS:

SQL> SELECT s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.state FROM dba_segments s, v$session_wait w
WHERE w.event ='buffer busy waits‘
AND w.p1 = s.header_file
AND w.p2 = s.header_block;

Use either:
– Change the object
To increase the number of free lists for the object, use the ALTER TABLE
command in Oracle9i, specifying the FREELISTS keyword.
or
– Move the object to an auto managed tablespace.

Example : Auto-management of Free Space

SQL> CREATE TABLESPACE BIT_SEG_TS
DATAFILE ’$HOME/ORADATA/u04/bit_seg01.dbf’ SIZE 1M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

SQL>CREATE TABLE BIT_SEG_TABLE (IDNUM NUMBER)
TABLESPACE BIT_SEG_TS;

• Create an auto-managed tablespace:
• Create a table that uses auto-management of free space:
Auto-Management of Free Space