Pragma UDF & Deterministic – Keep Learning Keep Living

Hello everyone,

I wanted to write about something simple and can improve the performance of your sql statements. Unfortunately, many database developers use too much plsql function in their SQL statements. I say too much because if you learn more about the SQL, you will use less plsql. SQL is very capable language to process data. Of course we don’t design and develop a whole system all the time. We start to work somewhere and use their standards and run old codes. Many of my clients have some basic plsql functions which does not interact with SQL like just doing basic calculations or conversions. there plsql functions are pure algorithms which can be also run by SQL too. for example:

create or replace function sf_add_vat (p number) return number as 
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat (p number) return number as

begin

return p*1.18;

end;

this is a very simple VAT adding function. As you can see this calculation can be done by SQL too but let’s say someone writes this code 20 years ago and now it is used everywhere in database (maybe also in the application too). So you might not be able to change all of them but you can increase it’s performance by deterministic or pragma UDF.

let’s make a small example first:

drop table tmp purge;
create table tmp as select * from dba_tables where rownum <= 1000;

insert into tmp select tmp.* from tmp, (select * from dual connect by level <= 799) ;
commit;

drop table tmp purge;

create table tmp as select * from dba_tables where rownum <= 1000;

insert into tmp select tmp.* from tmp, (select * from dual connect by level <= 799) ;

commit;

this will create a table with 800.000 rows. now I will run a query against this table with and without function:

set timing on;

----------------------------------------------------------------
  
select max(sf_add_vat(pct_free)) from tmp;

MAX(SF_ADD_VAT(PCT_FREE))
-------------------------
                     11.8

Elapsed: 00:00:01.133

----------------------------------------------------------------
  
select max(pct_free*1.18) from tmp;

MAX(PCT_FREE*1.18)
------------------
              11.8

Elapsed: 00:00:00.134

set timing on;

----------------------------------------------------------------

select max(sf_add_vat(pct_free)) from tmp;

MAX(SF_ADD_VAT(PCT_FREE))

-------------------------

11.8

Elapsed: 00:00:01.133

----------------------------------------------------------------

select max(pct_free*1.18) from tmp;

MAX(PCT_FREE*1.18)

------------------

11.8

Elapsed: 00:00:00.134

it takes almost 10 times more to complete the query when you use a PLSQL function.

Reason is, whenever you use a plsql function inside a query (or vice versa) something called “context switch” is happening. you can think this as, SQL statements are run by SQL Engine and PLSQL statements are run by PLSQL engines and when ever these two engines meet with opposite statement, they have to call the other engine. this is context switching. You must avoid as much as you can from this but as I said if the system you work on is run the queries like this, you might not have to change all but you can, at least, increase the performance. that where pragma udf and deterministic come.

Deterministic tells Oracle, this function will return same result for same parameter all the time! if your plsql object does not interact with sql objects like tables or time variables like sysdate then probably it is a good candidate for deterministic. Also, if you want to index a user defined plsql function it must be deterministic. you can add deterministic clause to a function which is interact with sql objects or sysdate etc but this will only cause you headaches. so be careful about that.

Pragma udf is a way to reduce context switch overhead. it does not reduce the number of context switch but it makes it smaller and faster. that way if you use a plsql function in sql, context switch will be lighter.

Let’s make a small test now, my example is very good candidate for both deterministic and pragma udf:

create or replace function sf_add_vat (p number) return number as 
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat_deterministic (p number) return number  deterministic as 
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat_deterministic_udf (p number) return number  deterministic as 
  pragma udf;
begin
  return p*1.18;
end;
/

create or replace function sf_add_vat (p number) return number as

begin

return p*1.18;

end;

create or replace function sf_add_vat_deterministic (p number) return number deterministic as

begin

return p*1.18;

end;

create or replace function sf_add_vat_deterministic_udf (p number) return number deterministic as

pragma udf;

begin

return p*1.18;

end;

I created 3 version of my vat function. first is the base one, second is with a DETERMINISTIC clause and third is both deterministic and pragma udf. they all do the same work. let’s compare their performance:

set serveroutput on

declare
  s timestamp;
  
  procedure run(p_function varchar2) as
    v_result number;
  begin
    s := systimestamp;
    for i in 1..10 loop
      if p_function = 'BASE' then
        select max(sf_add_vat(pct_free)) into v_result from tmp;
      elsif p_function = 'BASE_DETERMINISTIC' then
        select max(sf_add_vat_deterministic(pct_free)) into v_result from tmp;
      elsif p_function = 'BASE_DETERMINISTIC_UDF' then
        select max(sf_add_vat_deterministic_udf(pct_free)) into v_result from tmp;
      elsif p_function = 'GO_SQL' then
        select max(pct_free*pct_free) into v_result from tmp;
      end if;
                              
    end loop;
    
    dbms_output.put_line(Rpad(P_Function,25,' ') || ': ' || ((systimestamp - s)/10));
  end;
begin
  run ('BASE');
  run ('BASE_DETERMINISTIC');
  run ('BASE_DETERMINISTIC_UDF');
  run ('GO_SQL');
end;
/

BASE                     : +000000000 00:00:01.150900000
BASE_DETERMINISTIC       : +000000000 00:00:00.280100000
BASE_DETERMINISTIC_UDF   : +000000000 00:00:00.192900000
GO_SQL                   : +000000000 00:00:00.127300000

PL/SQL procedure successfully completed.

set serveroutput on

declare

s timestamp;

procedure run(p_function varchar2) as

v_result number;

begin

s := systimestamp;

for i in 1..10 loop

if p_function = 'BASE' then

select max(sf_add_vat(pct_free)) into v_result from tmp;

elsif p_function = 'BASE_DETERMINISTIC' then

select max(sf_add_vat_deterministic(pct_free)) into v_result from tmp;

elsif p_function = 'BASE_DETERMINISTIC_UDF' then

select max(sf_add_vat_deterministic_udf(pct_free)) into v_result from tmp;

elsif p_function = 'GO_SQL' then

select max(pct_free*pct_free) into v_result from tmp;

end if;

end loop;

dbms_output.put_line(Rpad(P_Function,25,' ') || ': ' || ((systimestamp - s)/10));

end;

begin

run ('BASE');

run ('BASE_DETERMINISTIC');

run ('BASE_DETERMINISTIC_UDF');

run ('GO_SQL');

end;

BASE : +000000000 00:00:01.150900000

BASE_DETERMINISTIC : +000000000 00:00:00.280100000

BASE_DETERMINISTIC_UDF : +000000000 00:00:00.192900000

GO_SQL : +000000000 00:00:00.127300000

PL/SQL procedure successfully completed.

I run 4 versions, with basic function, deterministic function, deterministic & pragma udf and finally of course pure sql statement. As you can see BASE version is the worst and takes 1.15 seconds average to complete. just adding a deterministic clause decreases time spent almost 5 times and adding pragma udf decrease another 30-35% based on deterministic and finally pure sql is of course the fastest. pure sql is 10 times faster than BASE version. so, if you can use pure sql use it! if it is not possible at least you can check for deterministic candidates and use pragma udf.

Of course there is something called, “subquery caching” :

set timing on

select max( (select sf_add_vat(pct_free) from dual)) from tmp;

MAX((SELECTSF_ADD_VAT(PCT_FREE)FROMDUAL))
-----------------------------------------
                                     11.8

Elapsed: 00:00:00.15

set timing on

select max( (select sf_add_vat(pct_free) from dual)) from tmp;

MAX((SELECTSF_ADD_VAT(PCT_FREE)FROMDUAL))

-----------------------------------------

11.8

Elapsed: 00:00:00.15

as you see, subquery caching might be even faster than pragma udf and deterministic but it depends. this solution also requires changing the sql statement too.

So you can improve the performance just by small changes. find a plsql function that is so simply and used everywhere in your database (I am pretty sure there is one) and try the suggestions above. by the way, 19c has something even stronger called “SQL Macro” which I mentioned a little bit before:

https://mustafakalayci.me/2020/10/28/sql-macro-is-finally-here/

https://mustafakalayci.me/2020/10/30/sql-macro-part2-column_value-nvlparam-column_value/

I mostly mentioned for table sql macro but you can use sql macro to generate parts of sql statement too.

I hope this helps you to tune your sql & plsql statements.

wish you all healthy days.

Leave a Reply Cancel reply