All users sql with wait events (Oracle10g)

Summary
It lists the high-level statistics that one needs in order to drill down even further. It includes new 10g features, including the use of the SQL_FULLTEXT column - which now is everwhere - as well as a call to dbms_xplan.display_cursor, which returns the complete explain plan for each statement. Of course, the query below is best used from a tool such as TOAD, because the aforementioned is a lot of information.
SELECT /* SQL Monitor Query for >= 10g */ 
          '(' || s.SID || ',' || s.serial# || ')' "sid/serial"
         ,s.program "pgrm"
         ,s.terminal "term"
         ,s.username "db user"
         ,DECODE (ROUND (s.last_call_et / 60),
                  '0', '< 1',
                  ROUND (s.last_call_et / 60)
                 ) "rtime (mins)"
         ,DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers"
         ,DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves"
         ,si.block_changes "sess bchgs"
         ,si.physical_reads "sess preads"
         ,pss_pr."px preads" "child px preads"
         ,si.consistent_gets "sess cgets"
         ,pss_cg."px cgets" "child px cgets"
         ,ss_cpu."sess cpu" "sess cpu"
         ,pss_cpu."px cpu" "child px cpu"
         ,sa.optimizer_cost "curr sql cost"
         ,su.blocks "temp blocks"
         ,t.used_ublk "undo blocks"
         ,s.event "wait"
         ,s.seconds_in_wait "wait secs"
         ,s.state "wait state"
         ,s.sql_id "current sql id"
         ,sa.sql_fulltext "sql text"
         ,dbms_xplan.display_cursor(s.sql_id, s.sql_child_number) "sql xplan"
    FROM v$session s,
         v$sort_usage su,
         v$transaction t,
         v$sess_io si,
         v$sql sa,
         (SELECT   qcsid, COUNT (DISTINCT server_set) "px oper cnt",
                   COUNT (*) "px count"
              FROM v$px_session
             WHERE NOT server_set IS NULL
          GROUP BY qcsid, DEGREE) ps,
         (SELECT   qcsid, SUM (VALUE) "px preads"
              FROM v$px_sesstat
             WHERE statistic# = 54
             AND   SID != qcsid
          GROUP BY qcsid) pss_pr,
         (SELECT   qcsid, SUM (VALUE) "px cgets"
              FROM v$px_sesstat pss
             WHERE statistic# = 50
             AND   SID != qcsid
          GROUP BY qcsid) pss_cg,
         (SELECT   qcsid, SUM (VALUE) "px cpu"
              FROM v$px_sesstat pss
             WHERE statistic# = 12
             AND   SID != qcsid
          GROUP BY qcsid) pss_cpu,
         (SELECT ss.SID, SUM(ss.VALUE) "sess cpu" 
          FROM v$sesstat ss WHERE statistic#=12
          GROUP BY ss.SID) ss_cpu
   WHERE s.sql_address = sa.address
     AND s.sql_hash_value = sa.hash_value
     AND s.saddr = su.session_addr(+)
     AND s.SID = ps.qcsid(+)
     AND s.SID = si.SID(+)
     AND s.saddr = t.ses_addr(+)
     AND s.SID = pss_pr.qcsid(+)
     AND s.SID = pss_cg.qcsid(+)
     AND s.SID = pss_cpu.qcsid(+)
     AND s.SID = ss_cpu.SID(+)
     AND s.TYPE != 'BACKGROUND'
     AND s.status = 'ACTIVE'
     AND program NOT LIKE ('%(C%')                 --Eliminate Streams Capture
     AND program NOT LIKE ('%(A%')                 --Eliminate Streams Apply
     AND program NOT LIKE ('%(P%')                 --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;
Reviews
Categories
Filters
Search