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

DBMS-Specific EXPLAIN Options and Restrictions
The EXPLAIN features of many DBMSs have been enhanced significantly over
the past 10 years. As well as a compact report producing a single line per execu-
tion step, graphs are available at the statement level. The suspicious access paths
may even be marked in some EXPLAIN outputs. The statistics on which the opti-
mizer's decision was based, together with a list of all the current indexes, and
their characteristics, for each accessed table may be provided, which is extremely
useful; without this, it would be necessary to access this information from the
system tables separately. The best EXPLAINs have already integrated the next
desirable step: to propose index improvements.
On the other hand, the current EXPLAINs still have a few irritating short-
comings. DB2 for z/OS V7 does not report index screening (rejecting a row
by using column values copied to the index row). This shortcoming does not
directly affect index reviews, but it does mean that some optimizer problems
are only revealed by measuring the number of table touches. Oracle 9i does not
report the number of index columns involved in the screening process; in fact it
doesn't even report the number of matching columns either (the PLAN TABLE
column SEARCH COLUMNS in Oracle 9i is not currently used). Oracle 9i does
report the type of index scan: UNIQUE SCAN (one row), RANGE SCAN, FULL
INDEX SCAN, or FAST FULL INDEX SCAN. Thus, the most suspicious access
paths are revealed, but the lack of the MC value makes it more difficult to ana-
lyze optimizer problems caused by difficult predicates (sometimes called index
suppression in the Oracle literature).
MONITORING REVEALS THE REALITY
Fortunately, most DBMSs now provide a trace that records the components of
the local response time, or at least the number of events taking place, such as
physical reads. The overhead of the required trace activity, at one time somewhat
prohibitive, now tends to be quite acceptable: In DB2 for z/OS V7, for instance,
it is a few microseconds of CPU time per SQL call. For a typical operational
transaction this means an increase in CPU time of a few percent, and even less for
the elapsed time. However, for a batch job that is mostly issuing simple FETCH
calls and causing sequential scans, the elapsed time may go up by as much as
20%.
Compared to EXPLAIN, measurements are a superior starting point in the
analysis of a slow program; this includes access path problems as well as all
the other reasons there may be for long elapsed times. However, reading monitor
reports may take a huge amount of time, particularly if a good monitoring strategy
is not implemented.
Measuring the performance of a computer system is a complex task—it can
be viewed from many different angles. The simple spike report , introduced later
in this chapter, is the result of a long evolution. An understanding of its history
will make it easier to appreciate the essence of the recommended approach.