rem *************************************************************

rem * Name : PERFORM.SQL

rem *

rem * Date: July 15 2001

rem * Purpose: Show active sessions with physical reads and hit-ratio

rem * Active sessions with a 

rem *

rem * Downloaded from www.oradev.com

rem *************************************************************

set lines 200



prompt Active sessions

prompt ...

column schemaname format a10 heading 'Oracle ID'

column username format a10 heading 'System ID' 

column program  format a32 heading 'Program' 

column hit_ratio format 9.90 heading'Hit Ratio' 

column physical_reads format 99999999 heading 'Reads'

column sid format 99999 



SELECT  s.schemaname

      , p.username

      , s.program

      , io.physical_reads

      , (io.block_gets+io.consistent_gets)/

        (io.block_gets+io.consistent_gets+io.physical_reads)

          hit_ratio

       ,s.sid

       ,s.serial#

FROM  V$Session s

     ,V$Process p

     ,V$Sess_io io

WHERE  s.paddr  = p.addr

AND  s.sid    = io.sid

AND  s.status = 'ACTIVE'

AND  (io.block_gets+io.consistent_gets+io.physical_reads) > 0

;