latch_sleeps.sql

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.