forked from fatdba/Oracle-Database-Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ash_event_trend.sql
174 lines (173 loc) · 4.55 KB
/
ash_event_trend.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
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
@plusenv
col event format a32 trunc
col sql_id format a13
col wait format 99999
col io format 99999 head 'IO'
col tot_t0 format 99999 head '-15m|*Tot*'
col tot_t3 format 99999 head '-60m|Tot'
col tot_t2 format 99999 head '-45m|Tot'
col tot_t1 format 99999 head '-30m|Tot'
col rnk format 99 head 'Rnk'
col delim format a01 head '|'
col pctio format 999 head 'IO%'
col pctwait format 999 head 'WAI%'
col pcttot format 999 head 'TOT%'
with t3 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-60/1440 and sysdate-46/1440
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=100
)
,t2 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-45/1440 and sysdate-31/1440
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=100
)
,t1 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-30/1440 and sysdate-16/1440
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=100
)
,t0 as
(
select event
,sql_id
,wait
,100*ratio_to_report (wait) over () pctwait
,io
,100*ratio_to_report (io) over () pctio
,tot
,100*ratio_to_report (tot) over () pcttot
,rownum rnk
from
(
select ash.event event
,ash.sql_id sql_id
,sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'User I/O',1,0),0)) wait
,sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) io
,sum(decode(ash.session_state,'ON CPU',1,1)) tot
from v$active_session_history ash
,v$event_name en
where event is not NULL
and sql_id is not NULL
and ash.session_state = 'WAITING'
and ash.event#=en.event# (+)
and sample_time between sysdate-15/1440 and sysdate
group by event, sql_id
order by sum(decode(ash.session_state,'WAITING',1,1)) desc
)
where rownum <=20
)
select t3.wait wait
,t3.pctwait pctwait
,t3.io io
,t3.pctio pctio
,t3.tot tot_t3
,'|' delim
,t2.wait wait
,t2.pctwait pctwait
,t2.io io
,t2.pctio pctio
,t2.tot tot_t2
,'|' delim
,t1.wait wait
,t1.pctwait pctwait
,t1.io io
,t1.pctio pctio
,t1.tot tot_t1
,'|' delim
--,t0.rnk rnk
,t0.event event
,t0.sql_id sql_id
,'|' delim
,t0.wait wait
,t0.pctwait pctwait
,t0.io io
,t0.pctio pctio
,t0.tot tot_t0
from t0
,t3
,t2
,t1
where t0.event = t3.event (+)
and t0.sql_id = t3.sql_id (+)
and t0.event = t2.event (+)
and t0.sql_id = t2.sql_id (+)
and t0.event = t1.event (+)
and t0.sql_id = t1.sql_id (+)
order by t0.rnk
;