Boneist's Oracle Blog

Wednesday – #ukoug_tebs Day 3
So, what with Tuesday evening’s shennanigans, I decided to give the first presentation of Wednesday (Joze Senegacnik’s “Execution Plan Stability in Oracle 11g”) a miss, and get more of a lie in. As I had packed before bed, there wasn’t much to do after I’d got up (there was method to my madness after all!), so more time in bed! *{;-)

Randolf Geist – Everything you always wanted to knw about FIRST_ROWS_N but were afraid to ask
Randolf is another person that I know and respect from the OTN SQL and PL/SQL forum, and I had been looking forward to attending this presentation ever since I’d booked to come to the conference.

I’ve never really used the ALL/FIRST_ROWS hint before, although obviously I knew about their existence beforehand; I’ve never needed to do Top-N or pagination queries before, and ALL_ROWS (the default) has always been sufficient for me, especially since I’ve usually needed… all the rows!

Randolf stated right at the beginning that he’d intended to reveal everything about how the ALL_ROWS_N hint/optimizer mode worked, but that he’d had problems with the results, which caused problems with his theories. I thought this was a fairly odd way to start the presentation, but after a while, it became clear that this was still clearly an area that Randolf was still experimenting with, to fine tune his theories.

I found it a fascinating insight into how one might go about working out what the database internals are actually doing, as well as learning more about the ALL_ROWS_N hint/mode. I did want to go over to introduce myself afterwards but unfortunately someone else had already had a similar idea, and I didn’t like to barge in. I should’ve gone up and stood waiting (hind-sight, oh how you’re 20-20!); how much does it cost to just say “Hi, I’m Boneist from the forums, just wanted to say ‘hi’ and great presentation!”?! D’oh. Next time, definitely.

Rob van Wijk – All about grouping
Poor Rob had developed a cold by the time he came to present *{:-( However, he managed not to sniffle at all (impressive!) despite sounding more and more full of cold as the presentation went on! My heart went out to him and I kept wanting to hand him a tissue, just in case he needed one!

The presentation was informative, and I’ve picked up a lot about how the grouping sets, cubes and rollups work as well as how they interact. Sounds like the optimizer still has a way to go on working out ways to identify cubes, given a mix of grouping sets, rollups etc. This was one of the presentations I was really looking forwards to, as I had always been confused about the extended grouping stuff. However, work wanted me to see if it was possible to do stuff in “my” database that currently gets done in another one further downstream in the lifecycle of our data, so I’d had to work out myself what the grouping sets, rollups etc is about. So, I knew a bit more about them than when I had originally booked to come to the conference.

The presentation clarified things for me, and I now feel confident in knowing how to use and manipulate grouping sets/rollups/cubes etc to try and get best performance out of them. Thanks Rob!

Again, Rob had people talking to him at the end of his presentation, so I left without talking to him *{:-( Still, at least we had met, so that wasn’t quite as bad as it could have been, but even so…

Wolfgang Breitling – Anatomy of a SQL Tuning session
I actually met Wolfgang at the Fire party on the Tuesday night, and he seemed like a really nice, down-to-earth kinda guy, although perhaps a little insulted that I’d only managed to skim read the paper of his that Doug had sent me a link to! (I didn’t have time to read it fully, but skim read it so I at least know to go should I need to use that information!) I’m not sure I managed to dig myself out of that particular hole, though. *{;-) I’d had this session in my agenda for a long while, so I wasn’t attending it just to make it up for my boo-boo of the previous night!

It took me a little while to click that we were taking a SQL statement that he had tuned and were going to run through what steps he had done (um, I blame my stupid headache for that!), but once it had, I sat back and enjoyed his demonstration. Feedback by cardinality; which sort of ties back to what Jonathan Lewis did in his presentation on Monday (“Writing Optimal SQL”) – I think a combination of using pictures to diagram the query plus this feedback by cardinality is a good place to start when trying to tune SQL statements; I shall certainly be using both methods to try and do that from now on.

I really enjoyed the session, even though the last step Wolfgang did was to use quite a specific set of hints to force the behaviour he wanted! Whilst I know that some hints are good if you know more about the data than the optimizer does (eg all_rows), I disagree with trying to force production code down one particular path, based on information that’s “correct” as of today – I think all that does is postpone the problem until tomorrow/next month/next year (delete as applicable).

However, I do understand the limits we all have to work under (upset users, irate managers, no time for investiaging proper fixes, etc, etc) and I guess hints really are the quick fix, albeit likely temporary!

The End
And that – as they say – is all, folks! The end of the conference!

I headed off back home, sad that it was all over, but glad to be going home and meeting up with my partner. Less happy to be heading back to work the following day, it has to be said! My 6am alarm was rather more of a shock than normal!