If there are latch free waits, then this script can be used to further diagnose the problem. For each latch type, it reports the estimated impact of sleeps, and the number of sleeps per get. It also reports the number of other waits while holding each latch type, together with the latch level.
To reduce latch free waits, one should concentrate on the latches with the greatest impact. However, if there are a significant number of other waits while holding those latches, then one should regard those sleeps as a secondary symptom of another problem, such as contentiocolumn name format a37 heading "LATCH TYPE" trunc
column impact format 9999999999 heading "IMPACT"
column sleep_rate format a10 heading "SLEEP RATE"
column holding format 99999999999 heading "WAITS HOLDING"
column level# format 9999 heading "LEVEL"
select
l.name,
l.sleeps * l.sleeps / (l.misses - l.spin_gets) impact,
to_char(100 * l.sleeps / l.gets, '99990.00') || '%' sleep_rate,
l.waits_holding_latch holding,
l.level#
from
sys.v_$latch l
where
l.sleeps > 0
order by
2 desc
n for a higher level latch.
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。