Deep Dive — PARALLEL DML

1. What It Is and When

Oracle can run a statement with several parallel execution (PX) servers, each handling a slice of the data — by block ranges for a scan, by partition or rowid range for DML. Two separate switches exist:

Use it for: statements that touch tens of millions of rows on a server with idle cores and I/O headroom. Do not use it for: OLTP statements, anything with per-row triggers, or on a system where the DBA has not sized PARALLEL_MAX_SERVERS for you.

2. Syntax

ALTER SESSION ENABLE PARALLEL DML;                       -- session-wide switch
ALTER SESSION FORCE PARALLEL DML PARALLEL <n>;            -- and force a degree
ALTER SESSION DISABLE PARALLEL DML;

INSERT /*+ ENABLE_PARALLEL_DML APPEND PARALLEL(t, n) */ INTO t SELECT /*+ PARALLEL(s, n) */ ... FROM s;   -- 12c+: per statement
UPDATE /*+ PARALLEL(t, n) */ t SET ... WHERE ...;
DELETE /*+ PARALLEL(t, n) */ FROM t WHERE ...;
MERGE  /*+ PARALLEL(t, n) */ INTO t USING ...;
COMMIT;                                                   -- required before touching the table again (ORA-12838 / ORA-12839)

-- degree forms
PARALLEL(t, 8)       -- object-level, explicit degree
PARALLEL(8)          -- statement-level (11.2+)
PARALLEL(AUTO)       -- let auto DOP decide (needs PARALLEL_DEGREE_POLICY = AUTO/LIMITED and I/O calibration)

Restrictions (statement runs serially, usually with a note in the plan): triggers on the target; self-referencing FK; tables with certain LOB/object configurations (pre-12c); distributed transactions; UPDATE/DELETE of non-partitioned tables before 12c (12c+ supports them).

3. Examples on the Course Schema

3a. Parallel direct-path archive

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a
SELECT /*+ PARALLEL(o, 8) */ * FROM orders o WHERE o.order_date < DATE '2025-01-01';
COMMIT;

3b. Reading the plan — was the DML actually parallel?

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC +PARALLEL'));

Parallel DML:

| Id | Operation                  | Name           |    TQ  |IN-OUT| PQ Distrib |
|  0 | INSERT STATEMENT           |                |        |      |            |
|  1 |  PX COORDINATOR            |                |        |      |            |
|  2 |   PX SEND QC (RANDOM)      | :TQ10000       |  Q1,00 | P->S | QC (RAND)  |
|  3 |    LOAD AS SELECT          | ORDERS_ARCHIVE |  Q1,00 | PCWP |            |   ← load is inside the PX slaves
|  4 |     PX BLOCK ITERATOR      |                |  Q1,00 | PCWC |            |
|  5 |      TABLE ACCESS FULL     | ORDERS         |  Q1,00 | PCWP |            |

Parallel query, serial DML (forgot ENABLE PARALLEL DML):

|  0 | INSERT STATEMENT           |                |        |      |            |
|  1 |  LOAD AS SELECT            | ORDERS_ARCHIVE |        |      |            |   ← load is ABOVE the coordinator: serial
|  2 |   PX COORDINATOR           |                |        |      |            |
|  3 |    PX SEND QC (RANDOM)     | :TQ10000       |  Q1,00 | P->S | QC (RAND)  |
|  4 |     PX BLOCK ITERATOR      |                |  Q1,00 | PCWC |            |
|  5 |      TABLE ACCESS FULL     | ORDERS         |  Q1,00 | PCWP |            |

The position of LOAD AS SELECT (or UPDATE/DELETE) relative to PX COORDINATOR is the tell. Also check the Note section: "PDML is disabled in current session" or "Degree of Parallelism is 1 because of ...".

3c. Confirming from statistics

SELECT statistic, last_query, session_total
FROM   v$pq_sesstat
WHERE  statistic IN ('DML Parallelized', 'Queries Parallelized', 'Server Threads', 'Allocation Height');
-- DML Parallelized = 1 for the last statement → it was parallel

3d. Parallel UPDATE on a partitioned table

ALTER SESSION ENABLE PARALLEL DML;
UPDATE /*+ PARALLEL(o, 8) */ orders o
SET    o.status = 'ARCHIVED'
WHERE  o.order_date < DATE '2025-01-01' AND o.status = 'CLOSED';
COMMIT;

Conventional (not direct-path) — full undo and redo, but 8 slaves generating it. For an update touching most rows of a partition, the direct-path alternative is often faster: CREATE TABLE AS SELECT with the new values + EXCHANGE PARTITION.

3e. Statement-level enable (12c+) — no session change

INSERT /*+ ENABLE_PARALLEL_DML APPEND PARALLEL(a, 8) */ INTO orders_archive a
SELECT /*+ PARALLEL(o, 8) */ * FROM orders o WHERE o.order_date < DATE '2025-01-01';
COMMIT;

Useful inside a package where you do not control the session; ALTER SESSION from PL/SQL needs EXECUTE IMMEDIATE and leaves the session changed for the caller.

3f. Choosing a degree

SELECT name, value FROM v$parameter
WHERE  name IN ('cpu_count', 'parallel_max_servers', 'parallel_servers_target', 'parallel_degree_policy');
SELECT COUNT(*) AS busy_px FROM v$px_session;      -- what is running right now

Rules of thumb: a nightly job on an otherwise idle server — up to cpu_count / 2; on a shared server — what the DBA allocates, and coordinate. A degree the system cannot serve is silently downgraded (plan note: "Degree of Parallelism is 4 because of parallel threshold / server limit").

3g. The ORA-12839 trap

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(a, 8) */ INTO orders_archive a SELECT ... ;
SELECT COUNT(*) FROM orders_archive;        -- ORA-12838: cannot read/modify an object after modifying it in parallel
UPDATE orders_archive SET ...;              -- ORA-12839: cannot modify an object in parallel after modifying it
COMMIT;                                     -- then both are fine

4. What Breaks at Scale

• Shared PX servers. PARALLEL_MAX_SERVERS is instance-wide. Your degree-32 archive takes every slave; the reporting queries run serially or queue (PARALLEL_DEGREE_POLICY = AUTO queues statements until slaves free up — resmgr:pq queued waits).
• TEMP and PGA. Each slave has its own sort/hash area: a parallel MERGE with a hash join at degree 16 needs 16 work areas. PGA_AGGREGATE_LIMIT and TEMP fill 16 times faster.
• Undo. A parallel conventional UPDATE generates undo from all slaves into the same transaction; 40M rows of undo held until commit, produced 8x faster.
• Skew. Partition-wise DML at degree 8 on a table with one huge partition and seven small ones runs at the speed of the huge one. Rowid-range (block) splitting avoids this for scans; for DML on partitioned tables check the distribution.
• Triggers turn the whole statement serial, silently. Disable them for the load, or load a staging table and exchange.
• RAC. Slaves may be spread across instances (PARALLEL_FORCE_LOCAL keeps them on one); cross-instance traffic can eat the gain.

5. Pitfalls

1. Forgetting ENABLE PARALLEL DML — parallel read, serial write. The most common one.
2. ORA-12838 / ORA-12839 — touching the table before COMMIT.
3. A degree with nothing behind it — downgraded to 1 with only a note in the plan.
4. PARALLEL on the table definition (ALTER TABLE ... PARALLEL 8) — every query on it now tries to go parallel, including the OLTP ones. Use hints for the batch, NOPARALLEL on the table.
5. Leaving PARALLEL on an index after REBUILD ... PARALLEL 8 — same problem; reset with NOPARALLEL.
6. Parallel DML from within a parallel query's function — ORA-12839-family errors. Keep it at the top level.
7. Expecting linear speed-up: I/O and redo bandwidth cap it. Degree 8 on a single spinning disk is 8 processes waiting for the same disk.
8. ALTER SESSION inside a package — the setting persists for the caller's session. Prefer the 12c statement-level hint.

6. Related

• APPEND HINT — direct path, the other half of a fast load.
• DBMS_PARALLEL_EXECUTE — parallelism for PL/SQL logic, when the DML cannot be one statement.
• INSERT SELECT — the statement.
• DBMS_XPLAN — verifying the plan.