Evaluation interval

As Steven Jobs would often say:

There is one more thing to say about rules. The Scheduler normally checks what to do at the start and the end of each and every job step. In many situations this may be good enough, but there are situations where something more is required. One example is where one step of a job not only depends on the status of a former step, but also on the row count of a table or on the time of day. If this is the case, it would be best if the job step starts running as soon as the condition becomes true; and not after we had to manually intervene to make the Scheduler evaluate the chain again. We can give the chain an evaluation interval to make the Scheduler not only check the status of the job when it starts the job (or a job_step ends), but also to repeat the evaluations on a timed basis. To run these repeated evaluations, we have to specify evaluation_interval. The evaluation interval is the way to make the Scheduler check what to do next for moments other than job starts or at the job_step ends. We will need this as soon as a step can depend on something that is not a part of the chain.

An example could be that a step is allowed to execute only when the Nth step SUCCEEDED and there are less than 1000 sessions in the database. However, it might not be possible to predict the number of sessions in the database. The database may be regularly busy. We want this step to run as soon as enough people disconnect:

':stepn.state=''SUCCEEDED'' AND select count(*) from v$session < 1000'

We can make the Scheduler re-evaluate the chain periodically by setting the evaluation_interval parameter at the time of creating the chain.

The following is an example to define an evaluation interval at the chain creation time:

--/
BEGIN
dbms_scheduler.create_chain
(
comments => 'freezing cold backup', 
evaluation_interval => numtodsinterval (5,'minute'), 
chain_name => '"C_COLD_BACKUP"'
);
END;
/

This creates a chain that has an evaluation interval (which makes the Scheduler check what to do) of five minutes. Forgetting things is human, and Oracle happens to know this. So it has given us the opportunity to add or modify the evaluation interval after the creation of the chain in the form of setting the following attribute:

--/
BEGIN
dbms_scheduler.set_attribute
(
name => 'c_cold_backup', 
attribute => 'evaluation_interval', 
value => numtodsinterval (6,'minute')
);
END;
/

Finally, there is also a way to get rid of this by setting the evaluation_interval to NULL. Use the set_attribute_null procedure for this purpose as follows:

--/
BEGIN
dbms_scheduler.set_attribute_null
(
name => 'c_cold_backup', attribute => 'evaluation_interval'
);
END;
/