Databases Reference
In-Depth Information
To describe a good baseline we need as much data as possible; most are acquired directly
from the database itself, as we will see in the next section. There is information from other
sources: Operating System logs, performance counters, application logs, trace files, network
statistics, and the like.
In today's multi-layered applications, it's simple to say "the database is slow" when an
application is suffering poor performance, but there will be many cases when the database
is performing very well but the application responsiveness is very weak.
With a solid baseline, we can isolate the layer in which the problem first occurred and
concentrate our efforts on that application layer. After a baseline is established, start
investigating the problem.
In the rest of the topic, we will learn how to interpret the results of the baseline to correctly
identify the problem. Sorry, there isn't a bullet list or a magic wand; this phase is based on
knowledge and previous experience. If a simple causal-effect was in place, it would have
already been coded with an automatic solution or a specific diagnostic advice, implemented
in the database itself. There are several automatic diagnostic tuning features in the latest
releases of Oracle database; SQL Tuning Advisor, SQL Access Advisor, Automatic Database
Diagnostic Monitor. These database-centric tools help solve common performance problems,
which tend to be easily identified. The real tuning process starts when the magic doesn't work,
or they don't work as good as we need them to.
We have seen the most common database performance issues in the previous recipe, divided
into several categories to help us in the investigation phase. During this stage, we decide what
database area is a bottleneck; for instance, the memory, the I/O, and the SQL code.
Once we have identified and delimited the database area involved in the performance
problem, we can assume a solution to the issue. As previously stated, both a test case and
a rollback strategy are necessary—the former to check the proposed solution, the latter to
revert back if the proposed solution wasn't satisfactory.
Once we have the solution, implementing it is often a trivial task, such as writing a small
SQL script to alter a database object or a initialization parameter. Be sure that the solution
is implemented using reproducible steps, especially when the task is quite complex or we
have to test the solution in a staged database before the production.
At the end of the implementation, we have to test the solution to verify its correctness—probably
in a test environment—and to know if the expected performance gain has been reached.
To test the solution there are various scenarios, depending on the work done in previous
steps and by the development team. A test case will verify the results; if there are application
test sets, they can be used to verify the correctness of the solution, especially if the
application logic has changed.
