Automatic Degree of Parallelism and In-Memory Parallel Execution in Oracle Database

Parallel execution in Oracle Database enables the decomposition of a SQL statement into multiple smaller tasks, each executed concurrently by server processes. The degree of parallelism (DOP) determines the number of parallel execution servers used. To optimize resource utilization and query performance, Oracle provides the PARALLEL_DEGREE_POLICY parameter, which controls whether the database automatically determines DOP, manages statement queuing, and leverages in-memory parallel execution.

Parameter Specification

The PARALLEL_DEGREE_POLICY parameter can be set at the system or session level. Its properties are summarized in Table 1.

PARALLEL_DEGREE_POLICY Properties

Operational Modes

MANUAL

Disables automatic DOP, statement queuing, and in-memory parallel execution. Parallel execution behavior reverts to that of Oracle Database 11g Release 1 and earlier. This mode is the default.

LIMITED

Enables Auto DOP only for statements that reference tables or indexes explicitly defined with the DEFAULT parallel degree using the PARALLEL clause. Statement queuing and in-memory parallel execution remain disabled. All other statements follow MANUAL behavior.

AUTO

Enables Auto DOP, statement queuing, and in-memory parallel execution. During parsing, Oracle estimates the execution time and cost. If the estimated cost exceeds the threshold defined by PARALLEL_MIN_TIME_THRESHOLD (default 10 or 30 seconds), the database automatically calculates a DOP based on the query requirements and system resources (e.g., CPU_COUNT, PARALLEL_MAX_SERVERS).

ADAPTIVE

Extends the AUTO mode by enabling performance feedback. After the initial execution of a SQL statement, the chosen DOP is compared with the DOP derived from actual execution performance metrics (e.g., CPU time). If a significant discrepancy exists, the statement is marked for re-parsing, and subsequent executions benefit from the refined DOP calculation.

Impact on Execution Plans

To illustrate the effect of PARALLEL_DEGREE_POLICY on query execution plans, consider the following structured analysis.

Initial Configuration (Default Behavior)

Without altering the parameter, the execution plan of a simple query is generated serially:

EXPLAIN PLAN SET STATEMENT_ID = 'MyPlan0001' INTO plan_table FOR SELECT * FROM hr.employees; 

SELECT id, LPAD(' ', 2*(LEVEL-1)) || operation AS operation, options, object_name, object_alias, position FROM plan_table START WITH id = 0 AND statement_id = 'MyPlan0001' CONNECT BY PRIOR id = parent_id AND statement_id = 'MyPlan0001' ORDER BY id;

Modified Configuration (Adaptive Mode)
Enabling adaptive parallel execution:

ALTER SESSION SET PARALLEL_DEGREE_POLICY = 'ADAPTIVE'; 

EXPLAIN PLAN SET STATEMENT_ID = 'MyPlan0001' INTO plan_table FOR SELECT * FROM hr.employees; 

-- Query plan retrieval as above or below


SET LINESIZE 130
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','MyPlan0001','BASIC'));

Under ADAPTIVE, the plan reflects parallel execution strategies, including distribution methods and parallel server allocation, as determined by the optimizer and refined through performance feedback.

You can also use PARALLEL hint in your queries for parallelism :

Parallel Hints

Oracle provides statement-level and object-level hints to control parallelism independent of PARALLEL_DEGREE_POLICY. These hints supersede legacy hints such as PARALLEL_INDEX and NO_PARALLEL_INDEX.

Statement-Level Hints

Example: Computed DOP

SELECT /*+ PARALLEL */ last_name FROM employees;

Example: Auto DOP with Possible Serial Execution

SELECT /*+ PARALLEL (AUTO) */ last_name FROM employees;

Object-Level Hints

Object-level hints are used within a table or index alias context and override table-level parallel definitions.

Example: Overriding Table DOP

CREATE TABLE parallel_table (col1 NUMBER, col2 VARCHAR2(10)) PARALLEL 5;

SELECT /*+ PARALLEL (MANUAL) */ col2 FROM parallel_table;

Example: Explicit DOP Override

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name FROM employees hr_emp;

Example: Default Computed DOP

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name FROM employees hr_emp;

We should also consider PARALLEL_DEGREE_LIMIT parameter if we set PARALLEL_DEGREE_POLICY .