SQL Query to Find SQLIDs Spend on CPU/Wait/IO

This query is used to find out the SQLIDs spend on the CPU, Wait and IO. 

SQL Query

set pages 1000 lines 220
col SQL_ID for a 15
select
a.SQL_ID SQL_ID,
sum(decode(a.session_state,'ON CPU',1,0)) "CPU",
sum(decode(a.session_state,'WAITING',1,0)) -
sum(decode(a.session_state,'WAITING', decode(ev_name.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(a.session_state,'WAITING', decode(ev_name.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(a.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history a,v$event_name ev_name
where SQL_ID is not NULL and ev_name.event#=a.event#
group by a.sql_id
order by 5 desc 

advertisements

 

SAMPLE Query Output

SQL_ID               CPU       WAIT         IO      TOTAL
------------- ---------- ---------- ---------- ----------
38jdh9n0wdkbt          0       1221        364       1585
bsyp0wc6pkmbc          0        370        122        492
8pp3aabsjvhrz          0        492          0        492
7thhjnwz78474          0        458          0        458
bfs5w299hh2bz          0        431          0        431
d986y889j8cjp          0         31        355        386