/
sr.sql
165 lines (151 loc) · 4.92 KB
/
sr.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
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Notes: This software is provided AS IS and doesn't guarantee anything
-- Proofread before you execute it!
--
--------------------------------------------------------------------------------
set feedback off null "[NULL]"
define grouping=&1
define start_snap=&2
define end_snap=&3
col wait_ms for 9999999999
col avg_wait_ms for 99999999.9
col ms_per_sec for 999999.9
col program for a30 truncate
col username for a15 truncate
col osuser for a20 truncate
col name for a40 truncate
col machine for a20 truncate
col "%Total" for a12
col "log10(D)" for a12
col grouping_break noprint new_value grouping_break
select
&2 snapid_begin,
&3 snapid_end,
' ' " ",
to_char(a.snaptime, 'YYYYMMDD HH24:MI:SS') snapshot_begin,
to_char(b.snaptime, 'YYYYMMDD HH24:MI:SS') snapshot_end,
(b.snaptime - a.snaptime)*86400 dur_sec,
(b.snaptime - a.snaptime)*86400/60 dur_min,
replace('&grouping', ',', ' on ') grouping_break
from
(select snaptime from sawr$snapshots where snapid = &2) a,
(select snaptime from sawr$snapshots where snapid = &3) b
/
break on &grouping_break skip 1
col grouping_break clear
select
&grouping,
substr(name,1,45) name,
decode(lower('&_tptmode'),'html','','|')||
rpad(
nvl(
lpad('#',
ceil( (nvl(round(sum(us_per_sec/1000000),2),0))*10 ),
'#'),
' '),
10,' ')
||decode(lower('&_tptmode'),'html','','|') "%Total",
sum(us_per_sec)/1000 ms_per_sec,
(sum(wait_us)/decode(sum(waits),0,1,sum(waits))/1000) avg_wait_ms,
sum(waits) waits,
sum(wait_us)/1000 wait_ms
-- ,avg(intrvl)/1000 sec_in_snap
from (
select
e2.sid,
e2.audsid,
nvl(e1.snapid, &start_snap) begin_snapid,
e2.snapid end_snapid,
e2.username,
e2.program,
e2.terminal,
e2.machine,
e2.osuser,
e2.process,
e2.name,
round(e2.time_waited_micro - nvl(e1.time_waited_micro,0)) wait_us,
round(
( e2.time_waited_micro - nvl(e1.time_waited_micro,0) ) / (
decode((e2.snaptime - nvl(e1.snaptime,(select snaptime from sawr$snapshots where snapid = &start_snap)))*86400, 0, 1,
(e2.snaptime - nvl(e1.snaptime,(select snaptime from sawr$snapshots where snapid = &start_snap)))*86400)
)
) us_per_sec,
(e2.snaptime - nvl(e1.snaptime,(select snaptime from sawr$snapshots where snapid = &start_snap)))*86400*1000 intrvl,
e2.total_waits - nvl(e1.total_waits,0) waits
-- e1.average_wait avg1,
-- e2.average_wait avg2,
-- e2.average_wait - nvl(e1.average_wait,0) avgdelta
from
( select * from sawr$sess_event where snapid = &start_snap ) e1,
( select * from sawr$sess_event where snapid = &end_snap ) e2
where
e1.audsid (+) = e2.audsid
and e1.sid (+) = e2.sid
and e1.serial# (+) = e2.serial#
and e1.logon_time (+) = e2.logon_time
and e1.event# (+) = e2.event#
) sq
where
( waits != 0 or wait_us != 0 )
group by
&grouping, name
order by
&grouping, ms_per_sec desc
/
col delta head Delta for 9999999999
col delta_sec head D/sec for 9999999.9
select
&grouping,
substr(name,1,45) name,
decode(lower('&_tptmode'),'html','','|')||
rpad(
nvl(
lpad('#',
ceil( nvl(log(10,abs(decode(sum(delta),0,1,sum(delta)))),0) ),
'#'),
' '),
10,' ')
||decode(lower('&_tptmode'),'html','','|') "log10(D)",
sum(delta) delta,
sum(delta)/(avg(intrvl)/1000) delta_sec
-- ,avg(intrvl)/1000 sec_in_snap
from (
select
s2.sid,
s2.audsid,
nvl(s1.snapid, &start_snap) begin_snapid,
s2.snapid end_snapid,
s2.username,
s2.program,
s2.terminal,
s2.machine,
s2.osuser,
s2.process,
s2.name,
s2.value - nvl(s1.value,0) delta,
(s2.snaptime - nvl(s1.snaptime,(select snaptime from sawr$snapshots where snapid = &start_snap)))*86400*1000 intrvl
from
( select * from sawr$sess_stat where snapid = &start_snap ) s1,
( select * from sawr$sess_stat where snapid = &end_snap ) s2
where
s1.audsid (+) = s2.audsid
and s1.sid (+) = s2.sid
and s1.serial# (+) = s2.serial#
and s1.logon_time (+) = s2.logon_time
and s1.statistic# (+) = s2.statistic#
) sq
where
delta != 0
group by
&grouping, name
order by
&grouping, abs(delta) desc
/
break on _nonexistent
set feedback on null ""