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
=20

Jayakumar wrote:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

From: S Jayakumar <jaykumar_at_mahindrabt.com>  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=3D1000; /* 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 =3D 'abc'
>
> the parsing may need 1s. Execution about 0.5s. If I query
>
> SELECT ...
> FROM my_view
> WHERE other_attribute =3D '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=FC=DFen
>
> i.A. Marc Blum
>
> SOPTIM GmbH
> Gr=FCner Weg 22-24
> D-52070 Aachen
>
> Telefon: +49 241 / 9 18 79-33
Received on Wed Oct 18 2000 - 03:42:27 CDT