jump to navigation

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL November 4, 2015

Posted by mwidlake in performance, PL/SQL, SQL.
Tags: , , ,

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

{please see this second post on some limitations of pragma UDF in respect of IN & RETURN data types and parameter defaults}.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.

A problem with this, though, is that every time you swap from SQL to PL/SQL (or the other way around) you have to do a context switch each time, which can be quite cpu and memory intensive. If you are using your own PL/SQL function in the SELECT list of a SQL statement and you are selecting a lot of rows (say as part of a business report) then the overhead can be quite considerable as you could be doing a context switch per row. I won’t go into too much detail here (partly as I go in to considerable detail on the subject in a book I am contributing to for 2016 {and is now out – look to the right!}) on how you can investigate the context switching and when exactly it occurs, but I will show you one of the two new ways in Oracle 12 to reduce the overhead, namely PRAGMA UDF. At present this seems to be a little used and rarely-mentioned feature on the blogsphere, with articles just covering simple examples of almost no-business-function, numeric functions.

I’ll give you a slightly less simple example but my next post will give you details on some limitations of pragma UDF. Here I am just setting the scene. I have the below PERSON table which has the parts of the names in distinct columns, with the contents forced to upper case (as is standard practice). We will create a function to provide a nicely init-capped and spaced display name and a second function which is identical but uses PRAGMA UDF.

Name                                     Null?    Type
---------------------------------------- -------- ---------------
PERS_ID                                  NOT NULL NUMBER(8)
SURNAME                                  NOT NULL VARCHAR2(30)
FIRST_FORENAME                           NOT NULL VARCHAR2(30)
SECOND_FORENAME                                   VARCHAR2(30)
PERS_TITLE                                        VARCHAR2(10)
SEX_IND                                  NOT NULL CHAR(1)
DOB                                               DATE
ADDR_ID                                           NUMBER(8)
STAFF_IND                                         CHAR(1)
LAST_CONTACT_ID                                   NUMBER(8)
PERS_COMMENT                                      VARCHAR2(2000)

create or replace function normal_disp_name (p_sn      in varchar2
                                            ,p_fn1     in varchar2
                                            ,p_fn2     in varchar2
                                            ,p_title   in varchar2  ) return varchar2 is
v_return     varchar2(1000);
  v_return := case when p_title is null then ''
                   else initcap(p_title)||' '
            ||initcap(p_fn1)||' '
            ||case when p_fn2 is null then ''
                   else substr(p_fn2,1,1)||' '
return v_return;
create or replace function udf_disp_name (p_sn      in varchar2
                                         ,p_fn1     in varchar2
                                         ,p_fn2     in varchar2
                                         ,p_title   in varchar2  ) return varchar2 is
-- The Below is the KEY bit
v_return     varchar2(1000);
-- {Identical to normal_disp_name from here}
-- demo of the code
-- select some data with one of the functions, it does not matter which
select pers_title title,    first_forename    ,second_forename    , surname
      ,normal_disp_name(p_sn =>surname           ,p_fn1  =>first_forename
                       ,p_fn2=>second_forename   ,p_title=>pers_title) display_name
from person

TITLE   first_fn   secon_fn   SURNAME         DISPLAY_NAME
------- ---------- ---------- --------------- ----------------------------
MR      HARRISON   RICHARD    HARRIS          Mr Harrison R Harris
MRS     ANNEKA     RACHAEL    HARRIS          Mrs Anneka R Harris
MRS     NICKIE     ALISON     ELWIG           Mrs Nickie A Elwig
MASTER  JAMES      DENZIL     ELWIG           Master James D Elwig
MR      JEFF                  GARCIA          Mr Jeff Garcia
MRS     AMELIA     MARIA      ORPINGTON-SMYTH Mrs Amelia M Orpington-Smyth

So we have our test table, you can see my normal_disp_name function and that the *only* difference with the second version, udf_disp_name, is the inclusion of PRAGMA_UDF in the declaration section. That is partly why it is such a nice feature, you can just add this one line to existing code and you should get the benefit. Should….. {see second post when I do it}

Finally, I show some code using the normal_disp_namefunction and the output.

To demonstrate the impact of context switching I will select 100,000 records from my test table in 3 ways: using only native SQL functions and thus no context switching; using my traditional PL/SQL function which suffers from context switching; with my new “pragma UDF” function to reduce the overhead of the context switching.

select avg(length(
      case when pers_title is null then ''
                   else initcap(pers_title)||' '
            ||initcap(first_forename)||' '
            ||case when second_forename is null then ''
                   else substr(second_forename,1,1)||' '
          )      )  avg_name_length
from person
where pers_id > 100100
and rownum < 100000

select  avg(length(normal_disp_name(p_sn =>surname           ,p_fn1  =>first_forename
                                   ,p_fn2=>second_forename   ,p_title=>pers_title)        ) ) disp_name_len
from person
where pers_id > 100100 and rownum < 100000

select  avg(length(udf_disp_name(p_sn =>surname           ,p_fn1  =>first_forename
                                ,p_fn2=>second_forename   ,p_title=>pers_title)        ) ) disp_name_len
from person
where pers_id > 100100 and rownum < 100000

One thing to mention in passing is that the code using either function is much easier to read and is self-documenting. These are a couple of the benefits of proceduralising your code, as well as creating just one place to maintain it. If I had to re-use that native-SQL section in a half-dozen reports I would probably mess up at least one of the times I cut-and-pasted it and I would now have several places to maintain that code.

I ran the test statements several times and took the average of the 2nd to 6th runs, to remove the initial parsing & caching overhead that comes with the first execution and to get more reliable figures than one further run would give me.

Version                      Run Time average (secs)
Native SQL                   0.03
Traditional PL/SQL           0.33
PRAGMA UDF PL/SQL            0.08

As you can see, just including PRAGMA UDF removed most of the overhead caused by context switching.

How does PRAGMA UDF work? I’m not sure, the official Oracle documentation is pretty light on it and just says:

“The UDF pragma tells the compiler that the PL/SQL unit is a user defined function that is used primarily in SQL statements, which might improve its performance

Note the italics (which are mine). “Might improve its performance” but no detail as to what it does. As I understand it, the ‘C’ calls that are required when processing the data within PL/SQL are reduced and SQL*Plus may be batching the calls – but treat that as a bit of wild speculation for now.

Pragma UDF can slow down slightly functions being called directly from PL/SQL. So use it only for functions you know will be called from SQL.

I’ll make one other observation. Using PL/SQL functions increased the run time to process 100,000 records on my modest test system by all of 0.3 seconds. But that is 10 times the time taken for the native SQL statement. Pragma UDF removes around 80% of this overhead. It’s a nice saving but is probably inconsequential if your code is actually doing any physical IO at all (my example is processing already cached blocks). And if you are only processing a few records or one record in a GUI screen, the context switching is moot {meaning, of no significance}.

But if you have code that processes a huge set of data and uses a lot of user defined PL/SQL functions (and again I go into a lot more detail about this in the book) using pragma UDF in 12C could gain you quite a bit of extra performance. If you have code where even 0.00001 seconds is important (think trading systems) then again there may be a worthwhile benefit.

I should also make it clear that the context switch happens when you use built-in PL/SQL functions. But of course you can’t add the pragma UDF to Oracle’s code. (Well, you might be able to but you should not!)


1. Brian Tkatch - November 4, 2015

Cool stuff. Thank you for showing it off!

2. Pragma UDF – Some Current Limitations | Martin Widlake's Yet Another Oracle Blog - November 11, 2015

[…] my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you […]

3. Jonathan Lewis - November 24, 2015


There’s an interesting item relating to how UDF is implemented at this URL: http://blog.ora-600.pl/2015/10/29/oracle-12c-pragma-udf-the-truth/

That seems to knock the “change the call stack” type of idea on the head – which is what I had been assuming.

It’s possible that the “disabling” effects that you’ve seen may also be related to the size of the fetch array (e.g. when the array is too large – either by rows or by total volume – the optimisation is bypassed)

mwidlake - November 24, 2015

Thank you Jonathan.
Yes, it is an interesting post. It does rather throw doubt on my wild speculation of what pragma UDF is doing. I’m away at the moment but when I am back at home I’ll have a look further into what Kamil is seeing and how it relates to my own tests.

Thanks also to Brendan Tierney who also made me aware of Kamil’s post.

I find it amusing that two people looking into pragma UDF have both called their company ORA600 🙂


4. sqlmdx - December 16, 2015

Please find in below post exaltation in internal improvements introduced by pragma UDF

sqlmdx - December 16, 2015

*explanation regarding

5. Pint with Oracle User Group – First International POUG (Polish) Conference | Martin Widlake's Yet Another Oracle Blog - October 14, 2016

[…] I first came across Kamil Stawiarski just a year or so back. I had blogged about Pragma UDF and how it improves the performance of PL/SQL functions called from SQL – and then came across his blog post on it. About the same time he came across mine and we […]

6. PabloE - March 28, 2017

Reblogged this on desc EMP and commented:
Optimización de cambios de contexto entre SQL y PL/SQL gracias a PRAGMA UDF en Oracle 12c.

7. Writing Efficient Pl/SQL is Hard – KC Database - May 27, 2017

[…] So, how can we make our little function more efficient?  The first thing we can try is PRAGMA UDF. Excellent articles written by Steve Feurerstein here: http://stevenfeuersteinonplsql.blogspot.com/2017/03/speed-up-execution-of-your-functions.html ….and by Martin Widlakes here: https://mwidlake.wordpress.com/2015/11/04/pragma-udf-speeding-up-your-plsql-functions-called-from-sq&#8230; […]

8. Ben - February 16, 2021

Just wanted to leave a comment saying “Thanks so much”. Normally I avoid using PL/SQL in my queries but wherever I can’t help it I’ll remember this 🙂

9. Writing Efficient Pl/SQL is Hard | Official TuningSQL.com Blog - September 26, 2021

[…] So, how can we make our little function more efficient?  The first thing we can try is PRAGMA UDF.Excellent articles written by Steve Feurerstein here: http://stevenfeuersteinonplsql.blogspot.com/2017/03/speed-up-execution-of-your-functions.html&#8230;.and by Martin Widlakes here: https://mwidlake.wordpress.com/2015/11/04/pragma-udf-speeding-up-your-plsql-functions-called-from-sq&#8230; […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: