Moving to the Scheduler - Part 1

In 10gR1, Oracle introduced the Scheduler, a new way to schedule jobs to run from within Oracle. The old DBMS_JOB mechanism is still there, but the Scheduler has several advantages over its predecessor. This article will take a quick look at those advantages and discuss transitioning from DBMS_JOB/ DBA_JOBS to the Scheduler. My next article will take a more in-depth look at some of the Scheduler's features.

Advantages

The Scheduler supports a lot of advanced operations that DBMS_JOB simply won't do. For example, you can use the Scheduler to:

• Schedule external jobs

• Make one job's execution dependent on the completion of another job

But even the jobs you're currently running using the job scheduling mechanism can benefit from the Scheduler's new features. For example, the Scheduler offers much improved logging. Job errors are logged to table, not to the alert log, and you can choose whether or not a job retries on failure. And each run of a job, whether successful or not, is logged and the logs are retained - unlike the traditional job scheduling mechanism, where only the last job run can really be interpolated from DBA_JOBS.

Some more advantages to moving your jobs to the Scheduler:

• On RAC, jobs can be pinned to one instance or allowed to "float" to the instance with lowest load

• You can group jobs for resource allocation and prioritization (known as "job classes")

• You can set different resource plans to affect your jobs at different times - eg. different resource plan for weekends (known as "windows")

• You can use the same interval syntax as you used for the INTERVAL parameter in DBMS_JOB, or you can use Oracle's new Calendaring syntax, which allows for more complicated expressions

• You can share schedules between multiple jobs

• It's easier to put in outage schedules

• The Scheduler has time zone support

• Job execution times are set and logged in TIMESTAMP WITH TIME ZONE, not DATE, allowing for much greater precision

Getting Started

Let's say we have a job in DBA_JOBS that we want to convert to the Scheduler.

select job, log_user, next_date, next_sec, interval, what
from dba_jobs
where job=152152;

       JOB LOG_USER   NEXT_DATE  NEXT_SEC INTERVAL                  WHAT
---------- ---------- ---------- -------- ------------------------- --------------------
    152152 SCOTT      05/01/2006 01:00:00 trunc(sysdate+1) + 1/24   SCOTTS_PROC;

This job belongs to SCOTT, and executes SCOTTS_PROC every day at 1 am. Let's look at how to create and run a similar job in the Scheduler. The rough equivalents to DBMS_JOB.SUBMIT and DBMS_JOB.RUN are:

DBMS_SCHEDULER.CREATE_JOB is an overloaded procedure; in this example, we'll look at just one of many ways to call it. Here's the call:

begin
dbms_scheduler.create_job
 (job_name => 'SCOTT.RUN_SCOTTS_PROC',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'SCOTT.SCOTTS_PROC',
  start_date=> trunc(sysdate+1)+1/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1;',
  enabled=>true,
  auto_drop=>false,
  comments=>'Converted from job 152152');
end;

There are a lot of things to notice here; let's go through the call line by line.

• Instead of a number, the job's identified by the name you pass as job_name. Job names follow the standard Oracle naming conventions. SYS can create a job for anyone. So, job_name => 'SCOTT.RUN_SCOTTS_PROC' creates a job in SCOTT's schema named RUN_SCOTTS_PROC .

• You need to specify the job type. As in the old job scheduling mechanism, you can schedule anonymous blocks (job_type: 'PLSQL_BLOCK') or stored procedures (job_type: 'STORED_PROCEDURE'). You can also schedule programs that run outside the database (job_type: 'EXECUTABLE') or schedule "job chains" (job_type: 'CHAIN'). A job chain is a sequence of jobs.

• job_action is the equivalent of "what" in dbms_job.submit. Note that if you're scheduling a stored procedure, you don't put a semicolon after the procedure name, as you did in dbms_job.submit . Note also that if you want to pass arguments to a stored procedure, like UPDATE_INDEXES('SCOTT'), you have to either wrap the stored procedure in an anonymous pl/sql block, or set the arguments with a separate call to SET_JOB_ARGUMENT_VALUE - a topic we won't cover here.

• start_date is the first date the job will execute. Note that if you specify the repeat_interval using calendaring syntax, which we'll cover shortly, start-date is used as a reference only - the job won't actually execute until the next date that matches your repeat_interval. For example, if you set the repeat interval to every Thursday, and pass a start date that's on a Tuesday, then the job will execute on the first Thursday following the start date.

• This call passes repeat_interval in calendaring syntax, which we'll discuss below. You could also pass it just as you had it in DBA_JOBS, as 'trunc(sysdate+1) + 1/24'.

• In DBMS_SCHEDULER, unlike DBMS_JOB, the default is that new jobs are disabled. If you want your job to be enabled as soon as you create it, as it would have been with DBMS_JOB, then pass enabled=>true.

• You can control whether or not a one-time job is dropped after it's run by setting auto_drop to FALSE.

• You can pass in comments, which are displayed alongside the job in the static data dictionary view that shows Scheduler jobs.

After you run this call to create_job, there's one more thing to notice: you don't have to COMMIT. The results are immediately visible to all users in the DBA_SCHEDULER_JOBS table:

OWNER JOB_NAME        JOB_ACTION        START_DATE REPEAT_INTERVAL STATE     COMMENTS
----- --------------- ----------------- ---------- --------------- --------- -------------------------
SCOTT RUN_SCOTTS_PROC SCOTT.SCOTTS_PROC 05/08/2006 FREQ=DAILY; BYD SCHEDULED Converted from job 152152

What if you want to run this job right away? Use dbms_scheduler.run_job:

connect scott/tiger
begin
DBMS_SCHEDULER.RUN_JOB (
   job_name => 'RUN_SCOTTS_PROC',
   use_current_session => false);
end;
/

Note that the default for use_current_session is TRUE -- that is, if you don't pass this parameter, RUN_SCOTTS_PROC will run synchronously, ie. in your current session, instead of running asynchronously, ie. being picked up and run by a job slave.

Calendaring Syntax

One of the big improvements in the Scheduler is its calendaring syntax. We saw an example of the calendaring syntax in the create_job call above, 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1;'. It's true that this syntax is much, much more flexible and extensible than expressions like 'trunc(SYSDATE+1) + 1/24', but it can have a bit of a learning curve.

The key to basic Calendaring Syntax is to build it up one piece at a time. The first piece is always the frequency - cron-style, you can set the frequency to yearly, daily, monthly, etc.

FREQ=[YEARLY | MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY] ;

The secpnd piece is to specify when exactly, in the frequency you just specified, the job will run. For example, if you specified YEARLY, you need to say which day(s) of the year the job will run. If you specified HOURLY, say which hour. If you want to run a job Fridays at 11:02 am, first specify the day as Friday, then the hour as 11, then the minute as 22.

For example, to run a job at 11:24 every day:

FREQ=DAILY;BYHOUR=11;BYMINUTE=24;

To run a job every Tuesday at 11:24, you could use any of the following (they are all equivalent):

FREQ=DAILY; BYDAY=TUE; BYHOUR=11; BYMINUTE=24;
FREQ=WEEKLY; BYDAY=TUE; BYHOUR=11; BYMINUTE=24;
FREQ=YEARLY; BYDAY=TUE; BYHOUR=11; BYMINUTE=24;

So far, this isn't much easier than the old-style Interval syntax. However, Calendaring syntax makes it much easier to specify more complex expressions. For example, to run a job every 3rd month on the 11th of the month, use the INTERVAL clause:

FREQ=MONTHLY; INTERVAL=3; BYMONTHDAY=11;

Varying intervals are much easier to express, too. To run a job Tuesday and Thursday at 11, 14 and 22 o'clock:

FREQ=WEEKLY; BYDAY=TUE,THUR; BYHOUR=11,14,22;

For more information on Calendaring syntax, see Chapter 93 of the Oracle 10g Database PL/SQL Packages and Types Reference; for more examples, see Chapter 27 of the Oracle 10g Database Administrator's Guide.

Job Run Log

While a job is running, it won't show up in DBA_JOBS_RUNNING; instead, it's in DBA_SCHEDULER_RUNNING_JOBS. Once the job has executed, log rows are written to DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS. These tables record the exit status (success, failure) of each job run, the run dates and duration, and provide any additional details such as error messages, who stopped a stopped job, etc.

More Job Tools

If you're going to move jobs to the Scheduler, you'll need to know how to change their next run dates (or other attributes), how to disable and reenable them, and how to kill a running job. Fortunately, these are all easy tasks in DBMS_SCHEDULER. Here are the rough equivalents to the DBMS_JOB commands:

Comments:

• dbms_scheduler.set_attribute does the work of dbms_job.next_date, dbms_job.what, dbms_job.interval and more. Pass in the job name, the attribute you want changed, and the value you want it changed to.

• Use DISABLE to break a job, ENABLE to unbreak it. Note: Unlike BROKEN, ENABLE doesn't allow you to set the next run date. But, a freshly ENABLED job that uses calendaring syntax will run the next time specified in the interval (eg. next Tuesday at 10 am), not right away.

• To stop a running job with dbms_job, you need to (1) break the job with dbms_job.broken and (2) kill the session running the job. With the Scheduler, you simply need to call dbms_scheduler.stop_job. You don't need to disable the job first. The job won't try to execute again until the next scheduled time.

Conclusion

In this article, we've taken a quick look at some of the Scheduler's advantage, and gone over the basics of moving jobs from the old job queue to the Scheduler. In my next article, we'll take a more in-depth look at some of the Scheduler's features.

About the author

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.