jump to navigation

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!

Friday Philosophy – Antisocial Social Media and Sociopaths September 25, 2015

Posted by mwidlake in Friday Philosophy, humour, Twitter.
Tags: , , ,
1 comment so far

Twice, on consecutive days a week or so back, I realised that someone I knew on Twitter but did not know in real life… was someone I knew in real life. But I’d never made the connection! With one person it was not that their real name was missing from their twitter profile, it was just my brain had linked all I knew about them to their handle. In the other case, as far as I can see there is nothing to link their slightly obscure handle to their physical persona, despite the fact they are tweeting quite often about Oracle and also present at conferences. So that was simply not playing fair to hide their real identity in that way and I feel slightly aggrieved.

I’ve also had the experience of meeting someone in the flesh who treats me like an old friend, is being nice to me {possibly some sales-person-type I should back away from, I initially wonder?} but also knows a fair bit about me {Oh no! Creepy-stalker-type! Must-run must-run must-run} – before it dawns on me that this is actually Randolph Toddlepoddle who I have known online for 5 years, comments on my blog and I respond. But I’ve never met. And who is now wondering why I am being so unfriendly, am backing slowly away from them with a fixed grin on my face and scanning for exits. This has actually happened to me several times now. Thankfully only once with each person (I think – names & faces are not my forte}.

XKCD comic 741

I have a relationship with you lot? {shudder!}

The result is that I am sure that for some people I have two utterly separate relationships with them – the online one and the in-the-flesh one. (According to xkcd comic 741 I have a relationship with you as you read my blog. When can I meet the parents?).

Another aspect of social media I feel is a little tricky for me, personally, is keeping track of what people have said to me and things I’ve said I’ll do. I have a poor memory, I can barely remember conversations last week. With email I can file them away and find them later (mostly I just file them away and wonder why my email directory is so massive). But with Twitter and Facebook comments? OK, so you can search but it is slow and it is not great. Only today (as I type) I remember being given some advice by my friend Brendan about writing articles. I went and checked my email ( under “friends/Brendan” or maybe “ora600/articles”, I can’t remember). Nope. Could I have put it elsewhere in my email store of information and event? Nope, no where in my email I could find. Ahh, it was a twitter conversation. Damn. Now I need to step back and find it…

Maybe there is an app to tie all this stuff together for me but I would have to find it and learn it and the vendor will get bored or go bust in 2 years and I’ll lose the lot then. I’d rather mow the lawn.

Then there is the much-commented-on aspect of online comments where some people seem to sign up to a service or follow someone, just so they can be snide or criticise. No, this is not the usual rant about these phalluses (phalli?), It’s more that I don’t read user comments on the BBC web site much anymore as it lowers my already pretty sociopathic outlook on the human race (don’t get me wrong, many individual humans are wonderful animals – but as a pack they are a nasty and destructive species). It’s not that there are nasty or thoughtless people who put these comments up, we learnt there are people like that in the school playground (or even in the classroom – Mr Jenkins, you know who you are). It’s just that seeing what people can put on social media reminds me more about how dysfunctional people can be than meeting people in the flesh does. Being able to have some control over people you meet in the flesh means real people don’t tend to enhance my sociopathic tendencies as much as social media.

I follow a couple of “humorous” twitter accounts. They put the same stuff up all the time, sometimes it’s obviously fake and they “borrow” from each other like crazy. But it’s just a few tweets and if I find the repetitive nature of it or their take on humour gives me less amusement than annoyance, I can always do that “unfollow” thing. I am not in any way being forced to be exposed to it. I don’t have to start commenting all the time about “you got this from redit!!!” or “You spelt that wrong you moron” or “That’s not funny, I can tell it’s a paper bag on a baby”. I made the mistake a couple of weeks back of responding to one saying “Dude, thanks for pointing out the totally obvious, it had CLEAR passed me by”. Yeah, I told you I had sociopathic leanings – I went and did what they had done to annoy me, there was no need for me to read the comments if I knew it would annoy me.

Of course, I could just stop joining in; close my twitter account, delete the blog, remove my inconsequential presence on facebook. But then, I’m now in all these relationships (sometimes two or three times with the same person). How can I break up with so many people?🙂

Friday Philosophy – The Issue of Exceptions in your Mailing Lists September 18, 2015

Posted by mwidlake in development, Friday Philosophy, Perceptions.
Tags: ,
1 comment so far

A couple of days ago I got the below email about the next UKOUG Database Server SIG being full. That’s great to see! A full user group meeting. If I still wanted to attend, I could be added to the waiting list:

ScreenHunter_42 Sep. 14 11.25

It made me smile as, guess what, I was already due to be there. I was the last speaker on the agenda! It was going to be awkward if I failed to get from the waiting list to an actual place at the event, it could be a very quiet session…

As I said, it made me smile – I’m not having a go at the UKOUG over this. Because, as I’m sure many of you are aware due to the day job, removing people from a mailing list who are no longer going to be interested is not always easy (I know, it should be easy, but it just seems not to be). Anyone registered for an event should be on an exclude list for the event. But only for that event. And for that to occur you have to make sure that all speakers and committee are registered (as committee and speakers can be very poor at actually registering!).

Sales and promotion communications are one thing and, let’s face it, in the scheme of things damned unimportant – except to the company doing the sales. If you sell clothes and you mess up on your communications to me, I’ll have a poorer opinion of your company and I don’t mind having that poor opinion. I won’t buy your clothes, no skin off my nose. As a recent example on a personal level, my mother was having a new door and window fitted. She was contacted by a company that replaced a couple of her windows the year before and as the service was fine then and she knew she had to have the door replaced, she said yes. But then she got called by them again a month later asking if she wanted any more windows or doors – “But I just ordered from you, have you lost the order?”. They apologised and said it would not happen again. But it did, a couple of times – including the day before the new door was to be fitted, whilst she was waiting for a call about the exact time for the work the next day. A lesson in How to Confuse A Little Old Lady. The end result is, she won’t use that company again. Especially as the new door seems to have a fault they won’t come and fix. Maybe the inability to take a current client off the pester-list should have warned us off them.

But there are other communications where the need to take care of the exclusions or keep the lists timely are far, far more important. People get really upset if the hospital sends a reminder for a checkup to their father – who died last month, in that hospital. In that situation people are so sensitive that they can’t just accept it as an administrative cock-up. It is now seen as an affront to their dead relative’s memory and a sign the hospital do not care.

An even worse situation than the above is if the hospital wrongly records you as dead. It happens and it happens “quite often” as it is very hard to keep individual, accurate records on people who keep going and changing names, addresses and have the same name and date of birth as other people. I know this as I once had to write the software to do all the test cancellations and notifications required internally in a hospital that occur when someone dies. And I also had to write the software to undo all of that when someone realised the wrong person had been recorded as dead. {The functions were initially called “kill patient” and “resurrect patient” – They got renamed pretty damned quick when people outside the medical staff saw them. Medics have a dark sense of humour that most other people don’t!}. It happens and when you see how many people in a large hospital system have very similar details, you can appreciate why. I bet that right not, somewhere in the UK, is a hospital with two people of the same name & date of birth as inpatients and at least one has a chance of not surviving the experience.

As for sets of duplicate records as no one realised Sarah Twoddypottle is the same person as Sarah Poddytwoddle who came into A&E 4 years ago and neither knew their NHS number… 20 years ago I could have done a PhD on the topic of duplicate patient records, the situation was so bad – and not at all helped by people wanting “privacy” ie no joining up of national medical records. I digress.

People get similarly upset about money (some more so than medical!) – any attempt to offer someone a loan who is already in debt and won’t be accepted for a loan causes all sorts of anger and annoyance. It’s all seen as personal by the wronged customer when in fact it is just an impersonal business function. No, they don’t care about you Mr Postlethwaite, but they don’t not care about you either. You are just one of a million customers. Level of care for the individual is not part of the equation.

I’ll finish on an interesting one. Companies that offer gambling services have to abide by some strict rules about who they can promote their services to or even allow to use their services. To try and halt the horrendous increase in people gambling away money they don’t have there are strict legal rules about self-exclusion and cooling off periods. If you get into serious debt due to gambling you can state you are an addict and it was partly the fault of the company you gambled with. So they must not encourage you any more. At least not for 6 months. {NB I am not a lawyer, I may have the exact details wrong, but the gist is right}. After the time period they are allowed to advertise to the person again as it would be wrong and immoral (???) to exclude them forever. Besides, they are a good source of revenue…

It’s important to get your communications correct, timely and exclusion-aware. And just consider in what situations people might be especially sensitive to what they see as an impersonal system not caring about them or their nearest and dearest. Most people find it hard to accept that such mistakes are not personal, even when they are blatantly impersonal.

Friday Philosophy – If You are reading this You are probably Pretty Smart September 11, 2015

Posted by mwidlake in Friday Philosophy, humour, Knowledge.
Tags: , , ,
10 comments

I bet I can predict a few things about you. And I mean You, the person reading this on my Blog right now.

At senior school (when you were aged between 11 and 16-ish) you were at the top of your classes. At least most of them. You were in the top few in your year for most science subjects and probably Maths too.

And you were not bad at any of your subjects once you had a choice over them, but there was at least one subject you were glad to get rid of as you were poor at it. But if you think back, I bet you were simply *almost average* at it, there were as many kids or more worse at it than you then there were better than you at it. You were just not as stand-out good as you were in other subjects. Come on, I’m right aren’t I? Even your poor subjects you were OK at compared to all the other kids.

You almost certainly went to college and, if you are under 35, you did to study a STEM subject – Science, Technology, Engineering & Maths. If you are 40-50 you did not study computing but you ended up working in it anyway. 35-40? You either studied computing, thought about it or took options in your course that had a major element of computer programming.

However, you were probably not quite the smartest kid in your year at school.

What makes me think this? Because I can mind-read(*)?

No, I think this simply due to the fact that you work in corporate IT or something very similar. You use your brain to solve issues or get tasks done. I think that the generally high level of intelligence is a more common trait in IT than the other thing I can probably predict about you – you don’t feel you are a people person, not really. Using myself as an example; I present at conferences, I have run training courses and I usually have something to say in a large group or discussion; I even partly run London Oracle Beers, a social Oracle user group. But I can’t make small-talk with people I don’t know and I am uncomfortable meeting new people.

We have to be intelligent to make a career in IT and most of us were probably drawn, at least in part, towards computers and software as we could work on it on our own and the computer did not have any feelings to dent or make any social demands on us. However, many (most) people are social nervous and so I think the more defining trait of people in IT is that they are intelligent – and intelligent in the way that IQ tests measure intelligence: A mixture of learnt patterns and various problem solving/logic/deduction skills. If you have had your IQ tested I am sure you are 120+. I would not be at all surprised if you score 130+. But you still have a little way to go to beat… my wife.

You probably were not the smartest kid in your year at school as the smartest kid either went into medicine/vet school, academia, law or crime – something more way-out-there.

So what point am I making? None really. It’s more just an observation. Though I do think you should occasionally take time to say a prayer of thanks to Pythagoras (or whoever you think is listening) and remind yourself how lucky you are. Most of your intelligence is inherited and the smaller (but not insignificant part) is due to schooling and your own efforts (note, I’m talking intelligence, not what you know – the two are different but aid each other). Your brains were given to your by your parents and grandparents, no matter how hard that can sometimes be to believe🙂. I was on a PL/SQL course with Steven Feuerstein about 10 years ago and he took a minute or two to passionately state how lucky all of us on the course were to have our brains, to be making our living by thinking and not back-breaking toil or dull manual work. It was a sentiment I whole-heatedly agree with. I actually love spending time digging a ditch or chopping down trees but I would hate having to do it every day for a living.

Of course, intelligence of the IQ type is not everything and it does not make you a better person. I’m sure we have all known some very smart assholes and some wonderful people who can’t think themselves out of a damp paper bag. Some people have average IQ and yet have talents most of use would struggle with, like making a violin sound anything but bloody awful. I’ve known academics with an IQ somewhere Way Up There but who had about as much common sense as a pigeon. I have come across a few examples of intelligence bigotry in my time too. I know one guy in an academic institute who tried to insist that the highest grade you could achieve and the top of your salary band be dictated by your best academic qualification. No one without some sort of degree should get above level 5, No one with less than a 2(i) allowed in grade four and to get to grade 2 a PhD was needed. He was a very smart asshole. I’ve known a couple of people without a degree in this business of IT and both of the ones I’m thinking of right now are very, very good at what they do.

So be grateful for that brain of yours and just remember that most people are not as intelligent as you, so show patience in explaining and working with them. And if you are not patient, you could well be an intelligent asshole. You might need to learn to not be like that.

(*) Just as an aside, deducing things about groups of people and, in fact, traits most people have is not hard. It’s called cold reading. It’s what mediums, mind readers, psychics and other intelligent assholes use to hoodwink people. Part of it is things you can guess at given one piece of information or even none. I can deduce things about you because you work in IT. I know you feel you’ve never reached your potential and you have more to give as *almost everyone does*. No one wants to be seen to be selfish but we all know we are, at least at times. Even Mother Theresa thought so at times. The other aspect to it is reading body language and empathy, which is why I can’t make a living as a psychic. I just don’t get people….

Just thinking on this aside for a second, maybe mediums and psychics could get less abusive jobs as data analysts? Deducing things about people based on averages and correlation is Big Data Business right now.

(Update – thanks to the person who quietly contacted me to point out my spelling error/poor grammar with “patience” and “patient” – in my section in intelligent assholes too! He showed real patience with me)