Remote DBA Plans
Remote DBA Service

The optimizer_max_permutations Parameter

Oracle Tips by Burleson Consulting

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 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

optimizer_max_permutations
--------------------------
(number of possible start tables + 1)

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 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 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 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.

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 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 procedure.

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.

Conclusion

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 these:

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.