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

jiawang

导读：
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));