The Oracle 10g Scheduler, Part 2: Implementation

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.

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles