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 









Oracle UNIX Adjusting the Buffer Cache Size Administration

Oracle UNIX/Linux Tips by Burleson Consulting

Adjusting the data buffer cache size

The following STATSPACK report alerts the DBA to those times when the data buffer hit ratio falls below the preset threshold. It is very useful for locating times when decision support type queries are being run, since a large number of large-table full table scans will make the data buffer hit ratio drop. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools because the KEEP pool should always have enough data blocks to cache all table rows, while the RECYCLE pool should get a very low buffer hit ratio. If the data buffer hit ratio is less than 90 percent, you may want to increase db_cache_size (db_block_buffers in Oracle8i and earlier).

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
2001-02-02 09 RECYCLE    81
2001-02-03 10 DEFAULT    69
2001-02-03 09 DEFAULT    69

Here we will note those times when we might want to dynamically increase the value of the db_cache_size parameter. 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.

Using the Oracle9i 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.

This new feature is very similar to the Oracle7 utility to predict the benefit from adding additional data buffers.  This utility used a view called x$kcbrbh to track buffer hits and the x$kcbcbh to track buffer misses.

Just like the Oracle7 model, you must pre-allocate the RAM memory for the data buffers in order to use this utility.  The cache advice feature is enabled by setting the init.ora parameter db_cache_advice to the values of ?on? or ?ready?. These values can be set dynamically with the alter system command, so the DBA can turn-on the predictive model while the database is running.

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

Once the db_cache_advice is enabled and the database has run for a representative time period, the following query can be run to perform the prediction.

column size_for_estimate
   format 999,999,999,999
   heading 'Cache Size (m)'
column buffers_for_estimate 
   format 999,999,999
   heading 'Buffers'
column estd_physical_read_factor
   format 999.90
   heading 'Estd Phys|Read Factor'
column estd_physical_reads      
   format 999,999,999
   heading 'Estd Phys| Reads'

   name = 'DEFAULT'
   block_size  = (SELECT value FROM V$PARAMETER
                   WHERE name = 'db_block_size')
   advice_status = 'ON';

Here is the output from this script.  Note that the range of values is from 10% of the current size up to double the current size.

                                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

Here, you can see no peak in total disk I/O and no marginal trends with the addition of more RAM buffers. This is very typical of data warehouse databases that read large tables with full-table scans. Consequently, there is no specific ?optimal? setting for the db_cache_size parameter.  In other words, Oracle has an insatiable appetite for data buffer RAM, and the more you give to db_cache_size, the less disk I/O will occur.

As a general rule, all available memory on the host should be tuned, and Oracle should be given to db_cache_size up to a point of diminishing returns (figure 7-6). There is a point where the addition of buffer blocks will not significantly improve the buffer hit ratio, and these tools give the Oracle DBA the ability to find the optimal amount of buffers.

Figure 6: Determining the optimal db_cache_size

The general rule for adding blocks to db_cache_size is simple: As long as marginal gains can be achieved from adding buffers and you have the memory to spare, you should increase the value of db_cache_size. Increases in buffer blocks increase the amount of required RAM memory for the database, and it is not always possible to ?hog? all of the memory on a processor for the database management system. Therefore, a DBA should carefully review the amount of available memory and determine the optimal amount of buffer blocks.

Tip: Since you must pre-allocate the additional RAM data buffers for the db_cache_size to use db_cache_advice, you may only want to use this utility one to determine an optimal size.  Remember, you can also use the data buffer cache hit ratio to gather similar data.

For more sophisticated Oracle9i databases, you can control not only the number of buffer blocks but also the block size for each data buffer. For example, you might want to make some of the buffer blocks very large so that you can minimize I/O contention. Remember, the cost for an I/O for a 32K block is not significantly more expensive than an I/O for 4K block. A database designer might choose to make specific data buffers large to minimize I/O if the application ?clusters? records on a database block, while keeping other data blocks small.  For more details on using multiple block sizes to reduce disk I/O, see Chapter 8.

When to trigger a dynamic reconfiguration

When your scripts detect a condition where a RAM memory region is overstressed, you are faced with a choice about which region will shrink to provide the RAM for the over-stressed area.  Table 7-2 below provides the threshold condition for triggering a dynamic memory change.

RAM Area

Over-stressed Condition

Over-allocated Condition

Shared pool

Library cache misses

No misses

Data buffer cache

Hit ratio < 90%

Hit ratio > 95%

PGA aggregate

high multi-pass executions

100% optimal executions

Table 2: Threshold conditions for dynamic RAM re-allocation

In practice, the choice of which area to reduce is size is a choice between the shared pool and the PGA aggregate memory (Figure 7-7).  This is because the shared pool is almost always a small region when compared to the regions for the data buffers and PGA session memory.

Figure 7: A typical RAM configuration for an Oracle database

Now, let?s conclude this chapter with a review of the major topics and points.


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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational