Pragma UDF – Some Current Limitations November 11, 2015
Posted by mwidlake in performance, PL/SQL, SQL, Testing.Tags: performance, PL/SQL, SQL, system development, Testing
trackback
There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions from SQL.
In 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 call a PL/SQL function from SQL.
In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds went up to 0.33 seconds when the formatting SQL was put in a user defined PL/SQL function. This impact on performance is a shame as it is so beneficial to encapsulate business logic in one single place with PL/SQL. Stating that the PL/SQL function is a user defined one with the pragma UDF option reduced the run time to 0.08 seconds – which is removing most of the context switching overhead. Check out the prior post for full details.
This improvement in performance is great news and is as good, and sometimes better, than using the other new capability of 12c – allowing you to state a function as part of a SQL statement using the WITH clause, if you know about that (I plan to do a further post on that). As a quick example, here is my display name function code expressed within a WITH clause:
with
function l_disp_name(p_sn in varchar2
,p_fn1 in varchar2
,p_fn2 in varchar2 :=null
,p_title in varchar2 :=null )
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 l_disp_name;
select max(l_disp_name(p_sn =>surname ,p_fn1 =>first_forename
,p_fn2=>second_forename ,p_title=>pers_title) ) text_output
,count(*)
from pers
/
The above runs in 0.10 seconds, just slightly slower than 0.08 for my pragma UDF function
However, I need to warn you of some current limitations to pragma UDF. Nearly all the examples on the web so far are
- using very, very simple functions that take in a number and return a number
- Use a stand-alone stored function
And they work fine. However, I had real trouble getting a performance gain when I was working with my function that took in four varchar2 inputs and returned a varchar2 value. No error was given when I marked the function with pragma UDF but there was no performance gain (or loss).
I eventually worked out some limitations to pragma UDF on my version of Oracle – 12.1.0.2.0
- It gives a performance boost when the inputs and return values are NUMBER, VARCHAR2, multiple VARCHAR2 IN parameters
- There is no performance boost when either or both the IN parameter or RETURN value is a DATE
- There is no performance boost if there are any default values for VARCHAR2 IN parameters
- If the function gains a performance benefit from pragma UDF as a standalone stored function, it appears to also gain an advantage if it is a function defined as pragma UDF within a package – so you can still keep all your functions in packages.
You might notice that my example of using the WITH clause states a function that has default values. The WITH option gains the performance advantage of that feature just fine with IN parameter defaults.
The take-home message is that, at present, pragma UDF only seems to help functions with certain types of IN or RETURN values and is nullified by default values – so if you see no performance gain for your functions, this might be why. I need to stress that my tests were not exhaustive, I have not investigated many other combinations.
I’ve discussed the issue with a couple of people within Oracle and the relevant Product Manager is looking to investigate further for me, which is jolly decent of the fellow.
My investigation is of course only by empirical testing, it does not reveal how pragma UDF works. But, as I said in my first post, it seems to aid how information is passed between the PL/SQL and SQL engines as it is variation in those that seem to nullify the benefit of pragma UDF. If you want to duplicate my tests, you can do with the below scripts. I show my test output first, with comments produced with PROMPT commands. I then give you the SQL to create the test table, the functions and package I used and the test script. Feel free to take, expand and let me know of anything different or further you may find. I say nothing of interest after the scripts, so this is in effect the end of the post 🙂
The output of my test, with prompts:
running udf_tests
investigating why pragma udf helps some simple functions and not others
---------------------------------------------------------------------- --
simple number in-number out function
NUMBER_OUTPUT COUNT(*)
------------- ----------
10000000000 100000
Elapsed: 00:00:00.12
NUMBER_OUTPUT COUNT(*)
------------- ----------
10000000000 100000
Elapsed: 00:00:00.03
** udf helps
simple varchar in-varchar out function
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
XYYYY 100000
Elapsed: 00:00:00.12
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
XYYYY 100000
Elapsed: 00:00:00.04
** udf helps
two varchar in-varchar out function, is the issue with more than one in parameter?
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
XYYYYYYYYY 100000
Elapsed: 00:00:00.14
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
XYYYYYYYYY 100000
Elapsed: 00:00:00.04
** udf helps
simple date in-date out function
DATE_OUTPUT COUNT(*)
-------------------- ----------
14-MAY-2010 13:11 100000
Elapsed: 00:00:00.15
DATE_OUTPUT COUNT(*)
-------------------- ----------
21-NOV-2004 13:11 100000
Elapsed: 00:00:00.15
***************************************************************SIMILAR TIME!!!
udf does not help
is date out the issue, date in-num out function
NUMBER_OUTPUT COUNT(*)
------------- ----------
2454431 100000
Elapsed: 00:00:00.17
NUMBER_OUTPUT COUNT(*)
------------- ----------
2454231 100000
Elapsed: 00:00:00.18
***************************************************************SIMILAR TIME!!!
udf does not help
is date in the issue, num in-date out function
DATE_OUTPUT COUNT(*)
-------------------- ----------
07-AUG-2018 18:11 100000
Elapsed: 00:00:00.21
DATE_OUTPUT COUNT(*)
-------------------- ----------
11-NOV-2015 17:57 100000
Elapsed: 00:00:00.21
***************************************************************SIMILAR TIME!!!
udf does not help
so back to my original function I had issues with
a difference with the multiple vcs in func and my orig func is my orig had defaults
thus I will try a version with no defaults
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.19
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.08
****************************************************************UDF has an IMPACT
now with one of the parameters set to a default
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.32
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.32
****************************************************************UDF has NO IMPACT
****************************************************************ALSO defaults cause both versions to be slower
now call the simple disp_name_udf function that benefits standalone from within a package
standalone
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.08
within package
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.08
*********************************************** WORKS - so long as neither spec of body have prm defaults
and just to round of, using a subquery factored function which my prior tests showed reduced overhead
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.10
the WITH function benefits even with defaults
TEXT_OUTPUT COUNT(*)
------------------------------ ----------
Ms Wyyyyyyyyy W Wyyyyyyyyy 100000
Elapsed: 00:00:00.10
Creating the test table
drop table pers;
create table pers
(pers_id number(8) not null
,surname varchar2(30) not null
,first_forename varchar2(30) not null
,second_forename varchar2(30)
,pers_title varchar2(10)
,sex_ind char(1) not null
,dob date
,addr_id number(8)
,pers_comment varchar2(2000)
)
/
insert into pers
select rownum
,rpad(chr(65+mod(rownum,24)),10,chr(65+mod(rownum,25)))
,rpad(chr(65+mod(rownum,24)),10,chr(65+mod(rownum,25)))
,rpad(chr(65+mod(rownum,24)),10,chr(65+mod(rownum,25)))
,decode(mod(rownum,4),0,'MR',1,'MRS',2,'Ms',3,'MR','DR')
,decode(mod(rownum,2),0,'M',1,'F')
,sysdate - (3000+mod(rownum,30000))
,rownum +1001
,rpad(chr(65+mod(rownum,24)),200,chr(65+mod(rownum,25)))
from dual
connect by level < 100001
/
Creating the functions and a small package
--num_num
CREATE OR REPLACE FUNCTION normal_num_num(p_id IN NUMBER) RETURN NUMBER IS
v_num number;
BEGIN
v_num:=p_id*p_id;
RETURN v_num;
END;
/
CREATE OR REPLACE FUNCTION udf_num_num(p_id IN NUMBER) RETURN NUMBER IS
PRAGMA UDF;
v_num number;
BEGIN
v_num:=p_id*p_id;
RETURN v_num;
END;
/
--
-- vc_vc
CREATE OR REPLACE FUNCTION normal_vc_vc(p_id IN varchar2) RETURN varchar2 IS
v_vc varchar2(100);
BEGIN
v_vc:=substr(p_id,1,5);
RETURN v_vc;
END;
/
CREATE OR REPLACE FUNCTION udf_vc_vc(p_id IN varchar2) RETURN varchar2 IS
PRAGMA UDF;
v_vc varchar2(100);
BEGIN
v_vc:=substr(p_id,1,5);
RETURN v_vc;
END;
/
-- vc_vc_2
CREATE OR REPLACE FUNCTION normal_vc_vc_2(p_id1 IN varchar2,p_id2 IN varchar2) RETURN varchar2 IS
v_vc varchar2(100);
BEGIN
v_vc:=substr(p_id1,1,5)||substr(p_id2,2,5);
RETURN v_vc;
END;
/
CREATE OR REPLACE FUNCTION udf_vc_vc_2(p_id1 IN varchar2,p_id2 IN varchar2) RETURN varchar2 IS
PRAGMA UDF;
v_vc varchar2(100);
BEGIN
v_vc:=substr(p_id1,1,5)||substr(p_id2,2,5);
RETURN v_vc;
END;
/
--
-- dt_dt
CREATE OR REPLACE FUNCTION normal_dt_dt(p_id IN date) RETURN date IS
v_dt date;
BEGIN
v_dt:=p_id+1000;
RETURN v_dt;
END;
/
CREATE OR REPLACE FUNCTION udf_dt_dt(p_id IN date) RETURN date IS
PRAGMA UDF;
v_dt date;
BEGIN
v_dt:=p_id-1000;
RETURN v_dt;
END;
/
-- dt_num
CREATE OR REPLACE FUNCTION normal_dt_num(p_id IN date) RETURN number IS
v_num number;
BEGIN
v_num:=to_char(p_id,'J')+100;
RETURN v_num;
END;
/
CREATE OR REPLACE FUNCTION udf_dt_num(p_id IN date) RETURN number IS
PRAGMA UDF;
v_num number;
BEGIN
v_num:=to_char(p_id,'J')-100;
RETURN v_num;
END;
/
-- num_dt
CREATE OR REPLACE FUNCTION normal_num_dt(p_id IN number) RETURN DATE IS
v_dt date;
BEGIN
v_dt:=sysdate+(p_id/100);
RETURN v_dt;
END;
/
CREATE OR REPLACE FUNCTION udf_num_dt(p_id IN number) RETURN DATE IS
PRAGMA UDF;
v_dt date;
BEGIN
v_dt:=sysdate-(p_id/100);
RETURN v_dt;
END;
/
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
PRAGMA UDF;
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 normal_disp_name_defaults (p_sn in varchar2
,p_fn1 in varchar2
,p_fn2 in varchar2
,p_title in varchar2 :=null ) 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_defaults (p_sn in varchar2
,p_fn1 in varchar2
,p_fn2 in varchar2
,p_title in varchar2 :=null ) return varchar2 is
PRAGMA UDF;
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 package t_pkg as
function udf_disp_name (p_sn in varchar2
,p_fn1 in varchar2
,p_fn2 in varchar2
,p_title in varchar2 ) return varchar2;
end t_pkg;
/
create or replace package body t_pkg as
function udf_disp_name (p_sn in varchar2
,p_fn1 in varchar2
,p_fn2 in varchar2
,p_title in varchar2 ) return varchar2 is
PRAGMA UDF;
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;
end t_pkg;
/
The test script
-- udf_tests
set lines 100 pages 50
set feed off
col text_output form a30
col number_output form 99999999999
col date_output form a20
spool udf_tests.lst
prompt running udf_tests
prompt
set pause off
set autotrace off
set timi on
prompt investigating why pragma udf helps some simple functions and not others
prompt -----------------------------------------------------------------------
--
prompt
--
prompt simple number in-number out function
select /* mdw_16a */ max(normal_num_num(pers_id)) number_output
,count(*) from pers
/
select /* mdw_16b */ max(udf_num_num(pers_id)) number_output
,count(*) from pers
/
prompt
prompt ** udf helps
prompt
--
prompt simple varchar in-varchar out function
select /* mdw_16c */ max(normal_vc_vc(surname)) text_output
,count(*) from pers
/
select /* mdw_16d */ max(udf_vc_vc(surname)) text_output
,count(*) from pers
/
prompt
prompt ** udf helps
prompt
--
--
prompt two varchar in-varchar out function, is the issue with more than one in parameter?
select /* mdw_16e */ max(normal_vc_vc_2(surname,first_forename)) text_output
,count(*) from pers
/
select /* mdw_16f */ max(udf_vc_vc_2(surname,first_forename)) text_output
,count(*) from pers
/
prompt
prompt ** udf helps
prompt
--
prompt simple date in-date out function
select /* mdw_16g */ max(normal_dt_dt(DOB)) date_output
,count(*) from pers
/
select /* mdw_16h */ max(udf_dt_dt(DOB)) date_output
,count(*) from pers
/
prompt ***************************************************************SIMILAR TIME!!!
prompt udf does not help
prompt
--
prompt is date out the issue, date in-num out function
select /* mdw_16i */ max(normal_dt_num(DOB)) number_output
,count(*) from pers
/
select /* mdw_16j */ max(udf_dt_num(DOB)) number_output
,count(*) from pers
/
prompt ***************************************************************SIMILAR TIME!!!
prompt udf does not help
--
prompt is date in the issue, num in-date out function
select /* mdw_16k */ max(normal_num_dt(pers_id)) date_output
,count(*) from pers
/
select /* mdw_16l */ max(udf_num_dt(pers_id)) date_output
,count(*) from pers
/
prompt ***************************************************************SIMILAR TIME!!!
prompt udf does not help
--
--
prompt
prompt so back to my original function I had issues with
prompt a difference with the multiple vcs in func and my orig func is my orig had defaults
prompt thus I will try a version with no defaults
prompt
select /* mdw_16m */
max(normal_disp_name(surname,first_forename,second_forename,pers_title)) text_output
,count(*) from pers
/
select /* mdw_16n */
max(udf_disp_name(surname,first_forename,second_forename,pers_title)) text_output
,count(*) from pers
/
prompt ****************************************************************UDF has an IMPACT
prompt
prompt
prompt now with one of the parameters set to a default
select /* mdw_16o */
max(normal_disp_name_defaults(surname,first_forename,second_forename,pers_title)) text_output
,count(*) from pers
/
select /* mdw_16p */
max(udf_disp_name_defaults(surname,first_forename,second_forename,pers_title)) text_output
,count(*) from pers
/
prompt ****************************************************************UDF has NO IMPACT
prompt ****************************************************************ALSO defaults cause both versions to be slower
prompt
prompt now call the simple disp_name_udf function that benefits standalone from within a package
prompt
prompt standalone
select /* mdw_16q */
max(udf_disp_name(surname,first_forename,second_forename,pers_title)) text_output
,count(*) from pers
/
prompt
prompt within package
select /* mdw_16r */
max(t_pkg.udf_disp_name(surname,first_forename,second_forename,pers_title)) text_output
,count(*) from pers
/
prompt *********************************************** WORKS - so long as neither spec of body have prm defaults
prompt
prompt and just to round of, using a subquery factored function which my prior tests showed reduced overhead
with
function l_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 l_disp_name;
select /*mdw_16s */
max(l_disp_name(p_sn =>surname ,p_fn1 =>first_forename
,p_fn2=>second_forename ,p_title=>pers_title) ) text_output
,count(*)
from pers
/
prompt the WITH function benefits even with defaults
with
function l_disp_name(p_sn in varchar2
,p_fn1 in varchar2
,p_fn2 in varchar2 :=null
,p_title in varchar2 :=null )
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 l_disp_name;
select /*mdw_16t */
max(l_disp_name(p_sn =>surname ,p_fn1 =>first_forename
,p_fn2=>second_forename ,p_title=>pers_title) ) text_output
,count(*)
from pers
/
--
spool off
Martin Widlake's Yet Another Oracle Blog 
The lack of performance gain when the function has defaults for the varchar2 parameters is especially disappointing as almost all of our functions have this scenario.
Hi Todd,
I know, it is a bit of a blow as it is such a nice feature. I do encourage you to check your implementation though (if you have a 12c system already) as although I have had a couple of other people confirm my results, it surprised the product manager. If it turns out that there is a real bug here, I would be hopeful that it will be addressed in reasonable time (I make absolutely no promises though).
We aren’t on 12c yet but this feature looked to be significant for us due to the number of PL/SQL functions used in our SQL queries. Thanks for such a detailed look and excellent analysis of the Pragma UDF feature.
[…] see this second post on some limitations of pragma UDF in respect of IN & RETURN data types and parameter […]
[…] SQL (novedad en 12c). Por cierto, y hablando sobre esta opción, hace unos días se ha publicado un artículo interesante sobre algunas de sus limitaciones […]
[…] You should also read a great blog post by Martin Widlake: Pragma UDF – Some Current Limitations https://mwidlake.wordpress.com/2015/11/11/pragma-udf-some-current-limitations/ […]
[…] I wrote a little bit more about it here: http://blog.ora-600.pl/2015/10/29/oracle-12c-pragma-udf-the-truth/ And Martin Widlake wrote a lot about it here: https://mwidlake.wordpress.com/2015/11/11/pragma-udf-some-current-limitations/ […]
Thanks for the article. It’s helping us wade through the limitations and benefits of pragma udf.
I went to copy/paste the script to load the pers table with sample data, and had to edit out the errant “@cre” characters to the right of line 4. Also noticed the misspelling “pragam” you might want to fix (though it’s just in a program comment).
I’m glad it helped – and thanks for the corrections, I’ve applied them to the article.
I know that pragma udf has similar limitations in 12.2 as it did in 12.1 – that depending on the input and output data types and the use of default values you may not see a benefit from pragma udf. Bryn Llewellyn (product manager for PL/SQL) has done far more extensive testing than myself and has shared with me some of the results. Without going into too much detail, pragma udf will not negatively impact performance but may give no benefit. I’m afraid it is a case of testing it yourself. Pragma udf is a work-in-progress and, as versions come out, the performance improvement it offers should increase in scope to more/all datatypes used.
I am testing in both 12.1.0.2.0 and 12.2.0.1.0. I compiled an ran your benchmark exactly as posted here. The test for “simple number in-number out function” reproduces the expected performance gains.
But with both systems, I cannot reproduce any performance gains with pragma udf and the “simple varchar in-varchar out function” test. Now I’m wondering what could be different on my 12.1.0.2.0 system compared to yours. I’ve tried all levels of PLSQL_OPTIMIZE_LEVEL (though I doubt that would be the issue, since the numeric parameter test does show improvement…).
I’m wondering if there is some other session or system setting that differs causing my issue. (The 12.2 instance I’ve tested is running right out of the box, in a VirtualBox VM downloaded directly from Oracle, with no init.ora tweaks of any kind.)
I’m baffled. I was really anxious to refactor string manipulations that end up necessarily cut-and-pasted in our code for speed.
I had heard from others that with V12.2, the case of using pragma UDF with VARCHAR2 in or out parameters no longer saw a performance gain – but not that it disappeared with V12.1. I cannot currently test as my V12.1 instance stopped working with a recent windows update. *sigh*. However, my install was done very soon after 12.1 was released and it had not been patched in any way so you might have a slightly more modern version than mine was.
With 12.2 expect a benefit with numeric, binary float and binary double parameters (where no defaults are used). If you are passing in many variables of different data types I have no information on what performance gain you will get from pragma UDF, you will just have to test empirically I am afraid.
This is what you get when the documentation says “this feature *might* help” but does not specify even any hints,let alone rules, for when it will. We have to test our specific use cases.
You may want to swap to using the WITH clause as the performance gains from using that are far more reliable, though you lose the simple compartmentalisation of your code.
The with clause does work, but as you say it doesn’t help with modularity. I anxiously await any improvements yet to come.
I would *love* if a similar thing could be done for the performance of aggregate/analytic functions written using the PL/SQL ODCI interface. The context-switching cost of those makes them almost unusable.
Thanks for your help shedding light on this. I’ve reached out to you on linkedin, btw.