DBMS_PARALLEL_EXECUTE summarized

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 ?

  1. Large single transaction can be broken into multiple chunks (transactions)
  2. Less impact to dependent systems like Golden Gate or streams
  3. Better performance compared to parallel DML when chunks are based on ROWID and blocks.
  4. Less undo space required  and minimizing the chance of ORA-1555
  5. Impact of Rollback due to error is not severe.
  6. Duration of lock is reduced.
  7. Restart  failed chunks (transactions)
  8. Unlike other parallel features , DBMS_PARALLEL_EXECUTE  does not require Enterprise Edition (at least for now)
  9. 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.

Advertisements

Tagged: ,

One thought on “DBMS_PARALLEL_EXECUTE summarized

  1. Jadoo SEO July 24, 2011 at 4:55 am

    Online Article……

    […]The info mentioned in the article is some of the best available […]………

Comments are closed.

%d bloggers like this: