0

I have written a Stored Procedure which has 10 DML statements in series where each DML statement takes around 3 minutes for execution. The total Stored Procedure runs for about 29 minutes in production (Each DML dumps millions of record).

I would need 1st two DML statements alone to run in series and the remaining 8 can run in parallel, since they have no dependency.

Need advice to achieve this without using dbms_job or dbms_scheduler

begin
insert all into table1 values () 
insert all into table2 values () 
select ...;
insert into table3 select ... from table1 join table2...;
insert into table4 select ... from table2 join tableA...;
insert into table5 select ... from table1 join tableB...;
insert into table6 select ... from table1 join tableC...;
insert into table7 select ... from table1 join tableD...;
insert into table8 select ... from table1 join tableE...;
insert into table9 select ... from table1 join tableF...;
insert into table10 select ... from table1 join tableG...;
end;
Dheepan
  • 11
  • 1
  • 1
  • 4
  • You could put each DML in a separate procedure (maybe in a package) and then [run them through the scheduler](https://docs.oracle.com/database/121/ADMIN/scheduse.htm). Can you leave them all to run and forget about them, or do you need to know when they have all finished? – Alex Poole Apr 06 '17 at 17:10
  • @AlexPoole: Thanks for your reply. Once the 10 DML statements complete, I would have to start a batch job in an external system [Tool] manually. The DBA team is reluctant to provide huge time window for this activity as multiple projects too require downtime. In case of error \ any issue existing SP rollsback fully. This has to be handled if I split the SP – Dheepan Apr 06 '17 at 17:26
  • Look at chains then, in the same document I linked to before. – Alex Poole Apr 06 '17 at 17:30
  • Yes., checked it and in the below thread too, I found the same solution., http://stackoverflow.com/questions/18200621/oracle-dbms-scheduler-to-run-multiple-procedures-in-parallel Wanna achieve single SP execution without using dbms_job or dbms_scheduler. Hence requesting for ways again., Possible? – Dheepan Apr 06 '17 at 17:46
  • No. You can have a single procedure that kicks off all the jobs, but you can't otherwise run things in parallel or in the background from PL/SQL. – Alex Poole Apr 06 '17 at 17:48

0 Answers0