/
ash_sql_top.sql
41 lines (36 loc) · 1.8 KB
/
ash_sql_top.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/*
TOP SQL from dba_hist_active_sess_history no v$active_session_history
filter by DBID
output looks like
SQL_ID PLAN_HASH TYPE CPU WAIT IO TOTAL
------------- ---------- ---------------- --------- ---------- --------- ----------
fgzp9yqqjcjvm 707845071 UPDATE 25.0 95 4081.0 4201
8u8y8mc1qxd98 131695425 SELECT 18.0 57 3754.0 3829
cfk8gy594h42s 3743737989 SELECT 2021.0 17 82.0 2120
cnx6ht8bdmf4c 0 PL/SQL EXECUTE 546.0 367 868.0 1781
gyj8wh7vx960y 1736948211 SELECT 197.0 11 1227.0 1435
1wmz1trqkzhzq 1384060092 SELECT 639.0 20 679.0 1338
5vjzz8f5ydqm7 1375932572 SELECT 538.0 0 541.0 1079
8w08jp8urfj6t 3134135242 SELECT 118.0 10 945.0 1073
*/
col type for a10
col "CPU" for 999999.9
col "IO" for 999999.9
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 dba_hist_active_sess_history ash,
audit_actions aud
where SQL_ID is not NULL
and ash.dbid=&DBID
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
/