We are actually now on the cruise ship! I am not going to put any pictures into this posting, because the WLAN here is very slow and the upload would take forever therefore. The first session I attended today was held by Jonathan Lewis about „Design Optimal SQL“. It was very interesting and well presented – almost the whole presentation is here.
Next came Doug Burns with „Performance and Stability with Oracle 11g SQL Plan Management“. I was curious to see how he would approach that topic that was covered by Maria Colgan yesterday already. It turned out that he emphasized his Real World (TM) experiences with that feature rather than explaining it again – in a very entertaining fashion, I may add 🙂
Some key points for me were:
- SQL Plan Baselines are very useful and easy to implement.
- After an upgrade to 11g, typically a few statements will show a degraded performance. These get the most attention although the majority of statements stays with the same performance or improves.
- Collecting SQL Plan Baselines on Test Systems and deploying them (via staging tables) to Production Systems works very well and „we do it all the time“.
- Setting of OPTIMIZER_FEATURES_ENABLE=10.2.0.4 indeed returned the optimizer behavior of the previous release – that is in line with Maria’s claim yesterday about that parameter.
- Now collecting SQL Plan Baselines for these statements will first only allow the good old 10g plans with the chance to get new better plans evolved later.
- Evolvement is not as much in the focus for customers than Plan Stability.
Next I attended again Maria Colgan with „Understanding and Managing Optimizer Statistics“. Again I knew most of the technical aspects she presented (e.g. the option that we can set statistic preferences instead of having to rewrite the statistic collection completely, but got some interesting background information. She recommended strongly to use the (greatly improved) AUTO_SAMPLE_SIZE that now works much better than in 10g. It is as fast as a 10% sampling but nearly as accurate as a 100% sample size. Maria reported about a guy from our Real World Performance Group who wanted to prove this claim wrong but had to admit (after 2 weeks of trying to falsificate) that it is indeed as good and they now use it with their systems as well 🙂
So much for today. Tomorrow will be my own presentation about Real-Time Query. I’m a bit excited about it – never did that on a ship yet 🙂