Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 395 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Execute procedures parallel

m.abohsin
Member Posts: 20
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.
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.
Tagged:
Answers
-
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
This discussion has been closed.