# Deep Dive — PARALLEL DML

> Parallel query is on by default. Parallel *DML* is not — and a parallel SELECT feeding a serial INSERT is still a serial INSERT.

Used in: [Chapter 9 — Huge Tables](../course-plan.md) · Appendix entry: [PARALLEL DML](../appendix.md#parallel-dml) · Related: [APPEND HINT](append-hint.md), [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md), [INSERT SELECT](insert-select.md), [DBMS_XPLAN](dbms_xplan.md)

---

## 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:

| | Parallel query (the `SELECT` part) | Parallel DML (the `INSERT`/`UPDATE`/`DELETE`/`MERGE` part) |
| --- | --- | --- |
| Enabled by default? | yes | **no** — `ALTER SESSION ENABLE PARALLEL DML` or the `ENABLE_PARALLEL_DML` hint (12c+) |
| Degree from | hint, table's `PARALLEL` attribute, auto DOP | same |
| Direct path? | n/a | parallel `INSERT` is direct-path by default |

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

```sql
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

```sql
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?

```sql
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

```sql
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

```sql
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](exchange-partition.md).

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

```sql
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

```sql
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

```sql
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](append-hint.md) — direct path, the other half of a fast load.
- [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) — parallelism for PL/SQL logic, when the DML cannot be one statement.
- [INSERT SELECT](insert-select.md) — the statement.
- [DBMS_XPLAN](dbms_xplan.md) — verifying the plan.