Buffer Cache Hit Ratio

Buffer Cache Hit Ratio

When you query v$sysstat you will find the below as described.

Consistent gets - number of the data blocks which were accessed in the buffer cache for SQL statements that do not modify data - just SELECT statements

DB block gets - number of the data blocks which were accessed in the buffer cache for SQL statements that modify data - INSERT, UPDATE, DELETE and SELECT FOR UPDATE statements;

Physical reads - number of the data blocks that where read from the disk.

Script to find Buffer Cache Hit Ratio

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Buffer Cache Hit Ratio Is"

FROM v$sysstat cur, v$sysstat con, v$sysstat phy

WHERE cur.name = 'db block gets'

AND con.name = 'consistent gets'

AND phy.name = 'physical reads';

O/P

Buffer Cache Hit Ratio Is

-------------------------

                    99.08