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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Rules for Adjusting the Oracle Data Buffer Size

The following STATSPACK report alerts the DBA when the data buffer hit ratio falls below the preset threshold. It is very useful for pinpointing those times when decision support-type queries are being run, since a large number of large-table, full-table scans may 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.

Remember, 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, since it seldom re-reads data blocks. 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).

See code depot for full scripts

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, you will note those times when you might want to dynamically increase the value of the db_cache_size parameter. In the case of the preceding output, you could increase the db_cache_size each day between 8:00 A.M. and 10:00 A.M., stealing RAM memory from pga_aggregate_target.

The single most important new feature of Oracle is the ability to dynamically modify almost all of the Oracle parameters. This gives the Oracle professional the ability to dynamically reconfigure the Oracle instance while it is running, whether in reaction to a current performance problem or in anticipation of an impending performance problem.

Because everything within the SGA can now be modified dynamically, it is critical to the Oracle professional to understand how to monitor the Oracle database to learn to recognize trends and patterns with the system and proactively reconfigure the database in anticipation of regularly scheduled resource needs.


The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.

Complete Oracle Script Collection Available

Burleson Consulting shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, BC experts share their secrets for navigating the Oracle data dictionary.

Packed with 681 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

BC has priced this collection of 681 scripts at $79.95.  You can download them immediately at this link:




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.