DBA AWR System Wait Event Summary

Description
Categories: Diagnostic Pack, Enginatics, Kcapps
Repository: Github
Summary of wait times by wait event and event class for a specified snapshot time interval.
Use the Session Type parameter to restrict either to foreground, background or all server processes.
Run DBA AWR System Wait Event Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.seconds/xxen_util.zero_to_null(sum(x.seconds) over ())*100 percentage,
x.*
from
(
select distinct
&columns2
dhse.wait_class,
&columns
xxen_util.time(sum(case when dhse.time_delta<0 then dhse.time_micro else dhse.time_delta end) over (partition by dhse.wait_class &partition_by)/1000000) time,
sum(case when dhse.time_delta<0 then dhse.time_micro else dhse.time_delta end) over (partition by dhse.wait_class &partition_by)/1000000 seconds,
sum(case when dhse.waits_delta<0 then dhse.waits else dhse.waits_delta end) over (partition by dhse.wait_class &partition_by) waits,
sum(case when dhse.timeouts_delta<0 then dhse.timeouts else dhse.timeouts_delta end) over (partition by dhse.wait_class &partition_by) timeouts
from
(
select
dhse.time_micro-lag(dhse.time_micro) over (partition by dhse.dbid, dhse.instance_number, dhse.event_name order by dhse.snap_id) time_delta,
dhse.waits-lag(dhse.waits) over (partition by dhse.dbid, dhse.instance_number, dhse.event_name order by dhse.snap_id) waits_delta,
dhse.timeouts-lag(dhse.timeouts) over (partition by dhse.dbid, dhse.instance_number, dhse.event_name order by dhse.snap_id) timeouts_delta,
dhse.*
from
(
select
decode(:session_type,'Foreground',dhse.time_waited_micro_fg,'Background',dhse.time_waited_micro-dhse.time_waited_micro_fg,dhse.time_waited_micro) time_micro,
decode(:session_type,'Foreground',dhse.total_waits_fg,'Background',dhse.total_waits-dhse.total_waits_fg,dhse.total_waits) waits,
decode(:session_type,'Foreground',dhse.total_timeouts_fg,'Background',dhse.total_timeouts-dhse.total_timeouts_fg,dhse.total_waits) timeouts,
dhse.*
from
dba_hist_snapshot dhs,
dba_hist_system_event dhse
where
1=1 and
dhs.dbid=dhse.dbid and
dhs.instance_number=dhse.instance_number and
dhs.snap_id=dhse.snap_id
) dhse
) dhse
) x
where
x.seconds>0
order by
x.seconds desc,
x.wait_class
Parameter Name SQL text Validation
Date From
dhs.end_interval_time>=xxen_util.server_time(fnd_date.displaydt_to_date(:date_from))-1/1440
LOV
Date To
dhs.begin_interval_time<=xxen_util.server_time(fnd_date.displaydt_to_date(:date_to))
LOV
Session Type
 
LOV
Level of Detail
dhse.event_name,
LOV
Request Id (Time Restriction)
(dhs.begin_interval_time<=(select fcr.actual_completion_date from fnd_concurrent_requests fcr where fcr.request_id=:request_id) and dhs.end_interval_time>=(select fcr.actual_start_date from fnd_concurrent_requests fcr where fcr.request_id=:request_id))
Number
Time Restriction
to_number(to_char(dhs.begin_interval_time,'HH24')) between 6 and 18 and to_char(dhs.begin_interval_time,'DAY') not in ('SATURDAY','SUNDAY')
LOV
Include Idle Events
dhse.wait_class<>'Idle'
LOV Oracle
Diagnostic Pack enabled
1=1
LOV
Container Data
dhs.dbid=(select vd.dbid from v$database vd)
LOV