job_queue_processes and dbms_scheduler

This parameter has some history:

from the reference manual

v8.1.7:

Parameter class:	Dynamic. Scope = ALTER SYSTEM.
Default value:	0
Range of values:	0 to 36
Oracle Parallel Server:	Multiple instances can have different values.
JOB_QUEUE_PROCESSES is relevant in replication environments. It specifies the number of SNPn job queue processes per instance (SNP0, ... SNP9, SNPA, ... SNPZ). Job queue processes process requests created by DBMS_JOB. Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher. 9iR2: Parameter type Integer Default value 0 Parameter class Dynamic: ALTER SYSTEM Range of values 0 to 1000 Real Application Clusters Multiple instances can have different values. JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package. Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher. 10gR1: Parameter type Integer Default value 0 Modifiable ALTER SYSTEM Range of values 0 to 1000 Basic Yes Real Application Clusters Multiple instances can have different values. JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package. Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher. 10gR2: Parameter type Integer Default value 0 Modifiable ALTER SYSTEM Range of values 0 to 1000 Basic Yes Real Application Clusters Multiple instances can have different values. JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package. Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher. 11gR1: Parameter type Integer Default value 1000 Modifiable ALTER SYSTEM Range of values 0 to 1000 Basic Yes Real Application Clusters Multiple instances can have different values. JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package. Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher. 11gR2: Parameter type Integer Default value 1000 Modifiable ALTER SYSTEM Range of values 0 to 1000 Basic Yes Oracle RAC Multiple instances can have different values. JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package. Some job queue requests are created automatically. An example is refresh support for materialized views. If you wish to have your materialized views updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.

observations

This was just looking at the job_queue_processes parameter in the various reference guides. Not much differences, other than a variation in the values it default has and maximally can have. The surprise is what else it is controlling. We all know that the way we can control the maximum number of concurrent jobs by setting the session pool in the resource groups where the jobs are running in. We could also limit the max number of job slave processes by setting this parameter it the scheduler global attributes level.

surprisingly, in the different release of oracle that have the Oracle Scheduler implemented (starting with 10gR1) there are some relations to be found with the job_queue_processes parameter. Oracle documentation is very vague on this point with phrases to be found like 'the job_queue_processes has no effect on the dbms_scheduler jobs' varying to 'the job_queue_processes has some impact on the dbms_scheduler jobs'.

And now comes 11.2.0.1 where job_queue_processes = 0 prevents all scheduled jobs to run... I can't tell if this is intentionally but surprising, or must I say disappointing it is. In earlier releases it is mentioned that setting job_queue_processes simply disables the outdated dbms_jobs jobs and has no influence on dbms_scheduler jobs. When working on issues is became clear that in 10gR2 and 11gR1 this parameter has effect and setting it to a low value will cause jobs to stop being started.

I really hope that Oracle is going to fix this unexpected, undocumented and unwanted relation between job_queue_processes and dbms_scheduler jobs. It is a bit confusing the way it now is.

The administrators guide

10gR1

Here the story is very clear: states:
Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Even though Oracle recommends you switch from DBMS_JOB to DBMS_SCHEDULER, DBMS_JOB is still supported for backward compatibility. Both Scheduler packages share the same job coordinator, but DBMS_JOB does not have the auto start and stop functionality. Instead, the job coordinator is controlled by the JOB_QUEUE_PROCESSES initialization parameter. When JOB_QUEUE_PROCESSES is set to 0, the coordinator is turned off and when it has a non-zero value it is turned on.The JOB_QUEUE_PROCESSES initialization parameter is only used for DBMS_JOB. When this parameter is set to a non-zero value, auto start and stop no longer apply because the coordinator will always be up and running. In this case, the coordinator will take care of execution of both DBMS_SCHEDULER and DBMS_JOB jobs.

If the initialization parameter is set to 0, or if it is not set at all, no DBMS_JOB jobs will be run, however, the auto start and stop feature will be used for all DBMS_SCHEDULER jobs and windows. If there is a DBMS_SCHEDULER job to be executed, the coordinator will be started and the job will be executed. However, DBMS_JOB jobs still will not be run.

10gR2

The same for 10gR2 release :
Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Even though Oracle recommends you switch from DBMS_JOB to DBMS_SCHEDULER, DBMS_JOB is still supported for backward compatibility. Both Scheduler packages share the same job coordinator, but DBMS_JOB does not have the auto start and stop functionality. Instead, the job coordinator is controlled by the JOB_QUEUE_PROCESSES initialization parameter. When JOB_QUEUE_PROCESSES is set to 0, the coordinator is turned off and when it has a non-zero value it is turned on.The JOB_QUEUE_PROCESSES initialization parameter is only used for DBMS_JOB. When this parameter is set to a non-zero value, auto start and stop no longer apply because the coordinator will always be up and running. In this case, the coordinator will take care of execution of both DBMS_SCHEDULER and DBMS_JOB jobs.

If the initialization parameter is set to 0, or if it is not set at all, no DBMS_JOB jobs will be run, however, the auto start and stop feature will be used for all DBMS_SCHEDULER jobs and windows. If there is a DBMS_SCHEDULER job to be executed, the coordinator will be started and the job will be executed. However, DBMS_JOB jobs still will not be run.

and the following tells about the same:
Scheduler Attribute max_job_slave_processes

The initialization parameter JOB_QUEUE_PROCESSES only applies to DBMS_JOB. When DBMS_SCHEDULER is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs. In special scenarios a dba can still limit the maximum number of slaves to be started by the coordinator by setting the MAX_JOB_SLAVE_PROCESSES with the DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure.

11gR1

The text gets a little more confusing but again tells about the same :
Using Both DBMS_JOB and Oracle Scheduler

DBMS_JOB and Oracle Scheduler (the Scheduler) use the same job coordinator to start job queue processes. (For the Scheduler, these processes are called job slave processes. This section uses these terms interchangeably.) Although you typically use the JOB_QUEUE_PROCESSES initialization parameter to limit the number job queue processes for DBMS_JOB and you use the Scheduler attribute max_job_slave_processes to limit the number of job slave processes for the Scheduler, the Scheduler is affected by the JOB_QUEUE_PROCESSES parameter.

The maximum number of job slave processes for Scheduler is determined by the lesser of the values of JOB_QUEUE_PROCESSES and max_job_slave_processes. For example:

*

If JOB_QUEUE_PROCESSES is set to 10 and max_job_slave_processes is set to 20, the job coordinator will start no more than 10 job slave processes to be shared between DBMS_JOB and the Scheduler.
*

If JOB_QUEUE_PROCESSES is 20 and max_job_slave_processes is 10, the coordinator will start up to 20 job slave processes. The Scheduler can use only 10 of these, but DBMS_JOB can use all 20.

If JOB_QUEUE_PROCESSES is 0, DBMS_JOB is disabled, and the maximum number of job slave processes for Scheduler is controlled by the max_job_slave_processes Scheduler attribute.

11gR2

The docu is the same as for 11gR1 :
Using Both DBMS_JOB and Oracle Scheduler

DBMS_JOB and Oracle Scheduler (the Scheduler) use the same job coordinator to start job queue processes. (For the Scheduler, these processes are called job slave processes. This section uses these terms interchangeably.) Although you typically use the JOB_QUEUE_PROCESSES initialization parameter to limit the number job queue processes for DBMS_JOB and you use the Scheduler attribute max_job_slave_processes to limit the number of job slave processes for the Scheduler, the Scheduler is affected by the JOB_QUEUE_PROCESSES parameter.

The maximum number of job slave processes for Scheduler is determined by the lesser of the values of JOB_QUEUE_PROCESSES and max_job_slave_processes. For example:

*

If JOB_QUEUE_PROCESSES is set to 10 and max_job_slave_processes is set to 20, the job coordinator will start no more than 10 job slave processes to be shared between DBMS_JOB and the Scheduler.
*

If JOB_QUEUE_PROCESSES is 20 and max_job_slave_processes is 10, the coordinator will start up to 20 job slave processes. The Scheduler can use only 10 of these, but DBMS_JOB can use all 20.

If JOB_QUEUE_PROCESSES is 0, DBMS_JOB is disabled, and the maximum number of job slave processes for Scheduler is controlled by the max_job_slave_processes Scheduler attribute.

http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/appendix_a001.htm tells about the same:

Using Both DBMS_JOB and Oracle Scheduler

DBMS_JOB and Oracle Scheduler (the Scheduler) use the same job coordinator to start job queue processes. (For the Scheduler, these processes are called job slave processes. This section uses these terms interchangeably.) Although you typically use the JOB_QUEUE_PROCESSES initialization parameter to limit the number job queue processes for DBMS_JOB and you use the Scheduler attribute max_job_slave_processes to limit the number of job slave processes for the Scheduler, the Scheduler is affected by the JOB_QUEUE_PROCESSES parameter.

The maximum number of job slave processes for Scheduler is determined by the lesser of the values of JOB_QUEUE_PROCESSES and max_job_slave_processes. For example:

• If JOB_QUEUE_PROCESSES is set to 10 and max_job_slave_processes is set to 20, the job coordinator will start no more than 10 job slave processes to be shared between DBMS_JOB and the Scheduler.

• If JOB_QUEUE_PROCESSES is 20 and max_job_slave_processes is 10, the coordinator will start up to 20 job slave processes. The Scheduler can use only 10 of these, but DBMS_JOB can use all 20.

If JOB_QUEUE_PROCESSES is 0, DBMS_JOB is disabled, and the maximum number of job slave processes for Scheduler is controlled by the max_job_slave_processes Scheduler attribute.

This certainly does not apply to 11.2.0.1. In 11.2.0.1 the number of scheduler jobs that can run concurrently is controlled by the job_queue_processes parameter, also when job_queue_processes = 0. A bit unexpected.

Also, a bit typical that for a parameter that is supposed to control a deprecated feature - dbms_job - has a default value changed from 0 to 1000. This gives me some nagging feeling there is more happening with this parameter.

conclusion

I am glad with the added features for the Oracle Scheduler in 11gR2, compared to 11gR1. It would even be better if the implementation would more closely match the documentation. With job control I would at least like to be able to control the concurrent number of jobs in a reliable and resource sensitive way.