db_cache hit rate

Posted on 2009-06-24 filed under: Oracle (RSS) 22 views   

Data Buffer in various regions of the query hit rate
SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO"
FROM sys.v $ buffer_pool_statistics
WHERE db_block_gets + consistent_gets> 0;

Example 1:
SQL> SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO"
2 FROM sys.v $ buffer_pool_statistics
3 WHERE db_block_gets + consistent_gets> 0;

NAME HIT_RATIO
-------------------- ----------
DEFAULT .996046807

Example 2:
SQL> SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO"
2 FROM sys.v $ buffer_pool_statistics
3 WHERE db_block_gets + consistent_gets> 0;

NAME HIT_RATIO
------------------ ----------
KEEP .983520845
RECYCLE .503866235
DEFAULT .790350047

Tags: statistics, sql, select name, buffer pool, data buffer

Permalink: http://www.kods.net/db-cache-hit-rate/