| |
 |
|
Oracle 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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
