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

be quite large. This is why the second question is important: Does the SQL call
appear to be a promising culprit?
To find the promising culprits, the third subset (3), we need to determine the
number of result rows. If the number of FETCHes (F) is included in the exception
report (or if it is easily obtained), we can use it to provide an approximate number
of result rows. If PR/F is more than 5, the SELECT is probably apromising
culprit because a typical FETCH with a good access path should require no
more than one or two page requests per table. The most likely cause would be
a large number of unproductive random touches, many rows being touched and
then rejected.
Subset (3) contains SELECTs that cause a large number of random reads
because they do not even have a semifat index; these are the most rewarding
calls. It will also contain some SQL calls that do actually have a good access
path (e.g., SELECT COUNT in a join with fat indexes), but most of the SELECTs
are likely to have interesting access path problems.
These should first be analyzed with the Basic Question (or the Basic Join
Question, to be discussed in the next chapter). This may immediately reveal
inadequate indexing. The next step is to EXPLAIN these SQL calls to see which
access path has been chosen by the optimizer. This may reveal an optimizer
problem: a difficult predicate (too few matching columns) or a poor filter factor
estimate resulting in a wrong index or wrong table access order, for instance. If
the optimizer is not guilty, we should design better indexes.
The values CPU and READS provide useful pointers to the access path. If
CPU is close to ET, for instance, processing must be mostly sequential. If CPU
is low, ET/READS will normally be close to the average I/O time per page.
If that value is clearly less than 1 ms, most of the I/Os are sequential; if it is
several milliseconds, most of them are probably random. This will complement
the information provided in the execution plan. In addition, any SQL call with a
high CPU, say more than 500 ms, can be considered to be an interesting culprit.
When the top calls in subset (3) have been analyzed (those that have at least
one very long ET and those that are frequently quite slow), it is time to look at
the slowest and most frequent SQL calls in the possible culprit subset (2). We
may then find SELECTs that are much too slow, even with a semifat index, and
which could be made dramatically faster with a fat index; with a profile such as
this, for instance:

ET = 8s

CPU = 0 . 06 s

READS = 900

1050
F = 1000
After a few SQL calls have been improved with better indexing or by helping
the optimizer, a new exception report should be produced. The index improve-
ments may have helped many SQL calls, culprits as well as victims, but it is
possible that the next list produced may show new access path problems—mainly

PR =