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

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (578) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips
 

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