Databases Reference
In-Depth Information
How it works...
In the first step, we have to elaborate a baseline, because without a comparison element we
will not be able to know if the adopted solution really solves the problems we are facing.
The kind of baseline to elaborate depends heavily on the performance issue. There are some
performance indicators which should always be checked, while others are more detailed
which can be verified only if a previous indicator points to a particular area of the database.
After the baseline is decided for the particular problem we are investigating, it is time to
automate the process of gathering data, so it is repeatable.
While investigating the problem the process is iterative, so you can return to the previous
step to add other elements to the baseline, for final testing of our solution.
When the investigation drives us to assume a particular solution, before we start
implementing it on the database we have to list all the changes we are going to do and
elaborate a "rollback solution" for these changes. This is especially the case if we don't have
the chance to test our solution over a test database similar to the production one which is
suffering the problem. If we think, for example, that adding an index
IX1
on table
T1
could
solve our performance problem, we have to prepare a SQL script to create the index, and
another SQL script to drop it, in case we want to go back if something goes wrong. In Oracle
11
g
, we have the opportunity to create an invisible index and check the execution plan of
the query, with minimal impact on other sessions.
We might want to prepare a test-case to test the solution we will implement. This task is
simpler if we have isolated the problem very well, so we are able to reproduce the issue. If
the problem is random, it might be a nightmare to isolate the steps that lead to poor
performance. In the latter case, we could evaluate the frequency of the problem, so we
could test our solution by measuring the number of occurrences and comparing the results.
After the solution has been implemented, it must be tested with the same process that
created the baseline. Check the results of the measure process and decide if the solution
has solved the issue. If the results are not acceptable, iterate the whole process until there
is a satisfactory outcome.
There's more…
The performance tuning process is a never-ending cycle; even when we solve our performance
issue there will be another aspect of the system we can tune to in order to obtain better
performance, or we need to satisfy more stringent requirements.
