Metric Buffer Cache Hit Ratio Tips
Oracle Consulting Tips by Burleson
- Enhanced 5/21/2013
Since the days of Oracle7 when
Oracle Corporation recommended keeping the buffer cache hit ratio
above a fixed threshold, research has show some important facts.
Is the Oracle buffer hit ratio a
useless metric for monitoring and tuning and
Optimal data buffer
cache hit ratio tips.
Buffer Cache Hit Ratio Oracle metric monitors the rate at which
Oracle finds the data blocks it needs in memory over the lifetime of
Using the data buffer advisory utility.
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
utilities to include a shared pool, Java and PGA advisory.
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!