Multi Threading with PL/SQL code to improve the Performance

Purpose of this document is achieve multi threading within a PL/ SQL code to improve the performance.

Consider this scenario

for i IN ( 1..100)

  LOOP

  {

      f(i) ;

  }

Function f is executed serially, 100 times one after another. Starting with 11gR2 we have the dbms_parallel_execute utility, this divides the work into smaller chunks and different sessions ( on different RAC instances) would execute the function concurrently ! Isn't this a cool new feature ?

Here is a quick little demo on how to get this working

SQL> create table T1 ( ID NUMBER);

-- Insert 4 rows into T1

SQL> insert into T1 values(1);

SQL> insert into T1 values(2);

SQL> insert into T1 values(3);

SQL> insert into T1 values(4);

SQL> commit;

SQL> create table T2 (A NUMBER);

-- Create function F that will insert a row into T2. This is the function which we would be running  concurrently. 

CREATE OR REPLACE PROCEDURE F ( start_id IN number, end_id IN number)

AS

BEGIN

FOR sql_rec IN (SELECT ID FROM T1 WHERE ID BETWEEN start_id and end_id)

LOOP

INSERT INTO T2 VALUES(9);

END LOOP;

END;

/

-- This is the real code. 

BEGIN 

    

-- Create a parallel execute task   

      

dbms_parallel_execute.create_task('MULTI_THREAD_TASK');

-- Create chunks by number col.  

DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col

                             (task_name => 'MULTI_THREAD_TASK',

                              table_owner => 'VISHAL',

                              table_name => 'T1',

                              table_column => 'ID',

                              chunk_size => 1);

-- Run the task with parallel level 4.

dbms_parallel_execute.run_task

                        ( task_name => 'MULTI_THREAD_TASK',

                          sql_stmt  => 'begin  F( :start_id, :end_id ); end;',

                          language_flag  => DBMS_SQL.NATIVE,

                          parallel_level => 4);

-- Just in case you want to drop the task.                                   

-- dbms_parallel_execute.drop_task('MULTI_THREAD_TASK' );

END;

/

You can query DBA_PARALLEL_EXECUTE_CHUNKS view to get the job names and then map it to  dba_scheduler_job_run_details. You would get the instance ID and session ID details. By default Oracle would distribute the work in a uniform way across all the RAC instances.

If in case you want all the concurrent sessions to run on a single node, create a service that runs only on that specific node. Then create a new job class and assign the service to it. And then run the task with the job class you just created.