Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Expired statistics?

Re: Expired statistics?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 30 Jul 2002 13:26:56 +1000
Message-ID: <GAn19.47114$Hj3.143616@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 Mon Jul 29 2002 - 22:26:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US