9 Replies Latest reply: Oct 3, 2013 11:39 PM by Billy~Verreynne RSS

    run oracle procedures in parallel

    Sudhir

      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