DBA AWR System Wait Class by Time

➜ Back to Library
Description
Categories: Diagnostic Pack, Enginatics
Repository: Github
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 identify unusual wait events that occured at specific times.
Use the Session Type parameter to restrict either to foreground, background or all server processes.
Run DBA AWR System Wait Class by Time and other Oracle EBS reports with Blitz Report™ on our demo environment
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.user_io_waits,
x.user_io_timeouts,
x.application,
x.application_waits,
x.application_timeouts,
x.network,
x.network_waits,
x.network_timeouts,
x.concurrency,
x.concurrency_waits,
x.concurrency_timeouts,
x.commit,
x.commit_waits,
x.commit_timeouts,
x.other,
x.other_waits,
x.other_timeouts,
x.configuration,
x.configuration_waits,
x.configuration_timeouts,
x.scheduler,
x.scheduler_waits,
x.scheduler_timeouts,
x.system_io,
x.system_io_waits,
x.system_io_timeouts,
x.administrative,
x.administrative_waits,
x.administrative_timeouts
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,
case when dhse.time_delta<0 then dhse.time_micro else dhse.time_delta end/1000000 seconds,
case when dhse.waits_delta<0 then dhse.waits else dhse.waits_delta end waits,
case when dhse.timeouts_delta<0 then dhse.timeouts else dhse.timeouts_delta end timeouts
from
dba_hist_snapshot dhs,
(
select
dhse.time_micro-lag(dhse.time_micro) over (partition by dhse.dbid, dhse.instance_number, dhse.wait_class, dhse.event_name order by dhse.snap_id) time_delta,
dhse.waits-lag(dhse.waits) over (partition by dhse.dbid, dhse.instance_number, dhse.wait_class, dhse.event_name order by dhse.snap_id) waits_delta,
dhse.timeouts-lag(dhse.timeouts) over (partition by dhse.dbid, dhse.instance_number, dhse.wait_class, 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_timeouts) timeouts,
dhse.*
from
dba_hist_system_event dhse
) dhse
) dhse
where
1=1 and
dhse.wait_class<>'Idle' 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),
sum(case when waits>0 then waits end) waits,
sum(case when timeouts>0 then timeouts end) timeouts
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>=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
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 Event Summary 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: