Long running scheduler jobs

Posted by

One of the nice things about the job scheduler in the Oracle database is the easily interpreted interval settings you can apply for job frequency. The days of cryptic strings like “sysdate+0.000694444” when all you really wanted to say was “Just run this job every minute” are a thing of the past. I covered how to get the database to convert interval strings into real execution dates here 

But it raises the question: What if I have a job that is scheduled to run every minute, but it takes more than 1 minute to run? Will the scheduler just crank out more and more concurrent executions of that job? Will I swamp my system with ever more background jobs? So I thought I’d find out with a simple test.

I created a table which will record the start and end time for executions of a procedure, and then crafted that procedure to always run for at least 2 minutes using dbms_lock.sleep. (If you are on 18c, you can replace this with dbms_session.sleep to avoid the need for an explicit grant.) Then I set this procedure to be run every minute via dbms_scheduler.

SQL> create table t ( tag varchar2(10), d date);

Table created.

SQL> create or replace
  2  procedure P is
  3  begin
  4    insert into t values ('start',sysdate);
  5    commit;
  6    dbms_lock.sleep(120);
  7    insert into t values ('end',sysdate);
  8    commit;
  9  end;
 10  /

Procedure created.

SQL> begin
  2    dbms_scheduler.create_job (
  3      job_name        => 'JOB1',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'begin p; end;',
  6      start_date      => systimestamp,
  7      repeat_interval => 'freq=minutely;bysecond=0;',
  8      enabled         => true);
  9  end;
 10  /

PL/SQL procedure successfully completed.

I waited 20 minutes and then looked at both my table and the scheduler logs to see how many concurrent executions were recorded.

SQL> select * from t order by d, tag;

TAG        D
---------- -------------------
start      25/03/2019 21:36:00
end        25/03/2019 21:38:00
start      25/03/2019 21:38:00
end        25/03/2019 21:40:00
start      25/03/2019 21:40:00
end        25/03/2019 21:42:00
start      25/03/2019 21:42:01
end        25/03/2019 21:44:01
start      25/03/2019 21:44:01
end        25/03/2019 21:46:01
start      25/03/2019 21:46:01
end        25/03/2019 21:48:01
start      25/03/2019 21:48:01
end        25/03/2019 21:50:01
start      25/03/2019 21:50:01
end        25/03/2019 21:52:01
start      25/03/2019 21:52:01
end        25/03/2019 21:54:01

18 rows selected.

SQL> select log_date
  2  from   dba_scheduler_job_log
  3  where job_name = 'JOB1'
  4  order by log_date;

25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00
25-MAR-19 PM +08:00

9 rows selected.

As you can see, the scheduler is not going to swamp your system. It will not run the “next” occurrence of your submitted job until the current execution has completed. So even though we requested an execution each minute, we are bound by the run time duration of the job itself. Once additional nice thing is that once the job has finished, the scheduler immediately sees that the next execution is overdue and launches the next job straight away. So no need to worry about an excessive number of jobs all running together.

Just as an aside, when you have a scheduler job that is “always” in a running state, then you need to take care when dropping the job because by default you cannot drop a running job. In such instances, you can always add the FORCE parameter to kill the current execution and remove the job from the scheduler.

SQL> exec dbms_scheduler.drop_job('JOB1')
BEGIN dbms_scheduler.drop_job('JOB1'); END;

ERROR at line 1:
ORA-27478: job "MCDONAC"."JOB1" is running
ORA-06512: at "SYS.DBMS_ISCHED", line 274
ORA-06512: at "SYS.DBMS_SCHEDULER", line 753
ORA-06512: at line 1

SQL> exec dbms_scheduler.drop_job('JOB1',force=>true)

PL/SQL procedure successfully completed.



  1. The question I have is, if you attempt to start the job using DBMS_SCHEDULER.RUN_JOB while the job is already running, does that have any impact at all? I assume the job will simply wait until its scheduled start time before starting again.

    1. Two different sessions can both issue: dbms_scheduyler.run_job and they’ll both run at the same time. (This is different to the scheduler itself, which wont start another one until the current has finished)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.