Solved

Optimum value for job queue processes parameter

  • 10 December 2019
  • 4 replies
  • 3580 views

Userlevel 6
Badge +13

Sometimes we have noticed performance drawbacks due to the value set for the "job queue processes” parameter in the oracle database. What is the correct formula for the set this value correctly and what are the things that need to consider?
 

icon

Best answer by Chathura Karunarathna 10 December 2019, 09:44

View original

4 replies

Userlevel 5
Badge +9

We can decide the optimum value for Job Queue Processes parameter using the below formula.
(TotalIFSBatchQueueProcesses + OracleReservedProcesses + 3) <= JobQueueProcesses

Once the value is decided please use the below command to set the value in the database.
Alter system set job_queue_processes=<value> scope=both;

Userlevel 7
Badge +31

Number of job_queue_processes should usually be "the sum of processes assigned for each batch queue in batch queue configuration” + “5 more processes for Oracle internal jobs”.

Theoretical limit for job_queue_processes as given by Oracle is 4000 processes for Oracle 12c release 2, but as you have mentioned, it could cause performance issues if it is given a high value. 

To get the total number of processes in batch queue configuration, you can execute the following query:

SELECT SUM(process_number) FROM batch_queue;

 

To set the value for job queue processes, you can use the below command:

ALTER SYSTEM SET job_queue_processes=<value> scope=both;

Userlevel 7
Badge +15

We can decide the optimum value for Job Queue Processes parameter using the below formula.
(TotalIFSBatchQueueProcesses + OracleReservedProcesses + 3) <= JobQueueProcesses

Once the value is decided please use the below command to set the value in the database.
Alter system set job_queue_processes=<value> scope=both;

 
@Chathura Karunarathna has mentioned the basic equation we follow to assign job_queue_processes in a database. The additional 3 Processes at the end of the equation are Reserved for Wiggle Room.


Usually, Oracle Reserved Processes is set to 5, since only very busy batch system will have n=more than five concurrently executing batch jobs.
If job_queue_processes is too low, jobs may be seen waiting in the execution queue for a process to become free.  You can also monitor the job queue and set job_queue_processes to the high watermark of executing jobs.  You can write a monitor to track the maximum count from dba_jobs_running to find the optimal value for job_queue_processes.

I hope this gives a broader picture to the equation mentioned :smile:

Userlevel 5
Badge +14

Can you specify exactly what falls under OracleReservedProcesses ? A customer is asking for this.

Reply