12 Replies Latest reply: Feb 8, 2013 3:57 AM by Etbin RSS

    Oracle stored procedure taking long time when updates in loop

    989221
      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
          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
            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
              Billy~Verreynne
              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
                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
                  Billy~Verreynne
                  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
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            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
                              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