Friday, July 17, 2015

How to get high load sqls which are currently running

How to get high load sqls which are currently running 


SELECT a.sid,d.spid a.username, a.machine, c.consistent_gets, c.block_gets,
       c.physical_reads, round((100 * (c.consistent_gets + c.block_gets -
       c.physical_reads)) / (c.consistent_gets + c.block_gets), 2) hitratio,
       b.sql_fulltext 
    FROM v$session a, v$sqlarea b, v$sess_io c , v$process d
    WHERE a.sql_hash_value = b.hash_value
      AND a.sid = c.sid
      AND c.consistent_gets + c.block_gets > 0
      AND a.username IS NOT NULL
      AND a.status = 'ACTIVE'
      and a.paddr      = d.addr
    ORDER BY a.status ASC, c.consistent_gets DESC, c.physical_reads DESC

No comments:

Post a Comment