The optimizer automatically determines the DOP for a statement based on the resource requirements of the statement. The optimizer uses the cost of all scan operations (full table scan, index fast full scan, and so on) and the cost of CPU of all operations in the execution plan to determine the necessary DOP for the statement.
However, the optimizer limits the actual maximum DOP to ensure parallel server processes do not overwhelm the system. This limit is set by the parameter PARALLEL_DEGREE_LIMIT. The default for value for this parameter is CPU, which means the number of processes is limited by the number of CPUs on the system (PARALLEL_THREADS_PER_CPU * CPU_COUNT * number of instances available) also known as the default DOP. This default DOP ensures that a single user operation cannot overwhelm the system. By adjusting this parameter setting, you can control the maximum DOP the optimizer can choose for a SQL statement. The parameter value is usually set to a lower limit to ensure some degree of concurrent parallel processing on a system before parallel statement queuing is initiated.
The DOP determined by the optimizer is shown in the notes section of an explain plan output (shown in the following explain plan output), visible either using the explain plan statement or V$SQL_PLAN.
EXPLAIN PLAN FOR SELECT SUM(AMOUNT_SOLD) FROM SH.SALES; PLAN TABLE OUTPUT Plan hash value: 672559287 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU) | Time | Pstart | Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 5 (0) | 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 4 | | | | | | 2 | PX COORDINATOR | | 1 | 4 | | | | | | 3 | PX SEND QC(RANDOM) |:TQ10000| 1 | 4 | 5 (0) | | | | | 4 | SORT AGGREGATE | | 1 | 4 | | | | | | 5 | PX BLOCK ITERATOR | | 960 | 3840 | 5 (0) | 00:00:01 | 1 | 16 | | 6 | TABLE ACCESS FULL | SALES | 960 | 3840 | 5 (0) | 00:00:01 | 1 | 16 | -------------------------------------------------------------------------------------------------- Note ----- - Computed Degree of Parallelism is 2