Databases Reference
In-Depth Information
detailed documentation about how to collect and read information about query
execution plans.
8.1.5 Knowing What to Use
How do you know which tools you need? It depends on the information that
you need to collect. First, determine whether the problem is related to the oper-
ating system or the DBMS. System hardware is controlled by the operating
FOR EXAMPLE
Database Optimization
Over time, users have started complaining about database performance. You
tried to write it off with the idea that no application is ever fast enough to
please all users, but the complaints become louder and more frequent. You
finally have to face facts—you have a problem.
You go through all the right steps. You monitor performance and com-
pare it against your performance baseline. You identify the most likely causes
of the performance bottleneck and take corrective action. You document
what you did and you're finished, right?
Wrong! Any time you are trying to correct a performance problem, you
have to test and verify that you've corrected it. Otherwise, how can you be
sure that the changes you made had any impact at all, let alone a positive
impact? There's a possibility that you've actually made the problem worse,
especially if your corrective action included changing database or server con-
figuration settings.
Monitoring and evaluation is at least as important during resolution and
correction as it is when diagnosing the problem. Don't forget that everything
is closely related to everything else. This includes hardware resources, operat-
ing system configuration, and database server configuration. Changes to any
one almost always have some impact on others, and not always for the better.
After you've taken your corrective action, run the same tests as you did
before when you were trying to diagnose the problem. Compare these both
to that data and to your baseline data to see where you stand. Only then,
when you have evidence that you've corrected the situation, should you ten-
tatively consider the problem solved. But you still are not completely fin-
ished. Spot check performance over time to make sure the problem doesn't
reappear. Also, go back to the users who originally reported the problem to
see if they think it's fixed. Only then should you consider yourself to be in
the clear.
Until the next problem, that is.
