Hi Marc,
Let me give you the explanation of what the parameter does and a
recommendation, based on some production sites where I have it
implemented and you can take it from there.
OPTIMIZER_MAX_PERMUTATIONS controls the number of tables that
the optimizer will consider as the 'driving table' while
building the execution plan for a SQL statement. It defaults to
a value of 80000. When set to a value below 80000, say between
79000-79999 (does not really matter what value in that range),
it forces the optimizer to try up to 4 different tables as the
driving table for queries that involve joins. This results, in
the optimizer picking the least expensive of the 4 plans that it
generates.
Usually, the default behavior is to build a plan with the
smallest table as the driving table. The default behavior may
not always generate the most suitable plan, especially for
packaged applications like SAP, Oracle Apps., PeopleSoft and
Baan. The result of setting this parameter to a value below
80000 is a nominal increase in “parse time”, but a significant
potential in reduction of “execution time” of SQL statements.
Overall, the queries on your system will benefit significantly.
I personally have not set it to 1000, although it seems very
low. You might want to approach this in a more conservative
fashion. Since I have no experience with the value 1000, and
its repercussions, I cannot comment on it, but for the optimizer
to consider upto 4 tables, as your driving table, a value of
between 79000 and 79999 is adequate. I personally have set it
to 79000, a multiple sites in the past 12 months.
Hope that helps,
Gaja
- "Blum, Marc" <blum_at_soptim.de> wrote:
> 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
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
subscribing).
Gaja Krishna Vaidyanatha
Director, Storage Management Products, Quest Software Inc.
Office : (972)-304-1170, E-mail : gajav_at_yahoo.com
Author - Oracle Tuning 101 by Osborne McGraw-Hill
"Opinions and views expressed are my own and not of Quest"
Received on Wed Oct 18 2000 - 12:58:33 CDT
