Automatic Degree of Parallelism

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what degree of parallelism (DOP) it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.

The following is a summary of parallel statement processing when parallel degree policy is set to automatic.

  1. A SQL statement is issued.

  2. The statement is parsed and the optimizer determines the execution plan.

  3. The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.

    1. If the expected execution time is less than the threshold limit, the SQL statement is run serially.

    2. If the expected execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates, including factoring for any defined resource limitations.

    3. If all tables referenced by a SQL statement use In-Memory Column Store (IM column store), then PARALLEL_MIN_TIME_THRESHOLD defaults to 1.