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'

select
   size_for_estimate,
   buffers_for_estimate,
   estd_physical_read_factor,
   estd_physical_reads
from
   v$db_cache_advice
where
   name = 'DEFAULT'
and
   block_size = (SELECT value FROM V$PARAMETER
                   WHERE name = 'db_block_size')
and
   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.