Execute Parallel task using DBMS_PARALLEL_EXECUTE in PLSQL

      DBMS_PARALLEL_EXECUTE is an Oracle Supplied package which was introduced with Oracle Database 11g Release 2.  This package enables the user to incrementally update table data in parallel.

There are 2 high level steps :

      ·  Group sets of rows in the table into smaller sized chunks.

·         Run a user specified statement on these chunks in parallel

       This package introduces the notion of parallel execution task. This task groups the various steps associated with the parallel execution of a PL/SQL block, which is typically updating table data.

steps for using the dbms_parallel_execute package:

1)      Create a task

Need to create a named task and this will be referred by DBMS_PARALLEL_EXECUTE

2)      Split the workload into chunks

You need to divide the tables into groups which will be executed parallel.

3)      Run the task

Need to run the task .While run the task you can specify the number of parallel jobs.

4)      Check the task status

You can check the status of the task . If it is error out you can correct the error and you can

Re-run

5)      Drop the task

Once the processing is completed you can drop the task which is created .

You can split the workload by using

·         dbms_parallel_execute.create_chunks_by_rowid

·         dbms_parallel_execute.create_chunks_by_number_col

·         dbms_parallel_execute.create_chunks_by_sql

Example :

This example uses create_chunks_by_rowid which is referred from oracle documentation.

In the example parallel_level  Specifies the number of parallel jobs.

Views referred by dbms_parallel_execute package are

USER_PARALLEL_EXECUTE_CHUNKS

USER_PARALLEL_EXECUTE_TASKS

If any error occurred at execution that will be stored in USER_PARALLEL_EXECUTE_CHUNKS. Once the errors have been corrected you can run the task again to process the failed chunks.

Chunk Status Value:

·         1->Assigned

·         2->PROCESSED

·         3->PROCESSED_WITH_ERROR

·         0->UNASSIGNED

Task Status Value:

·         1->CREATED

·         2->CHUNKING

·         3->CHUNKING_FAILED

·         4->CHUNKED

·         5->PROCESSING

·         6-FINISHED

·         7->FINISHED_WITH_ERROR

·         8->CRASHED

Some exceptions which is raised by DBMS_PARALLEL_EXECUTE:

·         CHUNK_NOT_FOUND

·         DUPLICATE_TASK_NAME

·         INVALID_STATE_FOR_CHUNK

·         INVALID_STATE_FOR_REDSUME

·         INVALID_STATUS

·         INVALID_TABLE

·         TASK_NOT_FOUND

Example: Step By Step for clear understanding

Step 1: Created a task ‘mytask’







  

Task can be viewed from USER_PARALLEL_EXECUTE_TASKS
 

  

Step 2: Split the workload into chunks

The split chunks can be viewed  from USER_PARALLEL_EXECUTE_CHUNKS with status

Step 3 :

Assign the DML statement to a variable and run the task .

Step 4: You can see the status of the chunks and tasks.

Step 5: Delete the task once it is processed

The data relating to the tasks will be deleted

If you try to split the workload after drop the task Exception will be raised

 If you like this post please click on 'g +1' . This might help some one else . Comments and suggestions are most welcome...!!!