Oracle Scheduler, the things you ought to know

At the KScope conference in Long Beach, CA one of the most interesting sessions I attended was a session called: Five Features You Ought to Know About the Oracle Scheduler by Eddie Awad. It was a nice presentation where I actually learned stuff I didn’t know. At least not in relation to the Scheduler.

Of course, we are all (or almost all) acquainted with DBMS_JOB to schedule jobs in the Oracle database it had been replaced in Oracle 10.2 with the Oracle Scheduler. You can use the scheduler to schedule jobs (hey, that’s pretty much what the name says), but you can do much more than that. The easiest way to schedule a job is create it and have it run at certain intervals, lets say every day or every week.

When you want to run you job every first day of the month, that’s harder and if you want to run it every last day of the month, that’s even harder. Using the Oracle Scheduler you can create rather complex schemes to run the job. The calendering syntax results in a set of timestamps when the job should be run. Eddie had a lot of examples in his  presentation ranging from rather simple ones like:
Run every hour
 FREQ=HOURLY;INTERVAL=1;
or
Run every 5 minutes:
 FREQ=MINUTELY;INTERVAL=5;
to rather complex ones like:
Run at 10 pm daily from Monday to Friday
 FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22; BYMINUTE=0; BYSECOND=0;
to very complex ones like:
Run on the 60th, 120th and 180th day of the year
FREQ=YEARLY; BYYEARDAY=60,120,180
Run on the last workday of every month, assuming that workday are Monday through Friday
 FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

Building a schedule using these intervals is very powerful, but if you want multiple jobs to use the same schedule it can be a very tedious job to write the intervals every time. And what happens when the schedule changes? You need to change a lot of jobs. Not very Single Point Of Definition.
Luckily Oracle provides us with the possibility to create schedules which can then in turn be used in the scheduled jobs. That way all the jobs that use the same schedule will be changed by changing the schedule once instead of changing all the jobs individually.

Another very powerful tool in the scheduler is the resource manager. Using this tool you can assign resources to certain jobs based on a schedule you created. For instance, you want an update job to run every hour, but during office hours you want to have it run with the lowest priority. Outside office hours I can consume as many resources as it needs.
This would involve creating a schedule in which the office hours are set. Then we would create a resource plan where we can limit the resources for a certain job class. Then, after this, we can create jobs, associated with job classes and have then use less resources during office hours, i.e. when the resource plan is active. Note: resources will only be limited when usage reaches 100%.

Another powerful feature is the possibility to implement a file watcher using the scheduler. This way you can start jobs when a file arrives on a system. This is a feature which is introduced in the version 11g Release 2. This is an example of an event based job. Other events include for instance:

Using these event you can for instance run a job to send out an email or a text message to a DBA that something is wrong and have him/her take appropriate action.

You can view Eddies entire presentation at:
http://www.scribd.com/doc/60199706/Five-Features-You-Ought-to-Know-About-the-Oracle-Scheduler-PPT

You can also read the article he wrote to accompany the presentation at:
http://www.scribd.com/doc/59265286/Five-Features-You-Ought-to-Know-About-the-Oracle-Scheduler