SQL Query to Find I/O Intensive SQL and SQLText in Last One Hour

advertisements

_____________________________________________________________________________________________________________________

This can be used for finding out most CPU intensive query in last one hour. There are 3 data dictionary views involved in this query. gv$active_session_history gv$event_name v$sqlarea 

SQL Query:-

set pages 1000 lines 220
col SQLID for a15
col SQLTEXT for a60
SELECT hist.sql_id SQLID, COUNT(*), sa.sql_text SQLTeXT
FROM gv$active_session_history hist, gv$event_name ev_name, gv$sqlarea sa
WHERE hist.sample_time > SYSDATE - 1/24
AND hist.session_state = 'WAITING'
AND hist.event_id = ev_name.event_id
AND ev_name.wait_class = 'User I/O'
AND sa.sql_id=hist.sql_id
GROUP BY sa.sql_text, hist.sql_id
ORDER BY COUNT(*) DESC; 
 
SQLID               COUNT(*) SQLTEXT
--------------- ---------- ------------------------------------------------------------
552b4naybd3s1             1 SELECT sql_id, COUNT(*) FROM gv$active_session_history ash,
                              gv$event_name evt WHERE ash.sample_time > SYSDATE - 1/24 AND
                               ash.session_state = 'WAITING' AND ash.event_id = evt.event_
                              id AND evt.wait_class = 'User I/O' GROUP BY sql_id ORDER BY
                              COUNT(*) DESC

_____________________________________________________________________________________________________________________

Website Stats