jump to navigation

Why oh Why Do We Still Not Have a Fast Bulk “SQL*Unloader” Facility? December 1, 2016

Posted by mwidlake in Architecture, database design, performance.
Tags: , , ,
5 comments

Way back in 2004 I was working at the UK side of the Human Genome project. We were creating a massive store of DNA sequences in an Oracle database (this was one of two world-wide available stores of this information, for free & open use by anyone {* see note!}). The database was, for back then, enormous at 5-6TB. And we knew it would approx double every 12 months (and it did, it was 28TB when I had to migrate it to Oracle 10 in 2006, over 40TB 6 months later and grew to half a petabyte before it was moved to another organisation). And were contemplating storing similar massive volumes in Oracle – Protein, RNA and other sequence stores, huge numbers of cytological images (sorry, microscope slides).

I did my little bit to work out how we all tick

I did my little bit to work out how we all tick

Significant chunks of this data were sometimes processed in specialist ways using Massively Parallel Processing farms (hundreds or thousands of compute nodes so we could do “2 years of compute” in a day or even a few hours). Oracle was never going to be able to support this within the database.

But Oracle had no fast, bulk data unloading offering, a “SQL*Unloader”. Other RDBMSs did. And that gave me an issue. I had to argue as to why hold this data in Oracle where it was hard to yank out chunks of it rather than keep it in say a simple file store or MySQL?

That year I was invited to Oracle Open World and I was given a slot with a senior VP in the RDBMS/OCFS area (as that is what were using – I know! But we were). I had been warned by someone that he could be a little “robust”. In fact my friend called him “Larry’s Rottweiler”. However my chat with the SVP went fine. Until he asked me for the one thing I would really want and I said “well, it’s been promised for the last 2 or 3 versions of Oracle, but it still does not exist in V10 – I’d like a SQL*Unloader so we can rapidly extract bulk data from Oracle into a structured file format”. He went very cold. I continued: “We need to do really intensive processing of the data with ‘C’ and if it is locked into the database we can’t do that. It’s hard for me to justify using Oracle as our primary data store if the data is now stuck there…”

I honestly thought he would bite!

I honestly thought he would bite!

He went ballistic. He was furious, he was raising his voice, leaning forward, I honestly thought he might hit me. I can’t remember the whole rant but one line stuck: “We will NEVER help you get your data out of Oracle!”. I ended the discussion as soon as I could – which obviously suited him fine also.

And here we are over 10 years later and no SQL*Unloader has appeared. And, as far as I can see, there is no longer even a suggestion that “one day” one will exist. You can obviously get data out of Oracle using SQL*Plus, SQLcl or PL/SQL’s UTL_FILE. But you have to write your own code and to make it parallel or fast is not that simple. And there are some commercial tools out there to do it. But fundamentally we do not have a simple, robust & fast data unloader tool within the Oracle toolset.

But I remain mystified as to why Oracle Corp will not provide a tool to do this. I can see the argument that doing so would help you migrate your Oracle Data set to a different platform and thus move away from Oracle, but I think that is a dumb argument. If you are willing to dump your Oracle environment for something else you are already not happy – and making life difficult is only going to make you less happy and almost certain to never come back! It’s incredibly inconvenient to extract all your data at present but compared to the cost to your organisation of changing a central part of your infrastructure, it’s just a bloody annoyance. It’s like domestic service providers (telephone, internet, TV) that make it easy for you to sign up but a nightmare to leave. Thus guaranteeing that once you leave (cos you will) you will certainly NOT be going back to them in a hurry!

So for the sake of what I see as a misplaced protectionist stance they close the door to rapidly extracting data from Oracle databases for processing in other ways. I’ve come across other situations like this, before and since, but the Human Genome issue threw it into sharp relief for me. The end result I’ve seen a few times (and effectively what we had at the Sanger) is the data gets stored at least twice – once in Oracle and then somewhere else where it is easy to access. Now that’s a daft and bad place to be, multiple copies and one of them at least lacking RI. Thanks for forcing that one on us Oracle.

Something that is changing is Cloud. Oracle wants us all to move our data and systems into the sky. Their whole marketing message at the moment is nothing but cloud-cloud-cloud and they give the impression that cloud is in fact everything, every solution. So how do you get 10TB of data into the cloud? It’s not so hard really. You can trickle it in over time, after all networks are constantly getting faster, but for bulk data this is a poor solution. Or you can ship your data physically. You can’t beat the bandwidth of a transit van. I’ve heard Oracle people at OOW and other conferences saying this is easy-peasy to arrange (as it should be, I’ve done it for clients doing migrations a couple of times).

But how are you going to get it out again? How well do you think that a company that has never provided SQL*Unloader is going to help you get bulk data back out of your Oracle cloud-based databases into on-premises systems? And how well are they going to help you shift it from one of their systems to another cloud system? One they have not sold you? Sometimes people talk about business relationships being like a marriage. Well once you gave gone cloud, you might have ensured any divorce is going to be really messy!

Update – see the comment by Noons on the fact that VLDBs and Data Warehouses are often not as static as you think – so there can be a constant need to move a lot data into and out of such very large data stores. Cloud is not currently going to support this well. And if you have a data life-cycle management policy, say archiving off data 3+ years old. Where are you archiving it off to and how will you do this once it is in the web?

* Please note, this was not human DNA sequence information gathered for medical reasons! It was mostly sequence information for other organisms (chimps, flies, potatoes, rice, viruses, bacteria all sorts) generated by the scientific and, in some cases, biopharma community. There was human DNA sequence in there but by consent and anonymised – you can’t have a human genome sequence without involving some humans! We did not hold your Dodgy Uncle’s DNA test that was taken for that court case…

Friday Philosophy – Your Experience can Keep You Ignorant November 18, 2016

Posted by mwidlake in Friday Philosophy, Knowledge, Perceptions, performance, SQL.
Tags: , , , ,
12 comments

This week I was in an excellent presentation by Kerry Osborne about Outlines, SQL profiles, SQL patches and SQL Baselines. I’ve used three of those features in anger but when I looked at SQL Patches I just could not understand why you would use them – they looked to me like a very limited version of SQL Profiles.

There is a prize for spotting Kerry without a baseball cap

There is a prize for spotting Kerry without a baseball cap

So I asked Kerry about it during his presentation (he had been encouraging us to ask questions and I was curious). The answer? They are almost identical in how they are used and the impact they have but, yes, they are more limited. You can only enter one line of hints (I think it can be more than one hint but I have not checked {update, see comment by Jonathan Lewis – it is 500 characters of hints you can add, so you can comprehensively hint most statements}) with a SQL Patch. But, and this is the crucial bit, they can be used without the tuning pack and can be used on Standard Edition. Which makes them very useful if you are limited to using a version of SE or have not paid for the extra cost tuning pack option on Enterprise Edition. Kerry told me the first part about no cost and Kamil Stawiarski the part about being available on SE.

That’s a really useful thing to know. Why did I not know it? Because nearly all my experience of performance work has been for clients who have Oracle Enterprise Edition and the tuning pack. Most companies who are willing to hire someone to do Oracle Performance work have paid for Oracle Enterprise Edition and usually for many of the options. A company who is saving money by having SE is far less likely to have the money to hire external consultants (more the pity, as I would really like to spend time working for smaller companies where you can usually get more done!)

My experience, or rather lack of it, had blinded me to the possible uses of an Oracle performance feature. I wonder how much other stuff I don’t know or appreciate about an area I claim to be knowledgeable and skilled in – because my experience is mostly for clients with a very full Oracle tool set? How true is this in all sorts of areas of my technical and personal life? Quite a lot I suspect. That can be a little dis-spiriting.

But mulling this over later that evening (with beer of course) four things occurred to me:

  • If someone claims skills in an area but does not know things you do, it could well be simply down to their personal experience to date. It does not mean you know more, it means you know different stuff.
  • How much does someone have to not know before you decide they are not the expert they claim?
  • Do we partial experts keep ourselves slightly ignorant by not asking questions – as we fear that second point?
  • I felt I did the right thing in asking a question about something I felt I should know – but did not. As now I know the answer (and two people at least got to show they know more than me).

I know I have said this before, as many others have. The more you know, the more you realise what you do not know. You just have to keep asking and remember that, we are all ignorant about something until someone tells us about it.

OTN Appreciation Day – Instrument Your Damned Code. Please! October 11, 2016

Posted by mwidlake in ACED, performance, PL/SQL.
Tags: , ,
1 comment so far

Today is OTN Appreciation Day.

This day is the idea of Tim Hall, Mr OracleBase, and you can See his post here. The idea is that as a sign of appreciation to OTN we do a technical (or not so technical) post on a feature of Oracle we like. I’m going to visit an area I have mentioned before…

DBMS_APPLICATION_INFO.

In my opinion, one of the key differences between good code and poor code is that it is instrumented in someway. I certainly believe that an application that is instrumented is much easier to live with and, over the lifetime of that application, runs much more smoothly and significantly more efficiently as (a) problems can be resoled easier and (b) any change in code performance is easy (and thus more likely) to be identified. It is because of this second point that I feel the overhead of instrumentation is not 10% or 20% but rather -15% – it improves overall performance.

The easiest way to instrument PL/SQL code is to use DBMS_APPLICATION_INFO. You use simple calls to set the values for the columns MODULE and ACTION in V$SESSSION and you can also populate a third column CLIENT_INFO.

ora122> desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
 SADDR                                              RAW(8)
 SID                                                NUMBER
 USERNAME                                           VARCHAR2(30)
...
 MODULE                                             VARCHAR2(64)
 MODULE_HASH                                        NUMBER
 ACTION                                             VARCHAR2(64)
 ACTION_HASH                                        NUMBER
 CLIENT_INFO                                        VARCHAR2(64)

It is incredibly easy to use the DBMS_APPLICATION_INFO built-in package to set these columns via the procedures set_module(module_name,action_name), set_action(action_name), and set_client_info(client_info). The values set are held in memory, and there is no inserting or updating of any table rows – so it is incredibly quick and light-weight. V$SESSION is a view on top of memory structures.

Part of the reason that DBMS_APPLICATION_INFO is so powerful is that nearly all performance and development tools that are used for Oracle will show you this information. Of course this is especially true of Oracle tools such as OEM/Grid Control and SQL*Developer. Thus when you are looking at what code is taking up time or running slowly, you can use MODULE and ACTION to identify the exact code and area where the issue is – you are not having to guess. One of the reasons that performance tuning and problem resolution can be so hard is that you are guessing as to the source of the problem. Instrumentation means you know, you are not guessing. DBMS_APPLICATION_INFO is simple and thus, almost as a direct result of this, efficient at identifying where the problems are without blinding you with too much information.

The below is from OEM Express, showing which modules and actions are taking up the most time.

OEM Express showing what modules take up time

OEM Express showing what modules take up time

I go into more details about DBMS_APPLICATION_INFO at this post about it and I go into a lot of detail about this and other types of PL/SQL instrumentation in the book I contributed to – click on the link on the right of this web page for details on that🙂

The OTN Program
Like many people I am doing a technical blog today in order to publicly thank the Oracle Technology Network for what they do. Why do I want to do this? Well, because they have helped me for years and they support me helping others.

As I write this blog I am rudely ignoring Ludovico Caldara explaining 12c dynamic optimizer features to a carefully listening crowd in Denmark, as part of the OTN Nordic tour. They help fund some of us on the ACE program to go on these tours and spread knowledge with people. I would struggle to do all the talks I do if, as an ACE Director, the OTN ACE program did not help cover some of my expenses. They do not pay everything but, heck, I can’t go complaining about them *only* paying for some of it!

OTN provide huge amounts of information on their web site(s), the place you go to download the software and documentation for free.

The final thing for me personally is that they help me contact people in Oracle who can help me better understand parts of the tech or even report back issues and help get them resolved.

Thank You OTN

OUG_Ireland – Bread, Beer & Playing with the Big Boys March 7, 2016

Posted by mwidlake in conference, performance, Presenting, User Groups.
Tags: , ,
2 comments
The last banner turned out to be wrong

The last banner turned out to be wrong

ougire16-hp-gen-v1

Learning, sharing & a small dose of fear – Last week was the OUG Ireland conference 2016. It is the second time I’ve been to the Irish Oracle user group conference and this year it had grown from one day to two days – and that was a great success. The expanded 2-day event, as initially sized, booked up with several weeks still to go. More places were made available – and they all filled as well. You could tell this, at least in the sessions I attended – they were all busy and many were full. The whole event had a busy feel.

I was doing two quite different session. The first was my overview of the core Oracle technology, a broad-brush, broad-appeal session. At the other end of the spectrum, the second was about the details of context switching between SQL & PL/SQL, the impact it can have and the improvements in 12C to help reduce that impact. People are becoming more aware of this aspect of calling PL/SQL from SQL and also the potential to lose your point-in-time view of the database, which many take for granted. I actually dropped the loss of point-in-time view out of the talk as it made the session too long – but someone asked directly about it! So I spent the last 5 mins I’d left for Q&A talking about it. If I repeat the session somewhere, perhaps I’ll add those slides back in and just talk less rubbish about me earlier on.

Oren Nakdimon actually mentioned one of the 12C improvements (PL/SQL in the WITH clause) in his excellent talk on the first day “Write less (code) with More (Oracle 12c new features)” including one aspect I had missed. So I stole his material and added a slide into my deck for Friday. I’m glad I referenced him on that slide – as he was sat in the front row in my talk🙂

(as an aside, this is my second time at OUG Ireland and the second time I’ve met someone from Israel who I shared beers with & spent quite a bit of time with – just one of those quirky coincidences in life. Oh, and I met his wife Inbal too and we had a chat about translating Sci-Fi from English to Hebrew! It’s not easy, but Klingon is easier to handle than you’d think).

See, no beer! (it's inside the podium...)

See, no beer! (it’s inside the podium…)

My other talk was on the first day and it was the one where I cover the core Oracle technology in a single presentation slot. It’s a bit of a Big Ask in 45 minutes but I tend to throw in less anecdotes or stories than normal in order to cover the material. However, this time I could not resist the temptation to include one, in order to continue the unfair myth that I can only present with a beer in hand…

I got a lot of great feedback about this talk, several people checked that they would be able to get the slides afterwards (they have been sent to the organisers and they should be put out in due course – but I can send anyone a copy), I got a few more questions during the conference, I spent about 30 minutes talking to one chap afterwards (about a very “interesting” project he is on where sense does not seem to be prevailing) and a lot of people said they enjoyed it. For a speaker, there is no better result and I was quietly happy. I might go as far as to say I was jolly chuffed.

I managed to get to a fair few presentations by others at this conference too and I did not see a bad one in the DB or Development stream. Jonathan Lewis and Joze Senegacnik were as excellent as ever (I think Jonathan was particularly on form) and I was reminded at how good Carl Dudley is at this presenting lark. I missed out seeing my Polish ORA600 counterpart, Kamil Stawiaski, due to a clash for one session and presenting at the same time as him on the other. But I got to have beer with him and his friend Adam Bolinski and a good few chats.

On the topic of Beer, as well as being forced to have one in my presentation on the first day (cough!), we had an excellent “presenters beers” on Wednesday evening before the event, organised by Brendan Tierney. Brendan had us meet up, on a foul and wet evening, at a pub with it’s own micro-brewery (so I was able to avoid Guinness which, I have to confess, outside of the Guinness Museum is Not My Thing) and my nephew Tim Hall’s (oracle) wife, Debra Lilley {they are not really married, but sometimes you wonder at the bickering…}, organised an excellent Oracle Ace meal on the Thursday night. I had to drop out early though as sleep deprivation was killing me (and only got worse for the final day – that was one of the noisiest hotels I have ever stayed in and I won’t be rushing back) but I did get to share the taxi back to the “Hotel Of Random Noise” with Debra (she was flying somewhere at early-O’clock the next day) and catch up.

I actually see the social side of conferences as just as important as the technical side. I have made some good friends with people at these technical meetings and the more you know people in the user community, the more you get out of it.

Well, Officer, It's yeast... Honest... Honest!!!! Argh!

Well, Officer, It’s yeast… Honest… Honest!!!! Argh!

So I’ve mentioned the beer. What about the bread? Well, I make sourdough. I have a pot of “starter mixture” in my fridge, which is a live culture of yeast in a slack dough mix. It’s alive (sorry, “It’s ALIIIIIIIVEEE!”), it needs looking after. It is, basically, one of the most boring pets you can have – but at least it never needs to be taken to the vet or does its business on the carpet. There are a few of us bakers in the Speaker Circuit. Jonathan is another one and so is Joze. And Joze also has a yeast colony pet in the fridge. We talked about our sour-dough pets at a conference last year and, as a result, Joze bought me a little bag of powder – dried mixture, stuffed with yeast. I’m going to start up a new jar of dough mixture, sourced from this powder. I’ll do a post about what happens.

There is one aspect of being given a bag of dried dough when on conference. You have to take it home. In your luggage. In my case, hand luggage. And in November, for the first time ever that I can remember, they decided to check my hand luggage. What if they did that this time?
“Is this your bag, Sir?”
“Yes”
“Is this plastic bag of a pale, dried mixture yours, Sir?”
“Yes Officer, but it’s OK it’s yeast!” – Bright Smile.
“Yes, of course it is Sir. Come this way.”
“But it’s just yeast and flour, it’s nothing dodggy!!!”
“Of course it is not Sir, and now my colleague here will now examine places you don’t want examining for more such samples”.

It added an extra level of excitement into what is usually a dull experience of international air travel.

The "rose" between two "thorns". If Thorns means The Best

The “rose” between two “thorns”. If Thorns means “The Best”

So that was the dose of fear? No. I did two talks at the conference but I had one other duty. At the UKOUG Tech conference last December, Jonathan Lewis hosted a Q&A session on the Cost Based Optimizer. We had Jonathan, Chris Antognini, Maria Colgan and Nigel Bayliss, with myself and Neil Chandler as Masters of Ceremony. ie, Question Wranglers. It worked really well, with some genuine-but-known questions to start until the audience warmed up, and they warmed up.

Jonathan decided to repeat this format in Ireland and he asked me to reprise my role, which I was delighted to do. I’m more than happy to fire in the questions to the experts, so long as I’m here at the side and they are there up on the stage.

One of our experts for Ireland had to drop out due to only being there on the Thursday. Jonathan asked me who can fill in. No problem, we have skills across the agenda, “Dave” can help. Dave said yes…but then no, it turned out Dave could not do it. So I was asked again who could fill and we got Jessica – but Jessica had to leave early. You can see what is coming, can’t you? We were out of replacements. A panel of 2 does not work really, you need three or more. So, I was promoted to Panel. Oh cr4p!

Alan Kelly stepped into the breach to be Question Master (Personally, I don’t know whether to thank Alan or not! But for the sake of the session, thank you Alan) and I joined two masters of the art on stage. Don’t get me wrong, this is not false modesty, I’m pretty good at this stuff. But I’m “Pretty Good”. Calm, Breath, Relax…

It was fine. Jonathan and Joze handled the tricky couple of questions and let me talk when I had stuff to add and the whole session was a hum-dinger. How do I know that? Well, the session was for 45 minutes at the end of the last day, on a Friday with all the usual pressures to get away and get home. And yet, despite that, we finally closed the session down after 90 minutes. I have to confess, I’m deeply proud of the fact I was able to play an active role and not just sit there.

All in all, I had a fantastic time and I think it was a cracking conference. Can I come next year? (Only, next year, can I keep to being Question Master! Alan can have a go up on stage)

Getting Your Transaction SCN – USERENV(COMMITSCN) January 19, 2016

Posted by mwidlake in development, performance, SQL.
Tags: , , ,
2 comments

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it so I asked Jonathan and he pointed me straight to this post he did about it a while back. What a nice chap. However, the post is from 1999 (last CENTURY!) so I thought I should just check it out first…

I’ll say up front that this is an undocumented feature of a now-deprecated function. Well, almost undocumented – older SQL reference manuals mention that ‘commitscn’ returns a NUMBER as opposed to the VARCHAR2 returned by most parameters you can use with USERENV, but it does not list it as a valid parameter for that function.
USERENV has been deprecated since Oracle 10g (see the old 10g documentation link here about USERENV) and you have been instructed to use SYS_CONTEXT(‘userenv’,’parameter‘) as described in the 12c database SQL reference manual here. However, there is no way to get the commit SCN from SYS_CONTEXT that I can find, so I thought I’d check out if USERENV(‘COMMITSCN’) still works. It does, on my version of Oracle 12.1.0.2!

There are some strict limits to this function. To begin with, you can’t select it, you can only use it on insert/update:


-- attempt a simple select of the SCN
mdw> select userenv('commitscn') from dual;
select userenv('commitscn') from dual
               *
ERROR at line 1:
ORA-01725: USERENV('COMMITSCN')  not allowed here

--But I can use in an insert,
mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (100,'abcd',userenv('commitscn'))

1 row created.

mdw> select * from test_scn where seq_no=100

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
       100 abcd         11144739

-- Now commit my new record
mdw> commit;
Commit complete.

mdw> select * from test_scn where seq_no=100
  2  /

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
       100 abcd         11144753

--LOOK at the value for SCN_NO now! Compare to before the commit!

If you look at the above you will see a couple of things. The first is that, as I said, you cannot SELECT the function USERENV(‘COMMITSCN’).
The other is, though a value is put into the column when I insert a row using it, and I see that when I query the information back, it changes when I commit. This is because Oracle is recording something at the point of commit, not at the point of the SQL statement running – and the new SCN is only generated when you commit. A lot could have happened since I did the INSERT, I might have gone for a cup of tea and a batch job kicked off doing 1 million transactions, each with it’s own SCN. So though a placeholder of the current SCN is put into your view of the table row, the value put in the actual table is generated at the time of the commit.

Another limiting rule is that you can only reference USERENV(‘COMMITSCN’) once in a transaction, for one row. If I try and create 2 rows using the function in the same transaction I get an error, if I try to update more than 1 row I get an error:

mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (101,'abcd',userenv('commitscn'))

1 row created.

mdw> insert into test_scn (seq_no,vc1,scn_no)
  2  values (102,'abcd',userenv('commitscn'))

insert into test_scn (seq_no,vc1,scn_no)
            *
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

-- now test updating several records
mdw> commit;
Commit complete.

mdw> select * from test_scn;

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
         1 AAAAAAA      11143743
         2 AAAAAAA      11143746
         3 AAAAAAA      11143749
         4 AAAAAAA      11143774
         5 AAAAAAA      11143777
       100 abcd         11144753
       101 abcd         11145543

mdw> update test_scn set scn_no = userenv('commitscn');
update test_scn set scn_no = userenv('commitscn')
       *
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

-- but one row works
mdw> update test_scn set scn_no = userenv('commitscn') where rownum =1;
1 row updated.

USERENV(‘COMMITSCN’) is old, undocumented and limited in use. So why am I looking at it, let alone even telling you all about it? {Apart from the fact that a lot of you *love* this sort of obscure, tid-bitty stuff}. Well, as there is no replacement for it that I am aware of. You can get the current SCN in a couple of ways, the easiest probably being to get it from V$DATABASE:

mdw> select current_scn from v$database;
any key>

CURRENT_SCN
-----------
   11146718

However, that is the last SCN used at the time you check it and is not the SCN when you commit. ie it is a different thing. I always find it irksome on those odd occasions when something is deprecated in Oracle with nothing really to replace it.

I just demonstrate again that USERENV(‘COMMITSCN’) is a little special below, and not the same as just selecting SCN from V$DATABASE. Before I go any further, I think the value USERENV(‘COMMITSCN’) puts into the table is the actual COMMIT SCN minus 1. I mostly think this as Jonathan said so🙂. I do see each time I run this test that the first select from V$DATABASE and then my insert and a commit straight away results in a value in the table 2 higher than the select.

Further iterations (2nd and 3rd in this case) show the value selected from V$DATABASE and the value inserted into TEST_SCN immediately after are the same, and are 3 higher than the previous iteration. I anticipated an increase of two, once for the change to the UNDO tablespace for the insert and once for the insert. I am not sure where the third one comes in.

However, in the fourth iteration I have a PAUSE in my SQL*Plus script between checking V$DATABASE and doing my insert and, in a second session, I do some simple inserts and commits {it does not matter what, so I don’t show it}. Thus the difference between the SCN I collected from V$DATABASE and the value inserted into the table.
Finally, in the fifth iteration, I check the value in V$DATABASE, do the insert, query it back and see the two are the same. And THEN I pause so I can do some changes and commit them in my second session. After I’ve done that I continue my first session which commits my latest insert into TEST_SCN. I check the values actually stored in the table and, just as at the top of this post, you see that the value actually preserved in the table is a later SCN than the placeholder one. It is doing something special.

(the below has the noddy code to create my test table and sequence as well as the test)

-- test_scn1
--create table test_scn (seq_no number,vc1 varchar2(10),scn_no number)
-- create sequence scn_seq;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
pause 'commit some stuff in second session and then press any key'
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
commit;
select * from test_scn;
select current_scn from v$database;
insert into test_scn values (scn_seq.nextval,'AAAAAAA',userenv('commitscn'));
pause 'commit some stuff in second session again and then press any key'
select * from test_scn;
commit;
select * from test_scn;
select current_scn from v$database;

-- the output of the test
mdw> @test_scn1

--check V$DATABASE SCN
CURRENT_SCN
-----------
   11147809

-- Create and commit 1 row
1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
-- note that the inserted SCN is 2 higher than the current SCN.

--Same steps, 2nd iteration
CURRENT_SCN
-----------
   11147814

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
-- select SCN and inserted SCN are the same, 3 higher than first iteration

-- same steps, 3rd iteration
CURRENT_SCN
-----------
   11147817

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
-- select SCN and inserted SCN are the same, 3 higher than first iteration

-- 4th iteration, a pause
CURRENT_SCN
-----------
   11147820

'commit some stuff in second session and then press any key'
-- I did indeed change and commit some stuff in second session, before I create my record in test_scn

1 row created.
Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
-- larger gap in SCN (11147817 to 11147831

-- 5th iteration, pause now after insert and before commit
CURRENT_SCN
-----------
   11147834

1 row created.
'commit some stuff in second session again and then press any key'
-- I did indeed change and commit some stuff in second session 

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
        15 AAAAAAA      11147834

-- Notice the current_scn from V$DATABASE and the last row in the table match, 11147834..

Commit complete.

    SEQ_NO VC1            SCN_NO
---------- ---------- ----------
        11 AAAAAAA      11147811
        12 AAAAAAA      11147814
        13 AAAAAAA      11147817
        14 AAAAAAA      11147831
        15 AAAAAAA      11147842

-- But after the commit the SCN in row "15" has increased to 11147842.

CURRENT_SCN
-----------
   11147851

-- and the next check of V$DATABASE SCN shows the usual increase of 3 by the commit.

As you can see from the above, USERENV(‘COMMITSCN’) is doing something a little special and, despite all the limitations, I might actually have a use for it…

Pragma UDF – Some Current Limitations November 11, 2015

Posted by mwidlake in performance, PL/SQL, SQL, Testing.
Tags: , , , ,
7 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

  1. It gives a performance boost when the inputs and return values are NUMBER, VARCHAR2, multiple VARCHAR2 IN parameters
  2. There is no performance boost when either or both the IN parameter or RETURN value is a DATE
  3. There is no performance boost if there are any default values for VARCHAR2 IN parameters
  4. 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 pragam 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))) @cre
      ,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 pragam 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

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

Posted by mwidlake in performance, PL/SQL, SQL.
Tags: , , ,
7 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 working on for 2016) 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 from here}

-- 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 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 function 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 self-documenting. This is one 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-paste 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, it alters the internal representation of data as it is passed between the SQL and PL/SQL engines via the IN and RETURN values (Note it does not change the data types!) – but treat that as a bit of wild speculation for now. I have some evidence for it that you will see in, yes, the next post.

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.

Where do my trace files go? V$DIAG_INFO October 19, 2015

Posted by mwidlake in development, performance, SQL Developer.
Tags: , ,
1 comment so far

Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.

If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, forgetful me.

V$DIAG_INFO has been available since oracle V11. All the trace files go into the Automatic Diagnostic Repository (ADR) by default.

ora122> desc v$diag_info
 Name                                                                Null?    Type
 ------------------------------------------------------------------- -------- ---------------
 INST_ID                                                                      NUMBER
 NAME                                                                         VARCHAR2(64)
 VALUE                                                                        VARCHAR2(512)
 CON_ID                                                                       NUMBER

Quick sql*plus script to get it out:

-- diag_info
-- quick check of the new v$diag_info view that came in with 11
col inst_id form 9999 head inst
col name form a25
col value form a60 wrap
spool diag_info.lst
set lines 120
select * from v$diag_info
order by name
/
spool off

Contents:

 INST_ID NAME                 VALUE                                                            CON_ID
-------- -------------------- ---------------------------------------------------------------- -------
       1 Diag Enabled          TRUE                                                                  0
       1 ADR Base              D:\APP\ORACLE                                                         0
       1 ADR Home              D:\APP\ORACLE\diag\rdbms\ora122\ora122                                0
       1 Diag Trace            D:\APP\ORACLE\diag\rdbms\ora122\ora122\trace                          0
       1 Diag Alert            D:\APP\ORACLE\diag\rdbms\ora122\ora122\alert                          0
       1 Diag Incident         D:\APP\ORACLE\diag\rdbms\ora122\ora122\incident                       0
       1 Diag Cdump            D:\app\oracle\diag\rdbms\ora122\ora122\cdump                          0
       1 Health Monitor        D:\APP\ORACLE\diag\rdbms\ora122\ora122\hm                             0
       1 Default Trace File    D:\APP\ORACLE\diag\rdbms\ora122\ora122\trace\ora122_ora_7416.trc      0
       1 Active Problem Count  0                                                                     0
       1 Active Incident Count 0                                                                     0

I should add some notes later about setting the trace file identifier…
Ohhh, OK, I’ll do it now. To make it easier to identify your trace file, set tracefile_identifier

alter session set tracefile_identifier = 'mdw151019'

--Now if I create a quick trace file
alter session set sql_trace=true

@test_code

alter session set sql_trace=false

I now go to the Diag trace directory I identified via V$DIAG_INFO and look for my trace files. I could just look for the latest ones or do a wilcard search on my tracefile_identifier string and, pop, there we are:

19/10/2015 13:59 39,751 ora122_ora_7416_mdw151019.trc
19/10/2015 13:59 426 ora122_ora_7416_mdw151019.trm

If you want a taste of the numerous ways of initiating a 10046 trace, sometimes called a SQL trace, see Tim Hall’s excellent post on his Oracle Base website:

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

Oh, one final nice thing. You can open trace files in SQL Developer and play with what information is shown. Maybe I should do a whole piece on that…

Actually, these two post from Oracelnerd and Orastory will get you going, it’s pretty simple to use in any case:

http://www.oraclenerd.com/2010/02/soug-sql-developer-with-syme-kutz.html
https://orastory.wordpress.com/2015/02/27/sql-developer-viewing-trace-files/

ScreenHunter_45 Oct. 19 14.25

Friday Philosophy – On “Being the Expert” September 4, 2015

Posted by mwidlake in contracting, Friday Philosophy, performance.
Tags: , ,
3 comments

Working as a recognised expert at something is a little…strange, I find.

I had an assignment this week to go visit a client, have a look at a performance issue and find out the root cause. I was also to at least come up with suggested resolutions with the ideal aim of giving them a proven fix they could implement. All to be done in two to three days. This is pretty standard fayre when you are putting yourself forward as some sort of expert in something. And it is not always an easy thing to do – for more reasons than you might expect.

When it comes to the core service you are providing you are certainly expected to know your stuff and if you are there as the expert and you don’t? Well, any pain you now suffer is self-inflicted so I have no sympathy. You might think actually being an expert is the hard part – the knowing all that stuff, remembering it, the ability to answer all the questions or look at an issue and in 5 minutes say “It’s because the CLOB settings are wrong”. ie matching the expectations of almost God-like knowledge and ability. But it is not. If you can listen to what their problem is, understand it and then explain something to them that they did not know before, it will be fine. What the client needs is to feel progress is being made. An immediate and inspired solution may occasionally be possible but on the occasions I have pulled that off, the client usually just feels uncomfortable, like they missed the obvious. Because they did. If I sort out the issue straight away that they have had for 3 weeks and that the in-house expert has looked at there is only really two possible reasons
(a) it is simple and they missed it.
(b) they ignored their expert.

The option of (c) my genius is sadly just a dream.

What I find more tricky is when they just accept what I say, when they treat everything I say as correct. Even if I say “it might be this” there can be an assumption I am being modest and it really is what I suggest. I’d like them to only believe me once there is some proof. Most of my time on such assignments is me sat at the SQL prompt trying to back up what I think is the issue/solution. Even when I have evidence, I know I could just be seeing what I want to see. I want some proof and I want them to challenge it.

There is also sometimes a tendency for the rest of the staff to regarded you as some sort of a weirdo, someone Not Like Them. After all, if you are an expert in Oracle Performance you must spend all your time looking at explain plans and 10046 traces and not doing normal people stuff. I have to say, I had a really nice (and in some ways quite worrying) complement a few years back. I was at a client site for a couple of months, plowing though what seemed like endless layers of bad code/design/decisions to make things run better. One lunch time I headed out to find some lunch with a couple of the developers. One of them turned to me and said something like “You know, I’m really glad you joined us. You’re just a normal bloke and not one of those freaky tuning experts!” He really thought all Oracle Performance people would be strange – and strange in the already bizarre context of all the other people that inhabit our profession. I wonder who else he had met?

You can also run into resentment – occasionally irrationally (fear of challenge? envy? just psychotic people?) but also for real reasons. I sort-of alluded to it earlier. You get listened to when you are “Being the Expert”. Even though you may say what Sarah had already pointed out last month, you get listened to. Sarah is not going to be happy about that. Sarah is going to be especially annoyed and resentful if she told Me, the expert, about the point I raised. In these situations I try and emulate what a friend of mine taught me about 10 years ago on “Being The Expert”. One of your jobs as an external consultant should be to tell the client to listen to their staff if their staff are getting things right. What the real problem is could well be that the client is not using the resources it already has. And you were, after all, hired to solve their problem.

The final thing I find strange that I’ll mention is this. As the expert I am constantly anxious I am going to be “found out”. I mean, right now, I am doing my final report on this assignment. I know I identified several issues, I backed them up with evidence, I moved the client forward. I found out things that they had not known. I taught some of the staff new stuff. I stressed that I will not have found everything as it was only 3 days with no access to the live system… But I worry that in 3 weeks I’ll hear that none of what I suggested worked and that the REAL issue was something I utterly missed and when they corrected that, the run time went down by a factor of a thousand. And I failed them.

I just worry about that. Because I am “Being the Expert”

With Modern Storage the Oracle Buffer Cache is Not So Important. May 27, 2015

Posted by mwidlake in Architecture, Hardware, performance.
Tags: , , , ,
11 comments

With Oracle’s move towards engineered systems we all know that “more” is being done down at the storage layer and modern storage arrays have hundreds of spindles and massive caches. Does it really matter if data is kept in the Database Buffer Cache anymore?

Yes. Yes it does.

Time for a cool beer

Time for a cool beer

With much larger data sets and the still-real issue of less disk spindles per GB of data, the Oracle database buffer cache is not so important as it was. It is even more important.

I could give you some figures but let’s put this in a context most of us can easily understand.

You are sitting in the living room and you want a beer. You are the oracle database, the beer is the block you want. Going to the fridge in the kitchen to get your beer is like you going to the Buffer Cache to get your block.

It takes 5 seconds to get to the fridge, 2 seconds to pop it open with the always-to-hand bottle opener and 5 seconds to get back to your chair. 12 seconds in total. Ahhhhh, beer!!!!

But – what if there is no beer in the fridge? The block is not in the cache. So now you have to get your car keys, open the garage, get the car out and drive to the shop to get your beer. And then come back, pop the beer in the fridge for half an hour and now you can drink it. That is like going to storage to get your block. It is that much slower.

It is only that much slower if you live 6 hours drive from your beer shop. Think taking the scenic route from New York to Washington DC.

The difference in speed really is that large. If your data happens to be in the memory cache in the storage array, that’s like the beer already being in a fridge – in that shop 6 hours away. Your storage is SSD-based? OK, you’ve moved house to Philadelphia, 2 hours closer.

Let's go get beer from the shop

Let’s go get beer from the shop

To back this up, some rough (and I mean really rough) figures. Access time to memory is measured in Microseconds (“us” – millionths of a second) to hundreds of Nanoseconds (“ns” – billionths of a second). Somewhere around 500ns seems to be an acceptable figure. Access to disc storage is more like Milliseconds (“ms” – thousandths of a second). Go check an AWR report or statspack or OEM or whatever you use, you will see that db file scattered reads are anywhere from low teens to say 2 or 3 ms, depending on what your storage and network is. For most sites, that speed has hardly altered in years as, though hard discs get bigger, they have not got much faster – and often you end up with fewer spindles holding your data as you get allocated space not spindles from storage (and the total sustainable speed of hard disc storage is limited to the total speed of all the spindles involved). Oh, the storage guys tell you that your data is spread over all those spindles? So is the data for every system then, you have maximum contention.

However, memory speed has increased over that time, and so has CPU speed (though CPU speed has really stopped improving now, it is more down to More CPUs).

Even allowing for latching and pinning and messing around, accessing a block in memory is going to be at the very least 1,000 times faster than going to disc, maybe 10,000 times. Sticking to a conservative 2,000 times faster for memory than disc , that 12 seconds trip to the fridge equates to 24,000 seconds driving. That’s 6.66 hours.

This is why you want to avoid physical IO in your database if you possibly can. You want to maximise the use of the database buffer cache as much as you can, even with all the new Exadata-like tricks. If you can’t keep all your working data in memory, in the database buffer cache (or in-memory or use the results cache) then you will have to do that achingly slow physical IO and then the intelligence-at-the-hardware comes into it’s own, true Data Warehouse territory.

So the take-home message is – avoid physical IO, design your database and apps to keep as much as you can in the database buffer cache. That way your beer is always to hand.

Cheers.

Update. Kevin Fries commented to mention this wonderful little latency table. Thanks Kevin.

“Here’s something I’ve used before in a presentation. It’s from Brendan Gregg’s book – Systems Performance: Enterprise and the Cloud”