DBA AWR Latch Summary

➜ Back to Library
Description
Categories: Diagnostic Pack, Enginatics, Kcapps
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
Download
 
Related Blitz Reports
DBA AWR Latch by Time DBA AWR System Wait Time Summary DBA SGA+PGA Memory Configuration DBA AWR Blocking Session Summary DBA AWR Active Session History 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: