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.
