Mogens Norgaard: No Cary, we're still not tuning the right stuff. (ODTUG 2006 )

Right after the Oracle Quiz Lucas and I presented last week on ODTUG, I went to Mogens Norgaard’s presentation. Here is a short report from that session, you may have read the announcement on
Dimitri Gielis’ Blog.
Besides talking about his firm, Miracle AS– they also brew beer, he gave an overview of "Tuning through the ages" and proposed a new way of tuning as well.

It started out with, what he called, the G&G technique of tuning. G&G stands for Guessing and Grimassing. "Lets try this and if it doesn’t work
let’s try something else."
After that came the "Tuning By Layers" principle: first you tune the IO, next you tune the contention, next the memory and so on.
Since that didn’t work either a System wide approach was introduced. But the trouble with that is that there are multiple interpretations
when people look at the same data. High ratio’s can be good, but they can also be bad. Not a really reliable way of analyzing a problem.
"Health Checks", when is a system healthy? Or when is a system sick? Impossible to determine. Again, there are multiple interpretations when different people look at the system.
Apparently Oracle also tried with Metrics, to gather all kinds of values and determine performance issues. The person that goes with this methology
is Dave Ensor. As a side note he called Jonathan Lewis the next Dave Ensor. Mogens told that Dave Ensor retired several times but always returned. The prediction Mogens
gave was that Jonathan Lewis will do the same. He will be studying and writing about the Cost Based Optimizer and will continue to do so with Oracle releases 13, 14 and so on.
"YAPP" (Yet Another Performance Profiling Method) was introduced by Anjo Kolk and kept track of response times. This still wasn’t good enough and "Method R" (According to Mogens: "Method Are,
that is not correct Cary. It should be Method Is…") and this formalised things.
This last method basically tells you to:

1. trace at the process level
2. find the biggest contributer time-wise
3. make it faster
4. repeat until happy

Crowd Control

What will the future bring, because "No Cary, we’re still not tuning the right stuff"?
He propsed a "Method I&KTTM, Identify and Kill the Trouble Maker". Sometimes a query which takes a long time to execute, simply takes a lot of time. What you
need to look out for is the change in execution time of queries. Why is this query all of a sudden taken much more time? Why did it change? This is of course a
lot harder to track than simply identify the query that takes the most time.
While this way of looking at tuning might seem strange at first, Mogens used a great analogy to clarify this view. This is where the crowd control comes into play.
When you have a crowd of people, how do you control them?

• At random lock up people and hope that you picked the ones that caused the uproar
• Pick out the biggest, tallest guy you can find and beat him until he is numb?
• Would you pick out the ones that are causing the problem and try to negogiate with them… and them lock them up?

Obviously you would choose the last bullet, this makes the most sense. The same is true for tuning. You need to identify the troublemakers, in this case the SQL-statements not
necessarily the creator of the SQL ;-), and modify these. Find the cause of why this query became so expensive without apperent reason. The troublemaker is not necessarily the
SQL which takes the most time.

Conclusion

His sense of humor really made this presentation a lot of fun. He also raised some interesting ideas on tuning. This could be the start of another way of looking at tuning. I can’t wait for writings on this topic.
Too bad there is no paper on the ODTUG conference CD. Maybe next week on their site when the presentations/ handouts will be available. Come to think of it, there were no handouts…