The Oracle 10g Scheduler, Part 2: Implementation
September 28, 2004
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:
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.