Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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 
   size_for_estimate          c1,
   buffers_for_estimate       c2,
   estd_physical_read_factor  c3,
   estd_physical_reads        c4
   name = 'DEFAULT'
   block_size  = (SELECT value FROM V$PARAMETER
                   WHERE name = 'db_block_size')
   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,
                                                               :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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.