Oracle Metric Buffer Cache Hit Ratio Oracle Tips by Burleson Consulting - Enhanced 8/29/2005

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 performance.

By itself, the buffer cache hit ratio is not very meaningful, and the data buffer cache hit ratio is largely meaningless for decision support and data warehouse applications because of their propensity to have full-table scans and parallel full-table scans (which may bypass the data buffers entirely, using PGA memory).

The buffer cache hit ratio is most meaningful for databases with an undersized db_cache_size, where the "working set" of frequently-referenced data has not been cached.  Oracle provides the data buffer cache advisory utility (v$db_cache_advice) in the standard AWR report. (and later releases of STATSPACK reports).  Oracle has expanded the advisory utilities to include a shared pool, Java and PGA advisory.

This, and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress.  You can buy it directly from the publisher and save 30% at this link:

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