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

6.1 Robust Execution Plans

A subset of
all possible execution plans can be described as
robust. While such plans are not always quite
optimum, they are almost
always close to optimum in real-world queries, and they have
desirable characteristics, such as predictability and low likelihood
of errors during execution. (A nonrobust join can fail altogether,
with an out-of-TEMP-space error if a hash or sort-merge join needs
more space than is available.) Robust plans tend to work well across
a wide range of likely data distributions that might occur over time
or between different database instances running the same application.
Robust plans are also relatively forgiving of uncertainty and error;
with a robust plan, a moderate error in the estimated selectivity of
a filter might lead to a moderately suboptimal plan, but not to a
disastrous plan. When you use robust execution plans, you can almost
always solve a SQL tuning problem once, instead of solving it many
times as you encounter different data distributions over time and at
different customer sites.

Uncertainty and error are inevitable in the inputs for an optimization problem. For example, even with perfect information at parse time (at the time the database generates the execution plan), a condition like Last_Name = :LName has uncertain selectivity, depending on the actual value that will be bound to :LName at execution time. The unavoidability of uncertainty and error makes robustness especially important.

Robust execution plans tend to have the following properties:

Their execution cost is proportional to rows returned.

They require almost no sort or hash space in memory.

They need not change as all tables grow.

They have moderate sensitivity to distributions and perform
adequately across many instances running the same application, or
across any given instance as data changes.

They are particularly good when it turns out that a query returns
fewer rows than you expect (when filters are more selective than they
appear).

In a sense, a robust plan is optimistic: it assumes that you have designed your application to process a manageably small number of rows, even when it is not obvious how the query narrows the rowset down to such a small number.

Robustness requirements imply that you should usually choose to:

Drive to the first table on a selective index

Drive to
each subsequent table with a nested loop on the index of the full
join key that points to a table that the database already read,
following the links in the query diagram

Nested loops on full join keys generally scale with the number of rows that match query conditions and avoid memory required to execute hash joins or sort-merge joins, for which memory use might turn out to be excessive. Such excessive memory use can even lead to out-of-temp-space errors if the cached rowsets turn out to be larger than you expect.

Drive down to primary keys before you drive up to nonunique foreign
keys

Driving down before driving up avoids a potential explosion of rows earlier in the plan than you want when it turns out that the detail table has more details per master row than you expected.

If you consider only robust plans, robustness rules alone answer the
first two questions of finding the best execution plan, leaving only
the question of join order:

You will reach every table with a single index, an index on the full
filter condition for the first table, and an index on the join key
for each of the other tables.

You will join all tables by nested loops.

I later discuss when you can sometimes safely and profitably relax
the robustness requirement for nested-loops joins, but for now I
focus on the only remaining question for robust plans: the join
order. I also later discuss what to do when the perfect execution
plan is unavailable, usually because of missing indexes, but for now,
assume you are looking for a truly optimum robust plan, unconstrained
by missing indexes.