前言:
现在你们对“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方面的内容,感兴趣的朋友可以关注一下~
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。