 |
|
The
optimizer_max_permutations Parameter
Oracle Tips by
Burleson
|
The optimizer_max_permutations
initialization parameter defines the upper boundary for the maximum
number of permutations considered by the cost-based optimizer.
Unfortunately, with large numbers of tables, the time spent evaluating
a single permutation can be significantly greater than with fewer
tables. This means that 50,000 permutations with a 15-way table join
can take significantly longer than a query with an 8-way table join.
The optimizer_max_permutations parameter is dependent on the
optimizer_search_limit initialization parameter, and the default
value for optimizer_max_permutations is 80,000.
When determining the upper boundary for the
number of query permutations to evaluate, the CBO uses the following
rule: If the number of non–single row tables in a query is less than
optimizer_search_limit+1, then the maximum number of
permutations is the larger of
optimizer_max_permutations
--------------------------
(number of possible start tables + 1)
and
optimizer_search_limit factorial
---------------------------------
(number of possible start tables + 1)
For example, if we are joining five tables, we get the
following values:
Maximum permutations = 80,000/6 = 13,333
Search Limit = 5/6 = 120/6 = 20
The larger of these values is 13,333, and this
is the maximum number of permutations that will be considered by the
optimizer. It should be readily apparent at this point that the CBO
will be quite slow if it must evaluate 13,333 possible query
permutations.
TIP: In your large data warehouse environment with
n-way table joins, make sure you use optimizer plan stability to avoid
the time-consuming parse phase. For new production queries, try
setting the optimizer_max_permutations to a low value such as
500. For queries with more than six tables, the parse phase can take
up to 20 minutes to evaluate more than 100,000 possible query
permutations. The best advice is always to use stored outlines with
data warehouse SQL queries to bypass the long parse times.
Even with a very high value of 80,000 allowed
permutation evaluations, there is still a chance that the optimizer
may stop before it has located the optimal join order for a large data
warehouse query. Consider a 15-way table join with 15! or over one
trillion (1,307,674,368,000) possible query permutations. By cutting
off the maximum permutations at 80,000, we leave open a good chance
that the optimizer will give up too early.
The following list is intended to indicate
total permutations and what percentage 80,000 is of this number. This
may give an idea of how accurate or not the evaluation of a particular
plan may or may not be.
Number of
tables Total number Proportion of
of possible total represented
permutations by 80,000 permutations
(n)
(n!) (
80,000 / n! * 100)
1
1
Not Relevant
2
2
Not Relevant
3
6
Not Relevant
4
24
Not Relevant
5
120
Not Relevant
6
720
Not Relevant
7
5040 Not
Relevant
8
40320 Not
Relevant
9
362880 22%
10
3628800 2.2%
11
39916800 0.2%
12
479001600 0.016%
13
6226020800 0.001284%
14
87178291200 0.000092%
15
1307674368000 0.000006%
Clearly, there is a problem when submitting queries
where the parse phase must evaluate over 80,000 possible permutations.
In the real world, most DBAs size down
optimizer_max_permutations in their production environment and
always use optimizer plan stability (stored outlines) to prevent
time-consuming reparsing of the large n-way table joins. Once the best
table join order has been found, you can make it permanent by manually
specifying the join order for the tables by adding the ordered
hint to the query and saving the stored outline for the hinted query.
See Chapter 13 for details on this procedure.
Before Oracle8i (8.1.7), the optimizer
often did not make enough permutations to find the optimal table join
order. A fix is created in Oracle8i (8.1.7) to change the
algorithm used to choose the initial join orders in an attempt to
improve the chance of finding the best plan. To enable the fix in
8.1.7, a new hidden initialization parameter called _new_initial_join_orders=true
must be added to your init.ora file.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
