Oracle教程 2023-02-26

Oracle 参数 JOB_QUEUE_PROCESSES 官方解释，作用，如何配置最优化建议

本站中文解释

JOB_QUEUE_PROCESSES参数用于控制Oracle能够并发进行作业处理的最大进程数。它控制着允许从表SYS.DBA_SCHEDULER_JOB_RUN_DETAILS选择作业，或者从表SYS.DBA_SCHEDULER_RUNNING_JOBS选择当前正在运行的作业的同时的最大数目。因此，该参数不会影响作业本身的运行，但它可以限制作业的同时运行数目。

参数JOB_QUEUE_PROCESSES的可用值是介于1和100之间的数字，默认的值是10。如果要修改该参数的值，可以执行以下步骤：

(1) 使用命令行或SQL*Plus连接到Oracle数据库

(2) 执行以下SQL：

Alter system set job_queue_processes = x (x为想要设置的值);

(3) 如果要使更改生效，应当重新启动实例

Alter system restart;

官方英文解释

JOB_QUEUE_PROCESSES specifies the maximum number of job slaves per CDB or PDB that can be created for the execution of DBMS_JOB jobs and Oracle Scheduler (DBMS_SCHEDULER) jobs.

Property	Description
Parameter type	Integer
Default value	Derived. The lesser value of: `CPU_COUNT` * 20 `SESSIONS` / 4 If the result of the above derivation is less than twice the number of open containers in the CDB, then the value of this parameter is adjusted to equal twice the number of open containers in the CDB. Containers include `CDB$ROOT`, `PDB$SEED`, PDBs, application roots, application seeds, and application PDBs. You can obtain the number of open containers in a CDB with the following query: `SELECT COUNT(*) FROM V$CONTAINERS WHERE open_mode != 'MOUNTED';`
Modifiable	`ALTER SYSTEM`
Modifiable in a PDB	Yes
Range of values	`0` to `4000`
Basic	No
Oracle RAC	Multiple instances can have different values.

DBMS_JOB and Oracle Scheduler share the same job coordinator and job slaves, and they are both controlled by the JOB_QUEUE_PROCESSES parameter. The actual number of job slaves created for Oracle Scheduler jobs is auto-tuned by the Scheduler depending on several factors, including available resources, Resource Manager settings, and currently running jobs. However, the combined total number of job slaves running DBMS_JOB jobs and Oracle Scheduler jobs in a CDB or PDB can never exceed the value of JOB_QUEUE_PROCESSES for that CDB or PDB.

The default value for JOB_QUEUE_PROCESSES provides a compromise between quality of service for applications and reasonable use of system resources. However, it is possible that the default value does not suit every environment. In such cases, you can use the following guidelines to fine tune this parameter:

In a CDB root:
Set JOB_QUEUE_PROCESSES to the maximum number of job slaves that can be used simultaneously in the entire CDB. Oracle recommends that you set the value of this parameter to at least twice the number of open containers in the CDB, otherwise, there might be severe starvation between PDBs trying to run multiple jobs. If JOB_QUEUE_PROCESSES is set to 0 in a CDB root, then DBMS_JOB and Oracle Scheduler jobs cannot run in the CDB root or in any PDB, regardless of the JOB_QUEUE_PROCESSES setting at the PDB level.
In a PDB:
Set JOB_QUEUE_PROCESSES to the maximum number of job slaves that can be used simultaneously in the PDB. The actual number depends on the resources assigned by Resource Manager and the demand in other containers. When multiple PDBs request jobs, Oracle Scheduler attempts to give all PDBs a fair share of the processes. Oracle recommends that you set the value of this parameter to at least 2 in a PDB. However, if you do not want to run DBMS_JOB and Oracle Scheduler jobs in a PDB, then set JOB_QUEUE_PROCESSES to 0 in the PDB.

Materialized views and AutoTask use Oracle Scheduler for automatic refreshes. Setting JOB_QUEUE_PROCESS to 0 will disable these features and any other features that use Oracle Scheduler or DBMS_JOB.

Note:

DBMS_JOB is deprecated in Oracle Database 12c Release 2 (12.2.0.1) and may be removed in a future release. Oracle recommends that you use DBMS_SCHEDULER instead.

本站中文解释

官方英文解释

相关推荐