Oracle DBMS_PARALLEL_EXECUTE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
CHUNKED NUMBER 4
PROCESSING NUMBER 5
FINISHED NUMBER 6
FINISHED_WITH_ERROR NUMBER 7
CRASHED NUMBER 8
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@pdborcl

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@pdborcl 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@pdborcl 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
DBMS_PARALLEL_EXECUTE_INTERNAL
DBMS_PCLXUTIL
Edition Based Redefinition
Partitioning
Packages

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-2014 Daniel A. Morgan All Rights Reserved