在Oracle中，如何查找最近1分钟内，最消耗CPU的SQL语句及会话信息？

DB宝 2017-05-10

1973

Q题目如下所示：在Oracle中，如何查找最近1分钟内，最消耗CPU的SQL语句及会话信息？

     
A答案如下所示：


可以根据V$ACTIVE_SESSION_HISTORY视图来获取，如下所示：
①　查找最近一分钟内，最消耗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;


②　查找最近一分钟内，最消耗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;


③　查找最近一分钟内，最消耗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;

④　查找最近一分钟内，最消耗资源的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;


⑤　查找最近一分钟内，最消耗资源的会话：
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));


& 说明：
有关SQL监控的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1262559/。

DB笔试面试历史连接

http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 文章内容来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。

oracle

最后修改时间：2020-01-10 21:12:28

文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

在Oracle中，如何查找最近1分钟内，最消耗CPU的SQL语句及会话信息？

评论