Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter