Handy Oracle Scheduler Troubleshooting Tip

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.

I’ve found the following query handy for investigating an ORA-12012 in an alert log. ORA-12012 is a dbms_scheduler execution error and it provides a job number without a name, where the Scheduler views give job names without numbers. So, to relate the job number to a name:

SELECT d.job_name, d.job_action
  FROM dba_scheduler_jobs d JOIN sys.scheduler$_job s
    ON d.job_action = s.program_action
 WHERE s.obj# = &job_number;

Maybe this will be useful for others as well.

Comments Off | Filed in Oracle, Tips | Tags: dbms_scheduler

---

The Oracle Scheduler, Daylight Saving Time and Timezones

The Oracle Scheduler is a very powerful and flexible scheduling engine that is part of the Oracle database. In this article, we will observe how the Scheduler behaves when daylight saving time (DST) begins and ends in time zones that use DST.

The datetime value is sufficient for Oracle to determine whether daylight saving time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight saving goes into or comes out of effect. For example, in the US/Pacific region, when daylight saving goes into effect (as of this writing the next one is on Sunday March 13, 2011 at 2:00 a.m.), the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight saving goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 a.m. and 2 a.m. is repeated (source).

The question is, what happens to your repeating Oracle Scheduler jobs during these boundary periods?

Let’s say you have a job you want to schedule every 6 hours starting at 5 a.m. You want the job to run every day at 5 a.m., 11 a.m., 5 p.m. and 11 p.m. (or 5, 11, 17 and 23 in HH24). The calendar expression for this schedule (the value of the repeat_interval in DBMS_SCHEDULER.create_job for example) could be defined in two ways:

1. ‘FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0′
2. ‘FREQ=HOURLY; INTERVAL=6′

What I am going to show you next are important differences between the two calendar expressions, specifically relating to the boundary cases and timezone definition.

For the test below, I am going to use a very handy procedure: DBMS_SCHEDULER.evaluate_calendar_string. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job or window will be. This is very useful for testing the correct definition of the calendar string without having to actually schedule the job.

We’ll start by setting the value of nls_timestamp_tz_format. This parameter defines the default timestamp with time zone format to use with the TO_TIMESTAMP_TZ functions. I made sure that both TZR and TZD are part of the format.

TZR represents the time zone region name. Examples are ‘Australia/North’, ‘UTC’, and ‘Singapore’. TZD represents an abbreviated form of the time zone region name with daylight saving information. Examples are ‘PST’ for US/Pacific standard time and ‘PDT’ for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view (source).

    eddie@DB112> SET SERVEROUTPUT ON;
    eddie@DB112> ALTER SESSION
      2  SET nls_timestamp_tz_format = 'MM-DD-YYYY HH24:MI:SS tzr tzd';

    Session altered.

Next I declare an inline procedure that is basically a wrapper around evaluate_calendar_string , just to save typing and repetition as I will be calling it multiple times:

eddie@DB112> DECLARE
  2     PROCEDURE list_next_run_dates (
  3        start_date_i        IN TIMESTAMP WITH TIME ZONE,
  4        calendar_string_i   IN VARCHAR2,
  5        date_count_i        IN INTEGER DEFAULT 8 )
  6     IS
  7        l_return_date_after   TIMESTAMP WITH TIME ZONE;
  8        l_next_run_date       TIMESTAMP WITH TIME ZONE;
  9     BEGIN
 10        l_return_date_after := start_date_i;
 11
 12        FOR i IN 1 .. date_count_i
 13        LOOP
 14           DBMS_SCHEDULER.evaluate_calendar_string (
 15              calendar_string     => calendar_string_i,
 16              start_date          => start_date_i,
 17              return_date_after   => l_return_date_after,
 18              next_run_date       => l_next_run_date);
 19
 20           DBMS_OUTPUT.put_line (l_next_run_date);
 21
 22           l_return_date_after := l_next_run_date;
 23        END LOOP;
 24     END list_next_run_dates;

Then I call list_next_run_dates multiple times, each time with a different start date and calendar string. In all cases, the month, day and year portion of the start date is the same, March 11, 2011. I made sure that the run dates span the period when daylight saving goes into effect (March 13, 2011 at 2:00 a.m.):

 25  BEGIN
 26   DBMS_OUTPUT.put_line ('1: 03-11-2011 23:00:00 US/Pacific : FREQ=DAILY');
 27   list_next_run_dates (
 28      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 US/Pacific'),
 29      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 30
 31   DBMS_OUTPUT.put_line ('2: 03-11-2011 23:10:00 US/Pacific : FREQ=DAILY');
 32   list_next_run_dates (
 33      TO_TIMESTAMP_TZ ('03-11-2011 23:10:00 US/Pacific'),
 34      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 35
 36   DBMS_OUTPUT.put_line ('3: 03-11-2011 23:00:00 -08:00 : FREQ=DAILY');
 37   list_next_run_dates (
 38      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 -08:00'),
 39      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 40
 41   DBMS_OUTPUT.put_line ('4:03-11-2011 23:00:00 US/Pacific : FREQ=HOURLY');
 42   list_next_run_dates (
 43      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 US/Pacific'),
 44      'FREQ=HOURLY; INTERVAL=6');
 45
 46   DBMS_OUTPUT.put_line('5: 03-11-2011 23:10:00 US/Pacific : FREQ=HOURLY');
 47   list_next_run_dates (
 48      TO_TIMESTAMP_TZ ('03-11-2011 23:10:00 US/Pacific'),
 49      'FREQ=HOURLY; INTERVAL=6');
 50
 51   DBMS_OUTPUT.put_line ('6: 03-11-2011 23:00:00 -08:00 : FREQ=HOURLY');
 52   list_next_run_dates (
 53      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 -08:00'),
 54      'FREQ=HOURLY; INTERVAL=6');
 55  END;
 56  /

Let’s examine the results:

Specifying FREQ=DAILY does not account for DST change. We lose one hour between these two runs as there are only five hours between “03-12-2011 23:00:00 US/Pacific PST” and “03-13-2011 05:00:00 US/Pacific PDT”. Notice that PST becomes PDT when DST starts:

1: 03-11-2011 23:00:00 US/Pacific : FREQ=DAILY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 05:00:00 US/Pacific PDT
03-13-2011 11:00:00 US/Pacific PDT
03-13-2011 17:00:00 US/Pacific PDT
03-13-2011 23:00:00 US/Pacific PDT

The second test is the same as the first except that I add 10 minutes to the start date. The result stays the same. The start date provides the month, day and year after which the job can run. It also provides the timezone. However, the hour, minute and seconds are read from the calendar string, “BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0″:

2: 03-11-2011 23:10:00 US/Pacific : FREQ=DAILY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 05:00:00 US/Pacific PDT
03-13-2011 11:00:00 US/Pacific PDT
03-13-2011 17:00:00 US/Pacific PDT
03-13-2011 23:00:00 US/Pacific PDT

Again, there is no change in the result if we use a timezone offset (-08:00) instead of a timezone region (US/Pacific). Because of the fact that we are using FREQ=DAILY, the DST is not taken into account. Moreover, a timezone offset is not DST aware.

3: 03-11-2011 23:00:00 -08:00 : FREQ=DAILY
03-12-2011 05:00:00 -08:00
03-12-2011 11:00:00 -08:00
03-12-2011 17:00:00 -08:00
03-12-2011 23:00:00 -08:00
03-13-2011 05:00:00 -08:00
03-13-2011 11:00:00 -08:00
03-13-2011 17:00:00 -08:00
03-13-2011 23:00:00 -08:00

Now, let’s switch to FREQ=HOURLY. Here, we are using a timezone region which means that we care about DST. The calendar expression in this case is an interval of 6 hours. This interval is preserved when DST changes. There are 6 hours between these two dates: “03-12-2011 23:00:00 US/Pacific PST” and “03-13-2011 06:00:00 US/Pacific PDT”. However, notice that the scheduled hours have changed from 5,11,17,23 to 6,12,18,00:

4:03-11-2011 23:00:00 US/Pacific : FREQ=HOURLY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 06:00:00 US/Pacific PDT
03-13-2011 12:00:00 US/Pacific PDT
03-13-2011 18:00:00 US/Pacific PDT
03-14-2011 00:00:00 US/Pacific PDT

The calendar expression ‘FREQ=HOURLY; INTERVAL=6′ does not contain hours, minutes and seconds definitions. In this case, where the hours, minutes and seconds are not explicitly defined in the calendar string, their values are defaulted from the start date. That’s why we see 10 minutes added to all the scheduled runs:

5: 03-11-2011 23:10:00 US/Pacific : FREQ=HOURLY
03-12-2011 05:10:00 US/Pacific PST
03-12-2011 11:10:00 US/Pacific PST
03-12-2011 17:10:00 US/Pacific PST
03-12-2011 23:10:00 US/Pacific PST
03-13-2011 06:10:00 US/Pacific PDT
03-13-2011 12:10:00 US/Pacific PDT
03-13-2011 18:10:00 US/Pacific PDT
03-14-2011 00:10:00 US/Pacific PDT

If we use a timezone offset instead of a timezone region, DST is not honored:

6: 03-11-2011 23:00:00 -08:00 : FREQ=HOURLY
03-12-2011 05:00:00 -08:00
03-12-2011 11:00:00 -08:00
03-12-2011 17:00:00 -08:00
03-12-2011 23:00:00 -08:00
03-13-2011 05:00:00 -08:00
03-13-2011 11:00:00 -08:00
03-13-2011 17:00:00 -08:00
03-13-2011 23:00:00 -08:00

Lessons Learned:

• When checking the various DBMS_SCHEDULER time fields make sure you are using a NLS_TIMESTAMP_TZ_FORMAT that display’s the timezone information, more specifically the “TZR TZD” mask so that you can quickly see in what format the data is stored.
• Timezone offsets are by definition not DST aware. So any job using an offset as timezone will run at a wrong time after a DST change.
• To have a job that automatically adjusts for DST you need to make sure it’s defined with a timezone region name that actually uses DST rules.
• If you want your daily repeating job to run at specific hours every day and you do not want these hours to change when DST changes, make sure you have a calendar expression that uses FREQ=DAILY and define the hours in “BYHOUR=…”.
• If you want your daily repeating job to run at a specific hourly interval and you do not mind the hour of day change when DST changes, make sure you have a calendar expression that uses FREQ=HOURLY and define the interval in “INTERVAL=…”.

Sources and resources:

• DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained – MOS ID 467722.1
• Switching to wintertime and Wintertime (again) by Freek D’Hooge.
• DBMS_SCHEDULER – Oracle Database PL/SQL Packages and Types Reference 11g Release 2 (11.2)
• Database Resource Management and Task Scheduling – Oracle® Database Administrator’s Guide 11g Release 2 (11.2)

6 Comments | Filed in Oracle, Tips | Tags: dbms_scheduler

---

eddie@DB112> SELECT banner FROM v$version;

BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

eddie@DB112> CREATE TABLE my_table
  2  NOPARALLEL
  3  AS
  4     SELECT *
  5       FROM all_objects
  6      WHERE ROWNUM <= 10000;

Table created.

eddie@DB112> EXECUTE dbms_stats.gather_table_stats(user,'MY_TABLE');

PL/SQL procedure successfully completed.

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (my_table) */
  4              COUNT (*) FROM my_table;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    23   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| MY_TABLE | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (my_table) */
  4              COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MY_TABLE | 10000 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
Plan hash value: 3996063390

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MY_TABLE | 10000 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------

eddie@DB112> EXPLAIN PLAN
  2     FOR
  3        SELECT /*+ PARALLEL (t) */
  4              COUNT (*) FROM my_table t;

Explained.

eddie@DB112> SELECT * FROM TABLE (DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2272413588

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    23   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| MY_TABLE | 10000 |    23   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------