The Oracle 10g Scheduler

Oracle 10g’s DBMS_SCHEDULER provides significant improvements over DBMS_JOB for scheduling jobs and tasks. Let’s take an overview of new functionalities that the Oracle Scheduler provides for DBAs.

There are three basic components to DBMS scheduler: Programs,Schedules & Jobs

Programs :	A program defines what Scheduler will execute. A program’s attributes include its name, its type (e.g. a PL/SQL procedure or anonymous block), and the action it is expected to perform.
Schedules :	A schedule defines when and at what frequency the Scheduler will execute a particular set of tasks.
Jobs :	A job assigns a specific task to a specific schedule. A job therefore tells the schedule which tasks – either one-time tasks created “on the fly,” or predefined programs – are to be run.

Job Classes: The Scheduler provides the capability to group together jobs that have similar resource demands into job classes.

Windows : Most businesses have periods of peak and off-peak. The scheduler provides concept of windows to assign resources to job classes.

Window Groups : The Scheduler also allows windows with similar scheduling properties – for example, normal business weekday off-peak time, weekends and holidays – to be collected within window groups for easier management of jobs and scheduled tasks.

Window Overlaps: It is possible to have windows overlap each other, and it does provide a simple conflict-resolution method to insure that the appropriate jobs do get the appropriate resources.

DBMS_JOB	DBMS_SCHEDULER
— Schedule an Analyze job at 10PM BEGIN DBMS_JOB.SUBMIT ( job => :jobno ,what => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”ORACLEDBA”);END;’ ,next_date => ’29/01/2009 22:00:00′ ,interval => ‘TRUNC(SYSDATE) + 1 + 22/24’); END;	BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => ‘REFRESH_STATS_ORACLEDBA_SCHEMA’ ,job_type => ‘PLSQL_BLOCK’ ,job_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”ORACLEDBA”);END;’ ,start_date => ”29/01/2009 10:00 PM’ ,repeat_interval => ‘FREQ=DAILY’ ,enabled => TRUE ,comments => ‘Refreshes the ORACLEDBA Schema every night at 10 PM’); END;

Let’s put the theory into practise. One of the common task as a DBA is setting up ANALYZE job for number of Oracle Database users.Let’s try and achieve it using the new DBMS_SCHEDULER API

————– Above task could be scheuled in a single API as below
BEGIN
DBMS_SCHEDULER.DROP_JOB ( job_name => ‘ANALYZE_USERS’ ,force => TRUE);
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘ANALYZE_USERS’
,job_type => ‘PLSQL_BLOCK’
,job_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”SALES”);DBMS_STATS.GATHER_SCHEMA_STATS(”Marketing”);DBMS_STATS.GATHER_SCHEMA_STATS(”HR”);END;’
,start_date => ’09/10/2009 22:00:00.000000′
,repeat_interval => ‘FREQ=DAILY’
,enabled => FALSE
,comments => ‘Refreshes the Schemas every night at 10 PM’
);
END;

Let’s try and use new feaures and strengths of 10g Scheduler

We need to create
1. A schedule object that controls when the various schema refreshes should run
2. A program object that handles calling appropriate procedure for Analyze refresh
3..A job object that invoke the program at the scheduled time

1. Creating Schedule Object: I want to schdule schema refresh on Sundays after 22:00 hours (off-peak time).

————– SQL for Creating a Schedule Object
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE( schedule_name => ‘RefreshSchemas’ ,force => TRUE );
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => ‘RefreshSchemas’
,start_date => ’25/10/2009 22:00:00′
,repeat_interval => ‘FREQ=WEEKLY’
,comments => ‘Weekly schema statistics refresh’ );
END;

2. Creating Program Objects : I will create a program object named REFRESHHRSCHEMA without any arguments that will handle refreshes of just the one HR schema. I have multiple schmas so I could use following SQL for each one of them.

————– SQL for Creating a Simple Program Object
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (program_name => ‘ReFreshSchema’
,program_type => ‘PLSQL_BLOCK’
,program_action => ‘BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(”HR”);END;’
,number_of_arguments => 0
,enabled => TRUE
,comments => ‘Freshens statistics for all objects in HR schema only’
);
END;

3. Creating Job Objects: Finally, I will add a job object that assigns specific task within a specific schedule. I will need to create one job object for each schema for which statistics need to be refreshed.

————– SQL for Creating Job Using Existing Schedule and Program Objects
BEGIN
DBMS_SCHEDULER.DROP_JOB (
job_name => ‘HR_REFRESHSCHEMA’
,force => TRUE);

DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘HR_REFRESHSCHEMA’
,program_name => ‘REFRESHSCHEMA’
,schedule_name => ‘REFRESHSCHEMAS’
,enabled => FALSE
,comments => ‘Refreshes the HR Schema every week’
);
END;

Now we have new scheduler and added a Analyze job but how would we locate Success /Failure of Scheduler jobs?

————– Show Scheduler database objects
TTITLE ‘Scheduler Objects:’
COL owner FORMAT A08 HEADING ‘Owner’
COL object_type FORMAT A12 HEADING ‘Object|Type’
COL object_name FORMAT A20 HEADING ‘Name’
COL created FORMAT A20 HEADING ‘Created On’
COL status FORMAT A12 HEADING ‘Status’

SELECT
owner ,object_type ,object_name ,created ,status
FROM dba_objects
WHERE object_type IN (‘PROGRAM’, ‘JOB’, ‘JOB CLASS’, ‘SCHEDULE’, ‘WINDOW’)
ORDER BY object_type, OBJECT_name;

————– Show Schedule objects
TTITLE ‘Schedules:’
COL owner FORMAT A08 HEADING ‘Owner’
COL schedule_name FORMAT A20 HEADING ‘Schedule’
COL start_date FORMAT A20 HEADING ‘Starts|On’
COL end_date FORMAT A20 HEADING ‘Ends|On’
COL repeat_interval FORMAT A45 HEADING ‘Interval’

SELECT
owner ,schedule_name ,to_char(start_date, ‘mm/dd/yyyy hh24:mi:ss’) start_date ,to_char(end_date, ‘mm/dd/yyyy hh24:mi:ss’) end_date ,repeat_interval
FROM dba_scheduler_schedules;

————– Show Program objects : What program objects are available?
TTITLE ‘Programs:’
COL owner FORMAT A08 HEADING ‘Owner’
COL program_name FORMAT A20 HEADING ‘Program’
COL program_type FORMAT A16 HEADING ‘Type’
COL program_action FORMAT A32 HEADING ‘Action’

SELECT
owner
,program_name
,program_type
,program_action
FROM dba_scheduler_programs;

— What program’s arguments are attached?
TTITLE ‘Program Arguments:’
COL owner FORMAT A08 HEADING ‘Owner’
COL program_name FORMAT A20 HEADING ‘Program’
C
OL argument_name FORMAT A12 HEADING ‘Arg Name’
COL argument_position FORMAT 999 HEADING ‘Arg|Pos’
COL argument_type FORMAT A12 HEADING ‘Arg Type’
COL default_value FORMAT A12 HEADING ‘Default|Value’
COL out_argument FORMAT A06 HEADING ‘Out|Arg?’

SELECT owner ,program_name ,argument_name ,argument_position ,argument_type ,default_value ,out_argument
FROM dba_scheduler_program_args;

————– Show Job objects
COL owner FORMAT A08 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL program_name FORMAT A20 HEADING ‘Program|Name’
COL job_class FORMAT A24 HEADING ‘Job Class’
COL job_type FORMAT A12 HEADING ‘Job|Type’
COL job_action FORMAT A12 HEADING ‘Job|Action’

TTITLE ‘Jobs:’
SELECT
owner ,job_name ,state ,job_class ,job_type ,job_action
FROM dba_scheduler_jobs;

COL owner FORMAT A08 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL program_name FORMAT A20 HEADING ‘Program|Name’
COL schedule_name FORMAT A20 HEADING ‘Schedule|Name’
COL enabled FORMAT A08 HEADING ‘Enabled?’
COL state FORMAT A08 HEADING ‘State’
COL restartable FORMAT A08 HEADING ‘Restart|-able?’
COL start_date FORMAT A32 HEADING ‘Start|Date’

TTITLE ‘Job Components:’
SELECT
owner ,job_name ,program_name ,schedule_name ,enabled ,state ,restartable ,start_date
FROM dba_scheduler_jobs;

————– What are a Job’s arguments?
COL job_name FORMAT A20 HEADING ‘Job’
COL argument_name FORMAT A12 HEADING ‘Arg Name’
COL argument_position FORMAT 999 HEADING ‘Arg|Pos’
COL value FORMAT A32 HEADING ‘Argument Value’

TTITLE ‘Job Arguments:’
SELECT job_name ,argument_name ,argument_position ,value
FROM dba_scheduler_job_args;

————– Show Scheduled Tasks History: Show a high-level view of scheduled task execution history
COL log_id FORMAT 9999 HEADING ‘Log#’
COL log_date FORMAT A32 HEADING ‘Log Date’
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL status FORMAT A10 HEADING ‘Status’

TTITLE ‘Scheduled Tasks History:’
SELECT
log_id ,log_date ,owner ,job_name ,status
FROM dba_scheduler_job_log;

————– What scheduled tasks failed during execution, and Error details
COL log_id FORMAT 9999 HEADING ‘Log#’
COL log_date FORMAT A32 HEADING ‘Log Date’
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL status FORMAT A10 HEADING ‘Status’
COL actual_start_date FORMAT A32 HEADING ‘Actual|Start|Date’
COL error# FORMAT 999999 HEADING ‘Error|Nbr’

TTITLE ‘Scheduled Tasks That Failed:’
SELECT
log_id ,log_date ,owner ,job_name ,status ,actual_start_date ,error#
FROM dba_scheduler_job_run_details
WHERE status <> ‘SUCCEEDED’
ORDER BY actual_start_date;

————– Which jobs are running right now ?
COL owner FORMAT A06 HEADING ‘Owner’
COL job_name FORMAT A20 HEADING ‘Job’
COL session_id FORMAT 999999 HEADING ‘Session’
COL running_instance FORMAT A10 HEADING ‘Running|Instance’
COL elapsed_time FORMAT A12 HEADING ‘Elapsed|Time’

TTITLE ‘Scheduled Tasks Running Right Now:’
SELECT
owner ,job_name ,session_id ,running_instance ,elapsed_time
FROM dba_scheduler_running_jobs;