So why does Justin Bieber owe me £120?
Well ... while I was on the Finnish boat, I started to see a few tweets from people who were planning to attend the conference in Oslo about the lack of hotel rooms being caused by Justin Bieber playing three nights in Oslo during the conference! Sigh. All I can say is that I'm not so sure that kids in the UK would be packing out hotel rooms to attend a gig. Can't these people go straight home afterwards or something? Someone pointed out to me later that it was even worse than I thought. Apparently (although I have no evidence of this), people were booking multiple hotel rooms in the hope that one of their bookings would be at the hotel where JB himself would be staying! That's why I'd struggled to find any decent hotel rooms in Oslo the week before. My partner works in business travel and tried to help out at the time, followed by the dire warning that 'there must be something going on in Oslo that night'. OUGN Conference? I didn't realise it was *that* big!
Which meant that by the time the boat arrived back in Helsinki, I still didn't have anywhere to stay in Oslo that night. I was counting on Mark Rittman to have tracked down some local party house but I think he probably blew it with his Jupiter Ace T-shirt and rubbish chat-up lines. Instead, I was lucky to be helped out by my friends Heli Helskyaho and Tuomas Pystynen of Miracle Finland Oy, who took me in hand, drove me to the Miracle offices and let me use their network so I could start hunting down hotels. Fortunately I managed to blag one of the few remaining rooms at OSL airport which would mean that I wouldn't be able to meet up with friends in the centre but I would at least be able to have a bed for the night! However, the price came in at £270 and, even allowing for the fact that hotel is usually pretty expensive, I reckon it would probably have cost me around £150 max on any normal night. So your card has been marked, Mr Bieber!
An unexpected upside to the Miracle solution to my problem (They are good at solutions) was the constant yummy smell coming from the food factory under the office which was helping me build up an appetite so the three of us headed back into the centre of Helsinki to meet for a final lunch with Alex and Debra. I wish I could remember the name of the mixed meat stew with loganberries that I had but it was very tasty and apparently quite traditional. (See, Mark, it's not all Fish Fingers round my way!) I did consider the reindeer but I'm not sure I could have looked the Cuddly Toys in the eye when I got home.
Then there was just time for a trip to the airport on the unsurprisingly efficient and cheap airport bus and managing to lose Debra while I was checking in and then having a smoke. I'm sure she thinks I did it deliberately but she really was gone in a flash, so I'm blaming her I stupidly missed out on valuable lounge time, too, not realising that HEL is one of those few airports where it's still possible to smoke airside. But nothing could get me down now that I had somewhere to sleep and it wasn't too long before I arrived at OSL to find myself in a surprisingly pleasant hotel. I might even stay there again if Bieber stays out of my way.
One long sleep, a fantastic breakfast and a typically enjoyable and efficient Norwegian train experience later and I was hanging around the ferry terminal watching speakers arrive from around the world. Because I'd been at the much smaller and more intimate OUGF, I'd forgotten just how many people attend OUGN and was quite taken aback by the massive crowd hovering around the terminal, waiting to board. However it was a fairly enjoyable wait as several OUGN people were there to take good care of me as always and I know so many people who attend now and don't see too often that I wasn't exactly stuck for a conversation.
Once boarded, it's was time for Duncan Harris' keynote covering some of the history of how Oracle has addressed security issues in it's product and, particularly for an old guy like me, it was funny to be reminded just how far we've come. There's always scope for improvement, of course.
Then it was time for Cary Millsap's "Learning about Life through Business and Software". I'd already seen this keynote at UKOUG where there was almost universal agreement that it was terrific but I could listen to Cary presenting all day and this was no exception. However, I'm a smart guy and previous attendee so I played my master card of leaving his presentation just before the end (much easier to do in the show theatre venue) so that I could avoid the rush to collect my luggage and get to my room before the time that it was supposed to be available officially It worked like a charm, just like last year but perhaps I shouldn't have given my secret away!
Which also meant that I was about to head for an early lunch and avoid queuing there - all important stuff as I was presenting immediately after the lunch break. Imagine my surprise, though, when I discovered that they had converted the smoking room inside the conference centre (that I frequented in the nervous build-up to presentations during previous years) into another conference room and it was where I was due to present! Probably someone's idea of a little joke
The main challenge of the presentations at OUGN was that the presentation slots at OUGF were 60 minutes and I had only asked for 45 minute slots at OUGN. All my own fault as the conference agendas were organised at different times. The Fast ETL processes using native Oracle features probably worked the better of the two and I was surprised to see a completely packed room because Cary was presenting at the same time. Even Oyvind came to my presentation, but probably only because he'd seen the other one before
After that the rest of the afternoon was all about catching up with some more sleep as I was still feeling pretty unwell and I vaguely recall spending a good bit of time with Mark Rittman. There might have been some beer involved, but a good bit of work and business chat too and crowing about just how great the iPad mini is. Mark's a complete Apple Fanboi of course, but it was nice to be able to agree with him about that stuff for a change
I know there was more eating and drinking and night-club time after that (Martin Nash is a bad influence on me ), but, erm, well my memory of that evening is a bit hazy. It *was* a long time ago, after all
As Connor McDonald hadn't been on the Finnish boat, I took the opportunity to catch his Odds and Ends presentation the next morning. He was excellent as usual although I might not have processed what he was saying too well on that particular morning! Let's put it down to nerves about how on earth I was going to get through the 60 minute 10053 trace presentation in 45 minutes. In the end it kind of went ok although, as I vaguely recall Connor tweeting or saying to me later, if I'd spent a bit less time talking about drinking beer later it might have helped! To be honest, it was starting to feel like a very long week and so I was just glad to have finished my presentations to be able to go onshore to Kiel with some friends for lunch.
Most of what happened next eludes me now although I vaguely recall hanging out with some of my *proper* mates from overseas over a quiet beer or two whilst Martin Widlake was serenaded with Happy Birthday by a much louder crowd in a nearby bar. The only reason I remember most of this is because I uncovered this awful photo of Gerald listening intently to Cary Millsap and Tom Kyte. I suspect he thought he'd learn something about Oracle, but the conversation was far more interesting than that!
By the time I'd had some sleep and maintained my tradition of only waking up as our arrival into port was being announced, there was only time for a last few beers (something of a pattern emerging here) in the Oslo sun and then it was time for the trip home.
Thanks again to several good people of OUGN and OUGF for making the double conference a wonderful, if somewhat tiring, experience.
My almost traditional pre-conference illness (maybe it's because these things are in Autumn and Spring or maybe it's pre-conference stress - who knows?) and the last frantic bits of work for the release on the horizon meant that I had very little time to work on my two new presentations so when I set off for Helsinki on Sunday, it was with laptop powered up and ready to go. Fortunately I had managed at least half of one of the presentations before I got ill. What I didn't have yet was a hotel room in Oslo for the Wednesday night because when I got around to checking on Friday, there was literally nothing. I was utterly baffled (really, how often is there *nothing* except for a hostel?) but decided I could sort that out later ....
The time difference stole another two hours from me (these things seem more signifcant when your back's against the wall) but it was a pleasant, uneventful trip and a quiet night in the hotel prepared me for setting sail on Silja Serenade the next morning.
Having never spent any time in Helsinki, I decided I might as well walk to the boat and Helsinki looks like a beautiful city that I'll be going back to at some point. Once on board and unpacked in my beautiful sea-view room, I realised that there was a stowaway on board! He has a habit of getting lost ...
Tom Kyte and Bryn Llewellyn had the good grace to give keynote presentations using slides that I'd seen several times by now which gave me most of the first day to work on ... (you get the picture). Never have I been so happy to presenting on the second day of a two-day conference But I was very keen to see Melanie Caffrey's 'Keeping It Simple in Database Application Development'. She'd been giving this presentation at Oak Table Sunday last year at the same time as my 8-bit presentation, so I'd missed out then.
Although I know Melanie through her being a regular at the annual UKOUG conference, I'm not sure that I've ever seen her present much before, but she was as engaging and smart as I expected as she went through some of the lessons learned in her role as a Senior Development Manager at Oracle, working on linux.oracle.com. I wish I could remember more of them now, but sometimes I'm so busy listening to the messenger that I can miss some of the message! LOL
I'd warned Melanie how weird it is to be presenting just as the boat sets sail because I remember it from my first time and, sure enough, stuff started clanging, the boat started humming and swaying ever so slightly - it's an experience everyone should have at least once She also learned just how polite and quiet Finnish people are when in a sober audience of more than a few people. Never try a rhetorical question to the audience - you're likely to just get stoney silence back! However, they truly come into their own when you add beer or have a more private conversation - somewhat like most Scots I know!
After that, Heli Helskyaho (@HeliFromFinland) gave the OUGF 25th Anniversary Speech, flipping effortlessly between Finnish and English which is about as impressive as it gets to my ears. Although I have to say, it seems that this 25th Anniversay thing is a bit debatable. Is it 25 or 26 or .... I think someone forgot to start counting!
One of the most enjoyable aspects of a boat conference is the food, and we had one of the first of many great meals, all with plenty of wine thrown in and I was lucky enough to have Melanie, Debra Lilley and Alex Nuijten for company so it was a great way to wrap the day up, particularly as I had almost finished my slides
Tuesday was a day of worrying about and deliverying two presentations and feeling not a little unwell. It wasn't so much seasickness as the continuation of the long-running cold that I'd shared with lots of other people, so I was a little tense when I started presenting "10053 Trace Files - Mostly Harmless", particularly as Tom Kyte and Melanie were in the audience, knowing Tom's encyclopedic knowledge of Oracle! But it seemed to go reasonably well for the first time giving this presentation, but with some areas that I can improve, particularly some more useful examples.
Then there was time to actually eat some lunch (which is an achievement for me on presentation day) before I moved on to "Fast ETL Processes using Native Oracle Features". But im the lead-up, I felt really sick and I was concerned I might perform the impressive but disgusting trick of being physically sick mid-presentation! I mentioned the possibility on Twitter, so Alex made sure he turned up with his phone at the ready so he could tweet the evidence! Fortunately I got through it ok. Again, it probably needs a little more polish but the small room was absolutely packed and it went well enough. What I needed most was to lie down and rest, which meant that I missed Alex's Analytic Functions presentation again (something I have a long personal history of doing ...)
It was worth it though, as I was in a much better state when I made it back to the conference area just in time for Heli's wrap-up session. As well as having a prize-giving raffle from the numbers on peoples conference badges during which some really inconsiderate delegates walked off with all of the Cuddly Toys ;-), Heli tossed balls around the room to elicit feedback from whichever attendee caught the balls and she asked what was their best thing about the conference. At first I thought it might be embarassing and took a while to get going (Scots and Finns have a lot in common!) but it actually worked really well because I don't think anyone would have volunteered otherwise Maybe the real reason I liked the idea was when one of the delegates said that his favourite thing was my presentations, which was brilliant to hear. I always speak at a lot of conferences with great speaker line-ups, so I've never actually been told that in the past! Others who I won't mention are used to it on a regular basis *cough* Jonathan, Tom, Cary ... *cough*
Which put me in a great mood for the final dinner which was delicious, as all the catering was. Did I mention? But if you could have seen Bryn, Tom and Melanie *piling* the starters on to their plates, you would know I wasn't exaggerating. In fact, there were so many interesting and tasty options (particularly for those with wider tastes than me) that a lot of people just about managed a bunch of starters and then a few puddings. Between the company and the food, I didn't think it could get much better, but then I was voted Speaker of the Day. Again, that's something that never happened to me and I probably shouldn't care, but I'm afraid I did, particularly when I considered the other speakers on the agenda. Of course, I cheated a bit by doing two presentations on one day but, hey, I'll take what I can get Heli presented me with a Moomin mug and some whisky
In the interests of actually submitting a blog post, I'll leave it there for now. Justin Bieber? He can wait!
Rather than just describing the contents of the trace file, I thought it might be a good idea to tie the various sections into how they might help you solve Real WorldTM problems. Which might not be immediately obvious when the first example I use is the trace file for :-
SELECT * FROM DUAL;
But here it is. The first thing to note is that it's a 66KB file of over 2000 lines, even for something so trivial, which is just a taste of just how massive these files can be. It will also be environment and version-specific, as you'll see. Such is the nature of low-level trace files.
Going through the initial sections at a very high level, we have ....
Lines 1-20 - The standard type of trace file pre-amble that you might have seen in other trace files including
- The trace file name
- Instance and version information
- Host and O/S information
- Session Instrumentation of the type set by calls to DBMS_APPLICATION_INFO
Then we get to the first 10053-specific information (lines 22-27) which registers the various Query Blocks in this query. Understandably there is only one entry here in the QUERY BLOCK SIGNATURE section, were Oracle automatically names the query block to SEL$1
Line 28 is a note from SQL Plan Management highlighting that this statement does not already exist in the SQL Management Base.
Lines 29-32 contain a note that 11g Auto-DOP is disabled and at this point hopefully you'll start to see that if you already have a reasonable understanding of the CBO and related features, the trace file is actually pretty descriptive and verbose. From memory, I'm not sure it was always as easy to read.
For more information on Predicate Move-Around (as mentioned in lines 33-35), this 1994 VLDB paper is worth a look. Of course, when your statement is SELECT * FROM DUAL, there aren't exactly a lot of predicates to move around!
Next we go into a long section describing OPTIMIZER INFORMATION gathered from a variety of sources.
Line 40 shows the SQL statement and dont underestimate how important this is as further confirmation that you're looking at the right tracefile.
Lines 42-94 are a very handy Legend that lists the abbreviations that are used in the trace file. Some of these might have been guessable but, with so many terms used, it's great that you don't have to guess any more.
Lines 95-419 are a section that I often find very handy in solving issues with bad plans in two different database environments. The classic case of a developer telling me that it runs fine in Test but not in Production. The developer might send along the two plans and, even with a couple of good SQL Monitoring reports or the output of DBMS_XPLAN, that doesn't really tell me why the two plans are different, just that they are different. Working in an environment with multiple Dev, Test and Prod environments, it's not unusual to find that there is some drift in the instance configurations or someone has different session parameters set. It's a quick job to just open up the two trace files in a visual diff tool and make absolutely sure that the parameters the optimizer references (and you'll see just how many there are these days!) are truly identical.
It's just a small tip, but you'd be surprised by the number of issues that's helped me identify!
Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the information it is using and the results of it's calculations whilst walking through the decision-making process to determine the best execution plan. It includes the options that it has considered and discarded, those that it has accepted and options which are unavailable for various reasons. Because the decision making process is detailed and extensive, the files tend to be large for all but the most trivial statements and I'd challenge most people to read and understand an entire 10053 trace file! However, you are often looking for the reason for a particular bad decision, which helps to narrow the scope and, personally, I've found recent versions of 10053 trace files much more verbose and readable.
The best and most detailed reference I've seen is Wolfgang Breitling's paper 'A Look under the Hood of the CBO' although it was written many years ago and, as with all low-level undocumented Oracle information, things change frequently and without warning. At the start of the paper he describes how to set event 10053 to generate the related trace file in the instance's user dump destination. However, since Oracle 11g, there is a more flexible way to generate the trace file that Maria Colgan describes in a couple of posts on the Optimizer Development Groups blog here and here. The latter post is particularly interesting because that approach automatically triggers a hard parse of the statement in order to generate the trace file.
Which solves what I suspect is one of the most confusing aspects of generating 10053 trace files when you're first getting used to it. The statement needs to be hard-parsed to ensure that the trace file will be generated. It might help you to remember what it is that is being traced - the CBO making it's decisions as it chooses an optimal execution plan. So, if the plan has already been generated then no trace file! One simple technique to get around this prior to 11g is to add a new comment to the statement to force a hard parse. Remember that if you find that the trace file is not being produced as expected, that might be the reason.
In the next post, I'll start to look at the contents of a 10053 trace file.
Mar 17: 10053 Trace Files
I remember a while ago that Neil Chandler wrote a blog post about why you probably don't need 10046 trace files that I didn't completely agree with and I kept thinking I must comment on it or write a post from a different perspective. Neil's a mate, a London Oracle Beers regular and I understood where he was coming from in that post. He makes some excellent points, so I recommend you read it. But I don't think it's so hard to get a trace enabled these days as people think. You don't necessarily need to change a line of code with the availability of the DBMS_MONITOR package and (although I accept this is unusual) the client I've worked for most recently makes trace file access very easy for developers. We also have ASH, AWR and SQL Monitoring though and I tend to agree with Neil that the times when 10046 trace files are truly required to solve problems are limited. Now I read this back, it sounds like I agree with him, but I know that I use 10046 trace files more regularly than the post suggests. I would say 10046 trace files are incredibly useful sometimes. In fact, when they are useful they are the only correct tool for the job.
But the real reason for referencing Neil's post is this statement.
"I have never used a 10053 trace on a Production system. I have simply never needed to know the decisions taken by the optimizer in that much detail."
I've had this discussion with most well-known experts in the Oracle community at some point or other and there's general agreement that there's no need to bother with those pesky and ridiculously geeky 10053 trace files most of the time. Most SQL performance problems simply aren't that complicated. I've probably agreed whenever the argument has come up but the fact is that I increasingly find myself using them and it worries me a little that 'most of the time you don't need them - concentrate on the basics' is heard as - 'don't ever bother looking at them - they won't help' and I've been tempted to redress the balance for a while.
But something stopped me. 10053 trace files are exceptionally long and contain a lot of information that I wouldn't begin to pretend I understand and so if you can't cover a subject properly and it is quite a technical subject then, not only are you in danger of doing people a disservice but you are also opening yourself up to all sorts of challenges, corrections and debates. But, hey, that's what a community and what blogging is all about - people learning from each other. I'm also not a big fan of any technical writing which is about how clever the writer is above it actually being useful! The more I know, the more I find myself avoiding the wilfully geeky stuff.
I still wasn't sure but at least one person at my current client has been badgering me about this for ages so I've decided, what the hell, I'm going to write a few blog posts about the things I find useful about 10053 trace files and hopefully give some very high level hints about how you might use them too.
If it's not technical enough for some people then tough and if I go astray, there are plenty of people out there who probably know the subject much better who can keep me straight (Just off the top of my head, I can think of Maria Colgan, Jonathan Lewis, Wolfgang Breitling, Christian Antognini .... well, it could be a long list).
Enough of the intro, but let me finish by saying what I think is the most important reason to use 10053 trace files. There are many posts about inaccurate row source cardinalities leading to bad plans (This is one of my favourites). But I believe that it's actually only the experts who can compare E-ROWS and A-ROWS and make educated guesses about why the cardinalities are wrong. (To give you an example, many is the time that Jonathan Lewis has said to me - 'Oh, that looks like a classic Optimizer 5% guess' - or words to that effect, anyway.) But most of us can't just 'see' those things when we look at a plan. At best, a 10053 trace file offers the possibility of knowing why the CBO picked the wrong plan.