SQL Script TOP Physical Read Users

Updated: 10-Oct-2002
Version: 2.0

Description

Script displays details so the session which have issued the most physical read calls.

Parameters

None

SQL Source

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

col program form a30 heading "Program"
col Phyread form 999,999,999 heading "Phy Reads"
col HitRatio format 999.99 heading 'Hit Ratio'


spool topcpu.lst

select rownum as rank, a.*
from (
    SELECT v.sid, program, v.value  Phyread
    FROM v$statname s , v$sesstat v, v$session sess
   WHERE s.name = 'physical reads'
     and sess.sid = v.sid
     and v.statistic#=s.statistic#
     and v.value>0
   ORDER BY v.value DESC) a
where rownum < 11
   ;

select rownum as rank, a.*
from (
    SELECT sid, block_gets, consistent_gets, physical_reads
           ,100*(consistent_gets+block_gets-physical_reads)/ (consistent_gets+block_gets) HitRatio
    FROM v$sess_io
   WHERE physical_reads >0
   AND (consistent_gets+block_gets) > 0
   ORDER BY physical_reads DESC) a
where rownum < 11
   ;

spool off

Related Links

Oracle 8: (SQL Script) TOP Physical Read Users

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