Oracle Scheduler Overview
By default, the Oracle 11g job coordinator
process is not always running.
It is started and stopped, as required.
If the database detects any jobs that
must be executed or windows opened in the near
future, the job coordinator background process
(CJQ0)
is started.
If there is no current job activity and
no open windows, the job coordinator is stopped.
The job coordinator
spawns as many job slaves (j000 to j999) as are
needed to execute the outstanding jobs.
A job slave
gathers metadata
from the scheduler tables to enable it to
execute a job.
Upon completion of a job, the slave
process updates any relevant information in the
job table, inserts data into the job run history
and requests another job from the job
coordinator.
If a job is not available, the job slave
sleeps until there is work to do.
The job coordinator periodically
terminates idle job slaves to reduce the slave
pool.
The job table
used by the scheduler is implemented using
Oracle Advanced Queuing
(AQ)
and the supporting tables listed below:
select
table_name
from
user_tables
where
table_name like '%SCHEDULER$_JOB%'
;
TABLE_NAME
------------------------------
SCHEDULER$_JOB_RUN_DETAILS
SCHEDULER$_JOB_STEP_STATE
AQ$_SCHEDULER$_JOBQTAB_S
SCHEDULER$_JOB
SCHEDULER$_JOBQTAB
SCHEDULER$_JOB_ARGUMENT
SCHEDULER$_JOB_CHAIN
SCHEDULER$_JOB_STEP
AQ$_SCHEDULER$_JOBQTAB_G
AQ$_SCHEDULER$_JOBQTAB_H
AQ$_SCHEDULER$_JOBQTAB_I
AQ$_SCHEDULER$_JOBQTAB_T
In addition to the conceptual job table,
the scheduler uses several other tables to store
metadata
about scheduler objects.
select
table_name
from
user_tables
where
table_name like '%SCHEDULER$%'
and
table_name not like
'%SCHEDULER$_JOB%'
;
TABLE_NAME
------------------------------
SCHEDULER$_EVENT_LOG
SCHEDULER$_WINDOW_DETAILS
SCHEDULER$_CHAIN_VARLIST
SCHEDULER$_CLASS
SCHEDULER$_GLOBAL_ATTRIBUTE
SCHEDULER$_OLDOIDS
SCHEDULER$_PROGRAM
SCHEDULER$_PROGRAM_ARGUMENT
SCHEDULER$_SCHEDULE
SCHEDULER$_WINDOW
SCHEDULER$_WINDOW_GROUP
SCHEDULER$_WINGRP_MEMBER
Under normal circumstances, one would not expect
to interact with any of the scheduler tables
directly.
Information about the scheduler is
displayed using the
dba_scheduler_%
views, and the
dbms_scheduler
package is used for the creation and
manipulation of several scheduler objects
including:
-
Schedules
- Components that define repeat intervals,
allowing several jobs and windows to share a
single schedule definition
-
Programs
- Components that define the work done by a
job, allowing multiple jobs to share a
single definition
-
Jobs
- Scheduled jobs that can be defined as
individual entities or defined using
existing schedules and programs
-
Job Classes
- Logical groupings of jobs that have
similar resource and administration
requirements.
Job classes provide a link between
the scheduler and the resource manager.
-
Windows
- Components that define a period of time
and link it to a specific resource plan,
allowing the automatic control of system
resources allocated to scheduled jobs.
Window Groups
- Logical grouping of windows
These scheduler objects and the usage of the
dbms_scheduler
package are presented in greater detail.
However, the following example demonstrates how
a simple job can be scheduled in Oracle.
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'dummy_job',
job_type
=> 'PLSQL_BLOCK',
job_action
=> 'BEGIN NULL; /* Do Nothing */
END;',
start_date
=> SYSTIMESTAMP,
repeat_interval => 'SYSTIMESTAMP + 1
/* 1 Day */');
END;
/
The above example is the Oracle 11g equivalent
of the job defined in the previous Oracle 9i
section.
From a quick look at this example, one
might conclude that there is little difference
between the old and the new schedulers; however,
that would be an incorrect assumption.
For backwards compatibility, it is possible to
schedule jobs using both the
dbms_job
and
dbms_scheduler
packages in Oracle 11g.
When jobs are scheduled using the
dbms_job package, they are still
dependent on the
job_queue_processes
parameter.
When this parameter is set to zero, jobs
scheduled using the
dbms_job package will not run, but
those scheduled using the
dbms_scheduler package will still
run normally.
If the parameter is set to a non-zero value, the
job coordinator
will run permanently, but the value will only
constrain the number of job slaves that can be
started to run jobs scheduled using the
dbms_job
package.
The value has no affect on the number of
job slave
processes that are allocated to jobs scheduled
using the
dbms_scheduler
package.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
