set showmode off echo off
set pagesize 24 heading on feedback 6 timing off linesize 80 termout on
rem
rem Script: cache_hit_ratio.sql
rem Purpose: Compute current Cache Hit Ratio for the database buffer cache
rem    from the ratio of physical reads (from disk) to logical reads (db
rem    block gets of your current blocks plus consistent gets of rollback
rem    segment blocks from others).  You can also figure a projected ratio
rem    by entering an adjustment for additional cache hits or additional
rem    cache misses (which must be a negative value) if you have just now
rem    gotten those figures from x$kcbrbh (after adding the parameter
rem    db_block_lru_extended_statistics to initSID.ora) or x$kcbcbh (using
rem    db_block_lru_statistics) respectively, or enter zero for the current
rem    ratio value.
rem
rem Author: Stephen Rea
rem Released: 9/30/98
rem
!echo
accept ach number prompt "Enter Adjustment For Additional Cache Hits, or -Addl Cache Misses, or 0: "
set verify off
select pr.value "1 - (Physical Reads", &ach "- Addl Cache Hits)",
    dbg.value "/ (DB Block Gets", cg.value "+ Consistent Gets) =",
    (1-(pr.value-(&ach))/(dbg.value+cg.value))*100 "Cache Hit Ratio",
    decode(sign((1-(pr.value-(&ach))/(dbg.value+cg.value))*100-80),
      -1,'ADD BUFFERS','  > 80% OK ') "  Results  "
  from v$sysstat dbg, v$sysstat cg, v$sysstat pr
  where dbg.name = 'db block gets' and cg.name = 'consistent gets'
    and pr.name = 'physical reads';
undefine ach
set timing on verify on echo on showmode both