Objects causing latch contention - Oracle Database Script.
col OBJECT_NAME
FOR a30 col OWNER
FOR a12 WITH bh_lc AS (
SELECT lc.addr,
lc.child#, lc.gets,
lc.misses,
lc.immediate_gets,
lc.immediate_misses,
lc.spin_gets,
lc.sleeps,
bh.hladdr,
bh.tch tch,
bh.file#, bh.dbablk,
bh.class,
bh.state,
bh.obj
FROM v$session_wait sw,
v$latchname ld,
v$latch_children lc,
x$bh bh
WHERE lc.addr =sw.p1raw
AND sw.p2= ld.latch# and ld.name='cache buffers chains' and lower(sw.event) like '%latch%' and bh.hladdr=lc.addr ) select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type, bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets, bh_lc.immediate_misses, spin_gets, sleeps from bh_lc, dba_objects o where bh_lc.obj = o.data_object_id(+) order by 1,2 desc;