Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Pragma UDF & Deterministic

Mustafa, 2021-04-09

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:

1
2
3
4
5
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:

1
2
3
4
5
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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” :

1
2
3
4
5
6
7
8
9
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.

19c 21c Administration Development Performance deterministicoraclepragma udfsql macrosql performancesubquery caching

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Sessions with details
  • DBMS_PIPE Connecting the Sessions
  • DBCA Templates and Dangerous “General Purpose” Template
  • NON-CDB to PDB Migration Horror
  • 23c is Finally Here!
  • Not Deploying is also Important!
  • Damaged rowid is referenced! Health Monitor
  • Old Obsolete Backup is not Deleted

Recent Comments

  • Mustafa on DBMS_PIPE Connecting the Sessions
  • Hemant K Chitale on DBMS_PIPE Connecting the Sessions
  • Mustafa on DBMS_PIPE Connecting the Sessions
  • Hemant K Chitale on DBMS_PIPE Connecting the Sessions
  • Phong on ORA-00942: table or view does not exist and sequences as default value

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23c
  • Administration
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • Sessions with details
  • DBMS_PIPE Connecting the Sessions
  • DBCA Templates and Dangerous “General Purpose” Template
  • NON-CDB to PDB Migration Horror
  • 23c is Finally Here!
  • Not Deploying is also Important!
  • Damaged rowid is referenced! Health Monitor
  • Old Obsolete Backup is not Deleted
  • Autoupgrade doesn’t Upgrade Timezone Version While RU Patching
  • Unique Columns For V$Sql

Archives

  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • Sessions with details
  • DBMS_PIPE Connecting the Sessions
  • DBCA Templates and Dangerous “General Purpose” Template
  • NON-CDB to PDB Migration Horror
  • 23c is Finally Here!
  • Not Deploying is also Important!
  • Damaged rowid is referenced! Health Monitor
  • Old Obsolete Backup is not Deleted
  • Autoupgrade doesn’t Upgrade Timezone Version While RU Patching
  • Unique Columns For V$Sql
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset="utf-8"`
©2023 Keep Learning Keep Living | WordPress Theme by SuperbThemes