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 support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Monitoring Jobs in Oracle 10g Scheduling is Much Easier

Oracle Tips by Burleson Consulting

Monitoring Jobs in Oracle 10g is Much Easier

This chapter will introduce how Oracle jobs can be monitored using both database views and Oracle Enterprise Manager (OEM). The scheduler available in Oracle10g is radically different from the one available in previous versions of Oracle. For this reason, it will be dealt with separately.

Explanations of the dbms_application_info and dbms_system packages are included as they can simplify the identification and monitoring of sessions related to scheduled jobs.

The monitoring of jobs scheduled using the dbms_job package will be presented first.

Monitoring Jobs Prior to 10g

The OEM GUI provided with Oracle9i includes very little support for jobs and scheduling. Chapter 2 of this text explained that jobs can be created and edited via OEM, but there is no support for monitoring running jobs directly.

This leaves only the dba_jobs_running view to identify running jobs. The following jobs_running.sql script lists the currently running jobs:

* jobs_running.sql

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

select
   jr.job,
   s.username,
   s.sid,
   s.lockwait,
   s.logon_time
from
   dba_jobs_running jr,
   v$session s
where
   jr.sid = s.sid
order by
   jr.job
;

The type of output that might be expected from this script is listed below.

SQL> @jobs_running

       JOB USERNAME         SID LOCKWAIT   LOGON_TIME
---------- --------- ---------- ---------- --------------------
        42 JOB_USER         265            23-JUN-2004 08:21:25
        99 JOB_USER         272            23-JUN-2004 08:55:35

2 rows selected.

Identifying the sessions that are executing jobs will allow closer monitoring of what the jobs are actually doing.

There is no job history associated with jobs scheduled using the dbms_job package, unlike those scheduled using the dbms_sheduler package in Oracle10g. It is possible to create a job history by mimicking the Oracle10g job history.

To do this, schema objects must be created to hold the history records. This achieved by using the job_run_details_schema.sql script as shown below.

* job_run_details_schema.sql

grant select on v_$session to job_user;
grant select on dba_jobs_running to job_user;
grant select on v_$sesstat to job_user;
grant select on v_$statname to job_user;

conn job_user/job_user

create table job_run_details (
log_id              NUMBER,
log_date            DATE,
owner               VARCHAR2(30),
job                 NUMBER,
  status              VARCHAR2(30),
error#              NUMBER,
actual_start_date   DATE,
actual_end_date     DATE,
run_duration        INTERVAL DAY(3) TO SECOND(0),
instance_id         NUMBER,
session_id          VARCHAR2(30),
cpu_used            NUMBER,
additional_info     VARCHAR2(4000)
);

alter table job_run_details add (
constraint job_run_details_pk primary key (log_id)
);

create sequence job_run_details_seq;

With the schema objects in place, the supporting packaged procedures available in the job_run_details_api.sql script can be compiled as listed below.

* job_run_details_api.sql

CREATE OR REPLACE PACKAGE job_run_details_api AS

PROCEDURE start_log;

PROCEDURE end_log;

PROCEDURE error_log;

FUNCTION get_job
RETURN job_run_details.job%TYPE;

FUNCTION get_cpu_used
RETURN job_run_details.cpu_used%TYPE;

END job_run_details_api;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY job_run_details_api AS

g_log_id job_run_details.log_id%TYPE;

-- -----------------------------------------------------------------
PROCEDURE start_log AS
-- -----------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
l_owner        job_run_details.owner%TYPE;
l_instance_id job_run_details.instance_id%TYPE;
l_session_id   job_run_details.session_id%TYPE;
BEGIN
SELECT job_run_details_seq.NEXTVAL,
         SYS_CONTEXT('USERENV','SESSION_USER'),
         SYS_CONTEXT('USERENV','INSTANCE'),
         SYS_CONTEXT('USERENV','SESSIONID')
INTO   g_log_id,
         l_owner,
         l_instance_id,
         l_session_id
FROM   dual;

INSERT INTO job_run_details (
    log_id ,
    log_date,
    owner,
    job,
    status,
    actual_start_date,
    instance_id,
    session_id
)
VALUES (
g_log_id,
    SYSDATE,
    l_owner,
    get_job,
    'IN PROGRESS',
    SYSDATE,
    l_instance_id,
    l_session_id
);

COMMIT;
END start_log;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE end_log AS
-- -----------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE job_run_details
SET    status          = 'COMPLETE',
         actual_end_date = SYSDATE,
         run_duration    = NUMTODSINTERVAL(SYSDATE - actual_start_date, 'DAY'),
       cpu_used        = get_cpu_used
WHERE log_id          = g_log_id;

COMMIT;
END end_log;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE error_log AS
-- -----------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
l_error#           job_run_details.error#%TYPE;
l_additional_info job_run_details.additional_info%TYPE;
BEGIN
l_error#          := SQLCODE;
l_additional_info := SQLERRM;

UPDATE job_run_details
SET    status           = 'ERROR',
         actual_end_date = SYSDATE,
         run_duration     = NUMTODSINTERVAL(SYSDATE - actual_start_date, 'DAY'),
         cpu_used         = get_cpu_used,
         error#           = l_error#,
         additional_info = l_additional_info
WHERE log_id           = g_log_id;

COMMIT;
END error_log;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
FUNCTION get_job
RETURN job_run_details.job%TYPE AS
-- -----------------------------------------------------------------
l_job      job_run_details.job%TYPE;
BEGIN
SELECT jr.job
INTO   l_job
FROM   dba_jobs_running jr,
         v$session s
WHERE jr.sid   = s.sid
AND    s.audsid = SYS_CONTEXT('USERENV','SESSIONID');
RETURN l_job;
EXCEPTION
WHEN OTHERS THEN
    RETURN 0;
END get_job;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------

FUNCTION get_cpu_used
RETURN job_run_details.cpu_used%TYPE AS
-- -----------------------------------------------------------------
l_cpu_used      job_run_details.cpu_used%TYPE;
BEGIN
SELECT ss.value
INTO   l_cpu_used
FROM   v$sesstat ss,
         v$statname sn,
         v$session s
WHERE ss.statistic# = sn.statistic#
and    ss.sid        = s.sid
AND    sn.name       = 'CPU used by this session'
AND    s.audsid      = SYS_CONTEXT('USERENV','SESSIONID');
RETURN l_cpu_used;
EXCEPTION
WHEN OTHERS THEN
    RETURN 0;
END get_cpu_used;
-- -----------------------------------------------------------------

END job_run_details_api;
/
SHOW ERRORS

With the package in place, it is necessary to make some adjustments to the existing job code to make sure the history is gathered correctly. The job_run_details_template_proc.sql script provides a template that shows how the job_run_details_api package should be used. At the start of the job, the start_log procedure is called, which creates the appropriate run entry in the job_run_details table. On successful completion of the job, the end_log procedure is called to close the run entry. In the event of a problem, the error_log procedure is called, which records the error code and the error message.

* job_run_details_template_proc.sql

CREATE OR REPLACE PROCEDURE job_run_details_template_proc AS
BEGIN
job_run_details_api.start_log;

-- Do something.
DBMS_LOCK.sleep(30);

-- Test error (uncomment to test).
-- RAISE_APPLICATION_ERROR(-20000, 'For a test error!');

job_run_details_api.end_log;
EXCEPTION
WHEN OTHERS THEN
job_run_details_api.error_log;
END job_run_details_template_proc;
/
SHOW ERRORS

The job_run_details_template_job.sql script creates a test job that calls the template procedure to test the action of the job_run_details_api package.

* job_run_details_template_job.sql

VARIABLE l_job NUMBER;

BEGIN
DBMS_JOB.submit (
    job       => :l_job,
    what      => 'job_run_details_template_proc;',
    next_date => SYSDATE,
    interval => 'SYSDATE + INTERVAL ''1'' MINUTE');

COMMIT;
END;
/

PRINT l_job

With the job created, the contents of the job_run_details table can be queried using the job_run_details.sql script as listed below.

* job_run_details.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
-- Parameters:
-- 1) Specific job or ALL which doesn't limit output.
-- 2) Number of records to be displayed.
-- *****************************************************************

set linesize 200
set verify off

column owner format a15
column status format a12
column completion_date format a20
column run_duration format a20

select
   *
from
   (select
       job,
       owner,
       status,
       TO_CHAR(actual_end_date, 'DD-MON-YYYY HH24:MI:SS') as completion_date,
       run_duration
   from
       job_run_details
    where
       job = decode(upper('&1'), 'ALL', job, upper('&1'))
    order by
       (actual_end_date) DESC) a
where
rownum <= &2
;

The type of output expected from the script is listed below.

SQL> @ job_run_details.sql all 10

JOB OWNER        STATUS       COMPLETION_DATE      RUN_DURATION
--- ------------ ------------ -------------------- -----------------
30 JOB_USER     IN PROGRESS
30 JOB_USER     COMPLETE     23-JUN-2004 19:02:26 +000 00:00:34
30 JOB_USER     COMPLETE     23-JUN-2004 19:01:24 +000 00:00:34
30 JOB_USER     COMPLETE     23-JUN-2004 19:00:22 +000 00:00:33
30 JOB_USER     COMPLETE     23-JUN-2004 18:59:21 +000 00:00:34
30 JOB_USER     COMPLETE     23-JUN-2004 18:58:19 +000 00:00:34
30 JOB_USER     COMPLETE     23-JUN-2004 18:57:17 +000 00:00:33
30 JOB_USER     COMPLETE     23-JUN-2004 18:56:16 +000 00:00:34
30 JOB_USER     COMPLETE     23-JUN-2004 18:55:14 +000 00:00:34
30 JOB_USER     COMPLETE     23-JUN-2004 18:54:12 +000 00:00:33

If the user uncomments the test error in the job_run_details_template_proc.sql script, the resulting error is trapped and recorded. After two runs of the job, the user would expect to see something like the results listed below.

column ADDITIONAL_INFO format a50

select
   error#,
   additional_info,
   status
from
   job_run_details
where
   error# is not null
;
    ERROR# ADDITIONAL_INFO                                    STATUS
---------- -------------------------------------------------- ------
    -20000 ORA-20000: For a test error!                       ERROR
    -20000 ORA-20000: For a test error!                       ERROR

2 rows selected.

The job_run_details table and supporting code can be extended to include additional system statistics as required.

Now that a way to monitor jobs prior to Oracle10g has been presented, it is time to introduce the new and improved scheduler available in Oracle10g.