Re: The optimizer makes me *desperate*...

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E39764D.BD22AB87_at_exesolutions.com>...
> Alex Filonov wrote:
>

<big snip>

> >
> > 1. If RULE hint works for you, stop here.
> > 2. If you can get decent performance using FIRST_ROWS hint, so be it.
> > 3. If neither 1 nor 2 works for you, you need to tune statement
> > manually, fixing the execution plan with hints.
> >
> > Attention! Solutions 2 and 3 are instance specific, so if you have
> > decent performance on the test instance it doesn't mean good
> > performance on production instance.
> >
> > Last but not least, increase optimizer_max_permutations parameter in
> > init.ora to the highest value available (AFAIK 80000 for 8.1.7).
> >
> > HTH
> >
> > > Maybe should I afford Jonathan Lewis' tutorial regarding cost-based
> > > optimisation... (not kidding)
> > >
> > > Thanks a lot.
> > > Sp
>
> 1. Of what value will RULE be after 9i?
> 2. Of what value is FIRST_ROWS in 9i?
> 3. Ok
>
> Daniel Morgan

Daniel,

I completely agree with your comments. I just don't see them relevant to the case. The initial post was about 8.1.7. Per my experience, for every major release of Oracle you need to re-tune some of the SQL statements no matter what. So if the initial poster moves to 9.2 from 8.1.7, there would be need for tuning again. May be not this particular statement, who knows? And careful manual tuning might not help, because the same hints force different execution paths on different versions of Oracle (of course!). Received on Fri Jan 31 2003 - 11:08:16 CST