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

The execution plan shows that the optimizer scans a slice, defined by

predicate Lname 'Lname265' (Index Seek), of CUST X1 index (LNAME,

FNAME), maintaining the order of the index rows (ORDERED) to avoid a sort.
For every index row in the slice, it checks the corresponding table row in table
Cust (Bookmark Lookup) and accepts only rows that have the right value in
column City (Filter).

=

Conclusion

Call-level exception monitoring, with the basic information assumed above,
reveals many index problems with minimal effort. However, due to many false
alarms, it may take a long time to find all index problems that cause significant
response time problems, much longer than with LRT-level exception monitoring.
Identifying SQL calls that are likely to be promising culprits would become
easier if the reports were to show more information than just the four values
shown in the examples. The number of result rows and the number of random
reads to tables would be most valuable. Ideally, one should have all the figures
we have shown in the bubble charts.

DBMS-SPECIFIC MONITORING ISSUES

From the index design point of view, the most significant differences between
environments (the DBMS and the operating system) today are those involved in
performance monitoring. Many other limitations can be overcome; for example,
if the maximum index key length is too short, a table can be created, maintained
by triggers, to be used in its place. It is, of course, impossible to measure the
components of the local response time if the system does not provide appropriate
trace records, or provides them with an inadequate degree of accuracy.
If it is not possible to produce the spike reports, bubble charts or SQL call
exception reports described in this chapter, index design must be based mainly
on predictions, quick estimates like the QUBE, or those made by the optimizer.
The spikes can be detected by timing the transactions and SQL calls, or by
users' complaints. It may then be possible to trace the program to determine the
elapsed time of each SQL call and the number of page requests. The slow SQL
calls should then be EXPLAINed and analyzed accordingly.
A QUBE for such a call, using the access path reported by EXPLAIN, may
be helpful in determining the main causes for the long response time.
In his excellent Oracle tuning guide (2), Mark Gurry shows the SQL ∗ Plus
commands, which reveal the SQL statements that will be slow— according to
the optimizer estimates:

IdentifyBadSQL

The SQL statements in this section demonstrate how to identify SQL statements
that have an expected response time of more than 10 seconds. The assumption