Oracle 12cR2 DBMS_PARALLEL

Oracle DBMS_PARALLEL_EXECUTE Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.

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