In heavily loaded system you must have seen that the jobs are not always started at their scheduled time.
REQ_START_DATE.DBA_SCHEDULER_JOB_RIN_DETAILS -- SHOWS WHEN THE JOB WAS SCHEDULED TO RUN
ACTUAL_START_DATE.DBA_SCHEDULER_JOB_RIN_DETAILS -- SHOWD WHEN THE JOB REALLY STARTED
By default the job can start any time after its scheduled time, as soon as there are resources available to run it.
By default the job can start any time after its scheduled time, as soon as there are resources available to run it.
Now what if I don’t want a job to start if could not start with in a defined time (specified interval) after the scheduled time.
What is schedule_limit?
With the scheduler, you can answer the question of how long past its time you're willing to let a job start. You do this by setting the job's schedule_limit attribute value (It can be a value of 1 minute to 99 days.). The following specifies that my TEST.NEW_JOB job must begin within 10 minutes of its scheduled start time:
sys.dbms_scheduler.set_attribute(
name => TEST.NEW_JOB ',
attribute => 'schedule_limit',
value => 10);
If ever this job is delayed for more than 10 minutes past its start time, that run of the job will be skipped. The default schedule_limit attribute is null, which imposes no limit. That is it will start ASAP.
An entry in the job log will be made to reflect the skipped run. select operation,additional_info from dba_scheduler_job_log
OPERATION ADDITIONAL_INFO
TIMED OUT REASON="Recurring job rescheduled after schedule limit exceeded"********************************************************************************
Please be aware of Bug 6071651, Delays executing a job if execution time exceeds SCHEDULE_LIMIT [ID 6071651.8]
Excerpts of the Metalink[ID 6071651.8] note on Bug 6071651:-
Description:
If a job has an execution time that is greater than both its repeat interval and
its schedule limit then there may be excessive delays in scheduling it.
Affects:
Product (Component) | Oracle Server (Rdbms) |
Range of versions believed to be affected | Versions < 11.2 |
Versions confirmed as being affected |
|
Platforms affected | Generic (all / most platforms affected) |
Fixed:
This issue is fixed in |
| |||
Additionally the schedule_limit set to one minute will not prevent the jobs due to run from starting when database starts up many minutes past the one minute due (schedule_limit)
********************************************************************************
Let’s forget the bugs and try to implement the SCHEDULE_LIMIT
exec dbms_lock.sleep (5);
We will use the sleep procedure to create the program, and pass the parameter (seconds to sleep) as an argument, to make it dynamic.
Thus making it a lot easy to play around.
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'TEST_PGM_V2',
program_action => 'dbms_lock.sleep',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.define_program_argument (
program_name => 'TEST_PGM_V2',
argument_name => 'SEC',
argument_position => 1,
argument_type => 'NUMBER',
default_value => '180'); -- in seconds
DBMS_SCHEDULER.enable (name => 'TEST_PGM_V2');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'TEST_SCH_V2',
start_date => Trunc(sysdate),
end_date => NULL,
repeat_interval => 'FREQ=DAILY;BYHOUR=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,0; BYMINUTE=5,10,15,20,25,30,35,40,45,50,55,0;',
comments => 'Every hour');
END;
/
BEGIN
dbms_scheduler.create_job(
job_name => 'TEST__JOB_V2',
job_class => 'default_job_class',
program_name => 'TEST_PGM_V2',
schedule_name => 'TEST_SCH_V2',
enabled => FALSE);
END;
/
--- in case if we have to alter the attribute of the scheduler -- to test with different intervals
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'TEST_SCH_V2',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYHOUR=1,2,3,4,5,6,7,8,9,10,11,12; BYMINUTE=5,10,15,20,25,30,35,40,45,50,55,0;');
END;
/
----
-- to diable enable job , if required ,to play around.
begin
DBMS_SCHEDULER.disable('TEST__JOB_V2');
end;
/
/*---if we try to stop an "running" job,we will get the below error ---*/
ORA-27478: job "USER.TEST__JOB_V2" is running
ORA-06512: at "SYS.DBMS_ISCHED", line 2805
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1753
ORA-06512: at line 2
select * from dba_scheduler_running_jobs; -- to view the RUNNING JOBS
select * from v$scheduler_running_jobs; -- to view the RUNNING JOBS
--we have to stop the job and then disable-
begin
DBMS_SCHEDULER.STOP_JOB('TEST__JOB_V2');
end;
/
begin
DBMS_SCHEDULER.enable('TEST__JOB_V2');
end;
/
---- TO SET THE SCHEDULE_LIMIT
begin
dbms_scheduler.set_attribute(
name =>'TEST__JOB_V2',
attribute => 'schedule_limit',
value => '10');
end;
/
/*---the above format will result in ORA-01867 ---*/
ORA-01867: the interval is invalid
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1870
ORA-06512: at line 2
-- use any of the below two methods to set the schedule_limit
begin
dbms_scheduler.set_attribute(
name =>'TEST__JOB_V2',
attribute => 'schedule_limit',
value => '+000 00:10:00');
end;
/
begin
dbms_scheduler.set_attribute(
name =>'TEST__JOB_V2',
attribute => 'schedule_limit',
value => NUMTODSINTERVAL(5,'MINUTE'));
end;
/
--Query to confirm
select schedule_name,schedule_limit from dba_scheduler_jobs;
--Query to monitor
select a.owner,a.job_name,a.status,a.req_start_date,a.actual_start_date,a.run_duration,a.log_id,
a.log_date,b.log_date,b.operation,b.status,b.additional_info
from dba_scheduler_job_run_details a, dba_scheduler_job_log b
where b.job_name='NEW_JOB'
and a.log_id(+) =b.log_id
order by b.log_id desc
