doc

    Name:   userhit.sql

    Author: Mark Gurry


The following script lists the User Session Hit Ratios, that is, the ratio
of blocks found in the buffer cache as a proportion of those read from 
disk for each user session. The closer to 100% the better.

#


ttitle ' User Hit Ratios'
column "Hit Ratio" format 999.99
column  "User Session" format a15;
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  
       < 60;