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:
INTERVAL CACHE_HITS
—————- —————
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,
estd_physical_reads
FROM v$db_cache_advice;
For the purposes or our discussion, we will assume that the output from the above query is shown below.
NAME BLOCK_SIZE SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS ---------- ---------- ----------------- ----------- ------------------- 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.
Start the discussion at forums.toadworld.com