Saturday 30 April 2016

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.

No comments:

Post a Comment

19c Multitenant RAC- Default service disables parallelism

We recently upgraded one of our RAC databases from 11.2 to 19.10 on Exadata. And to everyone's surprise, almost everything on the system...