DBA AWR Latch by Time

Description
Categories: DBA, Enginatics
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=(select vd.dbid from v$database vd) 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>=fnd_date.displaydt_to_date(:date_from)-1/1440
LOV
Date To
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to)
LOV
Date To
select * from
(
select * from
(
select
'''' ||dhl.latch_name||''' '||substr(translate(lower(dhl.latch_name),' .-:/','_____'),1,29)||',' pivot_text
from
(
select
dhl.wait_time-nvl(lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) wait_time_,
dhl.*
from
dba_hist_snapshot dhs,
dba_hist_latch dhl
where
dhs.end_interval_time>=fnd_date.displaydt_to_date(:date_from)-1/1440 and
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to) and
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhl.dbid and
dhs.instance_number=dhl.instance_number and
dhs.snap_id=dhl.snap_id
) dhl
where
dhl.wait_time_>=0
group by
dhl.latch_name
having
sum(dhl.wait_time_)>0
order by
sum(dhl.wait_time_) desc
)
where
rownum<=30
)
union all
select '''dummy'' dummy' pivot_text from dual
Date To
with x as
(
select * from
(
select
substr(translate(lower(dhl.latch_name),' .-:/','_____'),1,29) column_name
from
(
select
dhl.wait_time-nvl(lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) wait_time_,
dhl.*
from
dba_hist_snapshot dhs,
dba_hist_latch dhl
where
dhs.end_interval_time>=fnd_date.displaydt_to_date(:date_from)-1/1440 and
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to) and
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhl.dbid and
dhs.instance_number=dhl.instance_number and
dhs.snap_id=dhl.snap_id
) dhl
where
dhl.wait_time_>=0
group by
dhl.latch_name
having
sum(dhl.wait_time_)>0
order by
sum(dhl.wait_time_) desc
)
where
rownum<=30
)
select 'xxen_util.time(x.'||x.column_name||') '||x.column_name||',' from x union all
select 'x.'||x.column_name||',' from x union all
select 'null x' pivot_text from dual