Oracle Performance: LAST Cause is not the same as ROOT Cause

The Smear: How Shady Political Operat... Rule #1 Don't Be #2: You Get What You...

Oracle Performance: LAST Cause is not the same as ROOT Cause

[image error]

We use the "Cache Hit Ratio" Method

Looking for "What Changed" not so Helpful

When faced with a new problem to troubleshoot, many smart people immediate ask the question, "What Changed?" This seems logical, but is actually a weak way to solve a performance problem.

By looking for recent changes, you are making a questionable assumption. You are assuming that everything was indeed fine until the problem was reported. That is a bad assumption--especially in the world of performance tuning.

Just because something was running pretty well before, that doesn't mean it was well-designed; it could just mean you sometimes "get away" with a poor design.

In philosophy, the most recent cause is called the "Proximate" cause. The root cause is called the "Ultimate" cause.

Two examples will clarify:

Example 1: Caching Effect

A poorly designed sql statement requires 100,000 disk reads. The designer is not aware of how poorly the code is written, since the report runs "okay." Normally, all the blocks are "cached up," so that the performance isn't too bad. The key is, good performance requires all the blocks to be cached. Even a small reduction in caching will have a disastrous effect.

Then, other jobs are added to the server, and the caching goes down from 99% to 90%. Suddenly, the report takes much longer. The users mistakenly search for the "proximate" cause, and ask, "What changed?" They don't realize that the code was poor to begin with, and suggest that some other job is causing their bad performance.

I have personally worked on many cases just like Example 1. It is very common for users to blame another job, when their own design is really the root cause.

Example 2: Unstable Execution Plans

In this case, the sql is overly complicated, with lots of bind variables and awkward joins. The optimizer struggles to figure out a good execution plan. Sometimes it gets it right, sometimes it doesn't. On the current server, given the current statistics and perceived bind variables, the optimizer has a decent plan, the sql runs well.

Now, the same code is run on another database. Stats are re-gathered, and new queries are run. This time, the code runs horribly. The users again ask, "What changed?" assuming that that question is a good place to start.

Lessons Learned

I am reluctant to begin any investigation with "What changed." A good DBA normally has far better methods to use.

Lesson #1: You Don't Really Know What Changed

[image error]

I like to use the "Rule" hint

Folks mean well, but rarely does anyone really know for a fact what has changed and what hasn't. How often do people say, "We haven't changed anything," but really can't guarantee that.

Lesson #2: Some Things Always Change

Even when a database is cloned to another server, many things will soon be different, even when everyone says, "We haven 't changed anything." For example, the bind variables specified will usually be different. How the optimizer assesses the bind variables can also change. Here's another: The caching effect will always be different, depending on recent jobs run. When a user claims the the report being run is identical as before, they cannot really know that for a fact.

Lesson #3: Poor Design Can Yield Varying Performance

You can write terrible sql, and sometimes you get away with it; the optimizer happens to get it right in your particular case. The problem is, it's an unstable solution, and can easily shift to a bad solution. The slightest change--such as stats, how bind variables are used, size of objects, or caching can trigger plan changes and miserable results.

It would be easy to get caught up analyzing all the factors that cause plan changes, but they are not relevant to the root cause.

Wrap-up: We can Do Much Better

By looking for "What has changed," you are aiming at the wrong target.

By focusing on recent events, you are actually analyzing the consequence of the bad design, not isolating the bad design itself.

Of course, I must admit, you can sometimes guess the right cause, but the "What's Changed" method is not a consistently good approach. We can do much better.

DBAs have excellent ways to find root cause. Here's one: Become adept at using the Active Session HIstory views.