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



The Eucharistic Miracles of the World
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
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


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer