Execute procedures parallel

m.abohsin

Hi everyone,

I have a procedure which processes 80 million records, performing several data manipulation statements, the details are not important. What is important is that I want to divide the 80 million records into 4 groups of 20 million records. So I have a procedure called sp_data_manipulation(). I want to create a new procedure as follows:
create or replace procedure sp_data_manipulation as
begin
for i in (select id from masters_tab)
loop
exec sp_data_manipulation_p1();
exec sp_data_manipulation_p2();
exec sp_data_manipulation_p3();
exec sp_data_manipulation_p4();
end loop;
end;
/

All 4 procedures being called are identical, but each processes a different set of records. If procedure is executed in that way, it executes sequentially proc 1,2,3 then 4, which ofcourse is just the same as executing one procedure for all records. I want to execute them in parallel, so that all four procedures are executed at once. One suggestion I have done, but I don't like is:
nohup sqlplus user/[email protected] @proc1.sql &
nohup sqlplus user/[email protected] @proc2.sql &
nohup sqlplus user/[email protected] @proc3.sql &
nohup sqlplus user/[email protected] @proc4.sql &

where each proc.sql contains 'EXEC SP_DATA_MANIPULATION()', so all 4 are run in parallel, but I need to run this from operating system and not from inside Oracle.

Another solution, which did not convince me, is creating four different Scheduled Jobs, each calling one of the procedures, then they can all fire at same time, but I want to do it from within the PL/SQL code itself. If anyone can help it would be appreciated.

Thanks in advance.

Hoek

This will do it from PL/SQL code:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4712493580500
Use DBMS_JOB.SUBMIT instead of DBMS_SCHEDULER

edit

By the way, at second look at your procedure:

exec sp_data_manipulation_p1();

EXEC is a SQL*Plus command, you cannot use it in a PL/SQL block.
Just use:

sp_data_manipulation_p1();

to call/execute the procedures.

Edited by: hoek on Jan 23, 2010 12:03 AM