latch_where.sql

For those who are very familiar with the layers of the Oracle architecture and their functionality, this script may help them to know where in the code latch requests are failing.

set recsep off
column name format a30 heading "LATCH TYPE"
column location format a40 heading "CODE LOCATION and [LABEL]"
column kslsleep format 999999 heading "SLEEPS"
break on name

select
l.name,
rpad(lw.ksllwnam, 40) ||
decode(lw.ksllwlbl, null, null, '[' || lw.ksllwlbl || ']') location,
wsc.kslsleep
from
sys.x_$kslwsc wsc,
sys.x_$ksllw lw,
sys.v_$latch l
where
wsc.inst_id = userenv('Instance') and
lw.inst_id = userenv('Instance') and
wsc.kslsleep > 0 and
lw.indx = wsc.indx and
l.name = wsc.ksllasnam
order by
l.sleeps desc,
wsc.kslsleep desc
/