Oracle Buffer Hit Ratio

Oracle Buffer Hit Ratio

Oracle Tips by Burleson Consulting

2007 Update: 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.

Please read: Is the Oracle buffer hit ratio a useless metric for monitoring and tuning?

Don't be fooled by the buffer hit ratio

Many Oracle professionals misunderstand the data buffer hit ratio and claim that the data buffer hit ration is a meaningless metric because:

They can re-run queries (increasing buffer gets) and adjust the metric at-will.
The Data Buffer Hit Ratio is meaningless for systems that do not frequently re-read data blocks

In reality, the Oracle data buffer hit ration is a measure of the propensity of any given data block to be in the buffer upon re-read.

Physical disk reads are 100x slower than buffer fetches, and the data buffer hit ratio is very valuable, especially when monitoring the KEEP pool with should always have a buffer hit ratio of 100% (it's up to the DBA to anticipate the growth of segments in the KEEP pool).

See:

Monitoring the data buffer efficiency is a critical Oracle tuning task, and don't be fooled by beginners who claim that the data buffer hit ratio is meaningless simply because it does not apply in all cases.

Of course, if your database does not re-read data frequently (i.e. DSS applications) then the data buffer hit ratio is not meaningful, but that does not mean that the data buffer hit ratio is not extremely valuable as a measure of the effectiveness of any of Oracle's seven data buffers.

The data buffer hit ratio is also incorporated into the Oracle 10g automatic memory manager (AMM) whereby Oracle detects a too-small data buffer and adjusts the data buffer region size.

The data buffer hit ratio is also incorporated into Oracle's standard buffer cache advisory utility (v$db_cache_advice).

See these related notes on tuning the data buffer cache: