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

1.3 How This Book Can Help

There are three basic steps to SQL tuning:

Figure out which execution plan (path to reach the data your SQL
statement demands) you are getting.

Change SQL or the database to get a chosen execution plan.

Figure out which execution plan is best.

I deliberately show these steps out of logical order to reflect the
state of most material written on the subject. Almost everything
written about SQL tuning focuses almost exclusively on the first two
steps, especially the second. Coverage of the third step is usually
limited to a short discussion about when indexed access is preferred
to full table scans. The implied SQL tuning process (lacking a
systematic approach to the third step) is to repeat step 2,
repeatedly tweaking the SQL, until you stumble on an execution plan
that is fast enough, and, if you do not find such a plan, to keep
going until you utterly lose patience.

Here is an analogy that works pretty well. Understanding the first
step gives you a clear windshield; you know where you are.
Understanding the second step gives you a working steering wheel; you
can go somewhere else. Understanding the third step gives you a map,
with marks for both where you are and where you want to be. If you
can imagine being in a strange city without street signs, without a
map, in a hurry to find your hotel, and without knowing the name of
that hotel, you begin to appreciate the problem with the average SQL
tuning education. That sounds bad enough, but without a systematic
approach to step 3, the SQL tuning problem is even worse than our
lost traveler's dilemma: given enough time, the
traveler could explore the entire two-dimensional grid of a
city's streets, but a 20-way join has about 20! (20
factorial, or 1 x 2 x 3 x 4 x ... x 19 x 20) possible execution
plans, which comes to 2,432,902,008,176,640,000 possibilities to
explore. Even your computer cannot complete a trial-and-error search
over that kind of search space. For tuning, you need a method that
you can handle manually.

With this insight, we can turn the usual process on its head, and lay
out a more enlightened process, now expressed in terms of questions:

Which execution plan is best, and how can you find it without trial
and error?

How does the current execution plan differ from the ideal execution
plan, if it does?

If the difference between the actual and ideal execution plans is
enough to matter, how can you change some combination of the SQL and
the database to get close enough to the ideal execution plan for the
performance that you need?

Does the new execution plan deliver the SQL performance you needed
and expected?

To be thorough, I cover all of these questions in this book, but by
far the most important, and longest, parts of the book are dedicated
to answering the first question, finding the best execution plan
without trial and error. Furthermore, the range of answers to the
first question heavily color my coverage of the third question. For
example, since I have never seen a case, and cannot even think of a
theoretical case, where the ideal execution plan on Oracle is a
sort-merge join, I do not document Oracle's hint for
how to force a sort-merge join. (I do explain, though, why you should
always prefer a hash join on Oracle anywhere a sort-merge join looks
good.)

When we look at the problem of SQL tuning in this new way, we get a
surprise benefit: the only really significant part of the problem,
deciding which execution plan is best, is virtually independent of
our choice of relational database. The best execution plan is still
the best execution plan, whether we are executing the statement on
Oracle, Microsoft SQL Server, or DB2, so this knowledge is far more
useful than anything we learn that is specific to a database vendor.
(I even predict that the best execution plan is unlikely to change
much in near-future versions of these databases.)