job每小时执行一次 oracle_分析Oracle Job执行过程中修改下次执行时间

摘要：本文通过实验和事件跟踪来分析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

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

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

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

2 DBMS_JOB.SUBMIT(job =>

：jobno，

3 what => sp_test_next_date1；，

4 next_date => SYSDATE，

5 interval => SYSDATE+1/1440)；

6 COMMIT；

7 END；

8 /

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

2 DBMS_JOB.SUBMIT(job =>

：jobno，

3 what => sp_test_next_date；，

4 next_date => SYSDATE，

5 interval =>

trunc(SYSDATE)+3.5/24+1)；

6 COMMIT；

7 END；

8 /

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格式