-- The hit ratio is a Oracle database block buffer index used in 
-- tuning database buffer.  It is expressed as a percentage.
-- Hit Ratio =
--    100*(1-("physical reads"/("db block gets" + "consistent gets")))
--
-- This value should be typically close to 100%

WITH q_read AS (
 SELECT CAST(name AS VARCHAR2(20))  phy_read, value read_val
 FROM v$sysstat
 WHERE  name = 'physical reads'
),
q_dbg AS (
 SELECT CAST(name AS VARCHAR2(20))  db_block_get, value dbg_val
 FROM v$sysstat
 WHERE  name = 'db block gets'
),
q_cg AS (
 SELECT CAST(name AS VARCHAR2(20)) consistent_get, value cg_val
 FROM v$sysstat
 WHERE  name = 'consistent gets'
)
SELECT 
 phy_read, read_val,
 db_block_get, dbg_val,
 consistent_get, cg_val,
 100*(1-read_val/(dbg_val+cg_val)) hit_ratio
FROM  q_read, q_dbg, q_cg
;

Hit Ratio Query Output


-- Query to calculate statistics of the Buffer Cache Hit Ratio
-- from v$sysstat

SELECT CAST(name AS VARCHAR2(30)) name, value
FROM v$sysstat
WHERE name IN (
'db block gets from cache',
'consistent gets from cache', 
'physical reads cache'
);




-- Query to analyze/calculate the buffer cache hit ratio using
-- v$buffer_pool_statistics

SELECT
 name, 
 (1-(physical_reads/(db_block_gets + consistent_gets)))* 100 Hit_ratio 
FROM v$buffer_pool_statistics
;




-- Query to get predicted I/O for default buffer pool from
-- V$DB_CACHE_ADVICE

SELECT size_for_estimate   size_4_estmt,
       buffers_for_estimate  buffer_4_estmt,
       estd_physical_read_factor estd_phy_read_factor,
       estd_physical_reads   esdt_phy_reads
FROM V$DB_CACHE_ADVICE
WHERE block_size = (SELECT value
                    FROM V$PARAMETER 
                    WHERE name = 'db_block_size')
AND  name = 'DEFAULT'
AND  advice_status = 'ON';

v$DB_Cache_Advice Default Output

-- Query to get predicted I/O for the "KEEP" pool from
-- V$DB_CACHE_ADVICE

SELECT size_for_estimate   size_4_estmt,
       buffers_for_estimate  buffer_4_estmt,
       estd_physical_read_factor estd_phy_read_factor,
       estd_physical_reads   esdt_phy_reads
FROM V$DB_CACHE_ADVICE
WHERE block_size = (SELECT value
                    FROM V$PARAMETER 
                    WHERE name = 'db_block_size')
AND  name = 'KEEP'
AND  advice_status = 'ON';