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 ;

3 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

halimdba 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

Md. Tanweer Qasim Mohnavi said...

Assalamo Alaikum

I got the error when I run this script !

INSERT INTO pending_trans$ (local_tran_id, global_tran_fmt, global_oracle_id, state, status, session_vector, reco_vector, type#, fail_time, reco_time )
2 VALUES ('&&TRANSACTION_ID', 306206, /* */
3 'XXXXXXX.12345.1.2.3', /* These values can be used without any */
4 'prepared',
5 'P', /* modification. Most of the values are */
6 HEXTORAW ('00000001'), /* constant. */
7 HEXTORAW ('00000000'), /* */
8 0, SYSDATE,
9 SYSDATE
10 );
old 2: VALUES ('&&TRANSACTION_ID', 306206, /* */
new 2: VALUES ('33.9.424469', 306206, /* */
INSERT INTO pending_trans$ (local_tran_id, global_tran_fmt, global_oracle_id, state, status, session_vector, reco_vector, type#, fail_time, reco_time )
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_PENDING_TRANS1) violated

Need your suggestion ?

Thanks