解决热点块竞争问题的脚本
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 /
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博客 ” ，链接：https://blog.itpub.net/26180719/viewspace-721662/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
获取隐藏参数的脚本
下一篇：
获取trance文件名称的脚本
请登录后发表评论
登录
全部评论
