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