| |
 |
|
Oracle Tips by Burleson |
Oracle cache advice
2008 Update:
For my complete usage notes for the data buffer advisor, see:
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.
Bear in mind that the data buffer
hit ratio can provide data similar to v$db_cache_advice, and
most Oracle tuning professionals use both tools to monitor the
effectiveness of their data buffers.
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.
See
code depot for full scripts
--
****************************************************************
-- Display cache advice
--
-- Copyright (c) 2003 By Donald K. Burleson - All Rights reserved.
-- ****************************************************************
select
. . .
from
v$db_cache_advice
where
name = 'DEFAULT'
and
block_size = (SELECT . . . )
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 (Figure
3.1).
Estd Phys Estd Phys
Cache Size (MB) 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
Listing 3.1 – Output from
db_cache_advice
The output shows neither a peak
in total disk I/O nor a marginal trend with additional buffer RAM.
This result is typical of a data warehouse database that reads large
tables with full-table scans. In this case, there is no specific
“optimal” setting for the db_cache_size parameter. Oracle will
devour as much data buffer RAM as we feed to it, and disk I/O will
continue to decline. However, there is no tangential line that
indicates a point of diminishing returns for this application.
Taking the above into account,
the thrifty DBA will apply this simple rule: db_cache_size
should be increased if spare memory is available and marginal gains
can be achieved by adding buffers. Of course, increasing the buffer
blocks increases the amount of RAM running on the database. Hence, the
database management system may place more demands on the processor
than it can handle. The administrator must carefully juggle the amount
of available memory with the limitations of the hardware in
determining the optimal size of buffer blocks.
The DBA should prepare a strategy
for enabling cache advice. If he sets dba_cache_advice=on while
the database is running, Oracle will grab RAM pages from the shared
pool RAM area, with potentially disastrous consequences for the
objects in the library cache. If the existing db_cache_size
setting is 500 megabytes, Oracle will grab a significant amount of RAM
from the shared pool. For this reason, the DBA should set
db_cache_advice=ready in the init.ora file, and the RAM
memory will be pre-allocated by Oracle when the database is started.
For complex databases that can
benefit from Oracle’s sophistication, the DBA controls not only the
gross size of the buffers, but also the block size of each individual
buffer. For example, suppose the database tends to cluster records on
a single database block, while the other data blocks remain small.
Realizing that the I/O for a 32K block is virtually the same as the
I/O for a 4K block, the database designer might choose to make some of
the buffers larger to minimize I/O contention.
With the cache advice utility,
Oracle9i provides the DBA with another tool to streamline database
performance by predicting the optimal size of the RAM buffer pools.
Now that we understand the basics
of buffer block size allocation, let’s take a closer look at the
internal mechanisms of the data buffers.
The above
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
Burleson Consulting shares their personal arsenal of
Oracle data dictionary scripts in this comprehensive download. With
decades of experience using Oracle monitoring scripts and Oracle tuning
scripts, BC experts share their secrets for navigating the Oracle data
dictionary.
Packed with 681 ready-to-use Oracle scripts, this is
the definitive collection for every senior Oracle DBA.
It would take man-years to develop these scripts
from scratch, making this download the best value in the Oracle
industry.
BC has
priced this collection of 681 scripts at $79.95.
You can download them immediately at this link:
http://www.rampant-books.com/download_adv_mon_tuning.htm
|
