Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue!

Parallel execution of process is an important feature of any software for efficient performance. Any database management system must have this capability of executing multiple tasks at the same time and is usually built into the RDBMS. Oracle does it better than any other available platforms using DBMS_PARALLEL_EXECUTE.

There are situations when there is one task running for too much of the time. For example if we need to update the records of a table and they are in millions then the task will take a lot of time. But for most of the time the task will not be using all of the available resources.

If, there could be a way we can break this one task, into multiple smaller tasks and execute all of them at once, then we will be able to use the resources available more efficiently and also the task will be completed in much lesser time.

The new PL/SQL package DBMS_PARALLEL_EXECUTE exactly does this. You specify what task is in hand, and also specify how many smaller tasks should be created for this. With a few simple steps you can break your task into smaller checks fairly easily. The hectic task of manually breaking a longer task into smaller chunks is not required. This package is available from 11gR2 version of database.

The process is very simple and is illustrated below with an example. Suppose, any EMP table needs to be updated where ORG_NAME field has changed (just an example, normally organization related data is stored in another table). If the table has, say 45 to 50K records in it than depending on triggers, indexes etc on the table, it can take hours or days to complete this task using normal SQL. The normal SQL would be like this:

UPDATE EMP
SET ORG_NAME=’ABC’;
COMMIT;

You want to divide this task into multiple smaller tasks using DBMS_PARALLEL_EXECUTE. You can do this using these simple steps.

  • Creating the task
  • Chunking the task
  • Executing the task

Let us have a look on these steps one by one.

Creating the task

The first thing is to create the task. DBMS_PARALLEL_EXECUTE will use the Scheduler to create a task so your schema should have the privilege of creating tasks. Once you are sure that it has the privilege then you can execute the following command to create a task:

DBMS_PARALLEL_EXECUTE.CREATE_TASK (‘Update Org’);

This will create a simple task. Remember we have not told Oracle anything about what to do. We just created a simple task.

Chunking the task

The next step is to chunk the task. There are three ways about how you can chunk your task. We will look at them now.

Chunking by ROWID

The first method to chunk your task is using ROWID. You can use the following procedure of DBMS_PARALLEL_EXECUTE to do this.

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
(task_name => ‘Update Org’,
table_owner => USER
table_name => 'EMP'
by_row => TRUE
chunk_size => 1000);

We have provided the task name to be executed, provided who is owner of table, provided table name, specified that we want to chunk BY_ROW and defined what should be the size of each chunk. Your original Update Statement will also change a bit and will look like this.

UPDATE EMP
SET ORG_NAME=’ABC’
WHERE ROWID BETWEEN :START_ROW_ID AND :END_ROW_ID;

Here, the WHERE clause will help in chunking and bind variables will be replaced with actual values when the task is executed.

Chunking by SQL Statement

If you want to have more control on how chunk should be made then you can define your own criteria and use chunking by SQL statement. Suppose, in above example you want to make chunks based on employees within a specific departments like Sales, Marketing etc. You can use the following to do that.

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL (
task_name => ‘Update Org’,
sql_stmt => ,
by_rowid => FALSE);

Chunk Statement can be any SQL statement which can be used to divide the original update. Like you can select department ids from department table to chunk in department wise.

Chunking by Number Column

If you have a Number column table in your table and you think that it has enough distinct values then you can use that Number to chunk the task. For example

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL (
task_name => ‘Update Org’,
table_owner => USER,
table_name => ‘EMP’,
table_column => NUMBER_COLUMN,
chunk_size => 1000);

Here you just provide the Number column name, and Oracle will calculate the MIN and MAX values in the column and divide the task using the chunk size you provided.

Executing the task

The last step is to execute the task. You will use the following to do that.

DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => ‘Update Org’,
sql_stmt =>Update_Statement,
language_flag =>DBMS_SQL.native,
parallel_level => 10);

The task_name will specify which task to execute. The sql_stmt parameter will specify what to do. In our case it will be the actual update statement which is required to be executed. And lastly we will specify the parallel_level which will determine how many parallel tasks to be executed at any given time.

DBMS_PARALLEL_EXECUTE Procedures

There are other numerous procedure provided by DBMS_PARALLEL_EXECUTE which can provide different functionality. A few very important are:

TASK_STATUS – Provides the current status of task.
STOP_TASK – Stops the task temporarily.
RESUME_TASK – Resumes the stopped task.
DROP_TASK – Drops the task permanently after successful execution.

For a full reference of available packages, you can check the Oracle technology network guide on new packages and types.

Comments

  1. n.jaheer@gmail.com'Zaheer says

    Can you please let us know how to view the task configuration? Actually we have a task running and could able to find out the task name but not able to find out the parameters on which task is created i.e., when it runs, what it will do, whether it is created on ROWID or COL etc…. Your help will be greatly appreciated.

Leave a Reply

Your email address will not be published.