oracle jobs 执行耗时,JOB——手工指定Oracle中job执行的时间间隔

一、JOB时间间隔的问题

我们MIS系统中需要建立一些JOB，比如客户会要求在月底自动生成一些报表统计信息。

在指定JOB的执行时间时，一般我们会选择在每月月底的午夜来执行(或者有多个JOB，应该分散在不同的时间段来执行)。此时不能直接用sysdate来确定时间，因为每次执行后，oracle自动生成的下一次的执行时间，会有几秒的延迟。

比如，我指定第一次执行时间为：2008-03-27 10:58:01，时间间隔的方式为：last_day(add_months(sysdate, 1))。

那么，JOB执行后我会发现，第一次的执行时间是2008-03-27 10:58:05，而下一次的执行时间被更新为2008-04-30 10:58:05；而下次执行后，发现它的执行时间是2008-04-30 10:58:07，而它再次执行的时间变为2008-05-31 10:58:07；……

二、可能原因

(1)JOB的调用有时间延迟。我指定在10:58:01执行，可是由于延迟，真正执行的时间是10:58:05；

(2)根据实际的执行时间来自动计算下一次的执行时间。我指定下一次的时间计算方法是last_day(add_months(sysdate, 1))。但它是根据10:58:05来计算的，而不是10:58:01。

三、解决办法

这种延迟累计起来，就可能导致执行时间错误！为避免这种延迟，我们应该手工指定执行时间中的“时分秒”。比如，

每月月底的午夜：

last_day(add_months(to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd'), 1))

每月的任意指定时间：

last_day(add_months(to_date(to_char(sysdate, 'yyyy-mm-dd') || ' 11:24:31', 'yyyy-mm-dd hh24:mi:ss'), 1))

……

这样，每次执行的时间大概会比指定的时间延迟2-5秒，但oracle自动计算出来的下一次执行时间是没有问题的，所以不会累计这个延迟。

四、相关测试

下面是一个小测试：

SQL> conn tianyc/test

已连接。

SQL> select * from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

PL/SQL Release 9.2.0.1.0 - Production

CORE 9.2.0.1.0 Production

TNS for 32-bit Windows: Version 9.2.0.1.0 - Production

NLSRTL Version 9.2.0.1.0 - Production

-- 1. 创建初始环境

-- 1.1 创建表test_job，存放执行日期SQL> create table test_job ( a date );

表已创建。

-- 1.2 创建过程，向表test_job中插入执行时的日期

SQL> CREATE OR REPLACE PROCEDURE PRC_TEST_JOB(PRM_A IN DATE,

2 PRM_APPCODE OUT NUMBER,

3 PRM_ERRORMSG OUT VARCHAR2)

4 IS

5 BEGIN

6 PRM_APPCODE := 1;

7 PRM_ERRORMSG := 'succeed';

8 INSERT INTO test_job VALUES(prm_a);

9 COMMIT;

10 END PRC_TEST_JOB;

11

12 /

过程已创建。

-- 2. 创建任务

-- 2.1 查看当前时间，用来确定创建的任务的执行时间

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2008-03-27 10:52:20

-- 2.2 创建任务，调用步骤1.2中创建的过程。在10:58:01时执行。

SQL> variable x number;

SQL> begin

2 sys.dbms_job.submit(job => :x,

3 what => 'DECLARE

4 n_appcode NUMBER(10);

5 v_errormsg VARCHAR2(4000);

6 BEGIN

7 prc_test_job(SYSDATE, n_appcode, v_errormsg);

8 END;',

9 next_date => to_date('27-03-2008 10:58:01', 'dd-mm-yyyy hh24:mi:ss'),

10 interval => 'last_day(add_months(sysdate, 1))');

11 commit;

12 end;

13 /

PL/SQL 过程已成功完成。

-- 3. 验证数据

-- 3.1 在该执行时间之前没有数据

SQL> select * from test_job;

未选定行

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2008-03-27 10:57:46

-- 3.2 过了该时间后，可以查询到数据

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2008-03-27 10:58:18

SQL> select to_char(a, 'yyyy-mm-dd hh24:mi:ss') from test_job;

TO_CHAR(A,'YYYY-MM-

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

2008-03-27 10:58:05

-- 3.3 任务执行完毕后，自动根据条件(INTERVAL)更新下次的执行时间(NEXT_DATE)

SQL> select PRIV_USER, LAST_DATE, NEXT_DATE, INTERVAL from user_jobs;

PRIV_USER LAST_DATE NEXT_DATE INTERVAL

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

TIANYC 27-3月 -08 30-4月 -08 last_day(add_months(sysdate, 1))

SQL> select what from user_jobs;

WHAT

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

DECLARE

n_appcode NUMBER(10);

v_errormsg VARCHAR2(4000);

BEGIN

prc_test_job(SYSDATE, n_appcode, v_errormsg);

END;

SQL>

-- 4. 存在问题：

-- 4.1 test_job里的执行时间不是10:58:01， 而是10:58:05：

SQL> select to_char(a, 'yyyy-mm-dd hh24:mi:ss') from test_job;

TO_CHAR(A,'YYYY-MM-

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

2008-03-27 10:58:05-- 4.2 在视图user_jobs里也是如此。把这两个时间精确到秒，看看效果：

SQL> select PRIV_USER, to_char(LAST_DATE, 'yyyy-mm-dd hh24:mi:ss'),

2 to_char(NEXT_DATE, 'yyyy-mm-dd hh24:mi:ss'), INTERVAL from user_jobs;

PRIV_USER TO_CHAR(LAST_DATE,' TO_CHAR(NEXT_DATE,' INTERVAL

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

TIANYC 2008-03-27 10:58:05 2008-04-30 10:58:05 last_day(add_months(sysdate, 1))

SQL>

-- 4.3 经过多次测试，每次执行时并不是按照上次指定的秒执行的，而是滞后几秒。这样累计起来，就可能导致执行日期的错误。

-- 所以要想每次都精确地按照秒来执行job，应该手工来指定时间。比如：

-- 4.3.1 在每个月的月底的午夜执行

SQL> select to_char(last_day(add_months(to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd'), 1)), 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(LAST_DAY(AD

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

2008-04-30 00:00:00

-- 4.3.2 在每个月的月底的任意指定时间执行(这里我指定时间为11:24:31)

SQL> select to_char(last_day(add_months(to_date(to_char(sysdate, 'yyyy-mm-dd') || ' 11:24:31', 'yyyy-mm-dd hh24:mi:ss'), 1)), 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(LAST_DAY(AD

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

2008-04-30 11:24:31

SQL>[@more@]