Execute procedures parallel
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/password@db @proc1.sql &
nohup sqlplus user/password@db @proc2.sql &
nohup sqlplus user/password@db @proc3.sql &
nohup sqlplus user/password@db @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.