How to solve Latch: cache buffers chains on index
With the following query I found the segment with the top buffer gets:
SELECT e.owner || '.' || e.segment_name segment_name,
e.extent_id extent#,
x.DBABLK,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#,
l.gets,
l.misses,
l.sleeps,
l.wait_time / 1000000 / 3600,
l.latch#
FROM v$latch_children l, x$bh x, dba_extents e
WHERE 1=1
AND e.file_id = x.file#
AND x.hladdr = l.addr
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks - 1
and l.addr in ('00000019198D60F8')
ORDER BY x.tch DESC;