This discussion is archived
4 Replies Latest reply: Jan 23, 2013 12:24 AM by Nikolay Savvinov RSS

Performance of functions is sql

961833 Newbie
Currently Being Moderated
select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - 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.
Eg:
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= DECODE(LAST_UPDATED_TRACKER_MONTH , :B10 , LAST_MONTHLY_SUM , :B11 , 
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
    sb92075 Guru
    Currently Being Moderated
    What is performance change when you benchmark SQL that does not contain any functions?
  • 2. Re: Performance of functions is sql
    961833 Newbie
    Currently Being Moderated
    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
    6363 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

    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,
    Nikolay

Legend

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