set lin 450
col db_name for a20
col WAIT_CLASS for a20
col EVENT_NAME for a40
with dbtimevalue as (
select inst_id, snap_id, dbtimevalue/1000000 dbtime
from (
select instance_number inst_id, snap_id,STAT_NAME,
value, value - lag(
value) over (partition by stat_name
order by instance_number,snap_id
) dbtimevalue
from dba_hist_sys_time_model
where snap_id between &&begin_snap_id-1 and &&end_snap_id and stat_name
='DB time' order by instance_number
,snap_id )
where snap_id > &&begin_snap_id - 1 )
select sys_context('userenv','DB_NAME') ||decode(1,1,'-'||a.inst_id) db_name , a.snap_id, to_char(begin_interval_time,
'DD-MON-YYYY HH24:MI') timeo,
wait_class ,event_name , totalwaits, timewaited,av_wait_ms, round(timewaited/dt.dbtime
* 100,1) "%DB Time"
FROM (
select inst_id , snap_id, event_name, wait_class,
to_char(fg_waits_delta,'999,999,999') TotalWaits,
rank () over ( partition by inst_id,snap_id
order by fg_time_waited/1000000
desc ) as rnk, round((fg_time_waited/1000000),0)
TimeWaited, decode(waits_delta, 0, 0, round(waits_sec*1000/waits_delta,2)) av_wait_ms
FROM (
select instance_number inst_id , snap_id, s1.event_name,
s1.wait_class, total_waits - lag(total_waits) over(partition by event_name
order by instance_number,s1.snap_id)
as waits_delta, total_waits_fg - lag(total_waits_fg)
over(partition by event_name
order by instance_number,s1.snap_id)
as fg_waits_delta, TIME_WAITED_MICRO_FG
- lag( TIME_WAITED_MICRO_FG) over(partition by event_name
order by instance_number,s1.snap_id)
as fg_time_waited, (TIME_WAITED_MICRO
- lag(TIME_WAITED_MICRO) over(partition by event_name
order by instance_number,
s1.snap_id))/1000000 as waits_sec
from dba_hist_system_event s1
where s1.snap_id between &&begin_snap_id - 1 and &&end_snap_id
--and s1.INSTANCE_NUMBER = userenv('instance')
and s1.WAIT_CLASS not in ( 'Idle') )
where fg_waits_delta > 0 ) a, dbtimevalue dt,
dba_hist_snapshot dh
where a.snap_id = dt.snap_id
--and a.snap_id > &&begin_snap_id
and a.inst_id =dt.inst_id and a.inst_id
= dh.instance_number and a.snap_id
= dh.snap_id and a.rnk <
10 order by a.inst_id,
a.snap_id,timewaited desc