Oracle job coordinator tips

Oracle Tips by Burleson Consulting

Oracle Scheduler Overview

By default the Oracle job coordinator is not permanently 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 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, making the job_queue_processes parameter redundant in Oracle 10g. 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

12 rows selected.

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

12 rows selected.

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 in subsequent chapters. However, the following example demonstrates how a simple job can be scheduled in Oracle 10g.

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 current 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 10g. When jobs are scheduled using the dbms_job package, they are still dependant 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.

The next section will provide a brief comparison of the features available in the old and in the new style Oracle schedulers.

Features Comparison between dbms_job and dbms_scheduler

The Oracle scheduler has significantly more functionality than its predecessor, which invariably results in it having a significantly more feature rich API. The following table, Table 1.1, provides a brief feature comparison between the dbms_job and dbms_scheduler packages.

Feature	dbms_job	dbms_scheduler
Schedule jobs based on anonymous PL/SQL blocks.	Yes	Yes
Schedule jobs based on PL/SQL procedures.	Yes	Yes
Schedule jobs based on operating system command and executable scripts.	No	Yes
Schedule reoccurring jobs with a repeat interval based on a PL/SQL expression that equates to a DATE or TIMESTAMP.	Yes	Yes
Schedule reoccurring jobs with a repeat interval based on an INTERVAL.	Yes	Yes
Schedule reoccurring jobs with a repeat interval based on a calendar syntax expression.	No	Yes
Schedule reoccurring jobs with a repeat interval based on predefined Schedule object.	No	Yes
Schedule reoccurring jobs with a repeat interval based on predefined Window object.	No	Yes
Modify attributes of jobs that are already scheduled.	Yes	Yes
Assign priorities to jobs.	No	Yes
Provide extensive and configurable job run history.	No	Yes
Group related jobs into a Job Class to simplify job administration.	No	Yes
Allow multiple jobs to share a single schedule definition.	No	Yes
Allow multiple jobs to share a single program, or action, definition.	No	Yes
Link groups of jobs directly to resource manager consumer groups.	No	Yes
Define complex timetables for automatic resource plan switches using Windows.	No	Yes
Limit jobs to a single instance in a RAC configuration.	Yes	Yes
Limit jobs to a group of instances within a RAC configuration using Service definitions.	No	Yes
Allow jobs to run on several nodes in a RAC configuration, but define a basic preference list.	No	Yes
Provide full integration of job creation and modification in Oracle Enterprise Manager.	Yes	Yes
Provide full integration of job creation and modification in Oracle Enterprise Manager.	Yes	Yes
Provide privileges and roles specifically for the scheduler to increase control over the scheduling of jobs.	No	Yes