性能优化之热点块问题解决

--查询当前数据库中的热点块是哪些对象
 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博客 ” ，链接：http://blog.itpub.net/40011/viewspace-674037/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40011/viewspace-674037/