Reports2017-11-18T12:27:27+00:00

DBA AWR System Wait Event Summary

Description
Categories: DBA, Enginatics
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.

select
x.seconds/xxen_util.zero_to_null(sum(x.seconds) over ())*100 percentage,
x.*,
xxen_util.time(x.seconds) time
from
(
select distinct
&columns2
dhse.wait_class,
&columns
round(
(sum(dhse.time_micro) keep (dense_rank last order by dhse.snap_id) over (partition by dhse.wait_class &partition_by)-
sum(dhse.time_micro) keep (dense_rank first order by dhse.snap_id) over (partition by dhse.wait_class &partition_by))
/1000000) seconds
from
dba_hist_snapshot dhs,
(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, dhse.* from dba_hist_system_event dhse) dhse
where
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhse.dbid and
dhs.instance_number=dhse.instance_number and
dhs.snap_id=dhse.snap_id
) x
where
x.seconds>0
order by
x.seconds desc,
x.wait_class

Parameter Name SQL text Validation
Date From
dhs.end_interval_time>=fnd_date.displaydt_to_date(:date_from)-1/1440
LOV
Date To
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to)
LOV
Session Type
 
LOV
Time Restriction
(to_number(to_char(dhs.begin_interval_time,'HH24')) between 0 and 6 or to_number(to_char(dhs.begin_interval_time,'HH24'))>=18)
Level of Detail
, dhse.instance_number
Level of Detail
, dhse.event_name
Level of Detail
, dhse.event_name, dhse.instance_number
Level of Detail
dhse.instance_number,
Level of Detail
dhse.instance_number,
Level of Detail
dhse.event_name,
Include Idle Events
dhse.wait_class<>'Idle'
LOV Oracle
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
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
Level of Detail
dhse.event_name,
LOV