Database Reference
In-Depth Information
■
not all hints can be stored in stored outlines. to know which hint can't be stored, you can run the
following query:
Caution
SELECT name FROM v$sql_hint WHERE version_outline IS NULL
even though most of the hints that can't be stored in stored outlines don't impact execution plans (for example,
gather_plan_statistics
), some of them do (for example,
materialize
and
inline
). as a result, there are some
execution plans that can't be forced through a stored outline without specifying a hint in the SQL statement itself.
One of the advantages of a stored outline is that it applies to a specific SQL statement, but you don't need to
modify the SQL statement in order to apply the stored outline. Stored outlines are stored in the data dictionary, and
the query optimizer selects them automatically. Figure
11-2
shows the basic steps carried out during this selection.
First, the SQL statement is normalized by removing blank spaces and converting nonliteral strings to uppercase.
The signature of the resulting SQL statement (a hash value of the SQL statement's text) is computed. Then, based
on that signature, a lookup in the data dictionary is performed. Whenever a stored outline with the same signature
is found, a check is performed to make sure that the SQL statement to be optimized and the SQL statement tied to
the stored outline are equivalent. This is necessary because the signature is a hash value, and consequently there
could be conflicts. If the test is successful, the hints making up the stored outline are included in the generation of the
execution plan.
Figure 11-2.
Main steps carried out during the selection of a stored outline
