Question: What does the
dbms_parallel_execute DBMS package do? When would
I use dbms_parallel_execute instead of parallel
DML? Is it superior to parallel DML?
Answer: No, the dbms_parallel_execute
package
(new in 11g r2)
is not as easy to use as vanilla parallel DML,
IMHO, because Oracle parallel query (and DML) reliably
divides a table into equal-sized chunks automatically.
The
dbms_parallel_execute package is for special cases
of incremental bulk updates in PL/SQL.
It also appears that dbms_parallel_execute is a way to
implement parallel query in Oracle Standard Ediition (SE).
parallel-processing-with-standard-edition
"As of Oracle Database 11g Release 2,there
is a feature that provides parallel processing capabilities
in the StandardEdition. This feature is available through
the DBMS_PARALLEL_EXECUTE package."
Dr. Tim Hall notes you should use this package
only where straight parallel DML is not appropriate, such as
when you are using parallel DML inside PL/SQL loops (such as
incrementally bulk updating data). This do-it-yourself
parallelism in dbms_parallel_execute can be used to
manually specify the degree of parallelism to fully saturate
a dedicated server and he notes the following steps for
using the dbms_parallel_execute package:
1:
Create a task
2: Split the workload into chunks
exec
dbms_parallel_execute.create_chunks_by_rowid
exec dbms_parallel_execute.create_chunks_by_number_col
exec dbms_parallel_execute.create_chunks_by_sql
3:
Run the task
exec
dbms_parallel_execute.run_task
User-defined
framework
Task control
4:
Check the task status
5: Drop the task
You can view the status of dbms_parallel_execute with this
dictionary SQL against the dba_parallel_execute_chunks
view:
select
chunk_id,
status,
start_id,
end_id
from
dba_parallel_execute_chunks
where
task_name = 'test_task'
order by
chunk_id;
Here is a sample invocation of dbms_parallel_execute:
query_chunk_generator := 'SELECT ... '; --the statement to chunk
mystmt := ...; --the statement to be executed
DBMS_PARALLEL_EXECUTE.CREATE_TASK (parallel_task_name);
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL (
parallel_task_name,
query_chunk_generator,
true);
DBMS_PARALLEL_EXECUTE.RUN_TASK (
parallel_task_name,
mystmt,
dbms_sql.NATIVE,
parallel_level => 31);
