Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

 
 

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.


The above is an excerpt from the "Oracle UNIX Administration Handbook" by Oracle press, authored by Donald K. Burleson.

 

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

 

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter