--查询当前数据库中的热点块是哪些对象
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;
--热点块的竞争与解决 latch竞争 通过对热点buffer的跟踪找到对应的sql进行优化
SELECT /*+ rule */ hash_value,sql_text
FROM v$sqltext
WHERE (hash_value, address) IN (
SELECT a.hash_value, a.address
FROM v$sqltext a,
(SELECT DISTINCT a.owner, a.segment_name, a.segment_type
FROM dba_extents a,
(SELECT dbarfil, dbablk
FROM (SELECT dbarfil, dbablk
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE a.relative_fno = b.dbarfil
AND a.block_id <= b.dbablk
AND a.block_id + a.blocks > b.dbablk) b
WHERE a.sql_text LIKE '%' || b.segment_name || '%'
AND b.segment_type = 'TABLE')
ORDER BY hash_value, address, piece;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/40011/viewspace-674037/ ，如需转载，请注明出处，否则将追究法律责任。
