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

 

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.

 

select

   name,

   block_size,

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

from

   v$buffer_pool_statistics;

 

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.

 

NAME           BLOCK_SIZE CACHE_HIT_RATIO

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

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.

 

 

SEE CODE DEPOT FOR FULL SCRIPTS


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:

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

 


”call

  
 

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.