Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Scheduler Job Coordinator
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.