Reports2017-11-18T12:27:27+00:00

DBA AWR Latch Summary

Description
Categories: DBA, Enginatics
Summary of latch statistics such as misses and wait times for a specified snapshot time interval

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(dhl.wait_time_) over (partition by dhl.latch_name &partition_by)/1000000) seconds,
sum(dhl.misses_) over (partition by dhl.latch_name &partition_by) misses,
sum(dhl.sleeps_) over (partition by dhl.latch_name &partition_by) sleeps,
sum(dhl.immediate_gets_) over (partition by dhl.latch_name &partition_by) immediate_gets,
sum(dhl.immediate_misses_) over (partition by dhl.latch_name &partition_by) immediate_misses,
sum(dhl.spin_gets_) over (partition by dhl.latch_name &partition_by) spin_gets,
dhl.level#
from
(
select
dhl.gets-nvl(lag(dhl.gets) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) gets_,
dhl.misses-nvl(lag(dhl.misses) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) misses_,
dhl.sleeps-nvl(lag(dhl.sleeps) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) sleeps_,
dhl.immediate_gets-nvl(lag(dhl.immediate_gets) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) immediate_gets_,
dhl.immediate_misses-nvl(lag(dhl.immediate_misses) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) immediate_misses_,
dhl.spin_gets-nvl(lag(dhl.spin_gets) over (partition by dhl.dbid, dhl.instance_number, dhl.latch_name order by dhl.snap_id),0) spin_gets_,
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
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
) 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>=fnd_date.displaydt_to_date(:date_from)-1/1440
LOV
Date To
dhs.begin_interval_time<=fnd_date.displaydt_to_date(:date_to)
LOV
Time Restriction
(to_number(to_char(dhs.begin_interval_time,'HH24')) between 0 and 6 or to_number(to_char(dhs.begin_interval_time,'HH24'))>=18)
Level of Detail
, dhl.instance_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
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
Level of Detail
instance_number,
LOV

By continuing to use the site, you agree to the use of cookies. Accept