How to predict I/O reduction from a larger data cache
Oracle Tips by Burleson Consulting
For detailed internals on using the data buffer advisor, see my notes on
Using Oracle data buffer cache
The predictive models for Oracle RAM areas began with the
v$db_cache_advice utility in Oracle9i.
As of Oracle9i release 2, we also see the data buffer cache advisory in
STATSPACK reports, and in the AWR reports within 10g and beyond.
is similar to an Oracle7 utility
that also predicted the benefit of adding data buffers. The Oracle7
utility used the x$kcbrbh view to track buffer hits and the
view to track buffer misses. The utility is the foundation of the
Oracle10g Automatic Memory Management (AMM) utility that monitors and
adjusts the sizes of the Oracle data buffers.\
Oracle9i r2 now has three predictive utilities
PGA advice - Oracle 9i has introduced a new advisory utility dubbed
v$pga_target_advice. This utility will show the marginal
changes in optimal, one-pass, and multipass PGA execution for different
pga_aggregate_target, ranging from 10% to 200% of the current
Shared Pool advice - This advisory functionality has been extended
in Oracle9i release 2 to include a new advice called
v$shared_pool_advice, and there is talk to expending the advice
facility to all SGA RAM areas in future releases of Oracle.
Data Cache advice - The v$db_cache_advice utility show the
marginal changes in physical data block reads for different sizes of
db_cache_size. Bear in mind that the data
from STATSPACK can
provide similar data as v$db_cache_advice, and most Oracle tuning
professionals use STATSPACK and v$db_cache_advice to monitor the
effectiveness of their data buffers.
Inside the buffer cache advisor
These advisory utilities are extremely important for the Oracle DBA who must
adjust the sizes of the RAM areas to meet current processing demands.
The following query can be used to perform the cache advice function, once
the db_cache_advice has been enabled and the database has run long
enough to give representative results.
-- Display cache advice
column c1 heading
'Cache Size (meg)' format 999,999,999,999
block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
The output from the script is shown below. Note that the values range
from 10 percent of the current size to double the current size of the
Estd Phys Estd Phys
Cache Size (meg) Buffers Read Factor
---------------- ------------ ----------- ------------
18.70 192,317,943 <==
15,208 4.97 51,111,658
19,010 3.64 37,460,786
26,614 1.74 17,850,847
30,416 1.33 13,720,149
34,218 1.13 11,583,180
1.00 10,282,475 <== Current Size
From the above listing we see that increasing the db_cache_size from
304 meg to 334 meg would result in approximately 700,000 less physical
reads. This can be plotted as a 1/x function and the exact optimal
point computed as the second derivative of the function:
These advisory utilities are very important for the Oracle DBA who must
adjust their SGA regions to meet current processing demands. Remember,
SGA tuning is an iterative process and busy shops continually monitor and
adjust the size of their data cache, PGA and shared pool.
However, there are some imitation to
the Oracle data buffer cache advisor. Setting the dba_cache_advice=on
while the database is running will cause Oracle to grab RAM pages from the
shared pool RAM area, and the buffer cache advisory uses this RAM in a simple
simulation to help capture elapsed time data for logical I/O and physical disk
reads, using different buffer sizes.
While the internals of the utility are not
disclosed, we see these parameters that effect the execution of the
v$db_cache_advice. (Note: These may not be used within the
historical data buffer reports):
dba_cache_advice=on or ready
First, there are several versions of the data
buffer cache advisor:
Real-time - The
v$db_cache_advice utility samples data from the SGA in real-time, and
creates a limited elapsed-time report from the SGA accumulators and addition
memory from the dba_cache_advice settings.
Historical with AWR and STATSPACK -
It appears that the AWR and STATSPACK data buffer advisories use the
DBA_HIST and STATSPACK table data for the report.
The awrrpt.sql script (used to generate the report) calls
awrrpti.sql, which actually pulls the report. Unfortunately, they use
an internal query, so the internal machinations are hidden:
select output from
We also see some limitations to the accuracy
of the data buffer advisor because of the architecture:
Limited deltas - Using only two observations for
logical reads and physical I/O are not enough data for a meaningful
Limited metrics - All of the advice from the
data buffer cache advisory is limited to logical I/O and physical I/O at the
Assumption of optimization? - Some suggest that
v$db_cache_advice utility assumes that the existing data buffer size is
optimal, the point at which the working set of frequently-used data blocks
are cached, and additions to the data buffer result in marginally declining
reductions in physical reads.
Hence, on the margin, the data buffer cache advisory is
inaccurate for database with an undersized db_cache_size (and
db_keep_cache_size, etc.). With the data
buffer set to a very small size, a small increase to the size of the RAM data
buffers results in a large reduction in Disk I/O.
The data buffer cache advisory may underestimate the benefits
with a too-small cache
However, the high reduction in Disk I/O does not continue ad infinitum. As
the RAM size approaches the database size, the marginal reduction in Disk I/O is
smaller because all databases have infrequently accessed data.
The data buffer cache advisory does
not know that a cache is oversized
In sum, the usefulness of a data buffer cache advisory is
undisputed, but the true way to a successful predictive model is to use the
my book "Oracle
Tuning: The Definitive Reference". These provide a valid time-series
analysis since the single delta values in the advisory are not sufficient.
Using STATSPACK data for consistent gets and physical reads, statistically
significant trends can be established. See my related notes on v$db_cache_advice:
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.