Just as the architecture of the database buffer cache has changed dramatically from release to release, so too have the effectiveness of the tools Oracle supplies to assist with tuning the database buffer cache. In Oracle 7 Release 7.3, the DBA needed to be aware of two X$ tables used for SGA database block buffer tuning – the X$KCBRBH and X$KCBCBH tables. These tables are populated when the initialization parameters DB_BLOCK_LRU_EXTENDED_STATISTICS and DB_BLOCK_LRU_STATISTICS respectively are set and the instance is restarted. The X$KCBRBH table's COUNT column displays the number of additional cache hits gained by adding additional cache blocks (when summed over a contiguous interval of blocks). The X$KCBCBH table's COUNT column displays the number of lost cache hits gained by adding additional cache blocks (when summed over a contiguous interval of blocks).

Let's look at an example of setting each of these parameters. Say you wanted to see the effect of increasing DB_BLOCK_BUFFERS by 1000. To do this, you would set the value of the initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS to 1000 and restart the instance. To see the effect of the extra buffers, you would connect as SYS and run a query like this:

SELECT 250 * TRUNC( indx/250 ) + 1
          || ' to ' 
          || 250 * ( TRUNC( indx/250 ) + 1) Interval,
             SUM( count ) Cache_Hits
        FROM x$kcbrbh
        GROUP BY TRUNC( indx/250 );

The output from this query might look like this:

—————- —————
1 to 250                   14020
251 to 500                  8950
501 to 750                   430
750 to 1000                10950

The way this data reads if that you might expect:

  • 14,020 additional buffer cache hits by adding 250 buffers to the database buffer cache

  • 8,950 additional buffer cache hits by adding another 250 buffers (500 in total) to the database buffer cache

  • 430 additional buffer cache hits by adding another 250 buffers (750 in total) to the database buffer cache

  • 10,950 additional buffer cache hits by adding another 250 buffers (1000 in total) to the database buffer cache

Likewise, you could theoretically determine the performance impact of decreasing the number of buffers in the database buffer cache by setting DB_BLOCK_LRU_STATISTICS to the number of buffers you wanted to remove from the database buffer cache and restarting the instance. A similar query to the one shown above, but using the fixed table X$KCBCBH, would give you estimates of the performance hit you would take across a range of buffer counts.

As this particular example shows, it was often the case that one interval grouping (501 to 750 in this case) gained you very little in the way of additional database buffer cache hits. Contrary to what you might expect given that Oracle provided this tool for estimating the improvement of adding buffers to the database buffer cache or the impact of reducing the size of the database buffer cache, it was also (unfortunately) the case that the estimates determined by this mechanism were almost invariably wrong. Given that setting DB_BLOCK_LRU_EXTENDED_STATISTICS or DB_BLOCK_LRU_STATISTICS also caused a performance hit as additional statistics were collected, the tool largely was useless for giving accurate results.

In Oracle 8 Release 8.0, Oracle exposed the functionality of the fixed tables X$KCBCH and X$KCBRBH through a dynamic view, V$RECENT_BUCKET. The performance impact of turning on DB_ BLOCK_LRU_EXTENDED_STATISTICS or DB_BLOCK_LRU_STATISTICS remained much the same, but the accuracy of the estimates given by querying V$RECENT_BUCKET improved slightly. Most DBA's, however, remained skeptical about the effectiveness of this tool.

Presumably as a result of this ineffectiveness, Oracle 8i Release 8.1.5 removed the DB_BLOCK_LRU_EXTENDED_STATISTICS and DB_BLOCK_LRU_STATISTICS parameters altogether, and also did away with the V$RECENT_BUCKET dynamic view. 

Oracle 9i reintroduced a mechanism of estimating the impact of increasing or decreasing the database buffer cache size using a new initialization parameter, DB_CACHE_ADVICE. This is a dynamic parameter that can take the values OFF (the default, meaning the buffer cache advisory is turned off and no memory is allocated to it), ON (meaning the buffer cache advisory is turned on, with associated CPU and memory costs), and READY (meaning the advisory is turned off but the memory is still allocated). 

When switching the buffer cache advisory from OFF to ON, there is a danger that the memory required in the shared pool for using this functionality will not be available, and hence an "ORA-04031 Inability to allocate from the shared pool" error will be returned. Oracle does not recommend running your Production system with this parameter turned on all the time because of the performance impact, but it does recommend that once you have turned the buffer cache advisory on that you return it to the READY state when finished your tuning session to minimize future occurrences of this error.

One major change between the functionality of the buffer cache advisory and the DB_BLOCK_LRU_[EXTENDED_]STATISTICS parameters is that the 9i functionality does not use the existing buffer cache structures to calculate the statistics. Rather, a replica of the buffer headers, hash and LRU chains is created in the permanent area of the shared pool. This minimizes the skewing caused by the 8i configuration and increases the accuracy of the estimates, though at this stage there still isn't enough evidence to indicate whether or not the estimates are accurate enough to be of use.

The dynamic view V$DB_CACHE_ADVICE was introduced in Oracle 9i Release 9.0.1 to collect and display the buffer cache advisory statistics. Different rows display the estimated number of physical reads for the cache size corresponding to each row, as well as an estimated physical read factor (the ratio of the number of estimated reads to the number of reads in the actual cache) for cache sizes from 10% to 200% of the current size. Below is a SELECT statement that you can use to read information from this view:

COLUMN size_for_estimate FORMAT 999,999,999,999 HEADING 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 HEADING 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 HEADING 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 HEADING 'Estd Phys| Reads'

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';

The query might return rows like this:

                                Estd Phys    Estd Phys
 Cache Size (MB)      Buffers Read Factor        Reads
—————- ———— ———– ————
             150       18,748        3.56      142,654

If the current size of the database buffer cache was 300 Mb, this row would tell us that reducing the cache to half its current size would cause an estimated additional 142,654 physical reads.

The most recent versions of Oracle, versions 9.2 and above, provide the initialization parameter STATISTICS_LEVEL. This parameter controls all major statistics collections and advisories in the database.

The buffer cache advisory is primarily controlled by the STATISTICS_LEVEL parameter, yet can be overridden using the deprecated DB_CACHE_ADVICE parameter. For example, setting STATISTICS_LEVEL to TYPICAL and setting DB_CACHE_ADVICE to OFF would enable all advisories except the buffer cache advisory. Conversely, setting STATISTICS_LEVEL to BASIC and setting DB_CACHE_ADVICE to ON would disable all advisories except the buffer cache advisory.

Oracle 10g includes the Automatic Shared Memory Management (ASMM) to automatically configure the shared memory areas, including the buffer cache. Oracle 10g also has some Memory Advisors, including the Buffer Cache Advisor. This advisor is basically the buffer cache advice available in Oracle 9i, but with recommendations as to what you should do. The Memory Advisors are accessed through the DBA_RECOMMENDATIONS view or through the Oracle Enterprise Manager.

To get an idea of sizing of the buffer cache, we will look at the V$DB_CACHE_ADVICE dynamic performance view. A query against this view might look like the following:

SELECT name, block_size, size_for_estimate, size_factor,
FROM v$db_cache_advice;

For the purposes or our discussion, we will assume that the output from the above query is shown below.

---------- ---------- ----------------- ----------- -------------------
DEFAULT          4096                16       .1429           255749531
DEFAULT          4096                32       .2857           242381006
DEFAULT          4096                48       .4286           115219649
DEFAULT          4096                64       .5714            26721127
DEFAULT          4096                80       .7143            14716865
DEFAULT          4096                96       .8571            10137855
DEFAULT          4096               112           1             7466766
DEFAULT          4096               128      1.1429             6542661
DEFAULT          4096               144      1.2857             5411557
DEFAULT          4096               160      1.4286             4480060
DEFAULT          4096               176      1.5714             4220742
DEFAULT          4096               192      1.7143             3952325
DEFAULT          4096               208      1.8571             3845413
DEFAULT          4096               224           2             3559367
DEFAULT          4096               240      2.1429             3526952
DEFAULT          4096               256      2.2857             3521834
DEFAULT          4096               272      2.4286             3521834
DEFAULT          4096               288      2.5714             3425159
DEFAULT          4096               304      2.7143             3425159
DEFAULT          4096               320      2.8571             3424590

The size factor column indicates the sizing of the buffer cache for that row. If the sizing factor is 1, then that row is the current size of the database buffer cache. For example, in the report above the current size of the default cache is about 112 Megabytes, since the size factor is 1. Sizing factors increase or decrease as a factor of the current database block size. Thus, a sizing factor of 2 represents a database buffer cache that would be two times that currently in production.

Once you have collected the information, you will want to review it and determine the appropriate size for your database buffer cache. Continuing with our example, we can see that if we increase the cache size to 224MB, we have doubled our cache size. The estimated number of physical reads for this cache size is 3,559,367 reads. If we increase the cache size any more, we really don't reduce the estimated number of physical reads by very much. So it looks like a 224MB cache size is adequate for this workload. After changing the cache size, you will want to monitor the new data from this view at a future date.