ITPub博客

首页 > 数据库 > Oracle > 分析Oracle Job执行过程中修改下次执行时间

分析Oracle Job执行过程中修改下次执行时间

Oracle 作者:qiaoling5200 时间:2010-11-27 18:16:36 0 删除 编辑
本文作者:kamus

    摘要:本文通过实验和事件跟踪来分析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在每天的指定时刻运行?

    4. 假设我们的JOB设定第一次运行的时间是12:00,运行的间隔是30分钟,JOB运行需要耗时1小时,那么第二次运行是在12:30还是13:00还是根本就会报错?

    本文通过一些实验和跟踪来解释上面的所有问题。

    首先我们选择一个测试用户,假设该用户名为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

    DBMS_JOB.SUBMIT(job => :jobno,

    what => sp_test_next_date;,

    next_date => SYSDATE,

    interval => SYSDATE+1);

    COMMIT;

    END;

    /

    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

--------------------

    2004-12-02 00:44:11

    我们再手工运行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

--------------------

    2004-12-03 00:24:52

    现在我们再次修改存储过程,输出存储过程开始执行的时间,便于跟执行完毕以后的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

--------------------

    2004-12-03 00:38:24

    至此,我们已经说明了两个问题。就是: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的结果将前者放在了前面。

    接下来我们进入另外一个问题的探讨,本文最开始提到的第四个问题:

    假设我们的JOB设定第一次运行的时间是12:00,运行的间隔是30分钟,JOB运行需要耗时1小时,那么第二次运行是在12:30还是13:00还是根本就会报错?

    通过分析trace文件我们可以找到更新next_date的SQL语句是:

    update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_date=greatest(:3,sysdate),total=total+(sysdate - nvl(this_date, sysdate)) where job=:4

    注意到更新next_date字段的公式是greatest(:3, sysdate),此处的:3绑定的是job的this_date+interval.所以我们猜测实际上应该是有一个跟当前时间的比较机制,如果在执行 完JOB之后的时间比按照this_date+interval计算出的时间更晚一些,那么next_date就更新为当前时间,也就是几乎会立刻再重新 执行JOB.

    同样这样的猜测我们也需要通过实验来验证一下。

    创建一个新的存储过程sp_test_next_date1,简单地等待2分钟,但是我们将调用这个存储过程的JOB的interval设置为1分钟,看看会有什么情况。

    为了更方便得比较,我们创建一个表用来记录每次JOB执行的开始时间。

    SQL> create table t (cdate date);

    Table created

    创建存储过程的脚本

    create or replace procedure sp_test_next_date1 as begin——输出JOB开始执行的时间insert into t(cdate) values(sysdate);commit;——等待120秒退出dbms_lock.sleep(seconds => 120);end sp_test_next_date1;

    创建调用此存储过程的JOB

    SQL> variable jobno number;

    SQL> BEGIN

    DBMS_JOB.SUBMIT(job => :jobno,

    what => sp_test_next_date1;,

    next_date => SYSDATE,

    interval => SYSDATE+1/1440);

    COMMIT;

    END;

    /

    PL/SQL 过程已成功完成。

    jobno

---------

    7

    执行此JOB,然后过一段时间开始检查表t中的输出。

    SQL> select * from t order by cdate;

    CDATE

--------------------

    2004-12-3 14:10:43

    2004-12-3 14:12:47

    2004-12-3 14:14:55

    2004-12-3 14:16:59

    2004-12-3 14:19:07

    2004-12-3 14:21:11

    6 rows selected

    首先我们确认JOB每次都是成功执行了,并没有任何报错,然后检查cdate字段,发现时间间隔都是2分钟左右,也就是说因为JOB本身的interval设定比JOB本身的执行时间要长,所以Oracle将next_date设置为每次JOB结束的时间。

    同时我们也注意到,每次开始的时间都有4秒到8秒的延迟,没有继续深究,不确认这是因为oracle本身计算的误差,还是内部比如启动Job Process需要的时长。

    不论如何,到此我们也已经回答了第四个问题,即使interval的时长短于JOB执行的时间,整个作业仍然会继续进行,只是执行间隔变为了JOB真实运行的时长。

    由于trace文件过长,所以不在本文中贴出了,如果有兴趣可以发邮件给我。我的邮件地址是:kamus@itpub.net

    本文的最后一部分,解答本文开头提出的第三个问题,也就是:

    JOB的下一次运行会受到上一次运行时间的影响吗?如果受到影响,如何可以避免这个影响而让JOB在每天的指定时刻运行?

    JOB的下一次运行时间是会受上一次影响的,如果我们的interval仅仅是sysdate+1/24这样的形式的话,无疑,上次执行的时间再加上1小 时就是下次执行的时间。那么如果JOB因为某些原因延迟执行了一次,这样就会导致下一次的执行时间也同样顺延了,这通常不是我们希望出现的现象。

    解决方法很简单,只需要设定正确的interval就可以了。

    比如,我们要JOB在每天的凌晨3:30执行而不管上次执行到底是几点,只需要设置interval为trunc(SYSDATE)+3.5/24+1即可。完整的SQL如下:

    SQL> variable jobno number;

    SQL> BEGIN

    DBMS_JOB.SUBMIT(job => :jobno,

    what => sp_test_next_date;,

    next_date => SYSDATE,

    interval => trunc(SYSDATE)+3.5/24+1);

    COMMIT;

    END;

    /

    BTW:在trace文件中发现虽然通过select rowid from table返回的结果已经是扩展ROWID格式(Data Object number + File + Block + ROW)了,但是oracle内部检索数据仍然在使用限制ROWID格式(Block number.Row number.File number)。

    本文涉及到的额外知识可以参看我的其它技术文章:

    1. 通过事件跟踪SQL执行的后台步骤

    2. Oracle等待事件,比如本文提到的PL/SQL lock timer

    3. ROWID格式

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22331929/viewspace-1115832/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-08-13