-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathawr_r2_5_createxaxis-systemevent.sql
142 lines (138 loc) · 8.83 KB
/
awr_r2_5_createxaxis-systemevent.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
-- create the x axis - wait events (independent value)
-- drop table r2_x_value purge;
set echo off verify off
COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;
COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;
create table r2_x_value as
select
snap_id,
tm,
inst,
dur,
event stat_name,
waits diff
from
(select snap_id, tm, inst, dur, event, waits, time, avgwt, pctdbt, aas, wait_class,
DENSE_RANK() OVER (
PARTITION BY snap_id ORDER BY time DESC) event_rank
from
(
select * from
(select * from (select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
e.event_name event,
e.total_waits - nvl(b.total_waits,0) waits,
round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2) time, -- THIS IS EVENT (sec)
round (decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0))), 2) avgwt,
((round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2)) / ((s5t1.value - s5t0.value) / 1000000))*100 as pctdbt, -- THIS IS EVENT (sec) / DB TIME (sec)
(round ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS EVENT (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
e.wait_class wait_class
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
where
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
and b.dbid(+) = s0.dbid
and e.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
and b.instance_number(+) = s0.instance_number
and e.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND b.snap_id(+) = s0.snap_id
and e.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
and s0.startup_time = s1.startup_time
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
and b.event_id = e.event_id
and e.wait_class != 'Idle'
and e.total_waits > nvl(b.total_waits,0)
and e.event_name not in ('smon timer',
'pmon timer',
'dispatcher timer',
'dispatcher listen timer',
'rdbms ipc message')
order by snap_id, time desc, waits desc, event)
union all
select
s0.snap_id snap_id,
s0.END_INTERVAL_TIME tm,
s0.instance_number inst,
round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) dur,
'CPU time',
0,
round ((s6t1.value - s6t0.value) / 1000000, 2) as time, -- THIS IS DB CPU (sec)
0,
((round ((s6t1.value - s6t0.value) / 1000000, 2)) / ((s5t1.value - s5t0.value) / 1000000))*100 as pctdbt, -- THIS IS DB CPU (sec) / DB TIME (sec)..TO GET % OF DB CPU ON DB TIME FOR TOP 5 TIMED EVENTS SECTION
(round ((s6t1.value - s6t0.value) / 1000000, 2))/60 / round(EXTRACT(DAY FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 1440
+ EXTRACT(HOUR FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) * 60
+ EXTRACT(MINUTE FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME)
+ EXTRACT(SECOND FROM s1.END_INTERVAL_TIME - s0.END_INTERVAL_TIME) / 60, 2) aas, -- THIS IS DB CPU (min) / SnapDur (min) TO GET THE % DB CPU ON AAS
'CPU'
from
dba_hist_snapshot s0,
dba_hist_snapshot s1,
dba_hist_sys_time_model s6t0,
dba_hist_sys_time_model s6t1,
dba_hist_sys_time_model s5t0,
dba_hist_sys_time_model s5t1
WHERE
s0.dbid = &_dbid -- CHANGE THE DBID HERE!
AND s1.dbid = s0.dbid
AND s6t0.dbid = s0.dbid
AND s6t1.dbid = s0.dbid
AND s5t0.dbid = s0.dbid
AND s5t1.dbid = s0.dbid
AND s0.instance_number = &_instancenumber -- CHANGE THE INSTANCE_NUMBER HERE!
AND s1.instance_number = s0.instance_number
AND s6t0.instance_number = s0.instance_number
AND s6t1.instance_number = s0.instance_number
AND s5t0.instance_number = s0.instance_number
AND s5t1.instance_number = s0.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s6t0.snap_id = s0.snap_id
AND s6t1.snap_id = s0.snap_id + 1
AND s5t0.snap_id = s0.snap_id
AND s5t1.snap_id = s0.snap_id + 1
and s0.startup_time = s1.startup_time
AND s6t0.stat_name = 'DB CPU'
AND s6t1.stat_name = s6t0.stat_name
AND s5t0.stat_name = 'DB time'
AND s5t1.stat_name = s5t0.stat_name
)
)
)
where
-- event_rank <= 5 and
to_char(tm,'d') >= &&DayOfWeek1 -- day of week: 1=sunday 7=saturday
and to_char(tm,'d') <= &&DayOfWeek2
and to_char(tm,'hh24mi') >= &&Hour1 -- hour
and to_char(tm,'hh24mi') <= &&Hour2
and tm >= to_date('&&DataRange1','yyyy-mon-dd hh24:mi:ss') -- data range
and tm <= to_date('&&DataRange2','yyyy-mon-dd hh24:mi:ss')
;