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

calling a procedure in the loop

9876564 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Guru
    Currently Being Moderated
    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.

     

Legend

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