SQL Script TOP Physical Read Users

Updated: 10-Oct-2002
Version: 1.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 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
   ;

    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
   ;

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