Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  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.
-- ****************************************************************

  . . .
  name = 'DEFAULT'
  block_size = (SELECT . . . )
  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, Oracle 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:




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.