ttitle ' User Hit Ratios'
set echo off
set feedback off
set verify off
column nl newline;
column "Hit Ratio" format 999.99
column  "User Session" format a15;
set pagesize 999

Doc

      Name:     userhit.sql

      Author:   Mark Gurry


The following list of users has a hit ratio less than 80%. Investigate what they are doing to determine if it could be done more efficiently. 

#
spool userhit.lis 

select  se.username||'('|| se.sid||')' "User Session",
       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$sesstat ss, v$statname sn, v$session se
where   ss.sid    = se.sid
  and   sn.statistic# = ss.statistic#
  and   value != 0
  and   sn.name in ('db block gets', 'consistent gets', 'physical reads')
group by se.username, se.sid
having 
       (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 
  <   80
;
spool off