Oracle DBMS_PARALLEL_EXECUTE
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose APIs to chunk a table into smaller units and execute those chunks in parallel.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Chunk Status Value
UNASSIGNED NUMBER 0
ASSIGNED NUMBER 1
PROCESSED NUMBER 2
PROCESSED_WITH_ERROR NUMBER 3
Task Status Value
CREATED NUMBER 1
CHUNKING NUMBER 2
CHUNKING_FAILED NUMBER 3
NO_CHUNKS NUMBER 4
CHUNKED NUMBER 5
PROCESSING NUMBER 6
FINISHED NUMBER 7
FINISHED_WITH_ERROR NUMBER 8
CRASHED NUMBER 9
Dependencies
ALL_TABLES DBMS_OUTPUT DBMS_PRIV_CAPTURE
DBA_PARALLEL_EXECUTE_CHUNKS DBMS_PARALLEL_EXECUTE_CHUNKS$ DBMS_SCHEDULER
DBA_PARALLEL_EXECUTE_EXTENTS DBMS_PARALLEL_EXECUTE_INTERNAL DBMS_STANDARD
DBA_PARALLEL_EXECUTE_TASKS DBMS_PARALLEL_EXECUTE_SEQ$ USER_SCHEDULER_JOBS
DBMS_LOCK DBMS_PARALLEL_EXECUTE_TASK$  
Documented Yes
Exceptions
Error Code Reason
ORA-29490 MISSING_ROLE
ORA-29491 INVALID_TABLE
ORA-29492 INVALID_STATE_FOR_CHUNK
ORA-29493 INVALID_STATUS
ORA-29494 INVALID_STATE_FOR_RUN
ORA-29495 INVALID_STATE_FOR_RESUME
ORA-29497 DUPLICATE_TASK_NAME
ORA-29498 TASK_NOT_FOUND
ORA-29499 CHUNK_NOT_FOUND
First Available 11.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC

The "ADM" procedures require the ADM_PARALLEL_EXECUTE_TASK role.
Source {ORACLE_HOME}/rdbms/admin/dbmspexe.sql
Subprograms
 
ADM_DROP_CHUNKS
Administrative procedure that drops all the chunks of the given task owned by the given owner dbms_parallel_execute.adm_drop_chunks(
task_owner IN VARCHAR2,
task_name  IN VARCHAR2);
exec dbms_parallel_execute.adm_drop_chunks('UWCLASS', 'UWTASK');
 
ADM_DROP_TASK
Administrative procedure that drops the task of the given user and all its chunks dbms_parallel_execute.adm_drop_task(
task_owner IN VARCHAR2,
task_name  IN VARCHAR2);
exec dbms_parallel_execute.adm_drop_task('UWCLASS', 'UWTASK');
 
ADM_STOP_TASK
Administrative procedure that stops the task of the given owner and its job slaves dbms_parallel_execute.adm_stop_task(
task_owner IN VARCHAR2,
task_name  IN VARCHAR2);
exec dbms_parallel_execute.adm_stop_task('UWCLASS', 'UWTASK');
 
ADM_TASK_STATUS
Administrative procedure that returns the task status dbms_parallel_execute.adm_task_status(
task_owner IN VARCHAR2,
task_name  IN VARCHAR2)
RETURN NUMBER;
set serveroutput on

DECLARE
 x NUMBER;
BEGIN
  x := dbms_parallel_execute.adm_task_status('UWCLASS', 'UWTASK');
  dbms_output.put_line(x);
END;
/
 
CREATE_CHUNKS_BY_NUMBER_COL
Chunks the table by the column specified dbms_parallel_execute.create_chunks_by_number_col(
task_name    IN VARCHAR2,
table_owner  IN VARCHAR2,
table_name   IN VARCHAR2,
table_column IN VARCHAR2,
chunk_size   IN NUMBER);
conn sh/sh@pdbdev

desc user_parallel_execute_chunks

SELECT COUNT(*)
FROM user_parallel_execute_chunks;

BEGIN
  dbms_parallel_execute.create_task('SHTASK', 'Demo Task');
  dbms_parallel_execute.create_chunks_by_number_col('SHTASK','SH','SALES','PROD_ID',100);
END;
/

SELECT COUNT(*)
FROM user_parallel_execute_chunks;

col task_name format a10
col start_ts format a10
col end_ts format a10

SELECT chunk_id, task_name, status, start_rowid, end_rowid, job_name, start_id
FROM user_parallel_execute_chunks;

SELECT chunk_id, task_name, status, start_id, end_id, start_ts, end_ts
FROM user_parallel_execute_chunks;

exec dbms_parallel_execute.drop_task('SHTASK');

SELECT COUNT(*)
FROM user_parallel_execute_chunks;
 
CREATE_CHUNKS_BY_ROWID
Chunks the table by ROWID dbms_parallel_execute.create_chunks_by_rowid(
task_name   IN VARCHAR2,
table_owner IN VARCHAR2,
table_name  IN VARCHAR2,
by_row      IN BOOLEAN,
chunk_size  IN NUMBER);
conn sys@pdbdev as sysdba

SELECT DISTINCT salary FROM hr.employees ORDER BY 1;

DECLARE
 l_sql_stmt VARCHAR2(1000);
 l_try      NUMBER;
 l_status   NUMBER;
BEGIN
  -- create a TASK
  dbms_parallel_execute.create_task('HRTASK');

  -- chunk the table by ROWID
  dbms_parallel_execute.create_chunks_by_rowid('HRTASK', 'HR', 'EMPLOYEES', TRUE, 100);

  -- execute the DML in parallel
  l_sql_stmt := 'UPDATE /*+ ROWID(e) */ HR.EMPLOYEES e
  SET e.salary = e.salary - 1
  WHERE rowid BETWEEN :start_id AND :end_id';

  dbms_parallel_execute.run_task('HRTASK',l_sql_stmt,DBMS_SQL.NATIVE,parallel_level=>4);

  -- if there is error, RESUME it for at most 2 times
  l_try := 0;
  l_status := dbms_parallel_execute.task_status('HRTASK');

  WHILE(l_try < 2 and l_status != dbms_parallel_execute.finished) LOOP
    l_try := l_try + 1;
    dbms_parallel_execute.resume_task('HRTASK');
    l_status := dbms_parallel_execute.task_status('HRTASK');
  END LOOP;

  -- done with processing; drop the task
  dbms_parallel_execute.drop_task('HRTASK');
END;
/

SELECT DISTINCT salary FROM hr.employees;

ROLLBACK;

SELECT DISTINCT salary FROM hr.employees;
 
CREATE_CHUNKS_BY_SQL
Chunks the table by means of a user-provided SELECT statement dbms_parallel_execute.create_chunks_by_sql(
task_name IN VARCHAR2,
sql_stmt  IN CLOB,
by_rowid  IN BOOLEAN);
conn sys@pdbdev as sysdba

SELECT DISTINCT salary FROM hr.employees;

DECLARE
 l_chunk_sql VARCHAR2(1000);
 l_sql_stmt  VARCHAR2(1000);
 l_try       NUMBER;
 l_status    NUMBER;
BEGIN
  -- create the TASK
  dbms_parallel_execute.create_task('UWTASK');

  -- chunk the table by MANAGER_ID
  l_chunk_sql := 'SELECT distinct manager_id, manager_id FROM hr.employees';
  dbms_parallel_execute.create_chunks_by_sql('UWTASK', l_chunk_sql, FALSE);

  -- execute the DML in parallel the WHERE clause contain a condition on manager_id,
  -- which is the chunk column. We're grouping rows by manager_id

  l_sql_stmt := 'update /*+ ROWID (dda) */ HR.EMPLOYEES e
  SET e.salary = e.salary - 2
  WHERE manager_id between :start_id and :end_id';
  dbms_parallel_execute.run_task('UWTASK',l_sql_stmt,DBMS_SQL.NATIVE,parallel_level=>4);

  -- if there is error, RESUME it for at most 2 times
  l_try := 0;
  l_status := dbms_parallel_execute.task_status('UWTASK');
  WHILE(l_try < 2 and L_status != dbms_parallel_execute.finished) LOOP
    l_try := l_try + 1;
    dbms_parallel_execute.resume_task('UWTASK');
    l_status := dbms_parallel_execute.task_status('UWTASK');
  END LOOP;

  -- done with processing; drop the task
  dbms_parallel_execute.drop_task('UWTASK');
END;
/

SELECT DISTINCT salary FROM hr.employees;

SELECT chunk_id, task_name, status, start_rowid, end_rowid, job_name, start_id
FROM dba_parallel_execute_chunks;

SELECT chunk_id, task_name, status, start_id, end_id, start_ts, end_ts
FROM dba_parallel_execute_chunks;
 
CREATE_TASK
Creates a task for the current user dbms_parallel_execute.create_task(
task_name IN VARCHAR2,
comment   IN VARCHAR2 DEFAULT NULL);
exec dbms_parallel_execute.create_task('UWTASK', 'Demo Task');
 
DROP_CHUNKS
Drops the task's chunks dbms_parallel_execute.drop_chunks(task_name IN VARCHAR2);
exec dbms_parallel_execute.drop_chunks('UWTASK');
 
DROP_TASK
Drops the task and all its chunks dbms_parallel_execute.drop_task(task_name IN VARCHAR2);
exec dbms_parallel_execute.drop_task('UWTASK');
 
GENERATE_TASK_NAME
Returns a unique name for a task preceded by the indicated prefix dbms_parallel_execute.generate_task_name(prefix IN VARCHAR2 DEFAULT 'TASK$_')
RETURN VARCHAR2;
set serveroutput on

DECLARE
 tn VARCHAR2(30);
BEGIN
  tn := dbms_parallel_execute.generate_task_name;
  dbms_output.put_line(tn);
END;
/
 
GET_NUMBER_COL_CHUNK
Picks an unassigned NUMBER chunk and changes it to ASSIGNED dbms_parallel_execute.get_number_col_chunk(
task_name IN  VARCHAR2,
chunk_id  OUT NUMBER,
start_id  OUT NUMBER,
end_id    OUT NUMBER,
any_rows  OUT BOOLEAN);
TBD
 
GET_ROWID_CHUNK
Picks an unassigned ROWID chunk and changes it to ASSIGNED dbms_parallel_execute.get_rowid_chunk(
task_name   IN  VARCHAR2,
chunk_id    OUT NUMBER,
start_rowid OUT ROWID,
end_rowid   OUT ROWID,
any_rows    OUT BOOLEAN);
DECLARE
 l_sql_stmt varchar2(1000);
 l_try number;
 l_status number;
 l_chunk_id number;
 l_start_rowid rowid;
 l_end_rowid rowid;
 l_any_rows boolean;

 CURSOR c1 IS
 SELECT chunk_id
 FROM user_parallel_execute_chunks
 WHERE task_name = 'UWTASK'
 AND status IN (dbms_parallel_execute.PROCESSED_WITH_ERROR,dbms_parallel_execute.ASSIGNED);
BEGIN
  -- create the Objects, task, and chunk it by ROWID
  dbms_parallel_execute.CREATE_TASK ('mytask');
  dbms_parallel_execute.CREATE_CHUNKS_BY_ROWID('mytask', 'HR', 'EMPLOYEES', true, 100);

  l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e
  SET e.salary = e.salary + 10
  WHERE rowid BETWEEN :start_id AND :end_id';

  -- execute the DML in its own framework
  -- process each chucks and commit it.
  -- after processing one chunk, get another chunck to process until all are processed


  <<main_processing>>
  LOOP
    -- get a chunk to process; If there's nothing to process, then exit the loop
    dbms_parallel_execute.GET_ROWID_CHUNK('mytask', l_chunk_id, l_start_rowid, l_end_rowid, l_any_rows);
    IF (l_any_rows = FALSE) THEN
      EXIT;
    END IF;
    -- the chunck is specified by start_id and end_id.
    -- bind the start_id and end_id and then execute it
    -- them if no error occured, set the chunk status to PROCESSED
    -- if exception occur, store the error num/msg
    -- in the chunk table and continue to process the next chunk

    BEGIN
      EXECUTE IMMEDIATE l_sql_stmt
      USING l_start_rowid, l_end_rowid;

      dbms_parallel_execute.set_chunk_status('UWTASK', l_chunk_id, dbms_parallel_execute.processed);
    EXCEPTION WHEN OTHERS THEN
      dbms_parallel_execute.set_chunk_status('UWTASK', l_chunk_id,
      dbms_parallel_execute.processed_with_error, SQLCODE, SQLERRM);
    END;
    -- finished processing one chunk; commit here
    COMMIT;
  END LOOP;
...
END;
/
 
PURGE_PROCESSED_CHUNKS
Deletes all the processed chunks whose status is PROCESSED or PROCESSED_WITH_ERROR dbms_parallel_execute.purge_processed_chunks(task_name IN VARCHAR2);
exec dbms_parallel_execute.purge_processed_chunks('UWTASK');
 
RESUME_TASK
Retries the given the task if the RUN_TASK Procedure finished with error, or it will resume the task if a crash has occurred.

Overload 1
dbms_parallel_execute.resume_task(
task_name                  IN VARCHAR2,
sql_stmt                   IN CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger         IN BOOLEAN  DEFAULT TRUE,
parallel_level             IN NUMBER   DEFAULT 0,
job_class                  IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
force                      IN BOOLEAN  DEFAULT FALSE);
TBD
Overload 2 dbms_parallel_execute.resume_task(
task_name IN VARCHAR2,
force     IN BOOLEAN DEFAULT FALSE);
exec dbms_parallel_execute.resume_task('UWTASK', TRUE);
 
RUN_INTERNAL_WORKER
Internal routine for parallel execution dbms_parallel_execute.run_internal_worker(
task_name IN VARCHAR2,
job_name  IN VARCHAR2);
TBD
 
RUN_TASK
Executes the specified SQL statement on the chunks in parallel dbms_parallel_execute.run_task(
task_name                  IN VARCHAR2,
sql_stmt                   in CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger         IN BOOLEAN  DEFAULT TRUE,
parallel_level             IN NUMBER   DEFAULT 0,
job_class                  IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS');
See CREATE_CHUNKS_BY_ROWID Demo Above
 
SET_CHUNK_STATUS
Sets the status of the chunk dbms_parallel_execute.set_chunk_status(
task_name IN VARCHAR2,
chunk_id  IN NUMBER,
status    IN NUMBER,
err_num   IN NUMBER   DEFAULT NULL,
err_msg   IN VARCHAR2 DEFAULT NULL);
See GET_ROWID_CHUNK Demo Above
 
STOP_TASK
Stops the task and its job slaves dbms_parallel_execute.stop_task(task_name IN VARCHAR2);
exec dbms_parallel_execute.stop_task('UWTASK');
 
TASK_STATUS
Returns the task status dbms_parallel_execute.task_status(task_name IN VARCHAR2)
RETURN NUMBER;
set serveroutput on

DECLARE
 x NUMBER;
BEGIN
  x := dbms_parallel_execute.task_status('UWTASK');
  dbms_output.put_line(x);
END;
/
 
EBR Related Demo
Update a large table incrementally conn uwclass/uwclass@pdbdev

desc airplanes

ALTER TABLE airplanes ADD (ebr_demo DATE);

desc airplanes

DECLARE
 l_sql_stmt VARCHAR2(512);
 l_try      NUMBER;
 l_status   NUMBER;
BEGIN
  -- create a TASK
  dbms_parallel_execute.create_task('EBRTASK');

  -- chunk the table by ROWID
  dbms_parallel_execute.create_chunks_by_rowid('EBRTASK', 'UWCLASS', 'AIRPLANES', TRUE, 100000);

  -- execute the DML in parallel
  l_sql_stmt := 'UPDATE /*+ ROWID(a) */ UWCLASS.AIRPLANES a
  SET a.ebr_demo = SYSDATE
  WHERE rowid BETWEEN :start_id AND :end_id';

  dbms_parallel_execute.run_task('EBRTASK', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level=>1);

  -- if there is error, RESUME it for at most 2 times
  l_try := 0;
  l_status := dbms_parallel_execute.task_status('EBRTASK');

  WHILE(l_try < 2 and l_status != dbms_parallel_execute.finished) LOOP
    l_try := l_try + 1;
    dbms_parallel_execute.resume_task('EBRTASK');
    l_status := dbms_parallel_execute.task_status('EBRTASK');
  END LOOP;

  -- done with processing; drop the task
  dbms_parallel_execute.drop_task('EBRTASK');
END;
/

-- in a separate window, already logged in to the uwclass schema, run the following:
SELECT COUNT(*)
FROM airplanes
WHERE ebr_demo IS NOT NULL;

-- after all 250,000 records have been updated

SELECT ebr_demo, COUNT(*)
FROM airplanes
GROUP BY ebr_demo;

Related Topics
Built-in Functions
Built-in Packages
DBMS_PARALLEL_EXECUTE_INTERNAL
DBMS_PCLXUTIL
Edition Based Redefinition
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved