The Oracle 10g Scheduler, Part 2: Implementation

Tuesday Sep 28th 2004 by Jim Czuprynski

Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over DBMS_JOB for scheduling jobs and tasks. This article - the second in a series - provides practical examples of using the Oracle Scheduler's new functionalities, including a discussion of transitioning from DBMS_JOB to DBMS_SCHEDULER.

Synopsis. Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over DBMS_JOB for scheduling jobs and tasks. This article - the second in a series - provides practical examples of using the Oracle Scheduler's new functionalities, including a discussion of transitioning from DBMS_JOB to DBMS_SCHEDULER.

Basic Scheduling: A Scenario

The previous article provided a broad overview of the new Scheduler features and briefly compared the basic functionality of DBMS_JOB versus DBMS_SCHEDULER. So let's now turn our attention to using the Scheduler to handle a typical real-world DBA problem: refreshing a database's statistics, a thankless but critical task, especially if you expect the Oracle optimizer to make the right decisions about the data it retrieves and processes. (I will not bore you with a polemic on how the cost-based optimizer works, as there are plenty of white papers and Oracle books on that subject alone.)

Ideally, I will want to refresh statistics on all tables and indexes at least once per week, usually during an off-peak period for database utilization because statistics computation can consume large amounts of system resources. I will also want to refresh statistics for all tables and indexes that are part of the HR, SLSMGR, or SH schemas. Finally, I would like to refresh statistics for the schemas with the fewest number of objects and/or smallest tables first to get them "out of the way."

Prior to Oracle 10g, I would have to use DBMS_JOB to create job(s) for each schema in this scenario. Listing 1.1 shows how I would implement this via DBMS_SCHEDULER, creating one job that invokes the appropriate supplied PL/SQL procedure, DBMS_STATS.GATHER_SCHEMA_STATS, for each schema that needs its statistics refreshed.

However, another solution exists that takes advantage of the strengths of the new Scheduler: the creation of Scheduler objects to handle these tasks. For this solution, I will need to create:

  • A schedule object that controls when the various schema refreshes should run
  • A program object that handles calling the appropriate procedure for the refresh
  • Several job objects that invoke the program at the scheduled time

Creating Schedule Objects

I will tackle the creation of the schedule object first. I want my schema refresh schedule to run on Sundays after 18:00 hours (my client's slowest off-peak time) to minimize the impact on on-line transaction processing operations. I will use DBMS_SCHEDULER.CREATE_SCHEDULE to build the new schedule object named FRESHENSCHEMAS to accomplish this as shown in Listing 1.2.

I will set up the schedule object to run the schema refresh every Sunday evening starting at 18:00 U.S. Central Time on October 2, 2004. Again, this is a major improvement over DBMS_JOB, since I can specify the exact limits of when I want to start and stop the scheduled tasks. (I will cover the Scheduler's calendar features in greater detail, in my next article.)

Creating Program Objects

Next, I will tackle creating a program object to handle schema refreshes. For purposes of illustration, I will first create a program object named FRESHENHRSCHEMA without any arguments that will handle refreshes of just the HR schema. I will use DBMS_SCHEDULER.CREATE_PROGRAM to create a new program object, as shown in Listing 1.3.

I could create a program object in this manner for each schema for which I want to refresh statistics, but there is a better way: I can create a program object that accepts arguments and executes a stored procedure. If a suitable stored procedure does not already exist that the program can execute, I will need to create one; once it exists, I will create the program, and finally I will create the program's arguments.

Listing 1.4 implements this scenario. I have created a new stored procedure, SP_GATHER_STATS that is then referenced by a new program object named FRESHENSTATS. This program object will accept two arguments, SCHEMA and CASCADE, that will be used to pass values to SP_GATHER_STATS to specify which schema is to be refreshed and whether all dependent objects should be refreshed as well.

Putting It All Together: Creating Job Objects

Finally, I will create a job object that assigns a specific task within a specific schedule. I will need to create one job object for each schema for which statistics need to be refreshed. Since I want the smaller schemas to get their statistics first, I will make sure statistics for the HR, SLSMGR, and SH schemas are refreshed in that order. I will first use DBMS_SCHEDULER.CREATE_JOB to build the new job object named HR_FRESHENSCHEMA as shown in Listing 1.5.

Next, I will use another slick feature of DBMS_SCHEDULER, the COPY_JOB procedure, to build two new jobs for the SLSMGR and SH schemas (see Listing 1.6), and then I will assign arguments to all three jobs using the DBMS_SCHEDULER.SET_JOB_ATTRIBUTE procedure (see Listing 1.7). Finally, I'll activate the jobs using DBMS_SCHEDULER.ENABLE,SET_JOB_ATTRIBUTE procedure as shown in Listing 1.8.

To validate the schedule, I will temporarily reset the scheduled begin date, end date, and frequency of the FRESHENSCHEMA schedule object by calling DBMS_SCHEDULER.SET_ATTRIBUTE procedure for these attributes. I will then disable and re-enable the three jobs to propagate these new schedule parameters to the jobs as shown in Listing 1.9.

Viewing Scheduler Information

One of my pet peeves about DBMS_JOB is that only two views are provided to view the entire state of all scheduled tasks for the database. DBA_JOBS shows the current and future state of all scheduled tasks, and DBA_JOBS_RUNNING shows which tasks are running right now. Moreover, while DBA_JOBS can be queried to tell when a scheduled task last ran, it provides no information about how many times the task ran in the past, how many times it may have failed, and much more importantly why it failed.

Happily, the new Scheduler has rectified this by providing a plethora of views about its current and future state. I've assembled some queries that I have found useful in determining what Scheduler objects exist, what tasks have been scheduled, and the success or failure of each scheduled task activity after it has been run.

Scheduler Components. First, remember that all Scheduler components are actual database objects. The query in Listing 2.1 will show what jobs, programs, and schedules currently exist within the database:

                               Scheduler Objects:

Owner    Type         Name                 Created On           Status
-------- ------------ -------------------- -------------------- -------SYS
   JOB    GATHER_STATS_JOB   09/15/2004 20:47:25  VALID
SYS      JOB          HR_FRESHENSCHEMA     09/21/2004 17:46:29  VALID
SYS      JOB          PURGE_LOG            09/15/2004 20:26:09  VALID
SYS      JOB          SH_FRESHENSCHEMA     09/21/2004 17:48:02  VALID
SYS      JOB          SLSMGR_FRESHENSCHEMA 09/23/2004 19:13:13  VALID
SYS      JOB CLASS    AUTO_TASKS_JOB_CLASS 09/15/2004 20:47:25  VALID
SYS      JOB CLASS    DEFAULT_JOB_CLASS    09/15/2004 20:26:09  VALID
SYS      PROGRAM      FRESHENALLSCHEMAS    09/23/2004 18:54:50  VALID
SYS      PROGRAM      FRESHENSCHEMA        09/23/2004 18:57:57  VALID
SYS      PROGRAM      GATHER_STATS_PROG    09/15/2004 20:47:22  VALID
SYS      PROGRAM      PURGE_LOG_PROG       09/15/2004 20:26:09  VALID
SYS      SCHEDULE     DAILY_PURGE_SCHEDULE 09/15/2004 20:26:09  VALID
SYS      SCHEDULE     FRESHENSCHEMAS       09/19/2004 17:13:18  VALID
SYS      WINDOW       WEEKEND_WINDOW       09/15/2004 20:47:22  VALID
SYS      WINDOW       WEEKNIGHT_WINDOW     09/15/2004 20:47:18  VALID

15 rows selected.

Schedule Objects. The view DBA_SCHEDULER_SCHEDULES provides me with information about the schedules that are in effect in the database, as returned by the query in Listing 2.2.

Program Objects. View DBA_SCHEDULER_PROGRAMS shows all program objects and their attributes, while view DBA_SCHEDULER_PROGRAM_ARGS shows all program arguments for programs that have them. Listing 2.3 displays two sample queries against these two views.

Job Objects. Likewise, view DBA_SCHEDULER_JOBS shows all job objects and their attributes, while view DBA_SCHEDULER_JOB_ARGS shows the arguments that have been provided for each job that requires arguments. Listing 2.4 contains three queries for probing this information.

Scheduled Tasks Status and History. Three views track the status of all scheduled task activity for the database, including detailed history of each scheduled task's successful (or unsuccessful!) execution. This is a welcome addition to any DBA's tool belt for solving problems with scheduled tasks that may have failed unexpectedly. Listing 2.5 shows several +sample queries for gathering information on scheduled task execution, history, and current status.

View DBA_SCHEDULER_JOB_LOG shows a high-level summary of all job activity; its ADDITIONAL_INFO column is a CLOB that contains detailed information about the last run of the scheduled task.

View DBA_SCHEDULER_JOB_RUN_DETAILS shows an even greater level of detail about scheduled task run history; its ADDITIONAL_INFO column shows a brief description of what went wrong when a scheduled task has failed during execution.

Finally, view DBA_SCHEDULER_RUNNING_JOBS provides a method to look at a scheduled task while it is executing; this view replaces the old DBA_JOBS_RUNNING view.

Transitioning from DBMS_JOB to DBMS_SCHEDULER

To make the transition from using DBMS_JOB to DBMS_SCHEDULER, I suggest the following steps:

  • Identify all currently active scheduled tasks that were scheduled via DBMS_JOB, including their scheduled run times.
  • Analyze these tasks and identify any "overlaps" among the DBMS_JOB scheduled tasks. These overlaps might include identical tasks being run at different times; tasks being run in parallel; or tasks being run serially.
  • Convert tasks scheduled via DBMS_JOB into their corresponding DBMS_SCHEDULER components (program, schedule, and job objects):
    • "Break" the currently scheduled task to make sure that duplicate tasks don't fire off at the same time:
      DBMS_JOB.BROKEN(<job>, TRUE);
    • If the task is essentially a stand-alone task, use DBMS_SCHEDULE.CREATE_JOB to create a new instance of the task.
    • If several tasks can benefit from reuse, then, use DBMS_SCHEDULE.CREATE_JOB, DBMS_SCHEDULE.CREATE_PROGRAM, and DBMS_SCHEDULE.CREATE_JOB to create the required components and start them running on schedule.
  • Once the conversion is complete, then remove all the tasks using DBMS_JOB.REMOVE:.


    As these examples have shown, Oracle 10g's new Scheduler offers a whole new range of flexibility when scheduling tasks. In my next article, I will delve into the details of scheduling tasks at non-standard date and time intervals. I will also explore some of the advanced features of the new Scheduler, including the ability to manage resource conflicts between scheduled tasks with Scheduler windows and job classes.

    References and Additional Reading

    While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:

    B10802-01 Oracle 10g PL/SQL Packages and Types Reference

    B10739-01 Oracle 10g Concepts, Chapters 25-28

    » See All Articles by Columnist Jim Czuprynski

  • Home
    Mobile Site | Full Site