DBA AWR System Wait Event Summary

➜ Back to Library
Description
Categories: 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
Download
 
Related Blitz Reports
DBA AWR System Wait Class by Time DBA AWR Active Session History DBA AWR Blocking Session Summary DBA SGA+PGA Memory Configuration DBA AWR System Wait Time Summary DBA AWR PGA History DBA AWR Latch Summary DBA AWR SQL Performance Summary DBA AWR System Metrics Summary
Blitz Report™

Blitz Report™ provides multiple benefits: