This scripts prints a list of the latch types, and reports the number of child latches for each type. If a latch type has multiple child latches, V$LATCH_CHILDREN can be used to determine whether activity against the child latches is evenly distributed. V$LATCH_PARENT should also be checked to see whether there has been any activity against the parent latch.
column type_num format 999 heading "TYPE|NUMBER"
column type_name format a45 heading "TYPE|NAME"
column parent format 9 heading "PARENT|LATCH"
column children format 999999 heading "CHILD|LATCHES"
select
l.kslltnum type_num,
d.kslldnam type_name,
sum(decode(l.kslltcnm, 0, 1, null)) parent,
sum(decode(l.kslltcnm, 0, null, 1)) children
from
sys.x$kslld d,
sys.x$ksllt l
where
d.inst_id = userenv('Instance') and
l.inst_id = userenv('Instance') and
l.kslltnum = d.indx
group by
l.kslltnum,
d.kslldnam
order by
l.kslltnum,
d.kslldnam
