Oracle Scheduling Setting Scheduler Attributes

Setting Scheduler Attributes

There are currently four scheduler attributes:

* current_open_window (read-only)

* default_timezone

* log_history

* max_job_slave_processes

Management of the scheduler attributes requires the MANAGE SCHEDULER privilege.  To influence the default behavior of the scheduler, three of the attributes can be altered using the set_scheduler_attribute procedure.  These values can be displayed using the show_scheduler_attribute.sql script, which utilizes the get_scheduler_attribute procedure.

* show_scheduler_attribute.sql

set verify off

variable v_value VARCHAR2(1000);

BEGIN
  DBMS_SCHEDULER.get_scheduler_attribute (
    attribute => '&1',
    value     => :v_value);
END;
/

print v_value

The following sections will present more detail regarding each of the scheduler attributes starting with the current_open_window attribute.

current_open_window

This is a read-only attribute which returns the name of the window that is currently open or active. The show_scheduler_attribute.sql script is used to display the value of the current_open_window attribute.

SQL> @show_scheduler_attribute.sql current_open_window

V_VALUE
--------------------------------------------------------------------
WEEKEND_WINDOW

default_timezone

As the name implies, this attribute sets the default time zone for the scheduler.  When a job is scheduled using the calendar syntax to define a repeat interval, the scheduler needs to know which time zone to apply when calculating the next run date.  Since a time zone cannot be specified explicitly by the calendar syntax, it must be derived from the following sources, in the order noted below:

* The time zone of the job’s start_date attribute.

* The current session’s time zone.

* The scheduler’s default_timezone attribute.

* The time zone returned by the systimestamp function.

The following example sets the default_timezone attribute to a value of ‘US/Eastern’ and displays the change.

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'default_timezone',
    value     => 'US/Eastern');
END;
/

SQL> @show_scheduler_attribute.sql default_timezone

V_VALUE
---------------------------------------------------
US/Eastern

log_history

This parameter controls the length of time scheduler logs are kept.  Each day the scheduler purges any logs that are older than this retention time, specified in days.  Any value within the range of one to 999 can be specified, with the default value being 30 days.  The following example sets the log_history attribute to a value of 60 days and displays the change.

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'log_history',
    value     => 60);
END;
/

SQL> @show_scheduler_attribute.sql log_history

V_VALUE
-----------------------------------------------
60

max_job_slave_processes

Unlike the scheduler in Oracle 9i, the Oracle10g scheduler is not constrained by the job_queue_processes parameter.  Instead, it will start as many job slave processes as needed to cope with the current load.  Although limiting the total number of job slave processes should not be necessary under normal circumstances, the max_job_slave_processes parameter allows the capability to do so if required.  Any value within the range of one to 999 can be specified, with the default value being NULL.  The following example sets the max_job_slave_processes attribute to a value of 100 and displays the change.

To remove this limit simply set the value to NULL.

With that introduction to how to set scheduler attributes, the next section will present a look at how job priorities can be assigned within a job class.

Job Priorities

When several jobs within the same job class are scheduled to start at the same time, the job coordinator uses the job priority to decide which job to execute first.  In the following example, a job is created and its job_priority attribute is set to one using the set_attribute procedure.

The attribute can be set to any value in the range from one to five, in which one is the highest priority.  If a priority is not specified during the job creation, it is assigned the default value of three.

The priority of a job can be displayed using the dba_scheduler_jobs view, as shown by the following query:

This introduction to priorities illustrates that assigning a priority to jobs within a job class is easy.  The next section will present information on scheduler logging that is available as part of the Oracle10g scheduler.

Scheduler Logging

The Oracle10g scheduler logs a number of events including job maintenance, job run activity and window activity.  It also gives some degree of control over the level of logging performed by the scheduler.

The log_history scheduler attribute can be used to control the volume of historical logging information.  Yet, if a specific job or job class has a different history requirement, the set_attribute procedure can be used to override this value.

There are several types of scheduler logs which can be managed separately.  In the following sections, information will be presented on each type of logging available, starting with job logs.

Job Logs

There are three levels of logging associated with scheduled jobs. They are noted below, along with the appropriate constants defined in the dbms_scheduler package:

* logging_off - No logging.

* logging_runs - Only run events are logged.

* logging_full - All events that happen to a job during its lifetime are logged.

The logging level of a job is typically set by associating it to a job class with the appropriate logging level.  Since the default logging level for a job class is logging_runs and all jobs are associated with a job class, the default logging level for a job is logging_runs.

Alternatively, the logging_level parameter of a job can be set directly using the set_attribute procedure, as shown below.

For security reasons, this method cannot change the logging level to a value lower than that of its associated class.  For example, if the jobs associated job class has a logging level of logging_runs, the set_attribute procedure could only be used to switch the jobs logging level to logging_full and back to logging_runs.  By doing so, administrators of the scheduler can dictate a minimum level of auditing for job execution.

The job_log_lifecycle.sql script creates, updates, enables and drops a job; effectively producing a full lifecycle of events in the job log.

This script clears down any log information associated with the job it creates, allowing it to be run multiple times with the same result.

The job_logs.sql script uses the dba_scheduler_job_log view to display log information for a specific job or all jobs.

Using the previous two scripts, the sort of logging one would expect for a job with full logging enabled can be seen.

The job logs provide only top-level information about the jobs.  Further details are logged in the job run details log which is covered in the next section.

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.