4 Replies Latest reply: Jan 23, 2013 2:24 AM by Nikolay Savvinov RSS

    Performance of functions is sql

      select * from v$version;
      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE      Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production
      Can anyone share experiences on performance impact of using inbuilt functions (Like DECODE here) in SQL.

      I have couple of UPDATE and SELECT statements having large execution count (3 per sec) and have aggressive function usage in them.
      UPATE Statement
      UPDATE table SET 
      DAILY_SUM= DECODE(updt_date , :B13 , DAILY_SUM+:B9 , :B1 , DAILY_SUM , :B9 ) , 
      LAST_DAILY_COUNT= DECODE(updt_date , :B13 , LAST_DAILY_COUNT , :B1 , LAST_DAILY_COUNT + :B7 , :B14 , DAILY_COUNT , 0) , 
      DAILY_COUNT= DECODE(updt_date , :B13 , DAILY_COUNT+:B7 , :B1 , DAILY_COUNT , :B7 ) , 
      updt_date = DECODE(updt_date , :B1 , updt_date , :B13 ) , 
      LAST_MONTHLY_SUM + :B9 , :B12 , MONTHLY_SUM , 0) , 
      WHERE table.ID = :B3 AND updt_date <= :B1;
      update tab
      set last_commit_time = GREATEST(last_commit_time, CURRENT_TIMESTAMP) where id = :11 ;
      Update in FOR LOOP (This PL/SQL procedure is called 4 times a day and is processing 1M records (#of iterations in for loop))
      update tab set available_credit = available_credit+(decode(sign(available_change),1,available_change, 0)), 
      available_debit = available_debit+(decode(sign(available_change),-1,-available_change, 0)),  
      last_commit_time = GREATEST(last_commit_time, CURRENT_TIMESTAMP),  last_commit_order = last_commit_order + 1 
      where id = accountid;
      Are there any performance statistics i should watch for such function calls and their impact on CPU utilization (or on any other resource utilization) ?
      If there is any negative performance impact, is there any alternative approach to writing sql code such that it does not have such function calls ?
        • 1. Re: Performance of functions is sql
          What is performance change when you benchmark SQL that does not contain any functions?
          • 2. Re: Performance of functions is sql
            Thanks sb92075 for taking time.
            Application is using such functions ever since it went live on production (~1 year back). It never ran without functions and hence i have no performance data without functions.
            Since scalability is a concern in running prod database on commodity hardware, i want to check how much such function calls are contributing to overall service/db performance.
            (As of now 10 application servers runs such sqls every now and then. We have a plan of running it from 18 app servers and hence #of executions are almost going to get 2X)

            If you can point me towards any specific statistics to look for this function calls, i can certainly share those details.
            • 3. Re: Performance of functions is sql
              Mostly functions are neither fast nor slow, it is how they are used or abused that matters to performance.

              The example you show should have no noticeable impact on performance, if the DECODE was in the where clause and was used to join to different columns or tables then it could have a big impact
              958830 wrote:
              Update in FOR LOOP (This PL/SQL procedure is called 4 times a day and is processing 1M records (#of iterations in for loop))
              Calling a million updates in a loop instead of updating a million rows with a single update statement however will absolutely kill performance and will not scale at all.

              If you care at all about performance you will look at that instead of the use of inbuilt functions.
              • 4. Re: Performance of functions is sql
                Nikolay Savvinov

                I've never seen any measurable performance impact from built-in functions like DECODE, and I don't think you have any reason to be concerned about that. It is user-defined PL/SQL functions that you should be concerned about, because they can affect your performance either via expensive context switching (from SQL to PL/SQL and back), inefficient code or because of SQL limitations regarding PL/SQL code (e.g. when join conditions involve a user-defined function). But you don't have any in the posted code.

                A far more serious problem is, as already pointed out by others, row-by-row processing (e.g. UPDATE inside the loop).

                Best regards,