If set to less than its default of 80000, this parameter controls the number of join orders that will be considered by the cost-based optimizer. The maximum number of join orders considered is actually half the value of this parameter plus one. The extra join order considered is the first join order from the next top-level partition of the search space. This is only done if less than 4 tables have been considered as the driving table. If set to less than its default, this parameter also prevents more than 10 join orders from being considered in each OR expansion branch.

The parameter is available from 8.0.5 (and was backported to 8.0.4.2 as event 10180 and to 7.3.4.2 as event 10181). For 8.0 it is only documented in the readme.txt file. The setting of 1000 proposed in the documentation may sound low, but it is not really. Oracle only does an exhaustive traversal of the search space for Cartesian joins and you would need to have a 6 way Cartesian join, or a complex join topology involving numerous tables, for the optimizer to have more than 501 joins orders to consider.

If your long parse times are due to the consideration of large OR expansions, then you can set this parameter to 79999 for relief. Otherwise, you will need to set it to a much lower value (like 1000) to get significant relief from long parse times.

For completeness, I will mention that there is a related parameter _optimizer_search_limit that establishes a lower bound on the values of optimizer_max_permutations that will be respected. By default the search limit is 5. This means that Oracle will do an exhaustive examination of the search space for a 5-way Cartesian join, regardless of the setting of optimizer_max_permutations. For more complex joins, Oracle uses the factorial of the search limit instead of the optimizer_max_permutations value if an attempt has been made to set that parameter to a lower value. By default this means that optimizer_max_permutations values less than 120 (5 factorial) will not be respected. Oracle will consider at least 61 join orders regardless.

optimizer_max_permutations doesn't show up in the Oracle documentation (8.0.6), so can I rely on it?

Reducing optimizer_max_permutations from 80000 to 1000 is quite a lot. How can I be sure, that there's no negative impact to all other queries without testing the 6 applications sitting on that database?

Are there any recommendations/experience values for optimizer_max_permutations?