Database Reference
In-Depth Information
Part ii
Identification
Let's work the problem, people. Let's not make things worse by guessin.
—Eugene F. Kranz
1
The first thing to do when an application experiences performance problems is obvious: identify the root
cause of the problem. Unfortunately, all too often this is where the real trouble starts. In a typical scenario,
where everyone is looking for the source of a performance problem, developers blame the database for poor
performance, and the database administrators blame both the developers for misusing the database and the
storage subsystem administrators because their very expensive piece of hardware ought to provide much
better performance. And as the complexity of the application and infrastructure supporting it increases, so
does the mess.
The aim of the chapters in Part II is to describe the key database features that you can use to find out
where and how time is spent inside the database. As you read these chapters, keep Chapter 1's advice in mind
about collecting detailed information about the processing performed by the database engine
only when
an end-to-end response time measurement points out that there might be a problem in the database layer.
Otherwise, you may end up analyzing the wrong component. And if you're analyzing the wrong component,
you're likely to fail in identifying the cause of the performance problem that you're trying to troubleshoot.
First be sure the problem lies in the database layer. Then what's in Part II can help.
The next thing to consider when dealing with a performance problem is whether you can reproduce the
problem at will. If so, then everything is much easier than if you can't. If you can reproduce the problem, it
should be quite easy to perform a controlled measurement to pinpoint the problem while the application is
having it. That's covered in Chapter 3. If the problem can't be reproduced at will, you have two choices: either
wait till the problem occurs again or look into a repository containing historical performance statistics. These
two cases are covered in Chapters 4 and 5, respectively.
Independently of whether you can reproduce the problem or not, you must discover what the most
time-consuming SQL statements or PL/SQL code invocations are. And for each of those time-consuming
statements, you should gather any additional information that can help in understanding the problem.
This additional information often includes the execution plan, key runtime statistics like the number of
processed rows and the amount of CPU utilization, and the experienced wait events. Part II doesn't describe
what to do with this information—only how to find it. Parts III and IV cover what to do after you've found what
you need.
1
This
quote is from the movie
Apollo 13
, directed by Ron Howard. It can be heard about three minutes after the famous
line, “Houston, we have a problem.”
