龙空技术网

Oracle实用脚本分享--一键查询热点块和热点块的操作信息

波波说运维 192

前言:

现在你们对“oracle查看表的块号”大约比较重视,同学们都需要分析一些“oracle查看表的块号”的相关内容。那么小编同时在网摘上汇集了一些对于“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方面的内容,感兴趣的朋友可以关注一下~

标签: #oracle查看表的块号 #oracle数据库视频百度云 #oracle查询块详解