Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

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.

PERSON
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);
begin
  v_return := case when p_title is null then ''
                   else initcap(p_title)||' '
              end
            ||initcap(p_fn1)||' '
            ||case when p_fn2 is null then ''
                   else substr(p_fn2,1,1)||' '
              end
            ||initcap(p_sn);
return v_return;
end;
/
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
PRAGMA UDF;
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)||' '
              end
            ||initcap(first_forename)||' '
            ||case when second_forename is null then ''
                   else substr(second_forename,1,1)||' '
              end
            ||initcap(surname)
          )      )  avg_name_length
       ,count(*)
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
       ,avg(addr_id)
       ,count(*)
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
       ,avg(addr_id)
       ,count(*)
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!)