Oracle

Monitoring Running Jobs

If you have tons of jobs running with dba_jobs then it is not enough to check dba_jobs for monitoring purpose. you need to add extra spice like client identifier or module for the running part. This way you can have a clear Idea for an active session if it belongs to a job and which job is active at that moment. Also this extra info goes to the ASH views and great for the historical monitoring.

For this purpose I use the following procedure and I call it before every job.

select what from dba_jobs ;

begin
admin.job_set_id_module;
PROD_RUN_JOB;
end;

CREATE OR REPLACE PROCEDURE ADMIN.JOB_SET_ID_MODULE
AS
v_bg_job_id PLS_INTEGER;
v_fg_job_id PLS_INTEGER;
BEGIN

SELECT SYS_CONTEXT (‘USERENV’, ‘BG_JOB_ID’)
INTO v_bg_job_id
FROM DUAL;

SELECT SYS_CONTEXT (‘USERENV’, ‘FG_JOB_ID’)
INTO v_fg_job_id
FROM DUAL;

IF v_bg_job_id IS NOT NULL then

DBMS_SESSION.SET_IDENTIFIER(‘DB_JOB–>’||v_bg_job_id);
DBMS_APPLICATION_INFO.SET_MODULE(‘DB_JOB–>’||v_bg_job_id,NULL);

ELSIF v_fg_job_id IS NOT NULL then

DBMS_SESSION.SET_IDENTIFIER(‘DB_JOB–>’||v_fg_job_id||’ Manuel’);
DBMS_APPLICATION_INFO.SET_MODULE(‘DB_JOB–>’||v_fg_job_id||’ Manuel’,NULL);

END IF;

EXCEPTION WHEN OTHERS
THEN NULL;

END;
/

now we can monitor the running jobs by their module info.

select * from v$session where status = 'ACTIVE' and username is not null and module like 'DB_JOB%';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s