A Different Type of Keynote & Jonathan Lewis Panel Session at UKOUG Tech15 November 27, 2015
Posted by mwidlake in Presenting, UKOUG, User Groups.Tags: knowledge, Presenting, UKOUG, user group
2 comments
Technical people tend not to enjoy Keynotes at conferences. We are allergic to content-light “there has never been a better time to invest in our products” fluffy, frou-frou, big picture talks. We want how-it-works meat on the bones of what is served up to us.
OK, it’s a very poor photo but the best I have of Dom presenting. Sorry Dom.
Well, at the UKOUG Tech15 conference this year (Birmingham ICC 7th-9th December) we have a treat for you – The Database stream keynote is technical AND you get to ask whatever questions you want – questions about Oracle RDBMS technology that is.
Dominic Giles, Maria Colgan and Penny Avril have agreed to be up on stage and, after the first half telling us about some of the things introduced at Oracle OpenWorld 2015, they will take questions. Questions they do not know are coming. Real questions. From people at the conference. Your questions. These are not questions that have been placed by them or checked with them before hand.
Dom does this at smaller user groups; he stands up and asks for any questions from the audience and he just tells it the way he sees it. His incredible knowledge of the product is matched only by his humour (so no huge expectation for you to live up to there, Dom!). Maria and Penny are similarly endowed with knowledge and great presentation skills and are willing to give this a go for us. Brave people.
{I think in the photo Dominic has just been asked about why something in Oracle does not work – and he’s trying to decide whether or not to kill the person who asked…}
You will be able to ask questions on the day, at the session, but you will also be able to post questions at the UKOUG information desk on Monday and I am happy for you to send me any questions you have (mwidlake@btinternet.com or leave a comment on this blog – I don’t think Maria, Dom or Penny drop by here very often so they won’t see them…:-) ). Of course, there is no point asking a question if you do not intend to be at the conference and at that session!
Having run similar sessions to this at smaller events, I know that you need some questions to get the ball rolling and then, with a little luck, the audience warms up and asks questions. The key thing is, no matter the source, the panel do not know the questions before-hand. I’ve seen sessions like that, with placed questions, and it just comes over as fake.
Why did I mention Jonathan Lewis? Well, on Monday at 11:20 he is doing another panel session taking questions, with Nigel Bayliss, Christian Antognini and Maria Colgan (again – we work them hard). This session is focused on the Cost Based Optimizer. We already have enough initial questions but if you are curious about the optimizer and performance, maybe ask your own question from the floor, it’s a must-see session. Jonathan talks about this session in this blog post.
So at UKOUG Tech15 you have two panel sessions in the database stream where you can ask questions. We also have several “Roundtable” sessions across the whole agenda which are perfect for asking questions too. If you have never been to one, a Roundtable session is more a discussion in a smaller group, with one or two experts “officially” there as well as usually some unofficial experts in the crowd. Panel session are “pose your question, get expert answers”, roundtables are more interactive, more like a conversation in the bar. They can get quite lively (but fights are rare) :-).
All in all, we are aiming for a good dose of interaction between presenters and delegates. And never forget, most of us presenters are more than happy to chat and answer questions throughout the conference. Just don’t ask hard questions if you meet us in the evenings, when we are half-drunk…
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
12 comments
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
Friday Philosophy – We Could Be Heroes! {just for one day}. November 6, 2015
Posted by mwidlake in Friday Philosophy, humour, Perceptions.Tags: behaviour, Humour, perception, Presenting
3 comments
At Open World I overheard a snippet of conversation which went something like this:
Bob – “How’s it going? Did the last talk go down well?”
Bill – “Sure, it was on time, the audience seemed to like it.”
Bob – “Will you be here next year?”
Bill – “Errmm….” pause…*sigh*… “I don’t know…. I’ll see how I feel.”
Bob – “Oh? In what way?”
Bill – “It just that, at Open World… I have to fend off two dozen people just to go have a pee!”
Wild horses could not drag out of me the name of the person who said that (though several pints and the offer of a curry might do the trick – try me). It both made me smile and made me think. There are down-sides to becoming highly respected in your sphere.
There are definitely different levels of renown and respect in the relatively small world of the Oracle Database Technologist. I’m not doing bad in that respect; I’d put myself in the third of the seven circles, maybe tapping on gates of circle two. Occasionally I think it would be nice to be either technically or entertainingly good enough to join the Big Names in the innermost circle – but I really don’t think I can face the Hem-Touching!
What do I mean about “Hem-Touching”? It’s something a few friends and I came up with at the UKOUG Tech conference about 5 or 6 years ago to describe people who will approach one of the Oracle Names with a mixture of awe and fear in their eyes and just want them to acknowledge their presence,be allowed to speak, maybe to touch the hem of their cloak. If you go up to the balcony that is above the exhibition hall at the Birmingham ICC, you can sometimes watch an Oracle Name walk through the exhibition and see some people suddenly swerve and hurry towards them – especially if the Name currently has no one with them. I’ve even seen someone suddenly stop when another acolyte gets to their hero first. I don’t know why, these people will speak to more than one person at a time. And the thing is, people in the UK and Europe are generally more reserved than our cousins in other continents, so we are less forward in, well, being forward.
Am I being mean to these people? Well, a little I guess, but it’s mainly because of the little story I started with. I’m friends with some of the Names and I know a lot of them are uncomfortable with Hero Worship. Being respected and held in high regard is great, most of them are very happy about that, as they have worked damned hard and long to be knowledgeable enough to hold that position. But when people treat them like a living saint or the bestower of blessings, it’s just a bit weird. This is just an area of technology after all, not the eradication of Malaria. They are “just” people – OK, they are people who are usually very smart and very capable, but they are also people who are happy to share and teach – otherwise they would not be at the conferences sharing and teaching. Most of them are idiots in other areas of their lives too, we all are.
I’ve never felt the need to hero-worship myself. Not because I do not deeply respect people who achieve great things, it’s just not in my psychology I think. I did not put up any posters in my bedroom of the people I most respected when I was a teenager. I used to know a Nobel Prize Winner (though I doubt he’d recognise me in the street now) but when I met him the first time I had no idea who he was and just treated him like a person – and we got on fine. He treated me like a person too. I’ve been lucky enough to meet some very smart academics, many of the Oracle Names and even the odd traditionally famous person. It’s amazing how like people they are – if you treat them like people.
I’m certainly not above being pleased when someone I respect mentions me or refers to something I have done though. I’ll grin like an idiot on the rare occasions someone has name-checked me in a presentation or they tell me they liked something I said. I’m tickled pink when a Name follows me on twitter. But I feel hero worship is not what they want. Respect yes, being told you appreciate what they have taught you fine. Going shiny-eyed and asking to touch the hem of their coat, weird; don’t do it.
Oracle Names are people, treat them as such. They’ll probably appreciate you more if you do.
And if you ever find yourself in a group of several others, all trying to say “hello” to some gal or guy you just saw presenting, and they are looking a little uncomfortable and shifting from foot to foot and looking towards a door over there – let the poor sod go to the loo will you?
Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL November 4, 2015
Posted by mwidlake in performance, PL/SQL, SQL.Tags: performance, PL/SQL, SQL, Testing
11 comments
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!)
Martin Widlake's Yet Another Oracle Blog 