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


Donald K. Burleson

Oracle Tips

Time Series Monitoring of the Data Buffers

Before one can self-tune the data buffers, there needs to be a mechanism for monitoring the data buffer hit ratio (BHR) for all pools that have been defined.  All seven data buffers can be monitored with this script, but remember, unless objects are segregated into separate buffers, aggregate BHR values are largely meaningless.





  (1-(physical_reads/ decode(db_block_gets+consistent_gets, 0, .001, db_block_gets+consistent_gets)))*100   cache_hit_ratio




The following is a sample output from this script.  The names of the sized block buffers remain DEFAULT, and the block_size column must be selected to differentiate between the buffers.  The sample output shows all 7 data buffers.



-----------    ---------- ---------------

DEFAULT         32,767             .97

RECYCLE         16,384             .61

KEEP            16,384            1.00

DEFAULT         16,384             .92

DEFAULT          4,096             .99   

DEFAULT          8,192             .98

DEFAULT          2,048             .86   


Of course, this report is not extremely useful because the v$sysstat view only shows averages since the instance was started. To perform self-tuning of the data buffers, Oracle’s AWR views can be used to measure the data buffer hit ratios every hour.


To do this, an AWR data buffer exception report table can be used.  Figure 13.11 shows the output from a time based data buffer hit ratio report.


Figure 13.11: Time-based proactive problem detection


In Figure 13.11, it appears that the database regularly experiences a decline in the data buffer hit ratio between 9:00 and 11:00 AM.  Once it has been confirmed that this is a signature and repeats on a regular basis, action can be taken to correct the deficiency as follows:

§       Review and tune all SQL between 9:00-11:00 AM, using the SQL source captured in the stats$sql_summary table.

§       Schedule a job (dbms_job or dbms_scheduler) to increase the db_cache_size during this period.

The following section provides a look at time series disk monitoring and analysis using the powerful AWR tables.




This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




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.