| General Information |
| Note: This package contains APIs to chunk a table into smaller units and execute those chunks in parallel. |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmspexe.sql |
| First Available |
11.2 |
| 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_PARALLEL_EXECUTE_INTERNAL |
| DBA_PARALLEL_EXECUTE_CHUNKS |
DBMS_PARALLEL_EXECUTE_SEQ$ |
| DBA_PARALLEL_EXECUTE_EXTENTS |
DBMS_PARALLEL_EXECUTE_TASK$ |
| DBA_PARALLEL_EXECUTE_TASKS |
DBMS_SCHEDULER |
| DBMS_LOCK |
SESSION_ROLES |
| DBMS_OUTPUT |
USER_SCHEDULER_JOBS |
| DBMS_PARALLEL_EXECUTE_CHUNKS$ |
|
|
| Exceptions |
| Error Code |
Name |
| 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 |
|
| Security Model |
Execute is granted to PUBLIC.
The "ADM" procedures require the ADM_PARALLEL_EXECUTE_TASK role. |
| 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
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 / 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 / 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;
... |
| |
| 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 the various working demos on this page |
| |
| 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 |
| Updating a large table incrementally |
conn uwclass/uwclass
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; |
