ORACLE定时器停止运行

实施同事反馈，在机房停电后，ORACLE定时器经常停止运行，初步断定，在停电后，SQL语句的执行计划发生变化，导致定时器执行出现异常。经过查询存储过程中的SQL，发现语句存在性能问题

改进前的SQL：

SELECT sys_guid(),
       F.FAULT_INFORM_NO, --故障编号
       ----中间省去相关内容
       1 as STATE
  FROM DMS_FAST_INFORM@DIS_SAIP_LK F, DUAL
 WHERE F.STATE IN (0, 3)
   AND F.FAULT_SRC = 1
   AND F.FAULT_FIND_TIME > TO_DATE('2014-09-24', 'YYYY-MM-DD')
   AND NOT EXISTS (SELECT 1
          FROM DIS_FR_FAULT_REPORT DF
         WHERE DF.CODE = F.FAULT_INFORM_NO);

改进后的SQL：

SELECT /*+use_hash(F,DF)*/ sys_guid() ,
           F.FAULT_INFORM_NO, --故障编号
           --中间省去相关内容
           1 as STATE
      FROM DMS_FAST_INFORM@DIS_SAIP_LK F,DIS_FR_FAULT_REPORT DF
     WHERE F.STATE IN (0, 3)
       AND F.FAULT_SRC = 1/*只取客服报障数据*/
       AND F.FAULT_FIND_TIME > TO_DATE('2014-09-24','YYYY-MM-DD')
       AND DF.CODE = F.FAULT_INFORM_NO(+)
       AND DF.CODE is null;

发现改进前的SQL，使用的EXISTS关键字，由于DMS_FAST_INFORM、DIS_FR_FAULT_REPORT都是大表，使用EXISTS导致读表循环的次数非常多，因此修改成使用表连接的方式，通过使用HINT，使用HASH连接的方式。

1、定时器状态查询

select j.JOB, j.WHAT, j.BROKEN, j.LAST_DATE, j.NEXT_DATE
  from user_jobs j
 where j.WHAT like '%SAIP%';

2、定时器启动、停止

begin
  dbms_job.run(121);
  commit;
end;


 begin
  dbms_job.run(122);
  commit;
end;


 begin
  dbms_job.broken(121,true);
  commit;
end;


 begin
  dbms_job.broken(122,true);
  commit;
end;

3、定时器删除

execute dbms_job.remove(121);
execute dbms_job.remove(122);


COMMIT;

4、定时器创建

variable job_number number;
begin
     DBMS_JOB.submit (:job_number, 'P_INSERT_FAULT_FROM_SAIP;', SYSDATE,  'sysdate+1/(24*60)');
     commit;
end;
/




variable job_number number;
begin
     DBMS_JOB.submit (:job_number, 'P_INSERT_QJ_FAULT_FROM_SAIP;', SYSDATE,  'sysdate+1/(24*60)');
     commit;
end;
/




show parameter job_queue_process;