Oracle Optimization of Data Warehouse Queries
Oracle Tips by Burleson Consulting
Oracle provides initialization parameters to
control the amount of work performed by the cost-based optimizer
when evaluating a query. While parsing is quite fast for simple
queries, complex queries with more than six tables can parse for
many minutes while Oracle evaluates every possible table join
Sometimes, data warehouse Remote DBAs are perplexed
when they find that a 15-way table join takes 30 minutes to parse!
This is because there are 15 factorial possible permutations of the
query and over one trillion (1,307,674,368,000) query permutations.
While the ultimate solution is to employ
stored outlines to remove the parsing phase, Oracle has two
important initialization parameters that work together to control
the number of possible execution plans generated by the Oracle
TIP: If you do not have complex SQL
queries that join five or more tables together, you need not be
concerned with the optimizer_search_limit or
optimizer_max_permutations parameters. These only apply when
Oracle is computing possible table join combinations for queries
with large numbers of tables.
The optimizer_search_limit Parameter
The optimizer_search_limit 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.
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 join 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 5,
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 join permutations until the
higher of these two values is met. When the optimizer stops
evaluating table join combinations, it will choose the combination
with the lowest cost. For example, queries joining six tables will
exceed the optimizer_search_limit but still may spend
expensive time evaluating all 6! (720) possible table join orders
because the optimizer_max_permutations parameter has not been
exceeded with its default value of 80,000.
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 using the ordered hint with stored outlines to make
the change permanent.
Note: 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 their
specified order 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
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.