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.

Bloom Partition Pruning In Oracle Vs Postgres

In this blog post let's compare Oracle and Postgres optimizer transformation features.  For this test case I created two small tables an...