Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 28, 2004

The Oracle 10g Scheduler, Part 2: Implementation

By Jim Czuprynski

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.

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM