Re: optimizer_max_permutations

[Tanel Poder]

Title: Message

Hi!   Increasing it from it's default won't probaly help to get a better execution plan, unless you have 10-way or even more complex joins. However, having a large value of this parameter with complex joins can push your parse times (QEP calculations) very high! That's why one has to reduce it from 80000 to 2000 in latest Oracle Apps versions for example.   And reducing it in regular apps with not-so-complex joins won't kill either, because a 7-way join can be evaluated in 7! = 5040 permutations and Oracle uses several optimization mechanisms such QEP early elimination, join order intermediate cutoffs, putting cartesian joins last in evaluation sequence if there's more tables in join than specified by _optimizer_search_limit parameter, etc..   I'd say you definitely get the best plan (in CBO sense) with optimizer_max_permutations when doint 8-way joins, you probably get the best plan even with 9-way joins, and you get near-the-best plan with higher, 10-12 ones too, thanks to internal optimizations in finding the optimal plan.   Tanel. ----- Original Message ----- From: Boivin, Patrice J To: Multiple recipients of list ORACLE-L Sent: Tuesday, August 26, 2003 3:44 PM Subject: optimizer_max_permutations Has anyone worked with this one?   http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1123.htm#81357   Patrice.