Fact or Fiction? Oracle Buffer Hit Ratio as a valuable tuning metric.
 

Ever since the Oracle7 performance tuning class in the early 1990's recommended keeping the buffer hit ratio above 95%, (advice later adopted by SAP and other ERP vendors), there has been a huge amount of misunderstandings about the Buffer Cache Hit Ratio, and the value of ratios in-general as a tool for Oracle monitoring and tuning.

Lets start by separating the fact from the fiction.  Whether we are talking about the buffer cache hit ratio or the data buffer cache advisory (see v$db_cache_advice), we need to remember that they are just different formulas for twiddling the same metrics, namely the elapsed-time deltas for logical I/O (consistent gets) and physical I/O (disk reads).  Hence, any computations are fundamentally flawed:

If we decompose the problem, we are really taking about elapsed logical I/O vs. physical reads.  The formulas are incidental.

Most experts agree that the Buffer Cache Hit Ratios, by themselves, are of limited value, only one of hundreds of metrics that indicate database performance.  In  general, all ratios are limited because they only measure a probability, in the instance case, the overall probability that a data block will be found in the data buffer cache upon "re-read" (i.e. the BCHR has no bearing on blocks are are "brand new" to the data cache). 

Read more about the value of the Oracle Buffer hit ratio as a tuning metric here:

http://www.dba-oracle.com/t_buffer_cache_hit_ratio_value.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf