Re: Expired statistics?

Hi, Richard!

We hit this situation again today (the plans have changed, reanalyzed, returned to the previous plans), and I am really confused. And answering your questions - database structure, initialization/session parameters didn't change since last statistics gathering. Other methods (like optimizing with hints) of resolving the problem exists, but what's happening is odd to me and I don't need any unexpected results in a future.

Regards, Ilya.

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:GAn19.47114$Hj3.143616_at_newsfeeds.bigpond.com...
> Hi Ilya,
>
> Have any of the referenced tables been altered ?
>
> Have any indexes been added / dropped on these tables ?
>
> Have any "tuning" parameters (such as db(sort)_file_multiblock_read_count,
> parallel parameters etc. been changed) ?
>
> Any dependant stored programs been changed ?
>
> Any of the above could have invalidated the execution plans and/or caused
> plans to change. Were the new statistics generated significantly different
> from before ? If not, then it would appear slightly odd that regenerating
> the same statistics causes execution plans to change and revert back to
> "optimal".
>
> Note with 9i, the use of stored outlines (where satisfactory execution
plans
> through hints can kinda be stored) helps to prevent issues such as these.
>
> Cheers
>
> Richard
>
> "Ilya Kuzkin" <elliew_at_hotmail.com> wrote in message
> news:k1k19.13409$Vj3.669031_at_news0.telusplanet.net...
> > We recently migrated from 8.0.5 and 8.1.7 and found out some of the
> > execution plans have changed.
> > The statistics were computed right after the database was created. I
tuned
> > some queries and saved the new plans.
> > Today I discovered that some of those plans have changed, which led to a
> > performance degradation.
> > Statistics were unchanged since that first gathering.
> > So I computed stats again and this returned me to the original plans.
Now
> I
> > don't want this situation to reoccur but the question remains: what
forced
> > Oracle to change execution plans with the stats unchanged?
> > Thanks in advance,
> > Ilya.
> >
> >
>
>
Received on Tue Jul 30 2002 - 11:28:41 CDT