Hotsos 2010 - Day 3 - An excellent one (part 1)

Well, that was a nice start to the day! Someone came up to me just before Wolfgang Breitling's presentation to point out that something from my presentation had helped him fix a problem at work last night. It was the OEM Raw Data drill-down that shows you the underlying ASH data for sessions, including backgrounds. He'd used it to identify the timed event leading up to a problem with a crashing smon process. Actually, the more people I speak to, the more I get over the presentation. Most people recognised I made the best of a bad situation but I'm glad that someone actually learned something, too!

Wolfgang's "Anatomy of a SQL Tuning Session" was one that I managed to miss at UKOUG and I'm glad I made it this time. It revolved around taking a single SQL statement that took 9 minutes to execute and walking through various tuning iterations, using modern tools and optimiser possibilities, leading to a sub-second execution time. It was a natural extension of Wolfgang's Tuning by Cardinality Feedback that I've often recommended to colleagues. He covered a variety of techniques including :-

- Converting parts of the statement to scalar sub-queries
- Subquery factoring
- Transitive closure, with a useful tip that it may be worth experimenting with specifying apparently redundant join predicates to give the optimiser more information to work with.
- Using the Outline part of 10g DBMS_XPLAN to identify the set of hints that would create a specific plan and then using some of them yourself (but this is far easier if you name your query blocks).

What I particularly liked about this presentation was the way that Wolfgang illustrated execution plan steps and changes with some nice slides, highlighting a few steps at a time. But it made me feel a lot better about my mobile phone going off the previous day when Wolfgang's *own* mobile went off during this one

Next up was Neil Gunther with "How to Quantify Oracle Scalability - Part 1", a presentation about applying his Universal Scalability Law (USL) to Oracle systems. There were quite a few high-level points I picked up from this.

People often assume that the purpose of a model is purely to predict the future but it's probably just as (or more) useful as a method of validating test results because in his view 'Data comes from the devil and models come from god'. I'm not sure I agree with that. Test results, even incorrect results, represent reality to me that can't just be explained away by Maths which doesn't agree with them but I suspect that's my peculiar perspective. I much preferred the suggestion that we use models and data together because, whilst he might not trust my data, maybe I don't trust his model yet?

He talked about how the USL allows for the phenomenon of reduced throughput as workload increases which is something I think I've seen before by adding Coherency to Amdahl's Law. The USL is definitely worth more investigation. As a non-mathematician, though, I suspect I always struggle with this stuff.

Which was why I was *so* relieved that I made the tough decision to skip Riyaj's presentation and stayed for the second part of this two-hander by Peter Stadler - "How to Quantify Oracle Scalability - Part 2". This was a more practical examination of the USL in relation to Oracle systems and as someone who is very interested in performance in general and the relationship between Response Time and Throughput in particular, this hit the spot. What was slightly bizarre, though was when I recognised the URL for this blog post and the test results come up on screen. I think I'm right to say that this is the second consecutive Hotsos Symposium where this one post has been discussed (by Cary Millsap last year) so I must be doing something right

Peter spent the next 20 minutes or so talking about some of the comments on the post and plugging the results into the USL. I must admit to being slightly surprised by the fact that Peter didn't think to drop me a mail to let me know he was going to talk about it so much because I might have missed it and it was fascinating! He talked about the lack of detail in the results, but that was because the blog post had an extremely simple message - are you looking for High Throughput, Low Response Times or both? Regardless, if he'd asked me, I could have given him some more information to work with. For example, there was some discussion about measurement errors in data and performing multiple runs to address that which is something I did, but only published one set of fairly representative results.

So it was all a bit strange and unexpected, but utterly fascinating to see someone apply a mathematical approach to my empirical results. I hope that Peter might post the slides and add a URL to the blog post so that everyone can share what he found. I think that's the point of the comments thread and of blogging in general - sharing information and knowledge and building a discussion.

Next I managed to eat a little bit of much-needed lunch with Paul Matuszyk and then had to get ready for my important work call.