The optimizer_max_permutations Parameter
Oracle Tips by Burleson Consulting
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 value is dependent
on the optimizer_search_limit initialization parameter; 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
(number of possible start tables + 1)
(number of possible start tables + 1)
For example, if we are joining five tables, we get the
permutations = 80,000/6 = 13,333
Limit = 5!/6 = 120/6 = 20
The large 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
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 to always 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, you leave a good
change 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.
tables Total number Proportion of
of possible total represented
permutations by 80,000 permutations
80,000 / n! * 100)
Clearly, there is a problem when submitting queries
where the parse phase must evaluate over 80,000 possible
In the real world, most Remote 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
Before Oracle8i (release 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.
Data warehouse SQL queries are like all other
queries, except that they experience performance issues at parse
time and at execution time. The main points of this chapter include
Oracle partitioning can greatly improve the
performance and manageability of data warehouse queries by only
accessing the partitions that contain the required data.
There are several Oracle hints to improve the
performance of data warehouse queries.
The ordered hint can be used to bypass
the expensive evaluation of table join orders by allowing you to
manually specify the table join order in the from clause.
Parallel query can greatly improve response
time for legitimate full-table scans.
You should never set a table to parallel with
the alter table command because the CBO can wrongly
reevaluate many queries to perform full-table scans.
Oracle parallel query performance depends on
the number of CPUs on your database server and the distribution of
the data files across multiple disks.
Next, let’s take a look at how the STATSPACK
utility can be used to assist in Oracle SQL tuning.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.