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

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:

http://www.rampant-books.com/download_adv_mon_tuning.htm

 

 

  
 

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.