Oracle作业(JOB)更新next_date的探讨

　　摘要:本文通过实验和事件跟踪来分析Oracle Job执行过程中修改下次执行时间的机制。
　　
　　有些人问，Oracle的JOB在设定完next_date和interval之后，到底是什么时候决定下一次运行时间的。可以归纳成以下几个问题。
　　
　　1． 假设我们的JOB设定第一次运行的时间是12:00，运行的间隔是1小时，JOB运行需要耗时30分钟，那么第二次运行是在13:00还是13:30？
　　
　　2． 如果是在13:00那是不是说明只要JOB一开始运行，next_date就被重新计算了？
　　
　　3． JOB的下一次运行会受到上一次运行时间的影响吗？如果受到影响，如何可以避免这个影响而让JOB在每天的指定时刻运行？
　　
　　本文通过一些实验和跟踪来解释上面的所有问题。
　　
　　首先我们选择一个测试用户，假设该用户名为kamus。
　　
　　由于我们在实验用的存储过程中会用到dbms_lock包，所以需要由sys用户先授予kamus用户使用dbms_lock包的权限。
　　
　　d:\Temp>sqlplus "/ as sysdba"
　　
　　SQL*Plus: Release 9.2.0.5.0 - Production on 星期三 12月 1 23:56:32 2004
　　
　　Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
　　
　　连接到:
　　
　　Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
　　
　　With the Partitioning, OLAP and Oracle Data Mining options
　　
　　JServer Release 9.2.0.5.0 - Production
　　
　　SQL> grant execute on dbms_lock to kamus;
　　
　　授权成功。
　　
　　然后用kamus用户登录数据库，创建我们测试使用的存储过程sp_test_next_date。
　　
　　create or replace procedure sp_test_next_date as
　　p_jobno number;
　　P_nextdate date;
　　begin
　　--将调用此存储过程的job的next_date设置为30分钟以后
　　select job into p_jobno from user_jobs where what = 'sp_test_next_date;';
　　execute immediate 'begin dbms_job.next_date(' || to_char(p_jobno) || ',sysdate+1/48);commit;end;';
　　--修改完毕以后检查user_jobs视图，输出job目前的next_date
　　select next_date
　　into P_nextdate
　　from user_jobs
　　where what = 'sp_test_next_date;';
　　dbms_output.put_line('JOB执行中的next_date: ' ||
　　to_char(p_nextdate,'YYYY-MM-DD HH24:MI:SS'));
　　--等待10秒再退出执行
　　dbms_lock.sleep(seconds => 10);
　　end sp_test_next_date;
　　
　　创建调用该存储过程的JOB，定义interval为每天一次，也就是这次执行以后，下次执行时间应该在1天以后。
　　
　　SQL> variable jobno number;
　　
　　SQL> BEGIN
　　
　　2 DBMS_JOB.SUBMIT(job => :jobno,
　　
　　3 what => 'sp_test_next_date;',
　　
　　4 next_date => SYSDATE,
　　
　　5 interval => 'SYSDATE+1');
　　
　　6 COMMIT;
　　
　　7 END;
　　
　　8 /
　　
　　PL/SQL 过程已成功完成。
　　
　　jobno
　　
　　---------
　　
　　1
　　
　　然后我们手工执行存储过程，执行完毕以后再手工从user_jobs视图中获得JOB的下次执行时间，可以看到在存储过程中修改的JOB的下次执行时间已经生效，变成了当前时间的30分钟以后，而不是默认的1天以后。
　　
　　SQL> conn kamus
　　
　　请输入口令:
　　
　　已连接。
　　
　　SQL> set serverout on
　　
　　SQL> exec sp_test_next_date();
　　
　　JOB执行中的next_date: 2004-12-02 00:44:11
　　
　　PL/SQL 过程已成功完成。
　　
　　SQL> col next_date for a20
　　
　　SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs
　　
　　where what = 'sp_test_next_date;';
　　
　　NEXT_DATE
　　
　　我们再手工运行JOB，看看这次的结果，可以发现JOB没有运行完毕以前被修改了的下次运行时间跟JOB运行完毕以后再次手工检索user_jobs视图获得的下次运行时间已经不相同了。由此我们可以得出一个结论，next_date是在JOB运行完毕以后被Oracle自动修改的，而不是在JOB刚开始运行的时候，因为我们在存储过程中修改的next_date在JOB运行结束之后又被修改为默认的1天以后了。
　　
　　SQL> exec dbms_job.run(1);
　　
　　JOB执行中的next_date: 2004-12-02 00:54:52
　　
　　PL/SQL 过程已成功完成。
　　
　　SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs
　　
　　where what = 'sp_test_next_date;';
　　
　　NEXT_DATE
　　
　　现在我们再次修改存储过程，输出存储过程开始执行的时间，便于跟执行完毕以后的JOB下次执行时间进行比较。
　　
　　create or replace procedure sp_test_next_date as
　　p_jobno number;
　　P_nextdate date;
　　begin
　　--输出JOB刚开始执行的时间
　　dbms_output.put_line(' JOB开始执行的时间: ' ||
　　to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
　　--将调用此存储过程的job的next_date设置为30分钟以后
　　select job into p_jobno from user_jobs where what = 'sp_test_next_date;';
　　execute immediate 'begin dbms_job.next_date(' || to_char(p_jobno) || ',sysdate+1/48);commit;end;';
　　--修改完毕以后检查user_jobs视图，输出job目前的next_date
　　select next_date
　　into P_nextdate
　　from user_jobs
　　where what = 'sp_test_next_date;';
　　dbms_output.put_line(' JOB执行中的next_date: ' ||
　　to_char(p_nextdate,'YYYY-MM-DD HH24:MI:SS'));
　　--等待10秒再退出执行
　　dbms_lock.sleep(seconds => 10);
　　end sp_test_next_date;
　　
　　重新进行测试，我们可以发现JOB的next_date是JOB开始执行时间的1天以后，而不是JOB结束时间的1天以后（因为JOB结束需要经过10秒钟）
　　
　　SQL> exec dbms_job.run(1);
　　
　　JOB开始执行的时间: 2004-12-02 00:38:24
　　
　　JOB执行中的next_date: 2004-12-02 01:08:24
　　
　　PL/SQL 过程已成功完成。
　　
　　SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs
　　
　　where what = 'sp_test_next_date;';
　　
　　NEXT_DATE
　　
　　至此，我们已经说明了两个问题。就是：JOB在运行结束之后才会更新next_date，但是计算的方法是JOB刚开始的时间加上interval设定的间隔。
　　
　　下面我们通过trace来再次求证这个结论。
　　
　　SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
　　
　　会话已更改。
　　
　　SQL> exec dbms_job.run(1);
　　
　　PL/SQL 过程已成功完成。
　　
　　SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
　　
　　会话已更改。
　　
　　执行完毕以后在udump目录中查看生成的trace文件。如果我们用tkprof来格式化这个trace文件然后再查看格式化后的结果，我们会感到很诧异。因为在格式化完毕的SQL执行顺序中，更新job$表的语句出现在dbms_job.next_date语句之前，也就是看上去是Oracle先按照interval自动更新了JOB的next_date，然后才继续往下执行存储过程中定义的next_date更新语句，而这样显然无法解释我们在上面的实验中看到的结果。
　　
　　但是当我们跳过tkprof而直接去查看生成的trace文件，就会恍然大悟，同时也印证了steve adams在ixora上提到的观点：tkprof格式化完的结果会省略一些信息，甚至在有时候会给我们错误的信息。
　　
　　直接查看trace文件，我们可以看到如下的执行顺序：
　　
　　1． parse cursor #10（oracle根据interval和先前保存的this_date字段值更新job$表的语句，包括更新failures, last_date, next_date, total等）
　　
　　2． parse cursor #15（存储过程中的begin dbms_job.next_date语句）
　　
　　3． binds cursor #15（将加上了30分钟的时间绑定到cursor #15上）
　　
　　4． exec cursor #15（执行cursor #15）
　　
　　5． wait cursor #11（经历一个PL/SQL lock timer事件，也就是存储过程中执行的dbms_lock.sleep方法）
　　
　　6． binds cursor #10（将JOB刚开始执行时候的时间绑定到cursor #10上）
　　
　　7． exec cursor #10（执行cursor #10）
　　
　　也就是说虽然更新job$的语句被很早地解析过了，但是直到JOB运行结束时这个被解析过的游标才开始作变量绑定进而开始执行。
　　
　　正是因为解析update sys.job$语句的时间早于解析begin dbms_job.next_date语句的时间，所以tkprof的结果将前者放在了前面。
　　
　　由于trace文件过长，所以不在本文中贴出了，如果有兴趣可以发邮件给我。我的邮件地址是：kamus@itpub.net
　　
　　本文的最后一部分，解答本文开头提出的第三个问题，也就是：
　　
　　JOB的下一次运行会受到上一次运行时间的影响吗？如果受到影响，如何可以避免这个影响而让JOB在每天的指定时刻运行？
　　
　　JOB的下一次运行时间是会受上一次影响的，如果我们的interval仅仅是sysdate+1/24这样的形式的话，无疑，上次执行的时间再加上1小时就

查看本文来源