Tuesday, February 22, 2011

How to determine the correct setting for JOB_QUEUE_PROCESSES

How to determine the correct setting for JOB_QUEUE_PROCESSES
============================================================
For this, you can create a table to insert the count value of running job in a particular time. then monitor few days or weeks , then decide what would be the correct value........
like below.

Parameter JOB_QUEUE_PROCESSES is maximum number of jobs that would ever be run concurrently on your Database.

1) First set JOB_QUEUE_PROCESSES higher than the most number of jobs that could ever run concurrently

2) Login as SYS

3) Create the table to do the monitoring

CREATE TABLE highest_concurrent_job (
SAMPLE_DATE DATE,
JOBS_RUNNING NUMBER);

4) Create the job to do the monitoring

CREATE OR REPLACE PROCEDURE DRP_highest_concurrent_job IS
BEGIN
INSERT INTO highest_concurrent_job
SELECT SYSDATE, COUNT(*) FROM  DBA_JOBS_RUNNING;
COMMIT;
END;
/

5) Create the job queue job to do the monitoring

DECLARE
v_jobno number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => v_jobno,
WHAT => 'DRP_highest_concurrent_job;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

6) select * from MAX_CONCURRENT_JOBS ;

2 comments:

Anonymous said...

how to determine the INTERVAL (5/1440) on what basis this value assign
for e.g: if we have a job runing every 10 second
So this interval value would be 5 minute or need to change on what basis

Thankyou

Muhammad Abdul Halim said...

Thanks ,
Run this query, i think you can find you answer..

SELECT SYSDATE "Current_time",
SYSDATE + 7 "every 7 days",
SYSDATE + 1 "daily",
SYSDATE + 4 / 24 "every 4 hours",
SYSDATE + 10 / 1440 "every 10 minutes",
SYSDATE + 30 / 86400 "every 30 seconds",
SYSDATE + 10 / 86400 "every 10 seconds",
SYSDATE + 5 / 86400 "every 5 seconds"
FROM DUAL ;


Regards
Halim