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

DBA AWR System Wait Class Summary

Description
Categories: DBA, Enginatics
Non idle session wait times by wait class over time.
Each row shows the system-wide wait time per wait class of one AWR snapshot interval to quickly identify unusual wait events that occured at specific times.
Use the Session Type parameter to restrict either to foreground, background or all server processes.

select
x.day_of_week,
x.end_interval_time,
x.instance_number,
xxen_util.time(x.user_io) user_io_,
xxen_util.time(x.application) application_,
xxen_util.time(x.network) network_,
xxen_util.time(x.concurrency) concurrency_,
xxen_util.time(x.commit) commit_,
xxen_util.time(x.other) other_,
xxen_util.time(x.configuration) configuration_,
xxen_util.time(x.scheduler) scheduler_,
xxen_util.time(x.system_io) system_io_,
xxen_util.time(x.administrative) administrative_,
x.user_io,
x.application,
x.network,
x.concurrency,
x.commit,
x.other,
x.configuration,
x.scheduler,
x.system_io,
x.administrative
from
(
select
to_char(xxen_util.client_time(dhs.end_interval_time),'Day') day_of_week,
xxen_util.client_time(dhs.end_interval_time) end_interval_time,
dhs.instance_number,
dhse.wait_class,
(dhse.time_micro-lag(dhse.time_micro) over (partition by dhse.instance_number, dhse.wait_class, dhse.event_name order by dhse.snap_id))/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
dhse.wait_class<>'Idle' 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
)
pivot (
sum(case when seconds>0 then seconds end)
for
wait_class in (
'User I/O' user_io,
'Application' application,
'Network' network,
'Concurrency' concurrency,
'Commit' commit,
'Other' other,
'Configuration' configuration,
'Scheduler' scheduler,
'System I/O' system_io,
'Administrative' administrative
)
) x
order by
x.end_interval_time desc,
x.instance_number

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

By continuing to use the site, you agree to the use of cookies. Accept