Hotsos symposium 2006, day 2...
That last one was Wolfgang Breitling’s talk. I really enjoyed that one for the simple reason it is a method I follow myself. The crux of the matter is – you have a query that should perform better, you know it can. But the optimizer has generated the wrong plan (you know it did). Rather than just hinting it and fixing that one, sole query – you can try to figure out “why” (and hence fix an entire class of poorly performing queries in the process, instead of tuning by hand every single one). The premise: bad query plans result from incorrect cardinality estimates. If you want to “tune a query”, look to the estimated cardinality values (explain plan or v$sql_plan) and compare them to the actuals (row source operation in a TKPROF report, v$sql_plan_statistics). His approach, one I share, is to do that and then try to come up with a way to “fix it” – to get the optimizer to achieve the correct cardinality. It might be optimizer dynamic sampling, it might be setting/gathering more detailed statistics – but it will fix not only that particular query but an entire class of queries that might suffer from the same issue. I really liked this one.
Doug Burns and Eric Emrick also have nice write ups as well.
The other thing I like about some of these conferences – getting to meet people I only know “electronically”. Hotsos has been great for that year after year – I first met Jonathan Lewis in person here back the first year I came. This year I got to meet lots of new people, including David Aldridge:
Seen here with “Shrek” (you might recognize those names from this blog). You gotta like what David’s shirt reads “I make stuff up”.
Last night, Gary Goodman from Hotsos invited me to their customer dinner. They rented two limo’s and we all piled in. It reminded me vaguely of the caving experience – it was a long dark space that you had to crawl through. The limo was much much larger than the cave tunnels – and didn’t have all of the dirt.