Adjusting the Buffer Cache Size
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
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.
heading 'Cache Size (m)'
heading 'Estd Phys|Read Factor'
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.
Cache Size (MB) Buffers Read Factor
---------------- ------------ ----------- ------------
18.70 192,317,943 <== 10% size
10,282,475 Current Size
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
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.
Library cache misses
Data buffer cache
Hit ratio < 90%
Hit ratio > 95%
high multi-pass executions
100% optimal executions
Table 2: Threshold conditions for dynamic
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
Now, let?s conclude this chapter with a
review of the major topics and points.
If you like Oracle tuning, see the
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.