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