--最繁忙的 Buffer
SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11;
--获取当前持有最热点数据块的 Latch 及 Buffer 信息：
SELECT b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps
FROM (SELECT * FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch, hladdr FROM x$bh ORDER BY tch DESC)
WHERE ROWNUM < 11) a,
(SELECT addr, gets, misses, sleeps FROM v$latch_children WHERE NAME = 'cache buffers chains') b
WHERE a.hladdr = b.addr;
--查Buffer对应的对象：
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk;
