解决热点块竞争问题的脚本

SELECT /*+ rule */ hash_value,sql_text
2 FROM v$sqltext
3 WHERE (hash_value, address) IN (
4 SELECT a.hash_value, a.address
5 FROM v$sqltext a,
6 (SELECT DISTINCT a.owner, a.segment_name, a.segment_type
7 FROM dba_extents a,
8 (SELECT dbarfil, dbablk
9 FROM (SELECT dbarfil, dbablk
10 FROM x$bh
11 ORDER BY tch DESC)
12 WHERE ROWNUM < 11) b
13 WHERE a.relative_fno = b.dbarfil
14 AND a.block_id <= b.dbablk
15 AND a.block_id + a.blocks > b.dbablk) b
16 WHERE a.sql_text LIKE '%' || b.segment_name || '%'
17 AND b.segment_type = 'TABLE')
18 ORDER BY hash_value, address, piece
19 /

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26180719/viewspace-721662/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26180719/viewspace-721662/