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
Appendix entry
DBMS_PARALLEL_EXECUTE
Related
PARALLEL DML FOR UPDATE COMMIT DBMS_APPLICATION_INFO

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 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.

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.

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).

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.

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.

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.

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

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.

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).

  • 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.

  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 / 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.