10g Scheduling Feature

출처 :  https://support.oracle.com

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
"Checked for relevance on 20-APR-2007"

Goal

To understand the new terms and concepts of Scheduling in Oracle Database - 10g

Solution

Oracle Database 10g introduces a new feature called Oracle Scheduler.
The Scheduler provides complex enterprise scheduling functionality.


Benefits
-------------
1.Minimum development time is required since jobs can be easily defined, manipulated and
  scheduled using simple mouse operation through the graphical interface (Enterprise Manager).

2.Scheduler objects are modular and can be shared with other users thus reducing the development time for new jobs.
3.The same operations can be performed on multiple jobs. For example multiple jobs can be stopped in one call.

4.Jobs can be easily moved from one system to another, for example from a development environment
  to production, using Export/Import (data pump) utility in the database

5.All scheduler activities can be logged, providing an audit trail of all scheduler activities.

6.There is support for time zones, which makes it easy to manage jobs in any time zone.

7.Feature of the Database

-Existing database knowledge can be leveraged , therefore eliminating the need to learn a new system and syntax
-Since the scheduler is part of the database it is platform independent, therefore jobs can be managed similarly on all platforms
-The scheduler inherits all the database features : high security, high availability, high scalability


Architecture
-------------------
Database
Client  ---> Job Queue -----------------> Job Coordinator
J1 | | |
J2 V V V
J3 Job Salve Job Salve Job Salve


Users create and submit jobs to the job queue using either a GUI or DBMS_SCHEDULER package.
The database stores the Scheduler object information such as object definition , state change and
historical information. The job coordinator picks up jobs from the queue and passes them to the job slave.

The job slave executes the job and updates the job information in the queue.
The Job coordinator spawns the job slaves. It dynamically controls the slave pool, increasing and
reducing its size depending on the number of jobs that need to be executed.

In RAC configuration, there is only one Queue but each instance of the database will have a job
coordinator. The coordinators communicate with each other to exchange information to ensure that the
job system remains in Sync.



Scheduler Components
-----------------------------------
Oracle provides a collection of functions and procedures in the DBMS_SCHEDULER package.
Collectively these functions are called the Scheduler, and they are callable from any pl/SQL program.
The Scheduler uses three main components:


1. Schedule:
A schedule specifies when and how many times a job is executed. Similar to programs, schedules are
database entities and can be saved in the database. The same schedule can be used by multiple jobs.

When scheduling repeat intervals for a job, you can specify either a PL/SQL expression or a calendaring expression.

Example:
-Using PL/SQL expression: repeat_interval=> 'sysdate + 36/24'
-Using Calendar Expression:

(i) repeat_interval=> 'FREQ=HOURLY; INTERVAL=4' (indicates a repeat interval of every four hours)
(ii)repeat_interval=> 'FREQ=YEARLY; BYMONTH=MAR, JUN, SEP, DEC; BYMONTHDAY=15'
(Indicates as repeat interval of every year on Mar 15th, Jun 15th, Sep 15th and Dec 15th)


2. Program:
A program is a collection of Metadata about what is run by the scheduler. This includes information
such as the program name, the type of program, information about arguments passed to the program.
The program determines what should be run.

The program is separated from the job allowing a program to be reused by different users at different times.
You no longer need to redefine the program each time you schedule a job.
Having the program separate from the job also enables the creation of program libraries.
With a program library, you can select from a list of predefined programs and simply specify when
and how the program must be executed.


3. Job:
A job specifies what needs to execute and when. For example, the “ What” could be a PL/SQL
procedure, an executable C program, a java application, a shell script, or Client-side PL/SQL. You can
specify the program (what) and schedule (When) as part of the job definition, or you can use an existing
program or schedule instead. Job execution can be scheduled using a combination of date and time.
The schedule of the job can be explicitly specified in the job. Alternatively a reference to an existing schedule
or time window is also supported in the job definition.

Besides the job program and schedule there are several other attributes that can be specified in the job definition.
The job attributes define how the job should be handled during its execution.

The key supported attributes are:

- Job Class: This attribute specifies the resources that will be allocated to this job.
- Auto Drop: When set , the job definition will be deleted after the job is executed
- Restartable: In the case of a failure, either an application error or a database / system crash,
                    the job will automatically be restarted
- Scheduler limit: if the delay in starting the job is larger than the interval specified, then the job will not be started.
                   For example, if a job was supposed to strt at noon and the scheduler limit is set to 60 minutes,
                   the job will not be run if it has not started to run by 1PM
- Max runs: specifies the maximum number of times this job can run.
- For complete list of attributes see the ALL_SCHEDULER_JOBS view. You can set job attributes when creating
                   the job, or through the SET_ATTRIBUTE procedure of DBMS_SCHEDULER.



Job Class:
---------------
Job Class defines a category of jobs that share common resource usage requirements and other
characteristics. At any given time, each job can belong to only a single job class.

A job class has the following attributes:

=>A database service name. The jobs in the job class have affinity to the particular service specified.
=>A resource consumer group, which classifies a set of user sessions that have common resource
processing requirements. At any given time, a user session or job class can belong to a single
resource consumer group. The resource consumer group that the job class associates with determines
the resources that are allocated to the job class.


Window and Window groups
------------------------------------------
A window is represented by an interval of time with a well-defined beginning and end and is used to
activate different resource plans at different times. A window group represents a list of windows.
A window or window group is also a valid schedule for a job. You should use a window or a window
group as the schedule of a job if you want to ensure that the job runs only when a particular resource plan is active.



Data Dictionary Views
================
[DBA | ALL | USER ] _Scheduler_jobs
[DBA | ALL | USER ] _scheduler_jobs_args
[DBA | ALL | USER ] _scheduler_running_jobs
[DBA | ALL ] _scheduler_job_classes
[DBA | ALL | USER ] _scheduler_job_log
[DBA | ALL | USER ] _scheduler_job_run_details
[DBA | ALL | USER ] _scheduler _programs
[DBA | ALL | USER ] _scheduler_program_args

Previlege> ========
1. CREATE [ANY] JOB
2. EXECUTE ANY PROGRAM
3. EXECUTE ANY CLASS
4. MANAGE SCHEDULER


To create a job, schedule, or program in your own schema, you must first be granted the CREATE JOB
privilege. To be able to create windows, classes or window groups, you must first be grated the MANAGE
SCHEDULE system privilege.