RE: optimizer_max_permutations

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Thu, 19 Oct 2000 14:05:43 +1000
Message-Id: <10653.119732@fatcity.com>

Hi Marc,

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.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: Blum, Marc [mailto:blum_at_soptim.de] Sent: Wednesday, 18 October 2000 19:45
To: Multiple recipients of list ORACLE-L Subject: optimizer_max_permutations

First thanx to Jayakumar. this one works, but now some questions follow up:

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?

thanx to all
marc

Jayakumar wrote:

Date: Wed, 18 Oct 2000 10:36:40 +0530
Subject: Re: Exorbitant Parsingtime for view

Hi Marc ,

try reducing the optimizer_max_permutations parameter in init.ora & bounce the
DB.
It takes a lot of time to identify the best execution plan by doing permutations.
If bouncing is not possible try this. ( session wise )

SQL>alter session set optimizer_max_permutations=1000; /* default is 80000 */
run your query now. It should take less time.

Hope this may help you.

Regards,

Jayakumar

"Blum, Marc" wrote:

> Dear all,
>
> we have a rather complex View containing several UNIONs and 5-7 tables per
> FROM-clause. Now if I query
>
> SELECT ...
>   FROM my_view
>  WHERE attribute = 'abc'
>
> the parsing may need 1s. Execution about 0.5s. If I query
>
> SELECT ...
>   FROM my_view
>  WHERE other_attribute = 'xyz'
>
> the parsing needs 30-45s. Execution about 0.5s. Now I don't want anybody

to
> understand our data model or that view. But does anyone has any hints, where

> to start searching? Any experience with such exorbitant parsing?
>
> Thanx in advance
>
> Mit freundlichen Grüßen
>
> i.A. Marc Blum
>
> SOPTIM GmbH
> Grüner Weg 22-24
> D-52070 Aachen
>
> Telefon:        +49 241 / 9 18 79-33
> Fax:    +49 241 / 15 40 38
>
> mailto:marc.blum_at_soptim.de
> http://www.soptim.de

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Blum, Marc
  INET: blum_at_soptim.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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

Received on Wed Oct 18 2000 - 23:05:43 CDT