jump to navigation

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

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

OOW Report – No List of Talks, No Cloud, Just Thoughts on Community October 30, 2015

Posted by mwidlake in conference, Perceptions, User Groups.
Tags: , ,
4 comments

As I type I am in my hotel, sipping a final beer (it was a gift that has been to a few talks with me in my backpack) and looking back at Oracle Open World 2015. I must confess I am a little drunk so we will see if this post lasts…
{Update – it passed the next-morning-sobriety test. I was only a little drunk}

OOW15 beers

I am on record as saying I don’t like Open World. I came to previous events in 2003 and 2004 I think (yes, over a decade back), both times at short notice on the behest of the Mother Corporation. And at those times I only knew people at the event from Corporation Oracle – not people in the Oracle User Community. It is miserable being 1 of xx thousand people who you *should* share interests with but simply don’t know. Oracle employees are generally excluded from the event so that removed nearly all of my contacts. It is such a large event that if you meet someone on Sunday and chatted to them – you may well never see them again! After all, it is 1 in x thousand people even for your specific area of interest. I’m not good at chatting to people “cold” and the whole “entering the US” is such a bloody awful experience (Immigration just shout at you and growl and are, frankly, as welcoming as a Rottweiler at a kitten party) that the total experience from beginning to end was just, well, less pleasant than a bad week in the office.

This time was very, very different (though not the growling Rottweiler bit, sadly). Because I am now an active member of a couple of oracle “clubs” (Oracle ACE and OakTable) I knew more people. Because I blog and tweet I knew a lot of USA {and other} people, if only via social media. As a result of going to a good few different user groups (and often presenting) I have become friends with people from several communities. And I have also got better at “Cold chatting”. So for several days I have been meeting people like Danny Bryant (still my hero as he got my conference pass back to me after I dropped it on a bus!), Bobby Curtis, That Jeff Smith, Sarah CraynonZumbrum, Zahid Anwar… and about 37 other people I had never met or only met once. I have re-connected with a couple of dozen old friends too and hung around with closer friends from the UK & Europe. And it has been great. This is one of the great aspects of being an active member of the Oracle Community, there is a pool of people I can now talk to and relax with.

I’ve loved my OOW15 experience and that is fundamentally because I felt I was inside rather than outside. At this point I was planning to say that not everyone you meet in the flesh will turn out to be people you actually get on with – but I can honestly say that everyone I have met this week has been at least polite to me, most have been welcoming. I’m not saying all will be life-long friends and I am at long last wise enough to recognise that someone being polite to me does not mean they did not find me annoying. But one of the great things about a user group community is that almost everyone in it is actually on the “friendly” side of normal. If you are not, user groups are not going to be your thing!

It makes a huge difference. Being able to find someone (and modern social media makes that so much easier than a decade back when I hated this experience) to have a coffee with and a nice conversation can make a potentially lonely gap between presentations into an enjoyable afternoon. I missed half a dozen presentations this week as the conversations went on much longer and were more illuminating than you planned. I could just position myself at a central location and pretty soon a friend would wander by. Or, at least, someone who would not run away:-)

Being mindful of the above, if anyone came up to me to talk, I talked to them. There is a phrase that seems current in the US of “paying it forward” which means if you have had a nice experience, try to make someone else’s experience nice. Or is it “paying it backward”? I don’t know for sure but I like both. If you have been helped, help someone. If you think people should help each other, start it by being helpful first. I was able to do this a little bit myself by making sure I was around if a friend called Stew needed some company, as he is not as tied into the user community as others as he is new to this. However, I don’t think this will last as he is making such a name for himself that next year he’ll be introducing me to people! In turn, another friend, Brendan, made time to make sure I had company as he knew I’d not liked my prior experiences.

So all in all I now don’t dislike OOW. I like OOW. And the reason is the user community is there for me. It’s there for everyone who wishes to be a part of it. You won’t like everyone, everyone won’t like you – but that is fine, we all have our different characters – but you will gel with a good few people.

Note I have not mentioned presentation slots. Some were good, some were bad, a small number were great and a similar number were awful. But I did learn a lot and I appreciate the fact. I will say no more as, frankly, if you were not at the conference then a discussion of the presentations is pretty pointless!

I just want to end on a final consideration. I know I am now a member of a couple of “clubs” and that helps me in knowing people. But a lot of people I now know are not members of either of those clubs and I know them due to my simply being social-media active, a user group attender and I make myself cold-chat more. It almost hurts me to say it, but social media can be a good thing. Nothing beats face-to-face socialising, but knowing people virtually first is a great help in getting started with meeting them for real.

I really love the user group community. Or is that just the beer talking (which I finished over an hour back!)

{update – OK, it was the beer, I don’t love any of you. But I like you a lot…}
 

The “as a Service” paradigm. October 27, 2015

Posted by mwidlake in Architecture, Hardware, humour.
Tags: , , ,
4 comments

For the last few days I have been at Oracle Open World 2015 (OOW15) learning about the future plans and directions for Oracle. I’ve come to a striking realisation, which I will reveal at the end.

The message being pressed forward very hard is that of compute services being provided “As A Service”. This now takes three flavours:

  1. Being provided by a 3rd party’s hardware via the internet, ie in The Cloud.
  2. Having your own hardware controlled and maintained by you but providing services with the same tools and quick-provisioning ideology as “cloud”. This is being called On Premise (or just “On Prem” if you are aiming to annoy the audience), irrespective of the probably inaccuracy of that label (think hosting & dedicated compute away from head office)
  3. A mix of the two where you have some of your system in-house and some of it floating in the Cloud. This is called Hybrid Cloud.

There are many types of  “as a Service offerings, the main ones probably being

  • SaaS -Software as a Service
  • PaaS – Platform as a Service
  • DBaas – Database as a a Service
  • Iass – Infrastructure as a Service.

Whilst there is no denying that there is a shift of some computer systems being provided by any of these, or one of the other {X}aaS offerings, it seems to me that what we are really moving towards is providing the hardware, software, network and monitoring required for an IT system. It is the whole architecture that has to be considered and provided and we can think of it as Architecture as a Service or AaaS. This quick provisioning of the architecture is a main win with Cloud, be it externally provided or your own internal systems.

We all know that whilst the provision time is important, it is really the management of the infrastructure that is vital to keeping a service running, avoiding outages and allowing for upgrades. We need a Managed Infrastructure (what I term MI) to ensure the service provided is as good as or better than what we currently have. I see this as a much more important aspect of Cloud.

Finally, it seems to me that the aspects that need to be considered are more than initially spring to mind. Technically the solutions are potentially complex, especially with hybrid cloud, but also there are complications of a legal, security, regulatory and contractual aspect. If I have learnt anything over the last 2+ decades in IT it is that complexity of the system is a real threat. We need to keep things simple where possible – the old adage of Keep It Simple, Stupid is extremely relevant.

I think we can sum up the whole situation by combining these three elements of architecture, managed infrastructure and simplicity into one encompassing concept, which is:

KISS MI AaaS.

.

.

And yes, that was a very long blog post for a pretty weak joke. 5 days of technical presentations and non-technical socialising does strange things to your brain

Friday Philosophy – The Small Issue of Planes, Trains and…Coaches. October 21, 2015

Posted by mwidlake in Uncategorized.
7 comments

Today I get on a plane. It is a long flight, 10+ hours, and throughout all of it, some people will hate me. I even expect some Hard Stares. Why? Because I’ll be sitting in a seat with a large space in front of it – and my legs dangling off the front of the seat. Those over 6 foot will be fuming I have that extra leg room. See me sitting there. See me smiling:-) .

King of all I see

King of all I see

Well, I picked that seat for reasons that the tall (and average) do not appreciate. On flights I generally have leg and arm room others may wish they had. It is one of the few, very few, benefits of being small. Relatively speaking I have more space in my allocated seat to place my body parts in comfort. But, unlike most of you, all I see for the whole flight is a wall of seat-back. And I can’t sit straight as my thighs are slightly shorter than the seat base, so I sink even lower with an unsupported back, staring at that seat back. Just that seat back. Nothing but that seat back. Unlike you I can only see ceiling if I tip my head back, I can’t take a long look down the aeroplane above the seats; the angle is too steep. And now the seat back has moved closer to me as the person in front has reclined their seat. On short flights this is a pain, on long flights it might mean the film I was watching, on a screen slightly above my eyeline, is now well above my eyeline and the colours have gone weird. The bottom line is, I spend the whole flight in a box that ends THERE, 14.2 inches in front of my face. The whole flight, in a seat too long for my legs, going slowly batshit due to mild agoraphobia that becomes major after 5 hours. I can sit cross-legged in my seat to solve many of the issues but (a) the flight attendants don’t like it (b) I lose blood supply to my left foot and (c) I start thinking I’m a Ninja. An evil Ninja.

There is another reason I pick such a seat. Looking out the window from time to time helps keep me sane from the Box I am in, so I like to be by the window. But those people in funny costumes, way too much makeup and with the fixed smiles keep offering me these big drinks (big to me) and I soon need a pee. A pint in a half-pint can only lead to one event. Asking 2 or 3 people to let me out every hour soon gets trying for all, so a seat where I can just step front and go find the loo is good.

At least on a plane the person next to me is likely to be normal (or my wife). On normal public transport, they often are not. This next consideration has been an aspect of my life for as long as I have traveled on my own. When I was a student almost no students had their own cars, including me. Which means I often had to get to and from my parental home and college home by public transport. In my case coach (bus, large vehicle driving down roads). I would get on, sit down, watch others file in and fill the classic “I want a double seat to myself so I will spread out my shit” pattern. Until each double seat was full with coats, bags and handbags strategically placed to warn others to sod off. And I knew what was coming. It always did. Normal sized people would generally get on and choose to sit somewhere near the door, forcing someone on their own in a double seat to move their defensive stuff. I always sat towards the back. But then some massive, often fat but sometimes just hulking, person would appear (usually a man) and would look gloomily at all the single spots left. And spot “The Small Guy” way back down there. They would be over to me in a shot – lord knows how quick given how much blubber they had to drag along with them – and into the seat next to me. And then they would Sppprreeeaaaadddd. First the thigh would come over my side, followed by the rest of the leg. And the arm would push up against my am and then over the arm rest (if there was one) and shove me to one side. Soon a torso would be shoving into me. Within minutes they would by laying claim to 25% of my seat, my space. One of my few benefits of being small was being taken from me.

I was young, I was brash and I’d learnt over the years to take none of this crap. I clearly remember one trip, I think in my final year when I was tired and annoyed, when one massive chap sat next to me and started to spread and I just shouted “Oi! Get out of my seat! Get your fat arse and your fat arm out of my space! I have few benefits in my life from being small and my space on public transport is a rare one of them! Keep to your bloody side!”. I did not hold back at all on mentioning his massive blubbery state or his encroachment into my space. Oh, he was full of “Oh I did not realise, I’m not taking your space, how could you insult me for being larger” that I knew from experience was really “I chose the small guy to sit next to so I could have more space.” I’d watched him scan the seats, spot me and come over. I let rip and said “well move and sit next to someone normal size! Go on! MOVE!” He didn’t. He knew he needed some of my space. It was not a comfortable journey for either of us from then on and I suspect he did not need to get off at Sheffield, but for f***s sake, I get few benefits in life from being small. At least we were the entertainment for the other passangers for a while.

It is an aspect that has not gone away. For many years I’ve commuted into London and watched the pathetic games played by other commuters. Get on, put your stuff in the seat next to you (exactly how hard would it have been to put that coat and that little bag in the rack above your head? About the same as to spread it evenly over the seat by you) and look busy or angry. If you are lucky you can sit opposite someone by the window who has already played the double-seat-claim-game and can sit in the isle seat and put your crap on the inner seat – any new player will see it is easier to make someone move stuff from the isle seat than move stuff from the inner seat AND then climb past them to the spare window seat. Utterly selfish evil people will get on an empty coach, sit in the isle seat and then fill the window seat with their stuff that could so easily go in the overhead racks. They know how the game works and they have no sense of shame in being so selfish.

When I get on I often look at the pattern of seating in front of me and pick one of the buggers in the isle seat to move. Almost no one else does.

When I get on a train and sit down, I usually put my stuff under the seat or in the overhead racks. And given my height, if I can do it all you buggers can. And I sit. I try to sit forward-facing, I hate facing back as it make me feel a little sick, but other than that I have no rules. If I am in the isle, I will stand as soon as you ask to let you in. This is my little play to show I am nicer than all you other commuting buggers. Anyway, this train will get packed, I might as well get someone to the side of me so I can relax. Others get on and I often get someone next to me pretty soon as I am not playing silly buggers. It’s fine, soon all seats will be taken unless someone is being especially obnoxious about double seat protection. But I have to say, if someone massive (and usually a man) gets on and I see them scan the carriage and eyes fall in relief on me… I pat the seat next to me and smile. Sometimes I wave. It’s the only defense I know that works 95% of the time.

As a social commuter I hate the games the antisocial ones play, but as a small person, I bloody hate my space being bloody stolen by fat/large people. You could lose weight you know, I can’t grow! I paid for my seat, you paid for yours, for once in my short life, I bloody well want the benefit of my short stature. Now bugger off over to your side of the double seat.

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 – 3 months, 3 conferences October 16, 2015

Posted by mwidlake in ACED, conference, Friday Philosophy, Presenting, Tech15.
Tags: , ,
2 comments

Flights are booked, hotels reserved, plans made. Don’t ask about talks prepared, just don’t:-)

This is not the usual list of “I’m going to this talk and I’m seeing that speaker” blog that people write before an event – well it is a little – it’s more about the different flavors of conference we have available to us.

I have an Oracle conference a month until the end of the year and I’m really looking forward to all of them. Each is very different. I know I am lucky to be able to do this sort of thing, that is go to so many conferences, and partly it is because of being an ACED. But fundamentally it’s come about as a result of the decision I made back in 2003 to give something back to the community that I’d learnt so much from, and even more so when a couple of years back my wife gave me permission to do less stuff that pays and more stuff that I enjoy. Oracle Community stuff.

First up of course is Oracle Open World 15. This includes a couple of days before hand with the ACED briefings. We get a heads-up on what is happening with the direction of Oracle Tech and Oracle expect us to feed back what we think. After 25 years in the business and dozens of conferences, this will be a first for me so I will be a newbie again (hmm, maybe not so new thinking about it, I’ve been on Customer Advisory Boards and Beta tested in the past so it will be interesting to see the difference). I’ve said in the past how I was not so fond of my prior Oracle Open World experiences. Too big and too razzmatazz for my repressed British personality. But the huge difference between this time and 10 years ago is not my being ACED, it is being a member of the community and looking forward to seeing so many people, catching up and talking about all things tech.
Elton John is apparently doing the appreciation event. I’m hoping for “Yellow Brick Road” era stuff and none of that modern post Y2K stuff…
Oh, and don’t forget, there is also the Oaktable presence at OOW, OakTable World. It’s free to all at OOW15 and if you want technical meat on your presentation bones, that is where you will find it.

In November, Friday 20th to Sunday 22nd, it is a totally different experience, the Bulgarian Oracle User Group Autumn conference. This is purely a tech conference, no dancing girls, no laser-show keynotes and not a hint of Elton John. Just a shed load of top presenters (so many ACE badges next to names) with a good showing of local talent too. Several of the speakers are coming to it from DOAG, a conference I was seriously considering putting papers forward for but decided not to, as I felt I was too busy at the end of the year – and then I got sweet-talked into putting forward abstracts for Bulgaria. Next year I’ll try for DOAG. This will be my first time at a BGOUG conference but I know from my friends that it is like many of the smaller European conferences. It has a more inclusive, friendly feel as you see the same people over and over again for the couple of days and spend time getting to know people pretty well and often having longer, more involved discussions about whatever tech you are working with. I’ve been really well looked after by the organisers already, helping me sort things out and advising me on what to do outside the event.

I’m combining this one with a short holiday with my wife. (She speaks Bulgarian so she will be very helpful in ordering beer in local bars). One down side to going to more conferences is that, as she travels a lot herself for work, some months we don’t see a lot of each other. It will be really nice to wander around Sofia together for a few days. The ironic thing is that her employer, actually her department, is doing some work out there that week – and they did not schedule in the only person in the team who speaks the language!

Finally there is “my” conference. Mine as in I feel it is my home conference, being in the UK and one I have presented at or helped organise for 12 years now. The UKOUG Tech15 conference. This is from Monday 7th December to Wednesday the 9th, and if you get registered in time you can also be at Super Sunday on the 6th (half a day focused on deeper tech talks). Again, a conference that puts technical content at the top and the sales sides comes along for the ride. It is a very large conference, vying with DOAG to be the biggest after Oracle Open World. We are less show and more tell than OOW but it lacks the personal feel of smaller conferences. We are back in Birmingham for this one and I have to say it’s all looking set for a great event. Registrations are significantly up on the last couple of years at this stage, the exhibition is selling well and we have great content lined up. I need to tweet more about Tech15, both about how such an event is organised (I know some of you liked hearing about that) but also about some of the things that will be happening. I’m quietly excited about a couple of things. The only problem is that, by the time I get to the actual Tech15 conference I am usually a bit spaced out and knackered from all the prep work and by the end of Wednesday (the last day) I’m physically drained – but with a head full of new information.

As I said, all three conferences have a different vibe and which one you prefer is down to what you want from your conference.

After all that I’ll be done with conferences. I refuse to go to any more until the following year…

Which reminds me, I better start putting in some abstracts and seeing if I’ve got stuff people want in their conferences next Spring.

Friday Philosophy – Be Moral or Be Sacked? October 9, 2015

Posted by mwidlake in ethics, Friday Philosophy.
Tags: , ,
8 comments

How far will you bend your moral stance to keep your job?

This post was prompted by a Twitter discussion over the recent VW Emissions scandal development where software engineers are being blamed. Let’s just skip over the rather trite and utterly unbelievable proposition that a couple of rogue software engineers did this “for reasons unknown” – and the fuel engineers, mechanical engineers, and direct managers did not realise “hey, our engines are more efficient than we knew was possible, never mind seen”. Plus the testers, change control, release managers, etc were all circumvented by the rogue software engineers…. It would have to be incompetence of unbelievable levels for the whole stack of management up to the top did not in some way at least know about this – and I personally am sure they condoned or even demanded the results.

What made me think was a comment by a friend that the software engineers must have at least colluded and thus are at least partially responsible – and it struck a chord in me. What constitutes collusion? and would you or I do it? I’ve been in a very similar situation…

Back in my first job I worked for one of the regions of the UK National Health Service, as a programmer. An edict came down from high. Government high. We were to make the waiting list figures look better. “We” being the NHS management initially but, as I guess they were powerless to really do much about the reality of the situation, it come down the levels until it was realised it was the data used to show how the waiting times were doing that could so easily be changed.

I was given the job of altering the Waiting List Reports in a few ways. A key one was how the date you started waiting was measured. No matter how often the hospital cancelled your appointment or sent you home not having done the procedure, the date from which you started waiting remained the same. However, if you were offered an appointment and for any reason you could not attend – ANY reason, be you ill in another way, have a responsibility you could not avoid, were only given a day’s notice – the date you were waiting was reset to the day of the refused appointment. Of course this was utterly unjust and we were told it would not really mean Mrs Smith who had been waiting 3 months would now have to wait another 3 months – “it would be handled”. But it made the figures so much better.

I refused. In the first place it was a con, in the second I doubted all the Mrs Smiths would be handled as the NHS, even back then, was in a right state.

To this day I am proud I refused.

My colleague was given the task instead – and she did it. I asked her how she could do it? We had some shared political and philosophical views. How could she do something she knew was utterly false and misleading? Her answer was simple.

“You’re lucky – you can afford to take the risk. I’ve just got married, we have a mortgage and I have …other responsibilities – I can’t afford to damage my career or get sacked. You can.”

She was right. I did not know it then but she was trying for a baby, so yeah, getting sacked would have been devastating. On the other hand, I had no dependents (no one loved me), no mortgage and I was already muttering about leaving. She had in effect been bullied into doing a task she was morally against. And she knew, if she did not do it someone else would and she would have taken the hit.

And I confess, I did not simply stand up, shout defiance and proudly walk out the room, head held high. I had a long chat with my union rep about what support I could expect if things got bad before I refused. I knew he was ready to support me.

There were repercussions. I already had a poor relationship with my manager. After I refused to do that work I had an even worse relationship with him, and now his boss disliked me quite a lot too. It was a large part of me leaving to join some no-hope database company.

So, I think there is a very large difference in colluding and being coerced.

The same argument goes up the stack too. I can imagine there were lots of people involved in the VW scandal who knew what was going on, did not like it but, “hey, it’s my job I am risking and it’s not as if I’m the one *authorising* this”.

I can’t say I’ve always held to my moral ground so strongly, I’ve done a couple of things professionally I wish now I’d also said no to. But I’ve also said no to a couple more.

{I hope the statute of limitations on mentioning governmental evils is less that 25 years…}

PL/SQL bug with DBMS_RANDOM? October 8, 2015

Posted by mwidlake in bug, PL/SQL, SQL.
Tags: , , ,
7 comments

I think I’ve found an (admittedly obscure) bug with DBMS_RANDOM, group functions, PL/SQL and/or SQL.

Have a look and see if you also think this is odd – or have I missed the totally obvious?

(This is all on 12.1.0.2)

{Update – my conclusion is, and thanks to Joel and Sayan for their comments, that this is not a “bug”. Oracle do not promise us how PL/SQL functions are executed due to the way SQL can be re-written by the parser. I think most of us stumbling over something like this would treat it as a bug though. You have to look at the column projection, again see the comments, to see how Oracle is deciding to get the columns derived by a naked call to DBMS_RANDOM.VALUE (by naked I mean no inclusion of parameters passed in and, significantly, no reference to columns). It’s just the way it is}

Without going into the details (we would be here for hours if I did) I’m looking into the overhead of context switching between PL/SQL and SQL. It it fairly common knowledge that when you call a PL/SQL function from SQL there is a context switch when the SQL engine hands over control to the PL/SQL engine. I’ve been doing some work into how much the overhead is and that it is incurred for each distinct PL/SQL function (plus loads of other considerations around it).

In doing so I saw something unexpected (to me, anyway) which I have simplified down to this:

select /* mdw_z1 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg_dbrav2
      ,max(created) max_cre
from all_objects
where rownum <50000

AVG_DBRAV1 AVG_DBRAV2 MAX_CRE
---------- ---------- -----------------
.502234063 .502234063 11-SEP-2014 09:31
Elapsed: 00:00:00.89

Note that the two averages of DBMS_RANDOM.VALUE are exactly the same. It is so improbably as to be pretty much impossible that over all those rows, the different random values generated for each column add up to exactly the same. They are getting the same values for each row. I’m very, very dubious of any “identical seeding” issue (ie they both get the same seed and from then provide identical values) as even if DBMS_RANDOM is basing it’s output on something like initial seed, SCN of statement and number of iterations, it is still being referenced twice for each row.

Some further evidence is that when I increase the number of calls to DBMS_RANDOM the elapsed time is almost identical and the statement CPU and PLSQL_EXEC_TIME (pulled from V$SQL) do not increase in any significant way (PLSQL_EXEC_TIME actually goes down):

select /* mdw_z2 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg_dbrav2
       ,avg(dbms_random.value) avg_dbrav3,avg(dbms_random.value) avg_dbrav4
       ,avg(dbms_random.value) avg_dbrav5,avg(dbms_random.value) avg_dbrav6
      ,max(created) max_cre
from all_objects
where rownum <50000

AVG_DBRAV1 AVG_DBRAV2 AVG_DBRAV3 AVG_DBRAV4 AVG_DBRAV5 AVG_DBRAV6 MAX_CRE
---------- ---------- ---------- ---------- ---------- ---------- -----------------
.500367568 .500367568 .500367568 .500367568 .500367568 .500367568 11-SEP-2014 09:31
Elapsed: 00:00:00.84 -- (cf .89 before)

-- information about the two SQL statements from v$SQL, identified by my comments
SQL_ID        PLAN_HASHV  PRSE  EXCS     BUFFS DISCS     CPU_T PLSQL_EXEC    RWS
------------- ---------- ----- ----- --------- ----- --------- ---------- ------
SQL_TXT
--------------------------------------------------------------------------------
4y4jsj7uy12t3 1587414607     1     1     41481     0    843750     312691      1
select /* mdw_z2 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg
gpdga3qars8p2 1587414607     1     1     41481     0    828125     316648      1
select /* mdw_z1 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg

I’ve verified that adding other PL/SQL calls adds about 0.4 seconds to the execution time per extra (simple) PL/SQL function and you see the CPU Time and PLSQL_EXEC_TIME increase.

It is as if Oracle realises the call to DBMS_RANDOM is the same and so does it once per row and puts the results into each column. It does this for other deterministic PL/SQL functions but I would (a) be worried if DBMS_RANDOM was being treated as deterministic (as it’s purpose is to NOT be deterministic) and (b) I know I have used DBMS_RANDOM to generate test data many times, often for several columns in my generating SQL SELECT statement, and I never noticed this before. So, I decided to check that it was only returning 1 value per row to populate the results for each column derived from DBMS_RANDOM.VALUE:


select dbms_random.value drv1, dbms_random.value drv2,created
from all_objects where rownum < 6

      DRV1       DRV2 CREATED
---------- ---------- -----------------
.341919389 .020497964 11-SEP-2014 08:40
.569447631 .727672346 11-SEP-2014 08:40
.019986319 .709239586 11-SEP-2014 08:40
.286970852 .144263004 11-SEP-2014 08:40
 .14440676 .538196808 11-SEP-2014 08:40

Huh? That rather damages my theory and actually works the way I expected, hoped for and thought I remembered. And you can just look at the two columns and you know they are not going to add up to exactly the same! (the last digit adds up to 21 for DRV1 , 28 for DRV2) {How many of you checked that and got 27 for DRV1?}.

So I wrote some code that should be logically identical to my original SQL statement but does the data collection “manually”:

with source as
(select /*+ materialize */
        dbms_random.value  dbrav1    ,dbms_random.value dbrav2
       ,created
from all_objects
where rownum <50000
)
select/* mdw_z3 */ avg(dbrav1)  avg_dbrav1     ,avg(dbrav2)  avg_dbrav2
      ,max(created)  max_cre
from source

AVG_DBRAV1 AVG_DBRAV2 MAX_CRE
---------- ---------- -----------------
.497954489 .497633494 11-SEP-2014 09:31

Elapsed: 00:00:00.96

As you can see, all I do is force the data to be collected into an internal temporary table using the WITH clause and hint it to stop oracle merging the code together and then average the columns. And now I get two different values for the two DBMS_RANDOM.VALUE derived columns.

This version of the code also accrues more runtime and statement CPU/PLSQL_EXEC_TIME, as I mentioned above, when I add more PL/SQL calls. In the below the extended list of “columns” version takes 1.78 seconds, CPU time increases from 843,750 microseconds to 1,703,125 and PLSQL_EXEC_TIME increases from 316,589 microseconds to 917,208

with source /*2 */ as
(select /*+ materialize */
        dbms_random.value  dbrav1    ,dbms_random.value dbrav2
       ,dbms_random.value  dbrav3    ,dbms_random.value dbrav4
       ,dbms_random.value  dbrav5    ,dbms_random.value dbrav6
       ,created
from all_objects
where rownum <50000
)
select/* mdw_z4 */ avg(dbrav1)  avg_dbrav1     ,avg(dbrav2)  avg_dbrav2
      ,avg(dbrav3)  avg_dbrav3     ,avg(dbrav4)  avg_dbrav4
      ,avg(dbrav5)  avg_dbrav5     ,avg(dbrav6)  avg_dbrav6
      ,max(created)  max_cre
from source

AVG_DBRAV1 AVG_DBRAV2 AVG_DBRAV3 AVG_DBRAV4 AVG_DBRAV5 AVG_DBRAV6 MAX_CRE
---------- ---------- ---------- ---------- ---------- ---------- -----------------
.499007362 .501985119 .498591643  .50252316  .49939127  .49804233 11-SEP-2014 09:31

Elapsed: 00:00:01.78

--
--
SQL_ID        PLAN_HASHV  PRSE  EXCS     BUFFS DISCS     CPU_T PLSQL_EXEC    RWS
------------- ---------- ----- ----- --------- ----- --------- ---------- ------
SQL_TXT
--------------------------------------------------------------------------------
49zjaaj41dg00 3034557986     1     1     43465   962   1703125     917208      1
with source /*2 */ as (select /*+ materialize */         dbms_random.value  dbra
0rtbx97f14b0k 3034557986     1     1     42294   382    843750     316586      1
with source as (select /*+ materialize */         dbms_random.value  dbrav1    ,

I did have the execution plans in here too but the post is already quite long. They are identical though, as is shown by the same value of 3034557986 for the PLAN_HASH_VALUE for both statements

So in Summary, the below two versions of the (logically identical as far as I can see) code give different results. The difference is that one is allowing Oracle to do the averaging natively and in the other I am forcing the data to be collected into an internal temporary table and then averaged:

select /* mdw_z1 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg_dbrav2
      ,max(created) max_cre
from all_objects
where rownum <50000

AVG_DBRAV1 AVG_DBRAV2 MAX_CRE
---------- ---------- -----------------
.502234063 .502234063 11-SEP-2014 09:31

with source as
(select /*+ materialize */
        dbms_random.value  dbrav1    ,dbms_random.value dbrav2
       ,created
from all_objects
where rownum <50000
)
select/* mdw_z3 */ avg(dbrav1)  avg_dbrav1     ,avg(dbrav2)  avg_dbrav2
      ,max(created)  max_cre
from source

AVG_DBRAV1 AVG_DBRAV2 MAX_CRE
---------- ---------- -----------------
.497954489 .497633494 11-SEP-2014 09:31

If no one can explain what I am missing, I suppose I should raise a bug with Oracle. Which could be tricky seeing as my access to MyOracleSupport is a bit tenuous…

In case you want to play, this is my whole test script, which does everything but query V$SQL for the statement stats at the end. I am sure you can manage that yourselves…

-- the two columns from dbms_random get the same result - which I did not expect
select /* mdw_z1 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg_dbrav2
      ,max(created) max_cre
from all_objects
where rownum <50000
/

select /* mdw_z2 */ avg(dbms_random.value) avg_dbrav1,avg(dbms_random.value) avg_dbrav2
       ,avg(dbms_random.value) avg_dbrav3,avg(dbms_random.value) avg_dbrav4
       ,avg(dbms_random.value) avg_dbrav5,avg(dbms_random.value) avg_dbrav6
      ,max(created) max_cre
from all_objects
where rownum <50000
/
--
-- The below forces oracle to gather the data into an internal temporary table first.
-- and then I average that
with source as
(select /*+ materialize */
        dbms_random.value  dbrav1    ,dbms_random.value dbrav2
       ,created
from all_objects
where rownum <50000
)
select/* mdw_z3 */ avg(dbrav1)  avg_dbrav1     ,avg(dbrav2)  avg_dbrav2
      ,max(created)  max_cre
from source
/
-- and now I make it do more executions against dbms_random, to see if makes a difference
-- which will lend support to my idea it is doing more contect switching.
with source /*2 */ as
(select /*+ materialize */
        dbms_random.value  dbrav1    ,dbms_random.value dbrav2
       ,dbms_random.value  dbrav3    ,dbms_random.value dbrav4
       ,dbms_random.value  dbrav5    ,dbms_random.value dbrav6
       ,created
from all_objects
where rownum <50000
)
select/* mdw_z4 */ avg(dbrav1)  avg_dbrav1     ,avg(dbrav2)  avg_dbrav2
      ,avg(dbrav3)  avg_dbrav3     ,avg(dbrav4)  avg_dbrav4
      ,avg(dbrav5)  avg_dbrav5     ,avg(dbrav6)  avg_dbrav6
      ,max(created)  max_cre
from source
/

Being part of the Oracle Scene… Quite Literally October 2, 2015

Posted by mwidlake in Oracle Scene, publications, User Groups.
Tags: ,
1 comment so far

I have some advice for you all.

Do not get drunk with people from Ireland who claim to be your friend and, if you do, agree to no suggestions they might make whilst you are drunk….

I am now deputy editor of “Oracle Scene”.

os57cover

This is the magazine the UKOUG produce 3 times a year and you can click this link to see the latest issue. There are some very nice articles in there by the likes of That Jeff Smith (product manager of SQL Developer), Fanck Pachot, Philip Brown and prior (and up-coming) issues have contributions from the likes of Jonathan Lewis, Michael Abbey, Grant Ronald, Debra Lilley…

He sucked me into it by stages, a bit like anyone running an illegal street gang might. “Just keep a look out for us…. Just go in and nod when no-one is looking…. just hold this baseball bat for a bit…”. He got me drunk the first time and I agreed to attempt to write an article. He then got me drunk again and got me to agree to write a series of articles. I think I was actually sober when I agreed to review a few submissions. But I must have been very “happy” indeed when I said “Yes” to the being deputy editor…

Being serious, I’m actually very happy to help out though it’s one of those roles that is never going to garner me much notice in the community – but it is the sort of thing the community really needs. Just like people who volunteer to help organise user group meetings or chair sessions at a conference (more on that in a day or two) or help out with the administration of something behind the scenes. Those of us who present and blog and write get some reward in being noticed and recognized. But a lot of people do stuff with little or no recognition, doing it just to help the user community be a success. Maybe we should all keep an eye out for them and say thanks when we come across them.

What To Do at OOW15 (Social & Serious)? September 30, 2015

Posted by mwidlake in Meeting notes, Presenting, User Groups.
Tags: , ,
8 comments

I’m going to OOW15 this year, my first Oracle Open World in 11 years I think. And despite the Prom Queen rejecting all my offerings, I will actually be sneaking in a presentation – which I am very happy about.

The European Oracle User Group (EOUG) get a few slots and two are being used on Sunday 25th, 13:30 – 15:15 for “More Than Another 12 on Oracle Database 12c” – 12 European experts all doing six minutes each on a couple of tid-bits on 12C, including Christian Antognini, Bjoern Rost, Brendan Tierney, Julain Dontcheff, Jonathan Lewis… plus Tim Hall and Maria Colgan if we can squeeze them in (thus “more then 12…”). It was a great success last year, so if you are going to OOW15 sign up to the session at this link to avoid disappointment. You can see more details by our organiser, Debra Lilley (thanks Debra), in her blog post about it.

So I know what I am doing for 6 minutes. What do I do for the rest of the time?

A main aim I’ll have is to try and meet up with loads of people I either only know via antisocial media or have not seen in years.

I am sure things have changed in over a decade and, I have to confess, I did not make the most of my last OOW experience. It was all rushed, I was pulled out there very last-minute (as part of being named Oracle Beta Tester of the Year by Oracle Magazine – get me:-) ) and they wanted me to be able to do some press stuff (it sounds grand – but there was not a lot of interest in me as I was utterly unknown, but I spent what seems like hours being available in case someone wanted to talk to me). To make it worse, I did not know many people out there who were not actually Oracle employees, and oracle employees found it hard to get into things as priority was given to attendees. I felt pretty alone amongst 45,000 people interested in the same Tech as me. I did not even realise I had to sign up for the top talks and by the time I knew, they were all full. I did stumble into some very good Other Talks by accident though.

So, what should I be doing? What great talks should I be signing up for and which fantastic social events should I be trying to get invited to/slotted into my agenda? I don’t even know if many events are by-invite-only…

After over a decade of doing other conferences (and helping organise a few!), I feel a bit like a conference newbie again…

All help for a lonely out-of-towner gratefully received!

Follow

Get every new post delivered to your Inbox.

Join 225 other followers