导读:
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));
评论前必须登录!
注册