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

Oracle Data Buffer Metrics

The data buffer hit ratio (DBHR) measures the propensity for a data block to be cached in the buffer pool.  The goal of the DBA must be to keep as many of the frequently used Oracle blocks in buffer memory as possible.  However, this goal is clouded by the use of Solid-state disk (SSD) and the on-board RAM caches of the newer disk arrays.  Oracle may appear to be performing a disk I/O, when in reality; the data block is already in RAM on the disk subsystem cache.

 

Hence, the DBHR has become a largely meaningless number except in cases of predicting changes in system processing patterns and the initial sizing of the db_cache_size.

 

As the hit ratio approaches 100 percent, more data blocks are found in memory which normally results in fewer I/O’s and faster overall database performance. 

 

On the other hand, if the DBHR falls below 50 percent, fewer data blocks are resident in memory which requires Oracle to perform additional, often expensive disk, I/O to move the data blocks into the data buffer.  The formula for calculating the DBHR in Oracle8 is:

 

1 - (Physical Reads - Physical Reads Direct)

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

          (session logical reads)

 

The formula for calculating the hit ratio in Oracle7 and Oracle8 does not include direct block reads.  Direct block reads became a separate statistic in Oracle8i.

 

The hit ratio for Oracle8i can be gathered from the v$ views, as shown below.  However, this particular value is not very useful because it shows the total buffer hit ratio since the beginning of the instance.

 

select

   1 - ((a.value - (b.value))/d.value) "Cache Hit Ratio"

from

   v$sysstat a,

   v$sysstat b,

   v$sysstat d

where

   a.name='physical reads'

and

   b.name='physical reads direct'

and

   d.name='session logical reads';

 

Many novice DBAs make the mistake of using the DBHR from the v$ views.  The v$buffer_pool_statistics view does contain the accumulated values for data buffer pool usage, but computing the data buffer hit ratio from the v$ tables only provides the average since the database was started.

 

The next section explains how the AWR can provide a wealth of information for tracking buffer pool utilization and computing the data buffer hit ratio.

 

 

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.