转找到Oracle最吃CPU的会话并断开


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));   

6、删除会话:

alter system kill session 'session_id,session_serial#'; 

alter system kill session '26,36237'; 


转自:

https://blog.csdn.net/dtjiawenwang88/article/details/74906954


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值