This discussion is archived
9 Replies Latest reply: Oct 3, 2013 9:39 PM by BillyVerreynne RSS

run oracle procedures in parallel

Sudhir Newbie
Currently Being Moderated

We are having a functionality where we run a dayend job for 300 stores approx.

 

 

we have around 30 procedures that run daily for all the 300 stores where store_number is one of the parameter for all the 30 procedures.

 

 

ex:

 

 

package test(

 

 

     cursor loop  300 stores.

 

  begin

 

  open cursor loop

  begin

    p1(store1);

    p2(store1);

    p3(store1);

    p4(store1);

    p5(store1);

    p6(store1);

    p7(store1);

    ...

    ...

    ...

    p30(store1);

   end;

   end loop;

 

end test;

 

 

 

as this process is getting slow.we need to improve the performance.

So i am thinking of run the procedures in parallel i.e procedure p1 should run in parallel for 300 stores then procedure p2.

 

 

ex:

 

 

package test(

 

 

     cursor loop  300 stores.

 

  begin

 

  open cursor loop

    p1(store1);

  end loop;

 

  open cursor loop

    p2(store1);

  end loop;

    ...

    ...

    ...

   open cursor loop

    p30(store1);

   end loop;

 

 

end test;

 

 

Please advice, is this feasible to do.

if yes,what is the process.

our database is oracle 10.1.0.2.

p1 should be completed before p2.

 

please advice.

 

Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points