The _optimizer_search_limit parameter
has become a hidden parameter starting in Oracle 8.1.7. As you may know,
all parameters starting with an “underscore” are hidden, and Oracle does not
recommend changing them unless you are an expert.
The _optimizer_search_limit hidden
parameter specifies the maximum number of table join combinations that will
be evaluated by the CBO when deciding the best way to join multiple tables.
The reason is to prevent the optimizer from spending an inordinate amount of
time on every possible join ordering. The _optimizer_search_limit
parameter also controls the threshold for invoking a star join hint, and a
star hint will be honored when the number of tables in the query is less
than the _optimizer_search_limit. The default value is 5.
Most SQL tuning experts always use stored
outlines or the ordered hint for any query involving four or more
tables. This eliminates the time-consuming evaluation of the SQL parses for
table join orders, and it improves the speed of the query. However, when
tuning the SQL to determine the best table join order, it is wise to leave
_optimizer_search_limit to its default value so that all possible
table join orders are considered.
If the number of tables in the query is less
than _optimizer_search_limit, the optimizer examines all possible
table join combinations. The number of joins orders is the factorial value
of the number of tables in the query. For example, a query joining five
tables would have 5! = 5 * 4 * 3 * 2 * 1 = 120 possible combinations of
table join orders. The number of possible evaluations is the factorial of
the optimizer_search_limit, so with the default value for
_optimizer_search_limit of five, the cost-based optimizer will evaluate
up to 120 table join orders.
The _optimizer_search_limit and
optimizer_max_permutations parameters work together, and the optimizer
will generate possible table joins permutations until the value specified by
_optimizer_search_limit or optimizer_max_permutations is
exceeded. When the optimizer stops evaluating table join combinations, it
will choose the combination with the lowest cost. For example, queries
joining nine tables together will exceed the optimizer_search_limit but
still may spend expensive time attempting to evaluate all 362,880 possible
table join orders (nine factorial) until the optimizer_max_permutations
parameter has exceeded its default limit of 80,000 table join orders.
However, when tuning a SQL statement when you
plan to use optimizer plan stability to make the execution plan permanent,
it is acceptable to temporarily set the _optimizer_search_limit up to
the number of tables in your query, tune the query by reordering the table
names in the where clause, and then use the ordered hint with stored
outlines to make the change permanent.
Remember, the use of the ordered hint
overrides the _optimizer_search_limit and
optimizer_max_permutations parameters. This is because the ordered hint
requests that the tables be joined in the order that they appear in the
from clause of the query. The ordered hint is the way most SQL
tuning professionals disable table join evaluation, once the optimal join
order has been determined.
