Oracle Tuning And Performance Analysis

Computing the Hit Ratio

Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html

SELECT 
   SUM(DECODE(name, 'consistent gets',value, 0))  "Consis Gets",
   SUM(DECODE(name, 'db block gets',value, 0))    "DB Blk Gets",
   SUM(DECODE(name, 'physical reads',value, 0))   "Phys Reads",
  (SUM(DECODE(name, 'consistent gets',value, 0))  
    + SUM(DECODE(name, 'db block gets',value, 0))  
    -  SUM(DECODE(name, 'physical reads',value, 0)))
    / (SUM(DECODE(name, 'consistent gets',value, 0)) 
    + SUM(DECODE(name, 'db block gets',value, 0))  )  * 100 "Hit Ratio" 
FROM v$sysstat;

RAC External Results:

Consis Gets     DB Blk Gets     Phys Reads     Hit Ratio                                 
--------------  --------------  -------------  ----------------------------------------- 
547094930       10815948        316183         99.94332732834795165976312080439485533745

CAJE-Prod External Results:

Consis Gets     DB Blk Gets     Phys Reads     Hit Ratio                                 
--------------  --------------  -------------  ----------------------------------------- 
1222124214      26405978        540427901      56.71487125719423531569671484564307596656

Oracle Tuning And Performance Analysis

Computing the Hit Ratio

Views to access AWR data

Oracle Performance Tuning, 2nd Edition

Oracle Monitoring and Performance Tuning FAQ