DBA AWR Latch Summary

Description
Categories: Diagnostic Pack, Enginatics, Kcapps, Toolkit - DBA
Repository: Github
Summary of latch statistics such as misses and wait times for a specified snapshot time interval
Run DBA AWR Latch Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.seconds/xxen_util.zero_to_null(sum(x.seconds) over ())*100 percentage,
&columns
x.latch_name,
x.seconds,
xxen_util.time(x.seconds) time,
x.misses,
x.sleeps,
x.immediate_gets,
x.immediate_misses,
x.spin_gets,
x.level#
from
(
select distinct
&columns
dhl.latch_name,
round(sum(case when dhl.wait_time_<0 then dhl.wait_time else dhl.wait_time_ end) over (partition by dhl.latch_name &partition_by)/1000000) seconds,
sum(case when dhl.misses_<0 then dhl.misses else dhl.misses_ end) over (partition by dhl.latch_name &partition_by) misses,
sum(case when dhl.sleeps_<0 then dhl.sleeps else dhl.sleeps_ end) over (partition by dhl.latch_name &partition_by) sleeps,
sum(case when dhl.immediate_gets_<0 then dhl.immediate_gets else dhl.immediate_gets_ end) over (partition by dhl.latch_name &partition_by) immediate_gets,
sum(case when dhl.immediate_misses_<0 then dhl.immediate_misses else dhl.immediate_misses_ end) over (partition by dhl.latch_name &partition_by) immediate_misses,
sum(case when dhl.spin_gets_<0 then dhl.spin_gets else dhl.spin_gets_ end) over (partition by dhl.latch_name &partition_by) spin_gets,
dhl.level#
from
(
select
dhl.gets-lag(dhl.gets) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) gets_,
dhl.misses-lag(dhl.misses) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) misses_,
dhl.sleeps-lag(dhl.sleeps) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) sleeps_,
dhl.immediate_gets-lag(dhl.immediate_gets) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) immediate_gets_,
dhl.immediate_misses-lag(dhl.immediate_misses) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) immediate_misses_,
dhl.spin_gets-lag(dhl.spin_gets) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) spin_gets_,
dhl.wait_time-lag(dhl.wait_time) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id) wait_time_,
dhl.*
from
dba_hist_snapshot dhs,
dba_hist_latch dhl
where
1=1 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
) x
where
x.seconds>0 or
x.misses>0
order by
x.seconds desc,
x.misses desc,
x.latch_name
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
Level of Detail
instance_number,
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
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
Diagnostic Pack enabled
1=1
LOV
Container Data
dhs.dbid=(select vd.dbid from v$database vd)
LOV