Oracle 12c DBMS_PARALLEL

Oracle DBMS_PARALLEL_EXECUTE Version 12.1.0.1

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.

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_PARALLEL_EXECUTE_TASK$
DBA_PARALLEL_EXECUTE_CHUNKS	DBMS_PARALLEL_EXECUTE_CHUNKS$	DBMS_SCHEDULER
DBA_PARALLEL_EXECUTE_EXTENTS	DBMS_PARALLEL_EXECUTE_INTERNAL	SESSION_ROLES
DBA_PARALLEL_EXECUTE_TASKS	DBMS_PARALLEL_EXECUTE_SEQ$	USER_SCHEDULER_JOBS
DBMS_LOCK

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