oracle创建job：定时杀死慢查询

本文是记一次运维过程中实际发生的一个案例

背景介绍

应用这边运行缓慢，卡顿严重，查看数据库所在服务器，CPU100%，怀疑有大量查询占用资源导致。故查询DB中的慢sql，发现确实有耗时相当长的sql。
用kill 命令杀掉该进程之后，还是过一段时间后产生，但一时半会没法定位sql的产生来源，故只能想办法先定时处理掉这部分慢sql。
这里想到了 利用oracle 创建存储过程，循环查找慢sql，并kill session，并用oracle的job来定时执行该存储过程。

操作过程（基本可以复制粘贴使用）

以下是进入sqlplus进行的操作

1 创建表，用于记录被kill的sql；

--CREATE LOG TABLE 
CREATE TABLE KILL_SESS_HIST   AS 
SELECT S.*,T.START_TIME
      FROM V$TRANSACTION T,V$SESSION S 
           WHERE S.TADDR=T.ADDR AND T.START_TIME<TO_CHAR((SYSDATE-1/1440),'YY/HH/DD HH24:MI:SS')
                     
alter table KILL_SESS_HIST    add (kill_time date default sysdate);

2 创建存储过程；

CREATE 	OR REPLACE PROCEDURE  P_KILL_SES_BEF1S
  IS
BEGIN
  FOR
   REC_ACT_SESS IN (SELECT S.SID,
                           S.SERIAL#,
                           S.MACHINE,
                           S.LAST_CALL_ET,
                           S.STATUS,
                           S.USERNAME,
                           S.TYPE,
                           S.SQL_ID,
                           S.EVENT,
                           T.START_TIME
                      FROM V$TRANSACTION T, V$SESSION S
                     WHERE S.TADDR = T.ADDR
                       AND T.START_TIME <
                           TO_CHAR((SYSDATE - 1 / 1440), 'YY/HH/DD HH24:MI:SS'))

   LOOP
    INSERT INTO KILL_SESS_HIST
      (SID,
       SERIAL#,
       MACHINE,
       LAST_CALL_ET,
       USERNAME,
       TYPE,
       SQL_ID,
       START_TIME,
       EVENT)
    VALUES
      (REC_ACT_SESS.SID,
       REC_ACT_SESS.SERIAL#,
       REC_ACT_SESS.MACHINE,
       REC_ACT_SESS.LAST_CALL_ET,
       REC_ACT_SESS.USERNAME,
       REC_ACT_SESS.TYPE,
       REC_ACT_SESS.SQL_ID,
       REC_ACT_SESS.START_TIME,
       REC_ACT_SESS.EVENT);
    EXECUTE immediate 'ALTER SYSTEM KILL SESSION ' || CHR(39) ||
                      REC_ACT_SESS.SID || ',' || REC_ACT_SESS.SERIAL# ||
                      CHR(39);
    dbms_output.put_line('ALTER SYSTEM KILL SESSION ' || CHR(39) ||
                         REC_ACT_SESS.SID || ',' || REC_ACT_SESS.SERIAL# ||
                         CHR(39));
    COMMIT;
  END LOOP;
END;
/

注意末尾要以“/”结尾，命令行才能执行
下图标志表示成功

3 调用存储过程；

set serveroutput on
call P_KILL_SES_BEF1S();

4 创建job；

 DECLARE
     n number;
    begin
      dba_job.submit(n=>n,
                      'P_KILL_SES_BEF1S;',
                      to_date('22-02-2020 14:47:00', 'dd-mm-yyyy hh24:mi:ss'),
                      'sysdate+1/1440');
      commit;
    end;
    .

注意最后一行是一个英文句号，表示代码编辑完毕。用英文斜杠，执行该代码

结果如图所示job运行成功
关于存储过程介绍、job创建的其它指令，有很多，感兴趣可以自行百度了解。