163 lines
4.8 KiB
SQL
163 lines
4.8 KiB
SQL
--------------------------------------------------------------------------------
|
|
--
|
|
-- 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 ""
|