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.
