Schedule Away the Routine

Free up DBA time by using the new job scheduler.

Routine tasks are boring. If you perform them manually, you wind up doing the same task over and over, with the risk that one day you might slip up and skip a step or make some other mistake. Oracle Database 10g's new built-in job scheduler gives you powerful capabilities for scheduling routine work. Using the scheduler, you can define work to be done, specify when it is to be done, and monitor the work so that you can correct any problems. You can even control the database resources and priority assigned to the jobs you schedule, ensuring that the most important work gets done first, without creating an overwhelming drain on other system activity.

Architecture

You access the new scheduler through the DBMS_SCHEDULER package or via Oracle Enterprise Manager 10g Database Control. Figure 1 shows the scheduler elements most directly related to job creation and execution. A job combines a program with a schedule. The program defines what is to be run. For example, a program can be a PL/SQL block, a stored procedure, or an operating system script. The schedule defines when the program runs. For a one-off job, a schedule will include only a start time. For repeating jobs, you can specify a start time; a repetition schedule; and, optionally, an end time. Each time a job runs is considered a job instance. Jobs, schedules, and programs are the three core components you must master to begin using the scheduler.

Job classes, shown in Figure 1, connect the scheduling system with the resource management system, giving you control over how database resources are allocated to running jobs. Use a job class to place a job into a resource consumer group, which is a group of sessions sharing an allocation of CPU time, parallelism, and other resources. You can then use resource plans to control the allocation of those resources to that and other consumer groups. Figure 2 shows how scheduler windows can control when different resource plans take effect. Figure 2 also shows a window group, which groups like windows into a single entity. Windows and window groups give you a great deal of control over how you can allocate database resources to different job classes. 

Figure 1: Core scheduler components

Figure 2: Scheduler resource management

Getting Started

To create and run jobs, you need to have at least the CREATE JOB system privilege. If you're the DBA, you'll have CREATE JOB, and all other scheduler privileges, through the SCHEDULER_ADMIN role, which is granted to the DBA role. You can grant CREATE JOB to database users to allow them to create and run their own jobs, in their own schemas. For example, the following grants CREATE JOB to user gennick:

GRANT CREATE JOB TO gennick;

Unlike when you are using DBMS_JOB, you do not need to set an initialization parameter to start a job coordinator background process. If you create jobs with the new scheduler, your instance will start a job coordinator background process automatically, when it is needed.

If you plan to use resource plans and consumer groups to control allocation of database resources, set your instance's RESOURCE_LIMIT parameter to TRUE. You can do that via ALTER SYSTEM, as follows:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Last, if you granted CREATE JOB to a non-DBA user and you want that user to be able to schedule jobs by using Database Control's graphical user interface, you also need to grant that user the SELECT ANY DICTIONARY system privilege.

You can also use the DBMS_SCHEDULER package. Invoking DBMS_SCHEDULER via SQL*Plus gives you full command-line access to the scheduler. DBMS_SCHEDULER also provides the ability to embed scheduler functionality within user applications.

Creating Your First Job

To create a recurring job, you first need to define the program and the schedule. Suppose you have an external-table load you need to perform every other evening at 6:00 p.m. Begin by creating a scheduler program that provides a PL/SQL block to perform the load. The PL/SQL block in Listing 1 issues a call to DBMS_SCHEDULER.CREATE_PROGRAM, and the PL/SQL block provided as the program action contains a simple INSERT...SELECT FROM statement for loading new customers from the external table.

Next, create a schedule on which to run the program. Listing 2 shows an invocation of DBMS_SCHEDULER.CREATE_SCHEDULE for creating my PERIODICLOADS schedule. The comments and schedule name arguments are fairly straightforward. Comments may be any text that reminds you of why you created the schedule. The schedule_ name parameter specifies the owner and the name of the schedule. Like programs and jobs, schedules are schema objects, and their names must conform to the same naming rules as for tables, indexes, and so forth.

The start_date is a TIMESTAMP WITH TIME ZONE value that specifies when the schedule becomes effective. 

This schedule becomes effective at the beginning of Monday, March 22, 2004, U.S. Eastern Time.

The scheduler introduces a powerful calendaring syntax you can use to schedule repeating jobs. The syntax has three components: frequency, interval, and time of day. You still have the option of basing the repeat interval on a PL/SQL expression—if you define a schedule inline with a job—but I believe you're likely to find the calendaring syntax sufficient for all but the most unusual of schedules. The following is the calendaring syntax in the repeat_interval argument in my PERIODICLOADS schedule:

FREQ=DAILY;INTERVAL=2;BYHOUR=18;

BYMINUTE=0;BYSECOND=0

FREQ=DAILY specifies that the repeat interval between successive runs of any job based on the PERIODICLOADS schedule will be computed with units that are one day, or 24 hours, in length. INTERVAL=2 specifies the number of units, two days in this case. The three BY values—BYHOUR=18, BYMINUTE=0, and BYSECOND=0—specify the time of day at which the job runs, on a 24-hour clock. The PERIODICLOADS schedule created in Listing 2 will result in the following job runtimes:

• The first run will be Monday, 22 March 2004, at 6:00 p.m.
• The second run will be two days after Monday, 22 March 2004, which will be Wednesday, 24 March, at 6:00 p.m.
• The third run will be two days after two days after Monday, 22 March 2004, which works out to Friday, 26 March, at 6:00 p.m.

One very nice feature of the calendaring syntax is that it eliminates the schedule drift you could get with DBMS_JOB. The start time of one job instance does not influence the start time of the next. Instead, schedule repetition is always recomputed with respect to the schedule's start_date.

The calendaring syntax is quite versatile. Not only can you specify single values for any of the BY keywords but you can also specify lists of values. For example, if you wanted periodic loads to always run on Mondays, Wednesdays, and Fridays, you could specify the repeat interval as follows:

FREQ=WEEKLY;INTERVAL=1;

BYDAY=MON,WED,FRI;BYHOUR=18;BYMINUTE=0;

BYSECOND=0

This schedule would repeat each week (INTERVAL=1); it would trigger jobs on Monday, Wednesday, and Friday of each week (BYDAY=MON,WED,FRI); and those jobs would run at 6:00 p.m. on each of those days (BYHOUR=18; 

BYMINUTE=0;BYSECOND=0).

After you have defined the program and the schedule, the next step is to link those two objects together in the form of a job. Listing 3 shows a call to DBMS_SCHEDULER.CREATE_JOB that creates a job named LOADNEWCUSTOMERS. The job will run the LOADCUSTOMERS program according to the PERIODICLOADS schedule.

Separating schedules, programs, and jobs can actually reduce your work. You can create a new job anytime based on the PERIODICLOADS schedule, and that new job will run at the same times and intervals as the LOADNEWCUSTOMERS job just created.

The schedule defined in Listing 2 results in the following job start times:

Monday, 22-Mar-2004, at 6:00 p.m.
Wednesday, 24-Mar-2004, 6:00 p.m.
Friday, 26-Mar-2004, 6:00 p.m.

When you link a job to this schedule, the job's first run will be at the next scheduled time following the date and time when you link the two together. For example, if you create the job shown in Listing 3 on Tuesday, 23-Mar-2004, the job will first run on Wednesday, March 24 at 6:00 p.m. If you have several related jobs running on the same schedule and you need to change that schedule, one change will suffice for the entire set of related jobs.

Constraining Job Start Time

A really nice feature of the new scheduler is the ability to constrain a job's start time to a specific period following the nominal start time. For example, suppose you have a job scheduled to run every evening at 6:00 pm. Due to system load or other reasons, the job might not actually get the chance to start until 6:12 p.m. Do you still want the job to start, even though it's 12 minutes late?

With the scheduler, you can answer the question of how long past its time you're willing to let a job start. You do this by setting the job's schedule_limit attribute value (in minutes). The following specifies that my LOADNEWCUSTOMERS job must begin within 10 minutes of its scheduled start time:

sys.dbms_scheduler.set_attribute(

   name =>'GENNICK.LOADNEWCUSTOMERS',

   attribute => 'schedule_limit', 

   value => 10);

If ever this job is delayed for more than 10 minutes past its start time, that run of the job will be skipped. The default schedule_limit attribute is null, which imposes no limit. When using Database Control, you can set this attribute from the Options tab of the Edit Job page.

Creating Resource Windows

As a DBA, you can create resource plans to allocate resources to different consumer groups. Using scheduler windows, you can have these resource plans take effect automatically. Listing 4 shows the code for creating a nightly batch processing window that begins each night at 6:00 p.m. and runs until 8:00 a.m. the next morning. Note the resource_plan argument. The window activates the BATCH_PROCESSING resource plan. Similarly, you can create a second window that activates the ONLINE_PROCESSING resource plan each morning.

Job classes, which allow you to assign jobs to consumer groups, are the link between resource management and the scheduler. The default job class is DEFAULT_JOB_CLASS, but you can easily choose a different class when creating a job. You can also easily switch a job to another job class after you create the job.

The window created in Listing 4 is defined with an inline schedule, meaning that the schedule is part of the window definition. It's also possible to link a window to a separately defined schedule. You can go further and tie a window and several related jobs to the same schedule, so that when the schedule is triggered, the window's resource plan is made active and all the jobs linked to that schedule begin running.

If you have jobs you want to run only when a window and its resource plan are active, you can schedule those jobs according to that window rather than according to a predefined schedule. If you have two or more windows that share the same resource plan, you can combine those windows into a window group. The following shows the creation of a batch processing window group encompassing the nightly and weekend windows:

BEGIN

   DBMS_SCHEDULER.CREATE_WINDOW_GROUP(

      group_name=>'batch_processing',

      window_list=>'NIGHT,WEEKEND');

END;

Monitoring and Logging

The new scheduler provides excellent monitoring and logging facilities, which are especially easy to use through Oracle Enterprise Manager 10g's Database Control interface. Figure 3 shows the list of scheduled jobs you can see from the Scheduler Jobs page. Click on the Running tab to see which jobs are currently running. Click on the Run History tab, and you'll see a log like that in Figure 4. Each log entry represents a job instance, and you can see immediately which instances succeeded or failed. Drill down into a job by clicking on a job name, and you can drill further down and view the detailed log entries from each run. Log entries are retained for the period of time you specify. You can specify a global retention time for log entries, and you can also customize log entry retention time for different job classes. Once a day, the scheduler looks for log entries that are past their retention time and deletes them.

The new scheduler and its strong support in Database Control are welcome developments for any DBA who hopes to reduce the tedium of scheduling, managing, and monitoring recurring jobs. The architecture of programs, schedules, jobs, job classes, windows, and window groups represents a strong feature set now and also lays the groundwork for continued enhancements in future releases. Calendaring syntax is much easier and more reliable to use than the old DBMS_JOB method of writing PL/SQL expressions to compute job execution intervals. The new scheduler has a lot to offer. Take advantage of it. Automate. 

REFERENCES

Schedule Away the Routine By Jonathan Gennick