Oracle DBMS_PARALLEL_EXECUTE_INTERNAL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose This package contains APIs to chunk a table into smaller units and execute those chunks in parallel.
AUTHID DEFINER
Data Types CREATE TABLE "SYS"."DBMS_PARALLEL_EXECUTE_TASK$" SHARING=METADATA (
"TASK_OWNER#"                NUMBER NOT NULL ENABLE,
"TASK_NAME"                  VARCHAR2(128) NOT NULL ENABLE,
"CHUNK_TYPE"                 NUMBER NOT NULL ENABLE,
"STATUS"                     NUMBER NOT NULL ENABLE,
"TABLE_OWNER"                VARCHAR2(128),
"TABLE_NAME"                 VARCHAR2(128),
"NUMBER_COLUMN"              VARCHAR2(128),
"CMT"                        VARCHAR2(4000),
"JOB_PREFIX"                 VARCHAR2(128),
"STOP_FLAG"                  NUMBER,
"SQL_STMT"                   CLOB,
"LANGUAGE_FLAG"              NUMBER,
"EDITION"                    VARCHAR2(130),
"APPLY_CROSSEDITION_TRIGGER" VARCHAR2(130),
"FIRE_APPLY_TRIGGER"         VARCHAR2(10),
"PARALLEL_LEVEL"             NUMBER,
"JOB_CLASS"                  VARCHAR2(128),
CONSTRAINT "PK_DBMS_PARALLEL_EXECUTE_1"
PRIMARY KEY ("TASK_OWNER#", "TASK_NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
LOB ("SQL_STMT") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))


This object is a heap table but is referenced in this package as a RECORD.
Dependencies
ALL_TABLES DBMS_PARALLEL_EXECUTE_CHUNKS$ DBMS_STATS
DBA_EXTENTS DBMS_PARALLEL_EXECUTE_EXTENTS DBMS_STATS_INTERNAL
DBA_OBJECTS DBMS_PARALLEL_EXECUTE_INTERNAL DBMS_SYS_ERROR
DBA_PARALLEL_EXECUTE_TASKS DBMS_PARALLEL_EXECUTE_SEQ$ DBMS_SYS_SQL
DBA_SCHEDULER_RUNNING_JOBS DBMS_PARALLEL_EXECUTE_TASK$ DUAL
DBA_TABLES DBMS_ROWID GV$PARAMETER
DBA_USERS DBMS_SQL PLITBLM
DBMS_ASSERT DBMS_STANDARD USER_PARALLEL_EXECUTE_TASKS
DBMS_PARALLEL_EXECUTE    
Documented No
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 no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvthpexei.plb
Subprograms
 
ASSERT_CHUNK_EXISTS
Undocumented dbms_parallel_execute_internal.assert_chunk_exists(
owner# IN NUMBER,
task   IN VARCHAR2,
chunk  IN NUMBER);
TBD
 
ASSERT_TASK_EXISTS
Undocumented dbms_parallel_execute_internal.assert_task_exists(
owner# IN NUMBER,
task   IN VARCHAR2);
SELECT username, user_id
FROM dba_users
ORDER BY 1;

exec dbms_parallel_execute_internal.assert_task_exists(90, 'HRTASK');
 
CREATE_CHUNKS_BY_NUMBER_COL
Chunks the table by the column specified dbms_parallel_execute_internal.create_chunks_by_number_col(
owner#       IN NUMBER,
task_name    IN VARCHAR2,
table_owner  IN VARCHAR2,
table_name   IN VARCHAR2,
table_column IN VARCHAR2,
chunk_size   IN NUMBER);
TBD
 
CREATE_CHUNKS_BY_ROWID
Chunks the table by ROWID

Overload 1
dbms_parallel_execute_internal.create_chunks_by_rowid(
owner#       IN NUMBER,
task_name    IN VARCHAR2,
table_owner  IN VARCHAR2,
table_name   IN VARCHAR2,
table_column IN VARCHAR2,
num_rows     IN NUMBER);
TBD
Overload 2 dbms_parallel_execute_internal.create_chunks_by_number_col(
owner#       IN NUMBER,
task_name    IN VARCHAR2,
table_owner  IN VARCHAR2,
table_name   IN VARCHAR2,
table_column IN VARCHAR2,
num_blocks   IN NUMBER);
TBD
 
CREATE_CHUNKS_BY_SQL
Chunks the table by means of a user-provided SELECT statement dbms_parallel_execute_internal.create_chunks_by_sql(
owner#    IN NUMBER,
task_name IN VARCHAR2,
sql_stmnt IN CLOB,
by_rowid  IN BOOLEAN);
TBD
 
CREATE_TASK
Creates a task for the identified user dbms_parallel_execute_internal.create_task(
owner#    IN NUMBER,
task_name IN VARCHAR2,
comment   IN VARCHAR2);
exec dbms_parallel_execute_internal.create_task(90, 'UWTASK', 'Demo Task');
 
DEFAULT_PARALLELISM
Returns the default degree of parallelism dbms_parallel_execute_internal.default_parallelism RETURN BINARY_INTEGER;
SELECT name, value
FROM v$parameter
WHERE name = 'parallel_threads_per_cpu';

SELECT dbms_parallel_execute_internal.default_parallelism
FROM dual;
 
DROP_ALL_TASKS
Undocumented dbms_parallel_execute_internal.drop_all_tasks(owner_name IN VARCHAR2);
exec dbms_parallel_execute_internal.drop_all_tasks('UWCLASS');
 
DROP_CHUNKS
Undocumented dbms_parallel_execute_internal.drop_chunks(
owner#    IN NUMBER,
task_name IN VARCHAR2);
exec dbms_parallel_execute_internal.drop_chunks(90, 'UWTASK');
 
DROP_TASK
Undocumented dbms_parallel_execute_internal.drop_task(
owner#    IN NUMBER,
task_name IN VARCHAR2);
exec dbms_parallel_execute_internal.drop_task(90, 'UWTASK');
 
GENERATE_TASK_NAME
Returns a unique name for a task preceded by the indicated prefix dbms_parallel_execute_internal.generate_task_name(prefix IN VACHAR2)
RETURN VARCHAR2;
SELECT dbms_parallel_execute_internal.generate_task_name('UW')
FROM dual;
 
GET_RANGE
Undocumented dbms_parallel_execute_internal.get_range(
owner#      IN NUMBER,
task_name   IN VARCHAR2,
chunk_id    IN NUMBER,
start_rowid IN ROWID,
end_rowid   IN ROWID,
start_id    IN NUMBER
end_id      IN NUMBER);
RETURN BOOLEAN;
TBD
 
OWNER_NAME_TO_NUM
Given a schema name returns the user# from user$ dbms_parallel_execute_internal.owner_name_to_num(owner_name IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_parallel_execute_internal.owner_name_to_num('UWCLASS')
FROM dual;
 
PURGE_PROCESSED_CHUNKS
Undocumented dbms_parallel_execute_internal.purge_processed_chunks(
owner#    IN NUMBER,
TASK_NAME IN VARCHAR2);
exec dbms_parallel_execute_internal.purge_processed_chunks(90, 'UWTASK');
 
READ_TASK
Undocumented dbms_parallel_execute_internal.read_task(
owner# IN NUMBER,
task   IN VARCHAR2)
RETURN dbms_parallel_execute_task$;
TBD
 
RUN_INTERNAL_WORKER
Undocumented dbms_parallel_execute_internal.run_internal_worker(
owner#    IN NUMBER,
task_name IN VARCHAR2,
job_name  IN VARCHAR2);
TBD
 
SEQ_NEXT_VAL
Returns the next value from sys.dbms_parallel_execute_seq$ dbms_parallel_execute_internal.seq_next_val;
SELECT dbms_parallel_execute_internal.seq_next_val
FROM dual;

SELECT dbms_parallel_execute_internal.generate_task_name('UW')
FROM dual;

SELECT dbms_parallel_execute_internal.seq_next_val
FROM dual;
 
SET_CHUNK_STATUS
Undocumented dbms_parallel_execute_internal.set_chunk_status(
owner#    IN NUMBER,
task_name IN VARCHAR2,
chunk_id  IN NUMBER,
status    IN NUMBER,
err_num   IN NUMBER,
err_msg   IN VARCHAR2);
TBD
 
STOP_TASK
Stops execution of the specified task dbms_parallel_execute_internal.stop_task(
owner#    IN NUMBER,
task_name IN VARCHAR2);
exec dbms_parallel_execute_internal.stop_task(90, 'UWTASK');
 
TASK_STATUS
Returns the status of the specified task dbms_parallel_execute_internal.task_status(
owner#    IN NUMBER,
task_name IN VARCHAR2)
RETURN NUMBER;
set serveroutput on

DECLARE
 x NUMBER;
BEGIN
  x := dbms_parallel_execute_internal.stop_task(90, 'UWTASK');
END;
/
 
UNASSIGN_CHUNKS
Unassigns chunks for the specified task dbms_parallel_execute_internal.unassign_chunks(
owner#    IN NUMBER,
task_name IN VARCHAR2);
exec dbms_parallel_execute_internal.unassign_chunks(90, 'UWTASK');
 
UPDATE_TASK
Undocumented dbms_parallel_execute_internal.update_task(task IN dbms_parallel_execute_task$);
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_PARALLEL_EXECUTE
What's New In 18cR3
What's New In 19cR2

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