Oracle: topsql.sql


/*

SQL_ID         PLAN_HASH TYPE         CPU  WAIT    IO  TOTAL
------------- ---------- ---------- ----- ----- ----- ------
645841ajtvrfh          0 INSERT        36    95     0    131
fubg117bs3jv9 2670606341 SELECT         8     0     1      9
3j8q1ba478r2w          0 PL/SQL EXE     3     5     0      8
                         CUTE

*/

col type for a10
select * from (
select
     ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" ,
     sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
     audit_actions aud
where SQL_ID is not NULL
   and ash.sql_opcode=aud.action
   and ash.sample_time > sysdate - &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE   , aud.name 
order by sum(decode(session_state,'ON CPU',1,1))   desc
) where  rownum < 10
/

Comments