SQL Tuning [Electronic resources] نسخه متنی

9.3 Missing Indexes

The approach this book has taken so far is
to find optimum robust plans as if all driving-table filter
conditions and all necessary join keys were already indexed. The
implication is that, whenever you find that these optimum plans
require an index you don't already have, you should
create it and generate statistics for it so that the optimizer knows
its selectivity. If you are tuning only the most important
SQLSQL that contributes (or will contribute) significantly to
the load and the perceived performance (from the perspective of the
end user) of a real production systemany index you create by
following this method will likely be well justified.


Cary Millsap's book
Optimizing Oracle Performance
(O'Reilly), which I heartily recommend, provides a
method for finding the most important SQL to tune.

Unfortunately, you must often tune SQL without much knowledge of its
significance to overall production performance and load, especially
early in the development process, when you have only tiny data
volumes to experiment with and do not yet know the future end
users' patterns of use.

To estimate how important a SQL
statement will be to overall load and performance, ask the following
questions:

Is it used online or only in batch? Waiting a few minutes for a batch
job is usually no great hardship; end users can continue productive
work while waiting for a printout or an electronic report. Online
tasks should run in less than a second if they are at all frequent
for a significant community of end users.

How many end users are affected by the application delay caused by
the long-running SQL?

How often are the end users affected by the application delay per
week?

Is there an alternate way the end user can accomplish the same task
without a new index? For example, end users who are looking up
employee data might have both Social Security numbers and names
available to search on, and they need not have indexed paths to both
if they have the freedom and information available to search on
either. Some performance problems are best solved by training end
users to follow the fast paths to the data that already exist.

Compare the runtimes of the best execution plan under the current
indexes with the best plan under ideal indexes. How much slower is
the best constrained plan that requires no (or fewer) new indexes? A
nonideal index to the driving table, or even a full table scan, might
be almost as fast as the ideal index, especially if the driving table
is not the most expensive part of the query. A missing join-key index
can force a plan that drives from a second-best-filtered or even
worse driving node, where the alternate node has access to the whole
join tree through current indexes. How much worse is that? The only
way to know for sure is to try your query both ways. Alternatively,
try hash joins when join-key indexes are missing, and see whether the
improvement is enough to obviate the need for new indexes.

Estimate weekly lost productivity for online delays by using the
length of each delay times the frequency of the task per end user
times the number of end users. Summed delays of days per week add up
to serious lost money. Summed delays of a couple of minutes per week
amount to less than you might save by buying an extra coffee maker to
save employees steps during coffee breaks; don't get
carried away going after the little stuff!

Consider overriding external effects too. For example, online delays
for a live customer-support application that cause customers to find
a new vendor can be disproportionately expensive! Similarly, batch
delays that trigger penalties for missed deadlines might carry huge
costs. When SQL delays are costly and the improvement for adding a
new index is significant, don't hesitate to add the
index. Otherwise, consider the trade offs.

It is far easier to add indexes than to get rid of them! Once an index is in production use for any length of time, the perceived risk of dropping it is high. It is next to impossible to ensure in advance that an index can be safely dropped. After an index is in production, the initial justification for the index is soon forgotten, and new code might become dependent on the index without anyone realizing it. The time to say "No" to a poor index is before it becomes an established part of the production environment that end users depend on.