分享交流
合作共赢!

Oracle查看最近最消耗CPU的SQL语句及会话信息的方法总结

导读:
1、查找最近一分钟内,最消耗CPU的SQL语句;
2、查找最近一分钟内,最消耗CPU的会话;
3、查找最近一分钟内,最消耗I/O的SQL语句;
4、查找最近一分钟内,最消耗资源的SQL语句;
5、查找最近一分钟内,最消耗资源的会话。

在Oracle中,查找最近一段时间,最消耗CPU的SQL语句及会话信息:可以根据 V$ACTIVE_SESSION_HISTORY 视图来获取。

1、查找最近一分钟内,最消耗CPU的SQL语句:

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'ON CPU'
AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

2、查找最近一分钟内,最消耗CPU的会话:

SELECT SESSION_ID,
COUNT(*)
FROM V$ACTIVE_SESSION_HISTORY V
WHERE V.SESSION_STATE = 'ON CPU'
AND V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)
GROUP BY SESSION_ID
ORDER BY COUNT(*) DESC;

3、查找最近一分钟内,最消耗I/O的SQL语句:

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'WAITING'
AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
AND ASH.WAIT_CLASS = 'USER I/O'
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

4、查找最近一分钟内,最消耗资源的SQL语句:

SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'WAITING'
AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
AND ASH.WAIT_CLASS = 'USER I/O'
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;

5、查找最近一分钟内,最消耗资源的会话:

SELECT ASH.SESSION_ID,
ASH.SESSION_SERIAL#,
ASH.USER_ID,
ASH.PROGRAM,
SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 0)) "CPU",
SUM(DECODE(ASH.SESSION_STATE, 'WAITING', 1, 0)) -
SUM(DECODE(ASH.SESSION_STATE,
'WAITING',
DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
0)) "WAITING",
SUM(DECODE(ASH.SESSION_STATE,
'WAITING',
DECODE(ASH.WAIT_CLASS, 'USER I/O', 1, 0),
0)) "IO",
SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1)) "TOTAL"
FROM V$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
ORDER BY SUM(DECODE(ASH.SESSION_STATE, 'ON CPU', 1, 1));

 

赞(0) 打赏
未经允许不得转载:琼杰笔记 » Oracle查看最近最消耗CPU的SQL语句及会话信息的方法总结

评论 抢沙发

评论前必须登录!

 

分享交流,合作共赢!

联系我们加入QQ群

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏