Oracle-L: Re: optimizer_max

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 26 Aug 2003 06:49:27 -0800
Message-ID: <F001.005CD474.20030826064927@fatcity.com>

MessageHi!

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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Tanel Poder
INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Aug 26 2003 - 09:49:27 CDT

Re: optimizer_max_permutations