Skip to main content
Solved

Optimum value for job queue processes parameter

  • December 10, 2019
  • 4 replies
  • 4666 views

Charith Epasinghe
Superhero (Employee)
Forum|alt.badge.img+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?
 

Best answer by Chathura Karunarathna

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;

View original
Did this topic help you find an answer to your question?

4 replies

Chathura Karunarathna
Hero (Employee)
Forum|alt.badge.img+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;


Charith Epitawatta
Ultimate Hero (Employee)
Forum|alt.badge.img+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;


Ragaventhan Sathananda
Superhero (Partner)
Forum|alt.badge.img+15
Chathura Karunarathna wrote:

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:


ToBeNL
Hero (Employee)
Forum|alt.badge.img+14
  • Hero (Employee)
  • 116 replies
  • May 27, 2020

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


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings