Tune Buffer Cache

Introduction

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

Checking The Cache Hit Ratio

Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
    FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
    WHERE  P1.name = 'db block gets'
    AND    P2.name = 'consistent gets'
    AND    P3.name = 'physical reads'

You can also see the buffer cache hit ratio for one specific session since that session started:

     SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
    FROM   v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
           v$sesstat P3, v$statname N3
    WHERE  N1.name = 'db block gets'
    AND    P1.statistic# = N1.statistic#
    AND    P1.sid = 
    AND    N2.name = 'consistent gets'
    AND    P2.statistic# = N2.statistic#
    AND    P2.sid = P1.sid
    AND    N3.name = 'physical reads'
    AND    P3.statistic# = N3.statistic#
    AND    P3.sid = P1.sid

You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.

Adjusting The Size Of The Buffer Cache

The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.

Determining If The Buffer Cache Should Be Enlarged

If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.

For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:

     SELECT   250 * TRUNC (rownum / 250) + 1 || ' to ' ||
             250 * (TRUNC (rownum / 250) + 1) "Interval",
             SUM (count) "Buffer Cache Hits"
    FROM     v$recent_bucket
    GROUP BY TRUNC (rownum / 250)

    Interval           Buffer Cache Hits
    --------------- --------------------
    1 to 250                       16083
    251 to 500                     11422
    501 to 750                       683
    751 to 1000                      177

This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.

There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.

In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.

Determining If The Buffer Cache Is Bigger Than Necessary

If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.

By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:

     SELECT   1000 * TRUNC (rownum / 1000) + 1 || ' to ' ||
             1000 * (TRUNC (rownum / 1000) + 1) "Interval",
             SUM (count) "Buffer Cache Hits"
    FROM     v$current_bucket
    WHERE    rownum > 0
    GROUP BY TRUNC (rownum / 1000)

    Interval     Buffer Cache Hits
    ------------ -----------------
    1 to 1000               668415  
    1001 to 2000            281760  
    2001 to 3000            166940  
    3001 to 4000             14770   
    4001 to 5000              7030    
    5001 to 6000               959

This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.

There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.

In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.

Full Table Scans

When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:

     SELECT   A.file_name, B.phyrds, B.phyblkrd
    FROM     SYS.dba_data_files A, v$filestat B
    WHERE    B.file# = A.file_id
    ORDER BY A.file_id

    FILE_NAME                            PHYRDS   PHYBLKRD
    -------------------------------- ---------- ----------
    /u01/oradata/PROD/system01.dbf        92832     130721
    /u02/oradata/PROD/temp01.dbf           1136       7825
    /u01/oradata/PROD/tools01.dbf          7994       8002
    /u01/oradata/PROD/users01.dbf           214        214
    /u03/oradata/PROD/rbs01.dbf           20518      20518
    /u04/oradata/PROD/data01.dbf         593336    9441037
    /u05/oradata/PROD/data02.dbf        4638037    4703454
    /u06/oradata/PROD/index01.dbf       1007638    1007638
    /u07/oradata/PROD/index02.dbf       1408270    1408270

PHYRDS shows the number of reads from the data file since the instance was started. PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)

In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.

Spotting I/O Intensive SQL Statements

The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

     SELECT   executions, buffer_gets, disk_reads,
             first_load_time, sql_text
    FROM     v$sqlarea
    ORDER BY disk_reads

EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area. BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement. DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)

You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.

Conclusion

This brief document gives you the basic information you need in order to optimize the buffer cache size for your Oracle database. Also, you can zero in on SQL statements that cause a lot of I/O, and data files that experience a lot of full table scans.

HAPPY LEARNING!