 |
|
How to predict I/O reduction from a larger data cache
Oracle Tips by Burleson |
For detailed internals on using the data buffer advisor, see my notes on
Using Oracle data buffer cache
advisor.
The predictive models for Oracle RAM areas began with the
v$db_cache_advice utility in Oracle.
As of Oracle release 11g, we also see the data buffer cache advisory in
STATSPACK reports, and in the AWR reports within 10g and beyond.
The new
v$db_cache_advice
view
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
x$kcbcbh
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.\
Oracler2 now has three predictive utilities
-
PGA advice - Oracle 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
sizes of
pga_aggregate_target, ranging from 10% to 200% of the current
value.
-
Shared Pool advice - This advisory functionality has been extended
in Oracle release 11g 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
select
size_for_estimate c1,
buffers_for_estimate c2,
estd_physical_read_factor c3,
estd_physical_reads
c4
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 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
db_cache_size.
Estd Phys Estd Phys
Cache Size (meg) Buffers Read Factor
Reads
---------------- ------------ ----------- ------------
30 3,802
18.70 192,317,943 <==
10% size
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
304 38,020
1.00 10,282,475 <== Current Size
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
456
57,030 .76
7,824,764
486
60,832 .74
7,563,180
517
64,634 .71
7,311,729
547
68,436 .69
7,104,280
577
72,238 .67
6,895,122
608
76,040 .66
6,739,731 <==
2x 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):
-
_db_cache_advice_batch_size
-
_db_cache_advice_sample_factor
-
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
table(dbms_workload_repository.&fn_name( :dbid,
:inst_num,
:bid, :eid,
:rpt_options ));
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
prediction.
-
Limited metrics - All of the advice from the
data buffer cache advisory is limited to logical I/O and physical I/O at the
system-wide level.
-
Assumption of optimization? - Some suggest that
the
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
scripts from
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
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
and include the URL for the page.
