forked from fatdba/oracle-script-lib
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathash_top_sql_w_top_obj.sql
More file actions
79 lines (70 loc) · 2.44 KB
/
ash_top_sql_w_top_obj.sql
File metadata and controls
79 lines (70 loc) · 2.44 KB
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
/*
SQL_ID PLAN_HASH ACTION CPU WAIT IO TOTAL P_IO TOP_OBJ
------------- ---------- ------ --- ---- ---- ----- ---- --------
a22dcjdxfra19 3173797817 SELECT 50 0 10 60 100
8szmwam7fysa3 2976124318 INSERT 0 0 26 26 31 ORDERS
7jycxu86n60qh 1128103955 SELECT 15 0 0 15
7wgks43wrjtrz 3327148973 SELECT 0 0 10 10 60 ORDERS
fz2g0vsbq9j8m 1334423612 SELECT 7 0 0 7
5dfmd823r8dsp 646114857 INSERT 3 0 3 6 100
*/
define v_dbid=NULL;
select &v_dbid from dual;
col f_dbid new_value v_dbid
select &database_id f_dbid from dual;
select &v_dbid from dual;
select nvl(&v_dbid,dbid) f_dbid from v$database;
select &v_dbid from dual;
col PCT_IO_OBJ for a25
col aud_action for a11
with master as (
select
sql_id,
sql_plan_hash_value,
sql_opcode,
sum(cpu) cpu,
sum(wait) wait,
sum(io) io,
sum(total) total,
decode(sum(io),0,null, decode(objn,-1,NULL,objn)) objn,
row_number() over ( partition by sql_id order by io desc ) seq,
ratio_to_report( sum(io)) over ( partition by sql_id ) pct
from (
select
ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE , sql_opcode,
current_obj# objn,
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
where SQL_ID is not NULL
and dbid=&v_dbid
group by sql_id, SQL_PLAN_HASH_VALUE , sql_opcode, current_obj#
)
group by sql_id, SQL_PLAN_HASH_VALUE , sql_opcode, objn,io
)
select * from (
select
sql_id,
sql_plan_hash_value,
aud.name aud_action,
sum(cpu) cpu,
sum(wait) wait,
sum(io) io,
sum(total) total,
round(max(decode(seq,1,pct,null)),2)*100 pct_io,
max(decode(seq,1,o.object_name,null)) pct_io_obj
--from master,audit_actions aud , dba_objects o
from master,audit_actions aud , DBA_HIST_SEG_STAT_OBJ o
where
-- objn=o.object_id(+)
objn=o.obj#(+)
and sql_opcode=aud.action
group by sql_id,sql_plan_hash_value,aud.name
order by total desc )
where rownum < 10
/
-- and ash.dbid=&DBID
-- and ash.sample_time > sysdate - &minutes /( 60*24)