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.

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.

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.

Oracle Database VLDB and
Partitioning Guide for information about automatic degree of parallelism

Oracle Database PL/SQL
Packages and Types Reference for information on the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 Oracle 参数 PARALLEL_DEGREE_LIMIT 官方解释，作用，如何配置最优化建议