Skip to content

JOB_QUEUE_PROCESSES=4000 CAN ONLY HANDLE A MAXIMUM OF 1024 JOBS

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

Setting job_queue_processes=4000 should allow a maximum of 4000 jobs to be executed simultaneously as long as OS resources allow this to happen. However, on a system with sufficient resources the maximum number of simultaneous jobs that we can run is 1024.

Testcase:

SQL> declare
job_num number;
begin
for c in 1..4000 loop
dbms_job.submit(job=>job_num,
what=>’begin dbms_lock.sleep(900);end;’,
next_date => sysdate);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
56

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
167

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
248

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
304

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
494

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
768

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
1024 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

SQL> select count(*) from dba_jobs_running;

COUNT(*)
———-
1024 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Despite the fact that sufficient resources are available the number of maximum simultaneous jobs will not overcome a limitation of 1024.

 

CAUSE

This issue is caused by

BUG 26870128 – 12.2 NEW FEATURE JOB_QUEUE_PROCESSES=4000 CAN ONLY HANDLE A MAXIMUM OF 1024 JOBS

 

SOLUTION

Apply patch for BUG 26870128 – 12.2 NEW FEATURE JOB_QUEUE_PROCESSES=4000 CAN ONLY HANDLE A MAXIMUM OF 1024 JOBS where the issue is fixed.

The fix is applicable for both DBMS_SCHEDULER and well as DBMS_JOB since the problem is caused by the algorithm that spawns processes. Before applying this fix make sure that:
– there is no Resource Manager Plan that could be limiting the number of jobs or processes;

– job_queue_processes is high enough to accommodate 4000 jobs;

– process parameter in init.ora is also high enough;

– sessions is at least 2x the desired processes;

– transactions is also high enough;

– the OS user can spawn that many processes.