latch_get.sql

This script reports the breakdown of willing-to-wait gets for each latch type, into simple gets, spin gets and sleep gets. Spin gets and sleep gets are latch gets that require spinning or sleeping respectively. Simple gets require neither.

 

 

column name        format a30 heading "LATCH TYPE"  trunc
column simple_gets format a18 heading "SIMPLE GETS" justify right
column spin_gets   format a14 heading "SPIN GETS"   justify right
column sleep_gets  format a14 heading "SLEEP GETS"  justify right

select
  l.name,
  substr(
    to_char(
      l.gets - l.misses,
      '9999999990'
    ),
    2
  ) ||
  ' ' ||
  substr(
    to_char(
      100 * (l.gets - l.misses) / l.gets,
      '999.00'
    ),
    2
  ) ||
  '%'  simple_gets,
  substr(
    to_char(
      l.spin_gets,
      '9999990'
    ),
    2
  ) ||
  ' ' ||
  substr(
    to_char(
      100 * l.spin_gets / l.gets,
      '90.00'
    ),
    2
  ) ||
  '%'  spin_gets,
  substr(
    to_char(
      l.misses - l.spin_gets,
      '9999990'
    ),
    2
  ) ||
  ' ' ||
  substr(
    to_char(
      100 * (l.misses - l.spin_gets) / l.gets,
      '90.00'
    ),
    2
  ) ||
  '%'  sleep_gets
from
  sys.v_$latch  l
where
  l.gets > 0
order by
  l.name