Oracle教程 2023-02-26

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

本站中文解释

PARALLEL_DEGREE_LIMIT参数定义了 Oracle 并发最大的数量。这样在运行大的 DML 操作，Oracle可以开多个 parallel operator 来提高效率和减少操作要求的时间。如果设置为0，则表示禁用了parallel operator，一切都将在一个会话中处理。

PARALLEL_DEGREE_LIMIT的正确设置依赖于实际的系统，必须根据系统的实际类型、配置、性能和可用资源来设置该参数。

正确设置PARALLEL_DEGREE_LIMIT参数的步骤如下：

1、运行以下SQL语句，查看服务器的核心数：
SELECT value FROM v$parameter WHERE name = ‘cpu_count’;

2、设置PARALLEL_DEGREE_LIMIT，乘以4的最小值建议为该参数的值：
ALTER SYSTEM SET PARALLEL_DEGREE_LIMIT=112 SCOPE=BOTH;

官方英文解释

PARALLEL_DEGREE_LIMIT limits the degree of parallelism used by the optimizer to ensure that parallel server processes do not flood the system.

Property	Description
Parameter type	String
Syntax	`PARALLEL_DEGREE_LIMIT = { CPU \| AUTO \| IO \|` `integer` `}`
Default value	`CPU`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`
Modifiable in a PDB	Yes
Basic	No

With automatic degree of parallelism, Oracle automatically decides whether a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement. However, PARALLEL_DEGREE_LIMIT enforces the limit for the degree of parallelism used by the optimizer.

Values

CPU

The maximum degree of parallelism is limited by the number of CPUs in the system. The formula used to calculate the limit is PARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of instances available (by default, all the opened instances on the cluster but can be constrained using PARALLEL_INSTANCE_GROUP or service specification). This is the default.
AUTO

This value is equivalent to the CPU value.
IO

The maximum degree of parallelism the optimizer can use is limited by the I/O capacity of the system. The value is calculated by dividing the total system throughput by the maximum I/O bandwidth per process. You must run the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure on the system to use the IO setting. This procedure will calculate the total system throughput and the maximum I/O bandwidth per process.
integer

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to ADAPTIVE, AUTO, or LIMITED.

本站中文解释

官方英文解释

相关推荐