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

来源：互联网发布：个人商城源码编辑：程序博客网时间：2024/04/30 17:56

导读：
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 VWHERE  V.SESSION_STATE = 'ON CPU'AND    V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)GROUP  BY SESSION_IDORDER  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));

阅读全文

1 0