This discussion is archived
12 Replies Latest reply: Feb 8, 2013 1:57 AM by Etbin RSS

Oracle stored procedure taking long time when updates in loop

989221 Newbie
Currently Being Moderated
This is my stored procedure

I have below store procedure:

create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,
time_after_loop out timestamp,
endtime out timestamp)
IS
processingcount INTEGER;
failedcount INTEGER;
totalrecords INTEGER;
updatedrecords INTEGER;

BEGIN
o_statuscode := '0';
o_statusdescription := 'SUCCESS';
starttime :=systimestamp;


DECLARE
CURSOR TESTJOBid_cur
IS
SELECT AJ.JOBID
FROM CPA.TESTJOB aj
WHERE AJ.JOBSTATUS in (1,2);

BEGIN

time_after_query_TESTJOB := systimestamp;

FOR I IN TESTJOBid_cur
LOOP

select count(case when aut.transactionstatuscode=3 then 1 else null end),
count(case when aut.transactionstatuscode=4 then 1 else null end ),
count(case when aut.transactionstatuscode is not null then 1 else null end),
count(case when aut.transactionstatuscode=1 then 1 else null end)
Into updatedrecords,failedcount,totalrecords,processingcount
From cpa.TESTJOBTRANSACTIONS aut
where aut.jobid=I.jobid;

IF (processingcount = 0)
THEN
IF (failedcount = 0)
THEN

UPDATE CPA.TESTJOB aj
SET AJ.JOBSTATUS = 3,
AJ.JOBSTATUSDESCRIPTION ='COMPLETE',
AJ.NUMOFRECORDSFAILED = failedcount,
AJ.NUMOFRECORDSMATCHED = updatedrecords,
AJ.NUMOFRECORDSUPDATED = updatedrecords,
AJ.TOTALRECORDSINFILE = totalrecords,
AJ.JOBENDTIME = SYSDATE
WHERE AJ.JOBID = I.jobid;

ELSE

UPDATE CPA.TESTJOB aj
SET AJ.JOBSTATUS = 4,
AJ.JOBSTATUSDESCRIPTION ='FAILED',
AJ.NUMOFRECORDSFAILED = failedcount,
AJ.NUMOFRECORDSMATCHED = updatedrecords,
AJ.NUMOFRECORDSUPDATED = updatedrecords,
AJ.TOTALRECORDSINFILE = totalrecords,
AJ.JOBENDTIME = SYSDATE
WHERE AJ.JOBID = I.jobid;

END IF;

END IF;

END LOOP;

time_after_loop := systimestamp;

COMMIT;

END;

EXCEPTION
WHEN OTHERS
THEN
o_statuscode := SQLCODE;

o_statusdescription := SQLERRM;

END TESTPERFORMANCE;



This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records.Please help me to reduce exceution time

Edited by: 986218 on Feb 5, 2013 4:17 AM
  • 2. Re: Oracle stored procedure taking long time when updates in loop
    SomeoneElse Guru
    Currently Being Moderated
    Please help me to reduce exceution time
    Don't use a loop.

    Use UPDATE or MERGE.
  • 3. Re: Oracle stored procedure taking long time when updates in loop
    Etbin Guru
    Currently Being Moderated
    Looks like your cursor and loops could be replaced by something like
    merge into cpa.testjob tj
    using (select jobid,
                  count(case when transactionstatuscode = 3 then 1 end) updatedrecords,
                  count(case when transactionstatuscode = 4 then 1 end) failedcount,
                  count(case when transactionstatuscode is not null then 1 end) totalrecords,
                  count(case when transactionstatuscode = 1 then 1 end) processingcount
             from cpa.testjobtransactions
            where jobid in (select jobid
                              from cpa.testjob
                             where jobstatus in (1,2)
                           )
            group by jobid
          ) cnt
       on (tj.jobid = cnt.jobid
       and cnt.processingcount = 0
          )
     when matched
     then update
             set jobstatus = case when cnt.failedcount = 0
                                  then 3
                                  else 4
                             end,
                 jobstatusdescription = case when cnt.failedcount = 0
                                             then 'COMPLETE'
                                             else 'FAILED'
                                        end,
                 numofrecordsfailed = cnt.failedcount,
                 numofrecordsmatched = cnt.updatedrecords,
                 numofrecordsupdated = cnt.updatedrecords,
                 totalrecordsinfile = cnt.totalrecords,
                 jobendtime = sysdate
    Regards

    Etbin
  • 4. Re: Oracle stored procedure taking long time when updates in loop
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    986218 wrote:

    This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records.Please help me to reduce exceution time
    The issue is how the workload (processing x number of rows) is done. And perhaps you not understanding what PL/SQL is.

    PL/SQL is 2 different languages. The PL (Programming Logic) language is based on Ada (part of the Pascal family of languages). It is a "proper" programming language, like C/C++, Java, etc. It is not a scripting language. It is not an extension to the SQL language (like Transact SQL). It is a full blown declarative, procedure language, with object orientation features.

    To make using SQL easy, PL/SQL integrates with SQL at source code level. It allows you to code native SQL source code inside PL source code (kind of like mixing Java source code and SQL source code in the same method). The PL/SQL engine is clever enough to determine what is PL code, and what is SQL code - and parse, bind and execute SQL cursors for you, transparently.

    Now for the performance problem you have. You have already been told to use SQL to do the job, and Etbin has shown you a sample SQL MERGE.

    It is important for you to understand why the guys responded with "use SQL". Why is using SQL only (where and when possible) faster?

    When you open a SQL cursor in PL/SQL, the rows need to be fetched (via the SQL engine) and copied into PL/SQL memory. This data is then send back to the database (via the SQL engine) using another SQL cursor that does the update or insert. The data therefore travels from the database to PL/SQL and then from PL/SQL back to the database. This is an expensive route for data to travel. There is also context switching as the PL/SQL engine has to pause, call the SQL engine, wait for a response, and then resume. This is an expensive overhead.

    Using the MERGE statement, the read-data is done, together with the insert-data and update-data, using a single SQL cursor. The data does not need to go on a detour through PL/SQL engine code. There is no context switching back and forth while these rows are processed.

    The row data is processed as a set. And not in a row by row (also called <i>slow-by-slow</i>) fashion by fetching rows from one cursor and then using the data in another cursor.

    The basic database performance concept to always keep in mind is: Maximise SQL. Minimise PL/SQL (Java,C#,etc).

    Do as much of the data crunching as possible in SQL. It is the fastest and most scalable method to process database data.
  • 5. Re: Oracle stored procedure taking long time when updates in loop
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    I think your approach is wrong towards creating procedure.
    you can do like below:-
    create or replace procedure proc_name(parameter,parameter,parameter,parameter,parameter........)
    is
    variable_list;
    -------------now declare here cursor as below
    cursor cursor_name is select query;
    begin
    ----
    ----
    ----
    ----
    ----
    execption
    end;
    Try below
    create or replace
    PROCEDURE TESTPERFORMANCE (
    o_statuscode OUT NUMBER,
    o_statusdescription OUT VARCHAR2, 
    starttime out timestamp,
    time_after_query_TESTJOB out timestamp,
    time_after_loop out timestamp,
    endtime out timestamp)
    IS
    processingcount INTEGER;
    failedcount INTEGER;
    totalrecords INTEGER;
    updatedrecords INTEGER;
    
    CURSOR TESTJOBid_cur
    IS
    SELECT AJ.JOBID
    FROM CPA.TESTJOB aj
    WHERE AJ.JOBSTATUS in (1,2);
    
    BEGIN
    o_statuscode := '0';
    o_statusdescription := 'SUCCESS';
    starttime :=systimestamp;
    
    time_after_query_TESTJOB := systimestamp;
    
    FOR I IN TESTJOBid_cur
    LOOP
    select count(case when aut.transactionstatuscode=3 then 1 else null end),
    count(case when aut.transactionstatuscode=4 then 1 else null end ),
    count(case when aut.transactionstatuscode is not null then 1 else null end),
    count(case when aut.transactionstatuscode=1 then 1 else null end)
    Into updatedrecords,failedcount,totalrecords,processingcount
    From cpa.TESTJOBTRANSACTIONS aut
    where aut.jobid=I.jobid;
    IF (processingcount = 0 and failedcount = 0)
    THEN
    UPDATE CPA.TESTJOB aj
    SET AJ.JOBSTATUS = I.3,
    AJ.JOBSTATUSDESCRIPTION ='COMPLETE',
    AJ.NUMOFRECORDSFAILED = I.failedcount,
    AJ.NUMOFRECORDSMATCHED = I.updatedrecords,
    AJ.NUMOFRECORDSUPDATED = I.updatedrecords,
    AJ.TOTALRECORDSINFILE = I.totalrecords,
    AJ.JOBENDTIME = SYSDATE
    WHERE AJ.JOBID = I.jobid;
    ELSE
    UPDATE CPA.TESTJOB aj
    SET AJ.JOBSTATUS = 4,
    AJ.JOBSTATUSDESCRIPTION ='FAILED',
    AJ.NUMOFRECORDSFAILED = I.failedcount,
    AJ.NUMOFRECORDSMATCHED = I.updatedrecords,
    AJ.NUMOFRECORDSUPDATED = I.updatedrecords,
    AJ.TOTALRECORDSINFILE = I.totalrecords,
    AJ.JOBENDTIME = SYSDATE
    WHERE AJ.JOBID = I.jobid;
    END IF;
    END IF;
    END LOOP;
    
    time_after_loop := systimestamp;
    
    COMMIT;
    
    END;
    
    EXCEPTION
    WHEN OTHERS
    THEN
    o_statuscode := SQLCODE;
    
    o_statusdescription := SQLERRM;
    
    END TESTPERFORMANCE;
  • 6. Re: Oracle stored procedure taking long time when updates in loop
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Chanchal Wankhade wrote:

    I think your approach is wrong towards creating procedure.
    So is yours with code like this:
    EXCEPTION
    WHEN OTHERS
    THEN
    o_statuscode := SQLCODE;
    o_statusdescription := SQLERRM;
    END TESTPERFORMANCE;
  • 7. Re: Oracle stored procedure taking long time when updates in loop
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    No Billy. He is tring something this. so i assume that he have specified actual exception in that exception block area.
  • 8. Re: Oracle stored procedure taking long time when updates in loop
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Chanchal Wankhade wrote:
    No Billy. He is tring something this. so i assume that he have specified actual exception in that exception block area.
    The exception handler you posted as sample code is wrong. As simple as that. And it is as wrong in PL/SQL, as it would be wrong in Java, C#, Delphi, and other language that uses exceptions.

    Exceptions must not be suppressed and an error code returned as output parameter. That is a fundamental design violation in any language that uses exception processing.
  • 9. Re: Oracle stored procedure taking long time when updates in loop
    Karthick_Arp Guru
    Currently Being Moderated
    Chanchal Wankhade wrote:
    No Billy. He is tring something this. so i assume that he have specified actual exception in that exception block area.
    Below there are 2 anonymous block. Both does the same thing. But the difference is that

    1. Block 1 Tells the caller it has failed, Block 2 Tells the caller it has completed successfully.
    2. Block 1 Tells what error has occured and where exactly it has occured, Block 2 Tells what error has occured and lets the user keep guessing where it occured.
     
    SQL> declare 
      2    v char(1); 
      3  begin 
      4    select dummy into v from dual where 1=2; 
      5    select dummy into v from dual where 1=2; 
      6    select dummy into v from dual where 1=2; 
      7  end; 
      8  / 
    declare 
    * 
    ERROR at line 1: 
    ORA-01403: no data found 
    ORA-06512: at line 4 
    
    
    SQL> declare 
      2    v char(1); 
      3  begin 
      4    select dummy into v from dual where 1=2; 
      5    select dummy into v from dual where 1=2; 
      6    select dummy into v from dual where 1=2; 
      7  exception 
      8    when others then 
      9      dbms_output.put_line('SQLCODE - ' || SQLCODE || ' SQLERRM - ' || SQLERRM); 
    10  end; 
    11  / 
    
    SQLCODE - 100 SQLERRM - ORA-01403: no data found 
    
    PL/SQL procedure successfully completed. 
    Storing the error code in a variable and returning it is a very old and bad technique. If something fails, the called need to be told about that and the proper way is by raising an exception. If you want to suppress the actual exception log it somewhere else and give the caller a generic message, you can. Oracle offers good tools like FORMAT_ERROR_STACK, FORMAT_ERROR_BACKTRACK, FORMAT_CALL_STACK in DBMS_UTILITY to log the exceptions. SQLCODE and SQLERRM are very primitive tools. They do not offer much information.
  • 10. Re: Oracle stored procedure taking long time when updates in loop
    989919 Newbie
    Currently Being Moderated
    Hey Etbin ..you are savier..Thanks a lot for this:) Now my procedure is completed in seconds :)

    Thanks so much:)
  • 11. Re: Oracle stored procedure taking long time when updates in loop
    989919 Newbie
    Currently Being Moderated
    Thanks belly:) That was a very good explaination..This really helped :) Now i understand why we should prefer SQL over PL/SQL...Thanks again:)
  • 12. Re: Oracle stored procedure taking long time when updates in loop
    Etbin Guru
    Currently Being Moderated
    Something to take a look at: [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:73891904732164]the mantra and keep in mind permanently (nevertheless it seems you are aware of already)

    Regards

    Etbin

Legend

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