SQL Script Session Hit Ratios

Updated: 17-Jan-1999
Version: 1.0

Description

This script lists the database hit ratio for each session

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links