Complex Date Rules for Job Execution
- By Dr. Tim Hall
Prior to Oracle10g, the only way to define a
jobs repeat interval was to use a PL/SQL expression that evaluated
to a date. In Oracle10g and beyond, the calendar syntax is the preferred
way to define a jobs repeat interval, although PL/SQL expression can
still be used if they evaluate to a timestamp. In this section
we will compare how each method works.
The previous section used the
test_calendar_string.sql procedure to display the run schedule
expected for a specific calendar string. Before any
comparisons between the possible scheduling methods can be done, a
way to test the PL/SQL expressions that are used to schedule jobs
using dates and timestamps is needed.
The test_date_string
procedure listed below is similar to the test_calendar_string
procedure, but it displays run dates defined by interval strings
that might be used when scheduling jobs via the dbms_job package.
*
test_date_string.sql
set
serveroutput on;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR
REPLACE PROCEDURE test_date_string(
p_interval IN VARCHAR2,
p_iterations IN NUMBER DEFAULT 5)
AS
l_interval
VARCHAR2(1000) := p_interval;
l_start_date DATE :=
TO_DATE('01-JAN-2004 03:04:32',
'DD-MON-YYYY HH24:MI:SS');
l_next_run_date DATE;
l_start_date_str VARCHAR2(100);
BEGIN
See download for full script
FOR i IN 1 .. p_iterations LOOP
l_start_date_str := ?TO_DATE(??? ||
TO_CHAR(l_start_date, ?DD-MON-YYYY HH24:MI:SS?) ||
???,??DD-MON-YYYY HH24:MI:SS??)?;
l_interval := REPLACE(LOWER(p_interval), ?sysdate?,
l_start_date_str);
EXECUTE IMMEDIATE ?SELECT ? || l_interval || ? INTO :return
FROM dual?
INTO l_next_run_date;
DBMS_OUTPUT.put_line('Next Run Date: ' || l_next_run_date);
l_start_date := l_next_run_date;
END LOOP;
END;
/
The test_timestamp_string procedure listed
below is a copy of the test_date_string procedure that has been
adjusted to work with timestamps.
*
test_timestamp_string.sql
--
*************************************************
-- Copyright ? 2015 by Rampant TechPress
-- *************************************************
set
serveroutput on;
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR
REPLACE PROCEDURE test_timestamp_string(
p_interval IN VARCHAR2,
p_iterations IN NUMBER DEFAULT 5)
AS
l_interval VARCHAR2(1000)
:= p_interval;
l_start_ts TIMESTAMP :=
TO_TIMESTAMP('01-JAN-2004 03:04:32',
'DD-MON-YYYY HH24:MI:SS');
l_next_run_ts TIMESTAMP;
l_start_ts_str VARCHAR2(100);
BEGIN
See download for full script
FOR i IN 1 .. p_iterations LOOP
l_start_ts_str := ?TO_TIMESTAMP(??? ||
TO_CHAR(l_start_ts, ?DD-MON-YYYY HH24:MI:SS?) ||
???,??DD-MON-YYYY HH24:MI:SS??)?;
l_interval := REPLACE(LOWER(p_interval), ?systimestamp?,
l_start_ts_str);
EXECUTE IMMEDIATE ?SELECT ? || l_interval || ? INTO :return
FROM dual?
INTO l_next_run_ts;
DBMS_OUTPUT.put_line('Next Run Date: ' || l_next_run_ts);
l_start_ts := l_next_run_ts;
END LOOP;
END;
/
The best way to come to grips with defining
repeat intervals and comparing the different methods available is
looking at some examples. Table 3.11 below lists a range of
repeat intervals along with expressions than can be used to achieve
them.
The date expressions can be used to schedule jobs using
the dbms_job package, while the timestamp and calendar syntax
expressions can be used for jobs scheduled using the dbms_scheduler
package. Where possible, a literal and interval
literal example is given along with an example of the output
generated by the test procedures.
Run job
Every day.
?sysdate + 1?
?systimestamp + 1?
?sysdate +
interval ??1?? day?
?systimestamp + interval ??1?? day?
?freq=daily;?
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
Midnight every night.
?trunc(sysdate) + 1?
?trunc(systimestamp) + 1?Run job
?trunc(sysdate) + interval ??1?? day?
?trunc(systimestamp) + interval ??1?? day?
?freq=daily;
byhour=0; byminute=0; bysecond=0;?
Next Run
Date: 02-JAN-2004 00:00:00
Next Run Date: 03-JAN-2004 00:00:00
Next Run Date: 04-JAN-2004 00:00:00
Next Run Date: 05-JAN-2004 00:00:00
Next Run Date: 06-JAN-2004 00:00:00
Run job
6:00 AM every day.
?trunc(sysdate) + 1 + 6/24?
?trunc(systimestamp) + 1 + 6/24?
?trunc(sysdate) + interval ??1 6?? day to hour ?
?trunc(systimestamp) + interval ??1 6?? day to hour?
?freq=daily;
byhour=6; byminute=0; bysecond=0;?
Next Run
Date: 01-JAN-2004 06:00:00
Next Run Date: 02-JAN-2004 06:00:00
Next Run Date: 03-JAN-2004 06:00:00
Next Run Date: 04-JAN-2004 06:00:00
Next Run Date: 05-JAN-2004 06:00:00
Run job
Every hour.
?sysdate + 1/24?
?systimestamp + 1/24?
?sysdate +
interval ??1?? hour?
?systimestamp + interval ??1?? hour?
?freq=hourly;?
Next Run
Date: 01-JAN-2004 04:04:32
Next Run Date: 01-JAN-2004 05:04:32
Next Run Date: 01-JAN-2004 06:04:32
Next Run Date: 01-JAN-2004 07:04:32
Next Run Date: 01-JAN-2004 08:04:32
Run job
Every hour, on the hour.
?trunc(sysdate, ??HH24??) + 1/24?
?trunc(systimestamp, ??HH24??) + 1/24?
?trunc(sysdate, ??HH24??) + interval ??1?? hour?
?trunc(systimestamp, ??HH24??) + interval ??1?? hour?
?freq=hourly;
byminute=0; bysecond=0;?
Next Run
Date: 01-JAN-2004 04:00:00
Next Run Date: 01-JAN-2004 05:00:00
Next Run Date: 01-JAN-2004 06:00:00
Next Run Date: 01-JAN-2004 07:00:00
Next Run Date: 01-JAN-2004 08:00:00
Run job
Every minute.
?sysdate + 1/24/60?
?systimestamp + 1/24/60?
?sysdate +
interval ??1?? minute?
?systimestamp + interval ??1?? minute?
?freq=minutely;?
Next Run
Date: 01-JAN-2004 03:05:32
Next Run Date: 01-JAN-2004 03:06:32
Next Run Date: 01-JAN-2004 03:07:32
Next Run Date: 01-JAN-2004 03:08:32
Next Run Date: 01-JAN-2004 03:09:32
Run job
Every minute, on the minute.
?trunc(sysdate, ??MI??) + 1/24/60?
?trunc(systimestamp, ??MI??) + 1/24/60?
See download for full script
?trunc(sysdate, ??MI??) + interval ??1?? minute?
?trunc(systimestamp, ??MI??) + interval ??1?? minute?
?freq=minutely; bysecond=0;?
Next Run
Date: 01-JAN-2004 03:05:00
Next Run Date: 01-JAN-2004 03:06:00
Next Run Date: 01-JAN-2004 03:07:00
Next Run Date: 01-JAN-2004 03:08:00
Next Run Date: 01-JAN-2004 03:09:00
Run job
Every hour.
?sysdate + 1/24?
?systimestamp + 1/24?
See download for full script
?sysdate +
interval ??1?? hour?
?systimestamp + interval ??1?? hour?
?freq=hourly;?
Next Run
Date: 01-JAN-2004 04:04:32
Next Run Date: 01-JAN-2004 05:04:32
Next Run Date: 01-JAN-2004 06:04:32
Next Run Date: 01-JAN-2004 07:04:32
Next Run Date: 01-JAN-2004 08:04:32
Run job
Every hour, on the hour.
?trunc(sysdate, ??HH24??) + 1/24?
?trunc(systimestamp, ??HH24??) + 1/24?
See download for full script
?trunc(sysdate, ??HH24??) + interval ??1?? hour?
?trunc(systimestamp, ??HH24??) + interval ??1?? hour?
?freq=hourly;
byminute=0; bysecond=0;?
Next Run
Date: 01-JAN-2004 04:00:00
Next Run Date: 01-JAN-2004 05:00:00
Next Run Date: 01-JAN-2004 06:00:00
Next Run Date: 01-JAN-2004 07:00:00
Next Run Date: 01-JAN-2004 08:00:00
Run job
Every Monday at 9:00 AM
?trunc(next_day(sysdate, ??MONDAY??)) + 9/24?
?trunc(next_day(systimestamp, ??MONDAY??)) + 9/24?
See download for full script
?trunc(next_day(sysdate, ??MONDAY??)) + interval ??9?? hour?
?trunc(next_day(systimestamp, ??MONDAY??)) + interval ??9??hour?
?freq=weekly;
byday=mon; byhour=9; byminute=0; bysecond=0;?
Next Run
Date: 05-JAN-2004 09:00:00
Next Run Date: 12-JAN-2004 09:00:00
Next Run Date: 19-JAN-2004 09:00:00
Next Run Date: 26-JAN-2004 09:00:00
Next Run Date: 02-FEB-2004 09:00:00
Run job Every Monday,
Wednesday and Friday at 6:00 AM
?trunc(least(next_day(sysdate, ??monday??), next_day(sysdate, ??wednesday??),
next_day(sysdate, ??friday??))) + (6/24)?
See download for full script
?trunc(least(next_day(systimestamp, ??monday??),
next_day(systimestamp, ??wednesday??), next_day(systimestamp, ??friday??)))
+ (6/24)?
?trunc(least(next_day(sysdate,??monday??), next_day(sysdate, ??wednesday??),
next_day(sysdate, ??friday??))) + interval ??6?? hour?
?trunc(least(next_day(systimestamp, ??monday??),
next_day(systimestamp, ??wednesday??), next_day(systimestamp, ??friday??)))
+ interval ??6?? hour?
?freq=weekly; byday=mon,wed,fri; byhour=6; byminute=0; bysecond=0;?
Next Run
Date: 02-JAN-2004 06:00:00
Next Run Date: 05-JAN-2004 06:00:00
Next Run Date: 07-JAN-2004 06:00:00
Next Run Date: 09-JAN-2004 06:00:00
Next Run Date: 12-JAN-2004 06:00:00
Run job
First Monday of each quarter
?next_day(add_months(trunc(sysdate, ??q??), 3), ??monday??)?
See
download for full script
?next_day(add_months(trunc(systimestamp, ??q??), 3), ??monday??)?
?freq=monthly; bymonth=1,4,7,10; byday=1mon?
Next Run
Date: 05-APR-2004 00:00:00
Next Run Date: 05-JUL-2004 00:00:00
Next Run Date: 04-OCT-2004 00:00:00
Next Run Date: 03-JAN-2005 00:00:00
Next Run Date: 04-APR-2005 00:00:00
Table 3.11 - Repeat intervals and the
expressions than can be used to achieve them.
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 jobs 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 3.12
gives an example of the sort of slide that might be seen if the
scheduler is consistently 10 seconds late in executing a job that
was originally intended to run a 09:00:00 each day.
RUN |
ACTUAL START DATE |
NEXT RUN DATE |
1 |
01-JAN-2004 09:00:10 |
02-JAN-2004 09:00:10 |
2 |
02-JAN-2004 09:00:20 |
03-JAN-2004 09:00:20 |
3 |
03-JAN-2004 09:00:30 |
04-JAN-2004 09:00:30 |
4 |
04-JAN-2004 09:00:40 |
05-JAN-2004 09:00:40 |
5 |
05-JAN-2004 09:00:50 |
06-JAN-2004 09:00:50 |
6 |
06-JAN-2004 09:01:00 |
07-JAN-2004 09:01:00 |
Table 3.12 ? The time slide phenomenon
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.
my_schedule_function.sql
--
*************************************************
-- Copyright ? 2015 by Rampant TechPress
-- Requires
the following grant:
-- grant select on v_$database to job_user;
-- **********************************************************
CREATE OR
REPLACE FUNCTION my_schedule_function (
p_timestamp IN TIMESTAMP)
RETURN TIMESTAMP
AS
l_db_name v$database.name%TYPE;
l_timestamp TIMESTAMP;
BEGIN
See download for full script
SELECT name
INTO l_db_name
FROM v$database;
CASE
l_db_name
WHEN 'PROD' THEN l_timestamp := p_timestamp + INTERVAL '10'
MINUTE;
WHEN 'TEST' THEN l_timestamp := p_timestamp + INTERVAL '1'
HOUR;
ELSE l_timestamp := p_timestamp + INTERVAL '1' DAY;
END CASE;
RETURN
l_timestamp;
END;
/
When this script is run against the development
environment with a database name of DB10G, the following run
schedule is produced:
SQL1> exec
test_timestamp_string('my_schedule_function(systimestamp)');
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
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 chapter will allow this to be
done without having to actually schedule jobs, which in turn will
save lots of time.
 |
This is an excerpt from the book "Oracle
Job Scheduling" by Dr. Tim Hall. You can buy it direct
from the publisher for 30%-off and get instant access to the
code depot of Oracle job scheduling scripts. |