What is DBMS_PARALLEL_EXECUTE?
DBMS_PARALLEL_EXECUTE permits updating a large set of data incrementally. Basically you create smaller chunks of data by segregating the rows based on Rowid OR numeric column OR user-defined SQL and then update the chunks in parallel. Other major advantages are that the chunks are committed individually and better performance when chunks are based on ROWID.
Why is DBMS_PARALLEL_EXECUTE important ?
- Large single transaction can be broken into multiple chunks (transactions)
- Less impact to dependent systems like Golden Gate or streams
- Better performance compared to parallel DML when chunks are based on ROWID and blocks.
- Less undo space required and minimizing the chance of ORA-1555
- Impact of Rollback due to error is not severe.
- Duration of lock is reduced.
- Restart failed chunks (transactions)
- Unlike other parallel features , DBMS_PARALLEL_EXECUTE does not require Enterprise Edition (at least for now)
- Eliminates much of manual coding and better uniform distribution across parallel processes
Steps Involved
1.Create a task with CREATE_TASK.
2.Create chunks (size) using one of the following.
- CREATE_CHUNKS_BY_ROWID
- CREATE_CHUNKS_BY_NUMBER_COL
- CREATE_CHUNKS_BY_SQL
3.Define the Update statement.
4.Execute the task in parallel with RUN_TASK.
5.Perform error handling and run failed chunks with RESUME_TASK.
Tagged: DBMS_PARALLEL_EXECUTE, DBMS_PARALLEL_EXECUTE Licensing
TwelveC 
Online Article……
[…]The info mentioned in the article is some of the best available […]………