DBA Notes: 2011/10/12

Cheng Li

Buffer Cache Hit Ratio (BHR)

From AWR reports as following, BHR is 99.17

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:   87.85

            Buffer  Hit   %:   99.17    In-memory Sort %:  100.00

            Library Hit   %:   99.87        Soft Parse %:   99.77

         Execute to Parse %:   14.05         Latch Hit %:   99.76

Parse CPU to Parse Elapsd %:    0.00     % Non-Parse CPU:   98.70

Formula commonly defined to calculate BHR:

(db block gets+ consistent gets- physical reads)/ db block gets+ consistent gets

AWR Reports values:

db block gets                           103,294,208          503.3          71.4

consistent gets                       1,632,777,357        7,955.9       1,128.9

physical reads                          800,285,419        3,899.5         553.3

physical read total multi block           6,715,146           32.7           4.6

The Buffer Cache Hit Ratio Oracle metric monitors the rate at which Oracle finds the data blocks it needs in memory over the lifetime of an instance.

Also see: Using the data buffer advisory utility.

The buffer hit ratio (BHR) indicates the current ratio of buffer cache hits to total requests, essentially the probability that a data block will be in-memory on a subsequent block re-read. A correctly tuned buffer cache can significantly improve overall database

Conclusion: BHR is calculated by data blocks, and it is valued for re-read.

Reference:

http://www.dbspecialists.com/files/presentations/buffercache.html

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

select name, value From v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');