Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Execute procedures parallel

m.abohsinJan 22 2010 — edited Jan 22 2010
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 19 2010
Added on Jan 22 2010
1 comment
2,362 views