JOB_QUEUE_PROCESSES is an initialization parameter which controls the maximum number of processes of scheduler jobs in an instance.
In this post, we'll talk about the following topics.
Default Value of JOB_QUEUE_PROCESSES
The default value of JOB_QUEUE_PROCESSES derives from one of the following ways, whichever is smaller.
- CPU_COUNT * 20
- SESSIONS / 4
Most likely, the first way is the default value of JOB_QUEUE_PROCESSES in your database system.
SQL> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 80
For CPU-intensive database, it may adopt the second way to derive the default value from SESSIONS, it's also a derived parameter.
How to Disable Scheduler Job?
Oracle provides a way to let you turn off all scheduler jobs without changing their attributes. Just simply set JOB_QUEUE_PROCESSES to 0 to prevent scheduler jobs from running.
SQL> alter system set job_queue_processes=0 scope=both sid='*';
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
How to Go Back to the Default?
Once you have recovered from previous state, you may decide to go back the default value of JOB_QUEUE_PROCESSES.
SQL> alter system reset job_queue_processes scope=both sid='*';
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 80
We're back.