Oracle Data Buffer Hit Ratio Oracle Tips by Burleson Consulting

The Data Buffer Hit Ratio Oracle metric is a measure of the effectiveness of the Oracle data block buffer. The higher the buffer hit ratio, the more frequently Oracle found a data block in memory and avoid a disk I/O. 

Back in the 1990s when RAM was very expensive, Oracle University recommended keeping your data buffer hit ration above 90%, but this advice is too global. 

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.

The data buffer hit ratio is only meaningful under these circumstances:

• Your application frequently re-reads the same data blocks ( a common OLTP database)

and

• You data buffer is too small to cache the working set of frequently-referenced data blocks.

If you have an application that does lots of full-table scans, or an application that seldom re-reads the same data blocks (a typical data warehouse environment), the data buffer hit ratio may not be meaningful.

To see if your db_cache_size is too small, you can run a STATSPACK or AWR report and look at the data buffer cache advisory which predicts the marginal benefits from adding additional data buffer size.

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.

This, and many other Oracle performance metrics are discussed in my book "Oracle Tuning" by Rampant TechPress. 

With a super-tiny data buffer, increasing the db_cache_size will result in a large decrease in disk I/O.  As you approach full caching, the marginal benefit declines greatly, Oracle 10g AMM predicts the optimal point for you, or you see the v$db_cache_advice utility output in any STATSPACK or AWR report.

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_1002_oracle_tuning_definitive_reference_2nd_ed.htm