# Deep Dive — DBMS_PARALLEL_EXECUTE

> Split a table into chunks, run your statement on each chunk as its own job, commit per chunk, resume the failed ones. Parallelism and restartability for PL/SQL logic that cannot be a single SQL.

Used in: [Chapter 9 — Huge Tables](../course-plan.md) · Appendix entry: [DBMS_PARALLEL_EXECUTE](../appendix.md#dbms_parallel_execute) · Related: [PARALLEL DML](parallel-dml.md), [FOR UPDATE](for-update.md), [COMMIT](commit.md), [DBMS_APPLICATION_INFO](dbms_application_info.md)

---

## 1. What It Is and When

11.2+. `DBMS_PARALLEL_EXECUTE` takes a table, divides it into **chunks** (by rowid range, by a numeric column's range, or by a query you supply), and runs a statement of yours once per chunk with `:start_id` / `:end_id` bound to the chunk's bounds — on N scheduler jobs at a time. Each chunk is its own transaction: it commits when it finishes, its status is recorded, and a failed chunk can be re-run without touching the others.

Use it when the work per row needs PL/SQL (so [PARALLEL DML](parallel-dml.md) on one statement is not possible), or when a single giant DML is not acceptable (undo, locks, restartability). It replaces the "write a chunking loop with a progress table and start 8 sessions by hand" pattern.

Not for: statements that can be one parallel SQL — those are simpler and faster as [PARALLEL DML](parallel-dml.md).

## 2. Syntax

```sql
-- 1. task
DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name [, comment]);

-- 2. chunks — one of:
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(task_name, table_owner, table_name, by_row, chunk_size);
  -- by_row TRUE: chunk_size ≈ rows (from stats); FALSE: chunk_size = blocks
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL(task_name, table_owner, table_name, table_column, chunk_size);
  -- ranges of a NUMBER column, chunk_size = width of each range
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(task_name, sql_stmt, by_rowid);
  -- your query returns (start_id, end_id) pairs — rowids if by_rowid, numbers otherwise

-- 3. run
DBMS_PARALLEL_EXECUTE.RUN_TASK(task_name, sql_stmt, language_flag,
                               [edition], [apply_crossedition_trigger], [fire_apply_trigger],
                               parallel_level, [job_class]);
  -- sql_stmt: a DML statement OR a PL/SQL block; must reference :start_id and :end_id

-- 4. monitor / control
DBMS_PARALLEL_EXECUTE.TASK_STATUS(task_name)     -- returns a status constant
DBMS_PARALLEL_EXECUTE.RESUME_TASK(task_name)     -- re-run chunks that are not PROCESSED
DBMS_PARALLEL_EXECUTE.STOP_TASK(task_name)
DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name)
-- manual mode: GET_ROWID_CHUNK / GET_NUMBER_COL_CHUNK + SET_CHUNK_STATUS, for your own worker loop
-- views: USER_PARALLEL_EXECUTE_TASKS, USER_PARALLEL_EXECUTE_CHUNKS (status, start_id, end_id, job_name, error_code, error_message)
```

Privileges: `CREATE JOB` (the chunks run as `DBMS_SCHEDULER` jobs); `JOB_QUEUE_PROCESSES > 0`. Task and chunk metadata persist in the dictionary across sessions.

## 3. Examples on the Course Schema

### 3a. Reprice everything — rowid chunks, 8 jobs

```sql
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('reprice_2026');
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
    task_name   => 'reprice_2026',
    table_owner => USER,
    table_name  => 'ORDERS',
    by_row      => TRUE,
    chunk_size  => 100000);
  DBMS_PARALLEL_EXECUTE.RUN_TASK(
    task_name      => 'reprice_2026',
    sql_stmt       => 'UPDATE orders SET amount = pricing_pkg.reprice(order_id, amount)
                        WHERE rowid BETWEEN :start_id AND :end_id AND status = ''NEW''',
    language_flag  => DBMS_SQL.NATIVE,
    parallel_level => 8);
END;
/
```

500M rows → ~5,000 chunks → 8 jobs each taking the next chunk, committing, taking the next. Total elapsed ≈ single-threaded time ÷ 8 (minus contention).

### 3b. A PL/SQL block per chunk — when one UPDATE is not enough

```sql
DBMS_PARALLEL_EXECUTE.RUN_TASK(
  task_name      => 'settle_2026',
  sql_stmt       => 'BEGIN settlement_pkg.settle_range(:start_id, :end_id); END;',
  language_flag  => DBMS_SQL.NATIVE,
  parallel_level => 8);
```

```sql
-- settlement_pkg.settle_range: bulk logic on one chunk, its own COMMIT
PROCEDURE settle_range (p_start ROWID, p_end ROWID) IS
  CURSOR c IS SELECT rowid AS rid, order_id, amount FROM orders
              WHERE rowid BETWEEN p_start AND p_end AND status = 'PAID';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    -- compute in PL/SQL ...
    FORALL i IN 1 .. l_rows.COUNT
      UPDATE orders SET status = 'SETTLED' WHERE rowid = l_rows(i).rid;
  END LOOP;
  CLOSE c;
  COMMIT;
END;
```

The framework commits after each chunk's statement anyway; an explicit `COMMIT` inside is harmless and makes the procedure usable standalone.

### 3c. Chunks by a number column — stable across row movement

```sql
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL(
  task_name    => 'archive_flag',
  table_owner  => USER,
  table_name   => 'ORDERS',
  table_column => 'ORDER_ID',
  chunk_size   => 200000);              -- order_id 1..200000, 200001..400000, ...

DBMS_PARALLEL_EXECUTE.RUN_TASK('archive_flag',
  'UPDATE orders SET status = ''ARCHIVE'' WHERE order_id BETWEEN :start_id AND :end_id AND order_date < DATE ''2025-01-01''',
  DBMS_SQL.NATIVE, parallel_level => 6);
```

Rowid chunks break if rows move (partition maintenance, `ALTER TABLE ... MOVE`, row movement on partition-key updates) between chunking and running. Number-column chunks do not. Gaps in the column make chunks uneven, not wrong.

### 3d. Chunks by SQL — one chunk per partition (or per customer group)

```sql
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(
  task_name => 'per_partition',
  sql_stmt  => q'[SELECT MIN(order_id), MAX(order_id) FROM orders GROUP BY TRUNC(order_date, 'MM')]',
  by_rowid  => FALSE);
```

Any query returning `(start, end)` pairs. Partition-aligned chunks mean each job touches one partition — good for local indexes and for partition-level locks.

### 3e. Monitoring while it runs

```sql
SELECT status, COUNT(*) AS chunks,
       ROUND(100 * SUM(CASE WHEN status = 'PROCESSED' THEN 1 END) / COUNT(*), 1) AS pct_done
FROM   user_parallel_execute_chunks
WHERE  task_name = 'reprice_2026'
GROUP  BY status;
-- UNASSIGNED / ASSIGNED / PROCESSED / PROCESSED_WITH_ERROR

SELECT chunk_id, start_id, end_id, job_name, error_code, error_message
FROM   user_parallel_execute_chunks
WHERE  task_name = 'reprice_2026' AND status = 'PROCESSED_WITH_ERROR';

SELECT task_name, status FROM user_parallel_execute_tasks;
-- CREATED / CHUNKING / CHUNKED / PROCESSING / FINISHED / FINISHED_WITH_ERROR / CRASHED
```

### 3f. Failures — resume only what failed

```sql
DECLARE
  l_status NUMBER;
  l_try    PLS_INTEGER := 0;
BEGIN
  l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('reprice_2026');
  WHILE l_status IN (DBMS_PARALLEL_EXECUTE.FINISHED_WITH_ERROR, DBMS_PARALLEL_EXECUTE.CRASHED)
        AND l_try < 3 LOOP
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('reprice_2026');       -- re-runs non-PROCESSED chunks only
    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('reprice_2026');
  END LOOP;
  IF l_status = DBMS_PARALLEL_EXECUTE.FINISHED THEN
    DBMS_PARALLEL_EXECUTE.DROP_TASK('reprice_2026');
  ELSE
    RAISE_APPLICATION_ERROR(-20090, 'reprice_2026 still failing after ' || l_try || ' resumes');
  END IF;
END;
/
```

A chunk that hit `ORA-00054` or a transient error gets a second chance; a chunk with a data error keeps failing — inspect `error_message`, fix, resume.

### 3g. Manual mode — your own workers, the framework's bookkeeping

```sql
-- in each of N sessions you start yourself:
DECLARE
  l_chunk_id NUMBER; l_start ROWID; l_end ROWID; l_any BOOLEAN;
BEGIN
  LOOP
    DBMS_PARALLEL_EXECUTE.GET_ROWID_CHUNK('reprice_2026', l_chunk_id, l_start, l_end, l_any);
    EXIT WHEN NOT l_any;
    BEGIN
      settlement_pkg.settle_range(l_start, l_end);
      DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('reprice_2026', l_chunk_id, DBMS_PARALLEL_EXECUTE.PROCESSED);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('reprice_2026', l_chunk_id,
          DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR, SQLCODE, SQLERRM);
    END;
    COMMIT;
  END LOOP;
END;
```

For when scheduler jobs are not an option (no `CREATE JOB`, or you need a specific session context per worker).

## 4. What Breaks at Scale

- **Chunk size.** Too small (1,000 rows) → job-scheduling overhead per chunk dominates. Too large (10M) → long transactions, big undo, coarse restart. 50K–500K rows per chunk is the usual range; aim for chunks that take 10–60 seconds.
- **Uneven chunks.** `BY_ROWID` with `by_row => TRUE` uses statistics to estimate rows per block; stale stats → wildly different chunk sizes. Gather stats first, or chunk by blocks (`by_row => FALSE`).
- **Contention between chunks.** Eight jobs updating the same indexes, the same hot blocks (index right edge on a sequence-based key), the same undo segments. Watch `buffer busy waits` and `enq: TX - index contention`; reduce `parallel_level` or hash-partition the index.
- **Job slots.** `parallel_level` jobs need free scheduler slots (`JOB_QUEUE_PROCESSES`) and a job class with resources; on a busy system they queue.
- **Rowid chunks and partition maintenance** — `EXCHANGE`/`MOVE`/`DROP PARTITION` between chunking and running invalidates the rowids (`ORA-01410` or silently empty chunks). Chunk immediately before running, or chunk by a number column.
- **Each chunk = a session without your state.** Package variables, `DBMS_APPLICATION_INFO`, NLS settings — set them inside the chunk's PL/SQL block if needed.

## 5. Pitfalls

1. `ORA-27486: insufficient privileges` — the running user lacks `CREATE JOB`.
2. The statement without `:start_id` / `:end_id` — every chunk processes the whole table. `ORA-29491`-family errors or, worse, N full passes.
3. `DBMS_SQL.NATIVE` forgotten as `language_flag` — required positional argument.
4. Never calling `DROP_TASK` — tasks and thousands of chunk rows accumulate in the dictionary.
5. Reusing a task name that exists — `ORA-29497: duplicate task name`. Drop first, or generate unique names.
6. A chunk statement that raises for business reasons on one row fails the whole chunk (it is one transaction) — put [SAVE EXCEPTIONS](save-exceptions.md) / per-row handling inside the PL/SQL block if needed.
7. Expecting the chunks to run in order — they do not; do not depend on sequence.
8. Testing with `parallel_level => 1` and extrapolating — the contention only appears at 8.
9. `TASK_STATUS` polling in a tight loop — the job runs asynchronously; poll every few seconds, or wait on `DBMS_SCHEDULER` events.

## 6. Related

- [PARALLEL DML](parallel-dml.md) — when one SQL statement can do it.
- [FOR UPDATE](for-update.md) — the other worker-splitting technique (`SKIP LOCKED`).
- [COMMIT](commit.md) — chunk = transaction = restart unit.
- [SAVE EXCEPTIONS](save-exceptions.md) — row-level errors inside a chunk.
- [DBMS_APPLICATION_INFO](dbms_application_info.md) — tag each chunk job for monitoring.