DBA AWR Latch by Time

➜ Back to Library
Description
Categories: Diagnostic Pack, Enginatics
Repository: Github
Latch contention wait time history.
Each row shows the system-wide latch contention wait time per latch name of one AWR snapshot interval to identify high latch contention at specific times. 
select
x.day_of_week,
x.end_interval_time,
x.instance_number,
&columns
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,
dhl.latch_name,
case when dhl.time_delta<0 then dhl.wait_time else dhl.time_delta end/1000000 seconds
from
dba_hist_snapshot dhs,
(
select
dhl.wait_time-lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) time_delta,
dhl.*
from
dba_hist_latch dhl
) dhl
where
1=1 and
dhs.dbid=dhl.dbid and
dhs.instance_number=dhl.instance_number and
dhs.snap_id=dhl.snap_id
)
pivot (
sum(case when seconds>0 then seconds end)
for
latch_name in (
&pivot_columns
)
) 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
Diagnostic Pack enabled 
1=1
 LOV
Container Data 
dhs.dbid=(select vd.dbid from v$database vd)
 LOV
Download
Related Blitz Reports
DBA AWR Latch Summary DBA AWR System Wait Time Summary DBA AWR PGA History DBA SGA+PGA Memory Configuration DBA AWR Blocking Session Summary DBA AWR System Wait Class by Time DBA AWR SQL Performance Summary DBA AWR System Wait Event Summary DBA AWR System Metrics Summary
Blitz Report™

Blitz Report™ provides multiple benefits: