Oracle | DBMS_PARALLEL

Oracle DBMS_PARALLEL_EXECUTE Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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_STANDARD
DBA_PARALLEL_EXECUTE_CHUNKS	DBMS_PARALLEL_EXECUTE_CHUNKS$	USER_SCHEDULER_JOBS
DBA_PARALLEL_EXECUTE_EXTENTS	DBMS_PARALLEL_EXECUTE_INTERNAL	DBMS_STATS
DBA_PARALLEL_EXECUTE_TASKS	DBMS_PARALLEL_EXECUTE_TASK$	DBMS_STATS_INTERNAL
DBMS_ASSERT	DBMS_PRIV_CAPTURE	EXCHANGE
DBMS_LOCK	DBMS_SCHEDULER	USER_SCHEDULER_JOBS

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

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	CREATE_TASK	RESUME_TASK
ADM_DROP_TASK	DROP_CHUNKS	RUN_INTERNAL_WORKER
ADM_STOP_TASK	DROP_TASK	RUN_TASK
ADM_TASK_STATUS	GENERATE_TASK_NAME	SET_CHUNK_STATUS
CREATE_CHUNKS_BY_NUMBER_COL	GET_NUMBER_COL_CHUNK	STOP_TASK
CREATE_CHUNKS_BY_ROWID	GET_ROWID_CHUNK	TASK_STATUS
CREATE_CHUNKS_BY_SQL	PURGE_PROCESSED_CHUNKS	EBR Related Demo

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;