Oracle Job Scheduler Time Slide

Oracle Database Tips by Donald Burleson

It would appear that all of the above expressions give exactly the same run schedule regardless of which syntax is used.  In practice this not true because PL/SQL expressions can allow the run schedules of jobs to slide.

The scheduler attempts to execute all jobs on time, but in practice there is often a small delay.  When a job is executed, the first thing that happens is the next run date is calculated using the specified repeat interval.  Since most PL/SQL expressions use either the sysdate or systimestamp functions, the actual start date may be slightly later than the job's original next run date.  Over several iterations of the job, this could add up to a noticeable difference between the times the job is expected to run and when it actually does run.

Table 11.9 gives an example of the sort of slide that might be seen if the scheduler is consistently ten seconds late in executing a job that was originally intended to run a 09:00:00 each day.

This issue becomes even more noticeable on shorter repeat intervals such as hourly runs. This behavior can be prevented by always defining PL/SQL expressions that result in a specific time rather than one relative to the current time.  This is typically done using the trunc and round functions to remove the variable components. For example, 'trunc(sysdate) + 1 + 6/24' is always 06:00 tomorrow morning no matter what time it is evaluated because the time component has been truncated.  The earlier examples regularly make use of the trunc function for the same reason.

The calendar syntax does not suffer from the problem of sliding schedules as the repeat intervals it defines are always time specific.  If a component of the calendar string is not defined explicitly, it is defaulted using values from the start date specified when the job or schedule was defined.  For example, a schedule with a start date of 01-JAN-2004 09:45:31 and a calendar string with no byminute clause would actually be assigned byminute=45.  As a result, every next_run_date evaluated using this schedule would have a value of 45 minutes past the hour.

Sometimes it is either not possible or very difficult to define a repeat interval using the calendar syntax or a PL/SQL expression.  In these situations, it might be easier to use a database function which returns a date or timestamp as required.  The my_schedule_function.sql script creates a function which returns a different time interval depending on the contents of the database.

When this script is run against the development environment with a database name of DB10G, the following run schedule is produced:

Next Run Date: 02-JAN-2004 03:04:32

Next Run Date: 03-JAN-2004 03:04:32

Next Run Date: 04-JAN-2004 03:04:32

Next Run Date: 05-JAN-2004 03:04:32

Next Run Date: 06-JAN-2004 03:04:32

PL/SQL procedure successfully completed.

The same result could be achieved by running a different schedule in each environment, but it serves to illustrate the point.

It should now be obvious that there is an almost limitless combination of possible calendar string and PL/SQL expression variations.  The only way to become really confident with repeat intervals is to try as many variations as possible.  The test procedures presented in this section will allow this to be done without having to actually schedule jobs and this, in turn, will save lots of time.