3 Steps To Performance Tuning (Working Class Hero) July 3, 2008
Posted by Richard Foote in Oracle Opinion, Performance Tuning, Richard's Musings.trackback
Last night, I answered a question on the Database OTN forum regarding Database Re-Org and Performance Tuning. I thought it might be worthwhile sharing my response here as it’s something I feel quite strongly about.
Basically my response to the question of what basic steps one should follow when performing performance tuning was:
1) Identify an actual problem that needs addressing, one that’s problematic to the business, not one that only exists in some statistic or in one’s imagination
2) Determine what’s actually causing the problem as identified in Step 1.
3) Address the specific issue as identified in Step 2.
It all sounds rather obvious but it’s amazing how many don’t follow these 3 basic steps and attempt to jump straight to Step 3.
Unless you perform Step 1, you can’t accurately perform Step 2 which means you’ll only be guessing when performing Step 3.
The secret to performance tuning is not to guess …
I’ve lost count of the number of times I see people guessing at what a problem might be and hence get it all wrong …
The number of times people waste time and resources on problems that aren’t really problems and hence make no measurable difference …
The number of times people throw hardware at a problem without fully considering whether additional hardware will actually resolve the problem and hence waste money and resources for no measurable benefit …
The number of times people jump straight to applying a solution to a problem that they haven’t properly or correctly diagnosed and hence don’t actually solve the issue …
The number of times people attempt to resolve a problem by focusing on the symptoms rather than the root cause, only to fail dismally …
The number of times people are lucky and fix a problem by guesswork and by fumbling around in the dark without understanding why it fixed the problem, only to attempt the same thing again at another time and for it to fail dismally …
Like I said, the secret to performance tuning is not to guess.
John Lennon was once quoted as saying his secret to writing music was to:
1) Say what you want to say
2) Make it rhyme
3) Put a back beat to it
Three basic, fundamentally important steps. He would have made a good DBA 🙂
Richard Foote's Oracle Blog 
One I’ve seen too many times is
Perform steps 1 and 2, determinte the resolution will actually require code changes, meet resistanc eto change from managment and developers so throw hardware at the problem prommising to fix the code at a later date… Then forget to go back and actually fix the root cause and go back to step one.
LikeLike
Hi Chris
Oh yes indeed 😦
LikeLike
People who want to learn performance tuning might try Christopher Lawsons ‘The Art and Science of Oracle Performance Tuning’. It describes five roles of performance tuning:
-The role of a physician who listens to the patient what the problem is.
-The detective who goes on a fact-finding mission.
-The pathologist who determines the root cause of the problem.
-The artist who thinks up a solution for the problem
-The magician who implements the solution.
When people are guessing, they ignored the first two steps and go straight to determining the root cause.
In my experience listening to the customer is not only something “you should do”, but also makes the customer feel you take his or her problem serious.
And in performance tuning you can never do enough detective work. And I don’t mean querying v$ views, but actually talk to people: Why do you run this? What does it do? Why did it perform last week and not now? What has changed? etc.
LikeLike
Thanks from BAAG party. 🙂
LikeLike
Hi Marcel-Jan
I totally agree listening to the customer is important. That’s why I specifically mention the problem or issue being addressed in step one should be one that impacts the business, not a DBA guided statistic.
Sometimes just as important as listening is actually seeing what is causing the customer the issue by sitting at the desk with them and experiencing the issue with them as it can give a vital perspective of what is really going on.
Sounds like a decent book …
LikeLike
Hi Alex
Sounds like a worthwhile party 🙂
LikeLike
I think there should be a step 0: gather configuration information. Unless you are already familiar with the system, you just don’t know what-all is there. I just can’t believe I’m the only person in the world who’s ever walked into a place and seen default O7 init parameters, or worse.
Oddly enough, I went to elementary school with Chris Lawson, had lost touch, then didn’t realize for a while he was the same guy when I saw his work in the Oracle world. Besides that, I liked his book as a how-to-dba intro, at least when it was current.
LikeLike
Hi Joel
But I only wanted to have the 3 steps 😉
I agree although perhaps step 0 could be incorporated into step 2. But yes, understanding the current environment and configuration is important.
LikeLike