9 Replies Latest reply: Oct 3, 2013 5:49 PM by rp0428 RSS

    calling a procedure in the loop

    9876564

      Just want to know one thing :

      I am calling a procedure with the following parameters

       

      DECLARE

          PROFIT_CENTER NUMBER;

       

      BEGIN

        PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132,1122,3211,1111 one by one by passing values manually, it is taking almost a minute for each profit center)

      Prc_test_calc (  PROFIT_CENTER);

       

      COMMIT;

       

      END;

      For each profit_center proc is taking 1 mins (Approx) time.

       

      But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center

       

      Please let me know is there anything wrong with the below loop

       

      DECLARE

          PROFIT_CENTER NUMBER;

        cursor c_profit is

      select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); -- Plng_pc is a table from where profit_center information is getting fetched

       

      BEGIN

      For pc_rec in c_profit

      loop

         Prc_test_calc (  pc_rec .PROFIT_CENTER );

        COMMIT;

      End loop;

       

      END;

       

      Could you please sugget me if something is going wrong?

        • 1. Re: calling a procedure in the loop
          rp0428

          For each profit_center proc is taking 1 mins (Approx) time.

           

          But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center

           

          Please let me know is there anything wrong with the below loop

          What is wrong is that you are using a loop when you clearly don't need one. You already know which profit centers you want to call the proc for so just call the proc five times; once for each value.

           

           

          • 2. Re: calling a procedure in the loop
            user8916362

            May be issue in SQL statement in cursor?

            How long it executed?

             

            Try this:

             

            DECLARE

              TYPE center IS TABLE OF NUMBER;

              centers center  := center(1109, 1123,1132,1122,3211,1111); 

              tsStartTime TIMESTAMP;

              nSecElapsed NUMBER; 

               

              PROCEDURE test_proc(item IN NUMBER) IS 

              BEGIN

                dbms_output.put_line('Center: '||item);

                dbms_lock.sleep(10);

              END;

            BEGIN

              tsStartTime := CURRENT_TIMESTAMP;    

             

              FOR i IN centers.FIRST .. centers.LAST

              LOOP   

                test_proc(centers(i));

                nSecElapsed := TO_NUMBER(extract(SECOND FROM CURRENT_TIMESTAMP-tsStartTime));

                dbms_output.put_line('Sec. elapsed: '|| to_char(nSecElapsed));

              END LOOP; 

             

            END; 

            • 3. Re: calling a procedure in the loop
              Vite DBA

              Hi,

               

              as RP points out, the only variable here is the select you are using to build up your loop. How long does the query take to execute by itself? Perhaps you could try something like (untested)

               

               

              declare

               

                type profit_center_list_type is table of number;

                v_profit_center_list profit_center_list_type := profit_center_list_type(1109,1123,1132,1122,3211,1111);

               

              begin

               

                for i in 1..v_profit_center_list.last loop

               

                  prc_test_calc(v_profit_center_list(i));

                  commit;

               

                end loop;

               

              end;

              /

               

               

              Regards

              Andre

              • 4. Re: calling a procedure in the loop
                34MCA2K2

                That depends on what the code is doing inside the proc, however how do you know that the select is not consuming more time and it is the procedure only. Your select -

                 

                select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111);


                Is there a need to be commit inside the loop. It seems incomprehensible to me.

                • 5. Re: calling a procedure in the loop
                  9876564

                  The point is number of profit centers are more than 40( just for example i have given you 5 profit centers ) so if i am running for each profit center then proc is taking 1 min for each , then ideally it should talke 40-50mins max for all profit center , but this is not the case as it is running for more than 2 hours now....i know without  seeing code it is difficult to conclude but just wanted to know if there is any thing wrong with the loop i am taking above , even when i am looping 5 profit centers to test it , it is taking a hell amount of time.

                   

                  Not Sure what is wrong there..........

                  • 6. Re: calling a procedure in the loop
                    Vite DBA

                    Hi,

                     

                    as suggested, have you checked the query for the cursor loop and have you tried the process with a non-cursor loop.

                     

                    Regards

                    Andre

                    • 7. Re: calling a procedure in the loop
                      9876564

                      I tried the cursor loop without calling my proc and it is coming out in a fraction of second ...so the problem is with the procedure only...Let me seeeeeeeeeeee:(

                      • 8. Re: calling a procedure in the loop
                        Vite DBA

                        Hi,

                         

                        now that you have established that, this becomes a normal performance tuning exercise, the first stage of which is to gather information. I'm not sure what tools you have at your disposal but you should have the ability to turn on sql tracing for the session and then processing and analyzing the output, looking for queries consuming the most time and the waits associated with those queries.

                         

                        see

                         

                        http://docs.oracle.com/cd/E11882_01/server.112/e41573/sqltrace.htm#i4640

                         

                        for SQL trace.

                         

                        also for how to post a tuning request see

                         

                        https://forums.oracle.com/message/3298734#3298734

                         

                        and

                         

                        https://forums.oracle.com/thread/503834

                         

                        Regards

                        Andre

                        • 9. Re: calling a procedure in the loop
                          rp0428
                          I tried the cursor loop without calling my proc and it is coming out in a fraction of second ...so the problem is with the procedure only

                           

                          Which means that this statement you made at the top of your thread is NOT true:

                          For each profit_center proc is taking 1 mins (Approx) time.

                          It is not very likely that the same amount of work will be done for each and every profit center. It is MUCH more likely that one, or more, of them is requiring a LOT more work and that work may include full table scans for some but efficient use of indexes for others.

                           

                          You need to revisit that statement you made above and determine which profit centers are causing the problem.

                           

                          Also read the FAQ about how to make tuning requests and if you need more help provide ALL of the information (execution plans, table and index DDL, table and predicate row counts, etc) needed to help you.