How to Use Oracle Parallel Feature

Oracle Parallel Execution: Concepts, Types, and How to Use

Edition Requirement: Enterprise Edition only.

Summary

This is kind of a summary of how Oracle Parallel Execution works, when it can be used, and the main features behind Automatic Parallel Degree (AutoDOP), including automatic DOP, parallel statement queuing, and in-memory parallel execution. I will also lists the types of operations that support parallelism (DDL, DML, DQL), shows practical SQL examples (hints, session-level forcing), and finish with quick queries to monitor parallel sessions.

1) Parallelism Concepts

When a parallel statement starts, Oracle creates a coordinator query which acquires the requested number of parallel servers. Required operations (as planned by the optimizer—e.g., a full table scan used in a join) are executed in parallel whenever possible. After parallel servers finish their portions, the coordinator performs the non-parallel parts (e.g., aggregations like COUNT) and returns the result.

1.1 Automatic Parallel Degree Policy

Controlled by PARALLEL_DEGREE_POLICY:

• MANUAL — Disables Automatic DOP features (default). Parallelism must be explicit (DDL, hints, ALTER SESSION).
• LIMITED — Enables only automatic DOP.
• AUTO — Enables automatic DOP, parallel statement queuing, and in-memory parallel execution.

With AUTO, Oracle decides if/when to parallelize and the degree of parallelism. It can also queue statements until sufficient resources are available.

1.2 In-memory Parallel Execution

With PARALLEL_DEGREE_POLICY = AUTO, objects accessed in parallel may be cached in the buffer cache (SGA). The decision is heuristic (frequency of access, object size).

1.3 Parallel Statement Queuing

If a statement requests parallel servers beyond what is available (bounded by PARALLEL_SERVERS_TARGET), Oracle places it in a queue and runs it when enough servers are free.

2) Types of Parallelism

• DDL — CREATE INDEX, CTAS, ALTER TABLE MOVE, ALTER INDEX REBUILD, etc.
• DML — INSERT, UPDATE, DELETE, MERGE.
• DQL — SELECT.
• Function-based / Table functions — see Oracle docs.
• Other — Replication, External Tables, SQL*Loader (see Oracle docs).

3) Parallel Query (SELECT)

A SELECT can run in parallel when one of the following is true:

• There is a parallel hint on an object (e.g., PARALLEL or PARALLEL_INDEX).
• The object has a PARALLEL attribute defined in its DDL.
• Automatic DOP is enabled.
• ALTER SESSION FORCE PARALLEL QUERY (PARALLEL n) is used.

And one of these access methods occurs:

• Full table scan
• Index range scan spanning multiple partitions
• Index fast full scan
• Parallel table function

3.1 Example with hint

SELECT
  /*+ PARALLEL(employees, 4) */
  employee_id, last_name
FROM employees
WHERE department_id = 20;

4) Parallel DDL

4.1 What can be parallelized?

Non-partitioned tables & indexes: CREATE INDEX, CTAS, ALTER TABLE MOVE, ALTER INDEX REBUILD.

Partitioned objects: CREATE INDEX, CTAS, ALTER TABLE {MOVE|SPLIT|COALESCE} PARTITION, ALTER INDEX {REBUILD|SPLIT} PARTITION.

All these can run in NOLOGGING (parallel or serial). Parallel DDL is not allowed on non-partitioned tables with LOB columns.

Use NOLOGGING in CREATE/ALTER statements to reduce undo/redo generation.

4.2 When does DDL run in parallel? When one of these is met.

• When the PARALLEL clause is specified in the DDL (stored in the data dictionary).
• When Automatic DOP is enabled.
• When using ALTER SESSION FORCE PARALLEL DDL (PARALLEL n).

4.3 CTAS in parallel

CREATE TABLE objetos_db
PARALLEL 5
AS
SELECT * FROM DBA_OBJECTS;

4.4 CREATE INDEX in parallel

CREATE INDEX employees_ix
ON employees (department_id)
PARALLEL 4;

5) Parallel DML

Rules for UPDATE, MERGE, DELETE:

• The target table has PARALLEL in its DDL, or
• You use the PARALLEL hint, or
• Automatic DOP is enabled, or
• You use ALTER SESSION FORCE PARALLEL DML (PARALLEL n).

5.1 INSERT with APPEND + PARALLEL

INSERT
/*+ APPEND PARALLEL(departments, 4) */
INTO departments
SELECT * FROM departments;

6) Tracking Parallel Sessions

Use V$PX_SESSION to see the coordinator (QCSID) and slave sessions (if tracing is necessary, you must enable trace on all slaves to have a better interpretation).

If you need a detailed script to monitor sessions using parallel execution, use the following link: sessions_parallel.sql