Reactive Index Design - Relational Database Index Design and the Optimizers - page 121

For lock victims, the tuning potential is equal to the lock wait time, 9 s in
Figure 7.10. For other victims, it is equal to the relevant component in Other
Waits time.
Finding the Slow SQL Calls
Having identified the dominant component of the SQL time (SQL CPU time,
Synchronous Read, Wait for Prefetch, Lock Waits, or Other Waits), the next step
is to find the SQL calls that contribute the most to this component (it should be
realized that the figures shown in the bubble charts may well refer to a large
number of different SQL calls). This can be done simply by reading the SQL
statements of the dominant module. If the problem is due to CPU time or I/O
time, many statements may be excluded immediately, for instance, those that
access a row using the primary key (WHERE PK = :PK). Another way is to use
an SQL trace for the program if one is provided by the DBMS. These reports
show the elapsed time, together with the main components of the elapsed time,
by SQL statement.
The most commonly used (and affordable) trace is provided at the module
level. In this case, identifying the slow SQL calls becomes easier the smaller
the modules. From the tuning point of view an application built with one large
module is a nightmare. At the other extreme, some modules may contain only
a single SQL statement. The SQL statement that contributes significantly to the
local response time is then identified directly from an exception report.
When the SQL calls have been identified and shown to be caused by inad-
equate indexes, improvements need to be made according to the techniques
addressed in Chapters 4 and 5.
CALL-LEVEL EXCEPTION MONITORING
Reports that show the slowest SQL calls in a monitoring period are the next best
thing after LRT-level exception monitoring. They can also be of great assistance
in finding the slow SQL calls in a slow program that has a large number of
different SQL statements.
We will assume we have taken a peak hour trace and produced a list of the
worst SQL calls, based on the longest elapsed times during that period; here we
will be dealing with individual calls, not the averages . Let us further assume
that the tool we are using, as is often the case, only provides four relevant
measurements for each call; these are (together with the abbreviations we will
use): the elapsed time (ET), the CPU time (CPU), the number of pages read from
disk (READS), and the number of database page requests (PR). A database page
request is called get page in DB2, logical I/O in SQL Server, and get or LIO in
Oracle; to keep everything as simple as possible, we will use a single term that
reflects what they all mean—page requests (PR); refer to Figure 7.11.