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 Scheduling Setting Scheduler Attributes

Oracle Tips by Burleson Consulting

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.

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'max_job_slave_processes',
    value     => 100);
END;
/

SQL> @show_scheduler_attribute.sql max_job_slave_processes

V_VALUE
----------------------------------------------
100

To remove this limit simply set the value to NULL.

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'max_job_slave_processes',
    value     => NULL);
END;
/

SQL> @show_scheduler_attribute.sql max_job_slave_processes

V_VALUE
-----------------------------------------------

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.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_priority_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_LOCK.sleep(10); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely;',
    end_date        => SYSTIMESTAMP + 1/48,
    enabled         => FALSE,
    comments        => 'Job used to test priorities.');

  DBMS_SCHEDULER.set_attribute (
    name      => 'test_priority_job',
    attribute => 'job_priority',
    value     => 1);   

  DBMS_SCHEDULER.enable (name => 'test_priority_job');
END;
/

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:

select
   job_name,
   job_priority
from
   dba_scheduler_jobs
order by
   job_priority
;

JOB_NAME                       JOB_PRIORITY
------------------------------ ------------
TEST_PRIORITY_JOB                         1
GATHER_STATS_JOB                          3
PURGE_LOG                                 3

3 rows selected.

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.

BEGIN
  -- Alter log history for a specific job.
  DBMS_SCHEDULER.set_attribute (
    name      => 'test_job',
    attribute => 'log_history',
    value     => 30); 

  -- Alter log history for a specific job class.
  DBMS_SCHEDULER.set_attribute (
    name      => 'test_job_class',
    attribute => 'log_history',

 
  value     => 90);
END;
/

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.

BEGIN
  DBMS_SCHEDULER.set_attribute (
    name      => 'test_log_job',
    attribute => 'logging_level',
    value     => DBMS_SCHEDULER.logging_off);
END;
/

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.

* job_log_lifecycle.sql

BEGIN
  -- Remove all logs for this job.
  DBMS_SCHEDULER.purge_log(job_name => ?test_log_job?);

  -- Create job class with full logging.
  DBMS_SCHEDULER.create_job_class (
    job_class_name          => 'test_logging_class',
    resource_consumer_group => 'default_consumer_group',
    logging_level           => DBMS_SCHEDULER.logging_full);

  -- Create job links to previous job class.

  DBMS_SCHEDULER.create_job (

 
  job_name        => 'test_log_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    job_class       => 'test_logging_class',
    enabled         => FALSE,
    auto_drop       => FALSE,
    comments        => 'Job used to job logs.');   

  -- Update the job.

  DBMS_SCHEDULER.set_attribute (
    name      => 'test_log_job',
    attribute => 'start_date',
    value     => SYSTIMESTAMP);

 

  -- Enable the job.

  DBMS_SCHEDULER.enable (name => 'test_log_job');

  -- Pause to let the job run.
  DBMS_LOCK.sleep(30);

  -- Drop the job.

  DBMS_SCHEDULER.drop_job (job_name => 'test_log_job');

  -- Drop the job class.
 
DBMS_SCHEDULER.drop_job_class (job_class_name => 'test_logging_class');v

END;
/

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.

* job_logs.sql

set feedback off
alter session set nls_timestamp_tz_format='DD-MON-YYYY HH24:MI:SS.ff';
set feedback on

column owner format a10
column job_name format a30
column operation format a10
column status format a10
column log_date format a27

select
   owner,
   job_name,
   operation,
   status,
   log_date
from
   dba_scheduler_job_log
where
   job_name = decode(upper('&1'), 'ALL', job_name, upper('&1'))
order by
   log_date
;

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

SQL> @ job_log_lifecycle.sql

PL/SQL procedure successfully completed.

SQL> @ job_logs.sql test_log_job

OWNER      JOB_NAME       OPERATION  STATUS     LOG_DATE
---------- -------------- ---------- ---------- ---------------------------
JOB_USER   TEST_LOG_JOB   CREATE                21-AUG-2004 15:21:23.795000
JOB_USER   TEST_LOG_JOB   UPDATE                21-AUG-2004 15:21:23.811000
JOB_USER   TEST_LOG_JOB   ENABLE                21-AUG-2004 15:21:23.827000
JOB_USER   TEST_LOG_JOB   RUN        SUCCEEDED  21-AUG-2004 15:21:23.874000
JOB_USER   TEST_LOG_JOB   SUCCEEDED             21-AUG-2004 15:21:23.874000
JOB_USER   TEST_LOG_JOB   DROP                  21-AUG-2004 15:21:54.577000

6 rows selected.

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.


 

 
��  
 
 
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