oracle查询dblink连接信息_Oracle实用脚本分享--一键查询热点块和热点块的操作信息...

概述

很多时候我们想了解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_ID,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;

---

通过sql_id获取具体sql语句

select listagg(sql_text,' ') within group (order by piece) from v$sqltext where sql_id = '1akucfw1ccd22' group by sql_id

---

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~