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 Job Run Details

Oracle Tips by Burleson Consulting

Job Run Details

Every row in the dba_scheduler_job_log view for a run event has an associated row in the dba_scheduler_job_run_details view.  This view provides more details about the job run including requested start date, actual start date, duration and CPU usage etc.

The scheduled_job_details.sql and job_run_history.sql scripts from Chapter 5 use the dba_scheduler_job_run_details view to display the run history for a specific job.

SQL> @job_run_history all 5

JOB_NAME                 OWNER    STATUS    COMPLETION_DATE      RUN_DURATION
------------------------ -------- --------- -------------------- ------------
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:31:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:29:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:27:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:25:43            1
TEST_FULL_JOB_DEFINITION JOB_USER SUCCEEDED 21-AUG-2004 15:23:43            1

5 rows selected.

The job logs and job run detail logs allow the tracking of all job activity but do not reveal what the active resource plan was during the job runs.  This information is provided by the window logs, which is covered in the next section.

Window Logs

There is no logging level associated with window logs.  A window log entry is created whenever a window is created, dropped, opened, closed, overlapped, disabled or enabled.  The window_logs.sql script uses the dba_scheduler_window_log view to display window log information about a specific window, or all windows.

* window_logs.sql

-- -- Parameters:
--    1) Specific window name or �all� windows.
-- *****************************************************************

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

column window_name format a30
column operation format a10
column status format a10
column log_date format a27

select
   window_name,
   operation,
   status,
   log_date
from
   dba_scheduler_window_log
where
   window_name = decode(upper('&1'), 'ALL', window_name, upper('&1'))
order by
   log_date
;

An example of the output produced by this script is shown below.

SQL> @window_logs.sql all

WINDOW_NAME                    OPERATION  STATUS     LOG_DATE
------------------------------ ---------- ---------- --------------------
WEEKNIGHT_WINDOW               OPEN                  20-AUG-2004 07:00:01
WEEKNIGHT_WINDOW               CLOSE                 20-AUG-2004 15:00:01
WEEKNIGHT_WINDOW               OPEN                  21-AUG-2004 07:00:00
WEEKNIGHT_WINDOW               CLOSE                 21-AUG-2004 15:00:00
WEEKEND_WINDOW                 OPEN                  21-AUG-2004 15:00:02

5 rows selected.

Each entry in the dba_scheduler_window_log view for a close operation has an associated entry in the dba_scheduler_window_details view.  This view provides additional information including the requested start date, actual start date and window duration.  The window_details.sql script makes use of this view.

* window_details.sql

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

set linesize 120

column window_name format a30
column log_date format a27
column actual_start_date format a27
column actual_duration format 99999

select
   window_name,
   log_date,
   actual_start_date,
   extract(minute from actual_duration) as actual_duration
from
   dba_scheduler_window_details
where

  
window_name = decode(upper('&1'), 'ALL', window_name, upper('&1'))
order by
   log_date
;

An example of the output produced by this script is shown below.

SQL> @window_details.sql all

WINDOW_NAME                    LOG_DATE                    ACTUAL_START_DATE           ACTUAL_DURATION
------------------------------ --------------------------- --------------------------- ---------------
WEEKNIGHT_WINDOW               20-AUG-2004 15:00:01        19-AUG-2004 22:00:01                      0
WEEKNIGHT_WINDOW               21-AUG-2004 15:00:00        20-AUG-2004 22:00:00                      0

2 rows selected.

Now that the various types of scheduler logs have been introduced, it makes sense to examine their management.  It has been shown that the contents of the scheduler logs are managed automatically, but the next section will illustrate how to manually purge the scheduler logs.

Purging Logs

On occasion, it may be necessary to manually purge the scheduler logs prior to any regularly scheduled automatic purge.  This can be accomplished using the purge_log procedure.

PROCEDURE purge_log(
  log_history        IN PLS_INTEGER DEFAULT 0,
  which_log          IN VARCHAR2    DEFAULT 'JOB_AND_WINDOW_LOG',
  job_name           IN VARCHAR2    DEFAULT NULL)

The parameters and usages associated with this procedure are listed below:

* log_history - This determines the age of the logs that should be kept. Valid values range between zero and 999 with the default being zero.

* which_log - This indicates which log or logs should be purged. The possible parameters are job_log, window_log and job_and_window_log, with the latter being the default value.

* job_name - This limits the purge operation of a specific job, job class or comma separated list. By default, this parameter is set to NULL, which indicates logs for all jobs should be purged.

The following example shows how the procedure can be used:

BEGIN
  DBMS_SCHEDULER.purge_log (
    log_history => 5,
    which_log   => 'JOB_AND_WINDOW_LOG',
    job_name    => 'my_text_job');

  DBMS_SCHEDULER.purge_log (
    log_history => 15,
    which_log   => 'JOB_LOG',
    job_name    => 'my_text_job_class');

  DBMS_SCHEDULER.purge_log (
    log_history => 0,
    which_log   => 'WINDOW_LOG',
    job_name    => 'my_text_job');
END;
/

To purge all entries for both the job and window logs, simply call the procedure with no parameters.

SQL> EXECUTE DBMS_SCHEDULER.purge_log;

The auto_purge procedure uses the log_history values defined at the scheduler, job class and job log_history level to determine which logs should be purged. This procedure runs as part of the scheduled purge process, but can also be run manually.

SQL> EXECUTE DBMS_SCHEDULER.auto_purge;

Depending on the circumstances, it is possible to manage the purging of logs manually as well as automatically.  Moving on to the management of resources, it was mentioned previously that windows are involved in the link between the scheduler and the resource manager.  In the following section, the link between these two functional areas will be examined.

 

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