SQL Script Session Hit Ratios

Updated : 17-January-1999
Version : 1.0

Description

This script lists the database hit ratio for each session.

Parameters

None

SQL Source

col sid form 999 heading "Sid"
col username form a10 heading "Username"
col HitRatio form 999 heading Hit%
col consistent_gets form 999999999 heading "Cons Gets"
col physical_reads form 999999999 heading "Phys Reads"
col consistent_changes form  999999999 heading "Cons Changes"

spool sesshits.lst

select 	a.sid, a.username
              ,ROUND( 
                 (b.consistent_gets+b.block_gets-b.physical_reads)
                       /(b.consistent_gets+b.block_gets) * 100,2) HitRatio
              , b.block_gets, b.consistent_gets, 
		b.physical_reads, b.block_changes, 	
		b.consistent_changes
from 	v$session a, v$sess_io b
where	a.sid = b.sid
and (b.consistent_gets+b.block_gets) > 0
and Username is NOT NULL
order by a.sid, a.username
/
spool off                                                                                                               

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal