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>
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;
LOG_DATE
-----------------------------------------------
25-MAR-19 09.38.00.866000 PM +08:00
25-MAR-19 09.40.00.920000 PM +08:00
25-MAR-19 09.42.00.998000 PM +08:00
25-MAR-19 09.44.01.037000 PM +08:00
25-MAR-19 09.46.01.078000 PM +08:00
25-MAR-19 09.48.01.143000 PM +08:00
25-MAR-19 09.50.01.171000 PM +08:00
25-MAR-19 09.52.01.206000 PM +08:00
25-MAR-19 09.54.01.272000 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.
Learning is not a spectator sport 
by the way: who would use “sysdate+0.000694444” when “sysdate+numtodsinterval(1, ‘minute’)” is also possible ?
I’ve seen far worse 🙂
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.
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)
Is there a query showing the job completion percentage in DBMS_SCHEDULER?
No, because its indeterminate. You could explicitly control it with session longops.