oracle 找热点块,查询热点快和热点块的操作

---查询热块对象sql语句：

SELECT *

FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME,

FROM X$BH B, DBA_OBJECTS O

WHERE B.OBJ = O.DATA_OBJECT_ID

AND B.TS# > 0

GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE

ORDER BY SUM(TCH) DESC)

WHERE ROWNUM <= 10

--查找热点块操作语句

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/29135257/viewspace-2144919/，如需转载，请注明出处，否则将追究法律责任。