-- To identify the actual latch that's causing the contention for a given username

select name,misses,sleeps from v$latch,v$session_wait,v$session  where latch#=p2 and
v$session_wait.sid = v$session.sid and username = '&&user_name' and event = 'latch free'
/

-- Get the latch children information

select CHILD#  "cCHILD"
,      ADDR    "sADDR"
,      GETS    "sGETS"
,      MISSES  "sMISSES"
,      SLEEPS  "sSLEEPS" 
from v$latch_children 
where name in
(
select name from v$latch,v$session_wait,v$session  where latch#=p2 and
v$session_wait.sid = v$session.sid and username = '&&user_name' and event = 'latch free')
order by 4, 1, 2, 3;

-- Find the segments information

select /*+ RULE */
  e.owner ||'.'|| e.segment_name  segment_name,
  e.extent_id  extent#,
  x.dbablk - e.block_id + 1  block#,
  x.tch,
  l.child#
from
  sys.v$latch_children  l,
  sys.x$bh  x,
  sys.dba_extents e
where
  x.hladdr  = 'ADDR' and
  e.file_id = x.file# and
  e.segment_type in ('TABLE','TABLE_PARTITION','INDEX','INDEX PARTITION') and
  x.hladdr = l.addr and
  x.dbablk between e.block_id and e.block_id + e.blocks -1
  and l.name in
(select name from v$latch,v$session_wait,v$session  where latch#=p2 and
v$session_wait.sid = v$session.sid and username = '&&user_name' and event = 'latch free')
  order by x.tch desc ;