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 


 

 

 


 

 

 

 

 

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

-- *************************************************
-- 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
-- *************************************************
conn sys/password as sysdba

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

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

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


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


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

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

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.

 

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