SQL Performance (MOSC)

MOSC Banner

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center