Oracle dbms_job executes jobs with different delays in 5 secs. How make it to run job at specified interval without delays?

Question

I have created simple job via DBMS_JOB package (Tested under oracle 12.2 and 11.2)

begin
  sys.dbms_job.submit(job => :job,
                      what => 'null;',
                      next_date => to_date('19-06-2020 22:26:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate,''MI'')+1/24/60');
  commit;
end;

It should be runned exactly every minute (no seconds delay)

Then I use

select last_date, last_sec, next_date, what,interval from all_jobs where job=:job

to monitor last start time and next start time I grabbed the output every minute and placed it to the table bellow. Here are results of job runs:

| LAST_DATE           | LAST_SEC | NEXT_DATE           | WHAT  | INTERVAL                    |
| 2020-06-19 22:33:02 | 22:33:02 | 2020-06-19 22:34:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:34:07 | 22:34:07 | 2020-06-19 22:35:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:35:13 | 22:35:13 | 2020-06-19 22:36:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:36:18 | 22:36:18 | 2020-06-19 22:37:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:37:24 | 22:37:24 | 2020-06-19 22:38:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:38:30 | 22:38:30 | 2020-06-19 22:39:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:39:05 | 22:39:05 | 2020-06-19 22:40:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:40:10 | 22:40:10 | 2020-06-19 22:41:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:41:16 | 22:41:16 | 2020-06-19 22:42:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:42:21 | 22:42:21 | 2020-06-19 22:43:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:43:27 | 22:43:27 | 2020-06-19 22:44:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:44:02 | 22:44:02 | 2020-06-19 22:45:00 | null; | trunc(sysdate,'MI')+1/24/60 |
| 2020-06-19 22:45:08 | 22:45:08 | 2020-06-19 22:46:00 | null; | trunc(sysdate,'MI')+1/24/60 |

Hmm... NEXT_DATE shows right time, but LAST_DATE / LAST_SEC shows time with delay. The delay of start job time is constantly growing from zero to 30 seconds: 2, 7, 13, 18, 24, 30. Then again from zero: 5, 10, 16, 21, 27 Then again 2, 8, ... etc.

Why does it starts every time not exactly at :00 sec and is constantly growing? How to make start it exactly at desired time?

Generally, I cannot use DBMS_SCHEDULER with calendar syntax. I need to calculate the next job time exactly with pl/sql function according to my complex algorithm.

Addition: The same delay in starting a job can be seen if you set what parameter to something like:

insert into my_table(last_date) values(sysdate);

score 3 · Answer 1 · answered Jun 20 '20 at 02:23

DBMS_JOB has always been subject to scheduling creep, because the interval is calculated from when the job ends, so the interval is what you specify plus the time it took to run the previous job execution. If you need scheduling that precise, then you need to be using Oracle Scheduler (DBMS_SCHEDULER). It will allow you to schedule executions to the second, with no creep. Here are some resources to help you get better acquainted with it:

Below is an example of DDL to create a job that runs once per minute:

BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; bysecond=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/

Oracle dbms_job executes jobs with different delays in 5 secs. How make it to run job at specified interval without delays?

1 Answers1