 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
 |
Donald K. Burleson Oracle Tips: Using v$db_cache_advice |
The new v$db_cache_advice view
Starting in Oracle9i we have a new view that can predict the benefit of additional data buffers in the data buffer cache. This view shows the estimated miss rates for twenty potential buffer cache sizes, ranging from 10% of the current size to 200% of the current size.
2008 Update: For my complete usage notes for the data buffer advisor, see:
Optimizing the data buffers is especially important in Oracle9i and beyond, where you have up to five separate data buffer caches to maintain. These include:
-
DEFAULT pool
-
KEEP Pool
-
RECYCLE Pool
-
2K data buffer cache
-
4K data buffer cache
-
8K data buffer cache
-
16K data buffer cache
-
32K data buffer cache
Warning - When the DBA sets
dba_cache_advice=on, Oracle will steal RAM pages from the shared pool, often
with disastrous result to the library cache. For example, if the
existing setting for db_cache_size is 500m, Oracle will steal a significant
amount of RAM from the shared pool. See
how
to predict I/O reduction from a larger data cache
for details.
To avoid this problem, the
DBA should set db_cache_advice=ready in the init.ora file. When this
is done, Oracle will pre-allocate the RAM memory at database startup time.
To show how cache advice works, we start with a STATSPACK report to identify those times when the data buffer hit ratio falls below 90%.
***********************************************************
When the data buffer hit ratio falls below 90%, you
should consider adding to the db_cache_size parameter
***********************************************************
yr. mo dy Hr. Name bhr
------------- -------- -----
2001-01-27 09 DEFAULT 45
2001-01-28 09 RECYCLE 41
2001-01-29 10 DEFAULT 36
2001-01-30 09 DEFAULT 28
2001-02-02 10 DEFAULT 83
In the case of the above output, we could increase the db_cache_size each day between 8:00 AM and 10:00 AM, stealing RAM memory from pga_aggregate_target.
Below we see the output from the db_cache_advice utility. This chart provides complete information about the estimated reduction in disk I/O when adding new data buffers to the cache.
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
If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
|
