PARALLEL_DEGREE_LIMIT limits the degree of parallelism used by the optimizer to ensure that parallel server processes do not flood the system.
Modifiable in a PDB
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.
The maximum degree of parallelism is limited by the number of CPUs in the system. The formula used to calculate the limit is
CPU_COUNT* the number of instances available (by default, all the opened instances on the cluster but can be constrained using
PARALLEL_INSTANCE_GROUPor service specification). This is the default.
This value is equivalent to the
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_IOprocedure on the system to use the
IOsetting. This procedure will calculate the total system throughput and the maximum I/O bandwidth per process.
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_POLICYis set to
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