Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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

Table 1.1 - Features comparison between the dbms_job and dbms_scheduler packages

The next section presents an overview of the approach that can be taken during the migration of jobs from the old to the new scheduler.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational