Re: optimizer_max_permutations

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 4 Mar 2004 01:42:47 +0200
Message-ID: <0deb01c40179$3bb69ab0$a2a623d5@porgand>

In my experience it works. I've seen it working well with even lower value in one place.
The reason why you should now set optimizer_max_permutations much lower than before, is not that Oracle suddenly accepts less optimal execution plans, but the optimizer permutation mechanism itself has got more intelligent, i.e. there are cutoff points in some branches of permutation trees, where optimizer sees that a particular join order of tables can't possibly provide better cost than already calculated best value, etc. And it's getting more intelligent every release.

And when we have first release of Oracle running on a quantum computer, you can set optimizer_max_permutations to 1.

Tanel.

Original Message ----- From: <ryan.gaffuri_at_cox.net> To: <oracle-l_at_freelists.org> Sent: Thursday, March 04, 2004 1:34 AM Subject: optimizer_max_permutations

> I've been talking to Oracle Support about some performance problems and
they told me to set optimizer_max_permutations=2000
>
> Has anyone tested how this effects the CBO? This is on 9.2.0.4 on Solaris.
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 03 2004 - 17:40:11 CST