the is a option called plan_stability using outline. you may want to look
into this.
"Sharon" <Sharon_at_kipnuk.marinliving.net> wrote in message
news:3B9F8703.55F5CE1_at_kipnuk.marinliving.net...
> Sun Solaris 2.7 latest patch set
>
> Oracle 8.1.6.0
>
> We're all the sudden having problems with incorrect execution plans
> (i.e. queries not using indexes
> where they once did). I've analyzed this table -- partitioned with
> over 100 million
> rows on a twice monthly basis for months-- with the compute statistics
> option.
>
> Everything was fine doing this, however after this past run -- queries
> started doing full table scans where they were using
> indexes before. I look in the user_tables view, and see that for some
> reason
> the last_analyzed is listed as July 31st!, and the num_rows is
> innacurate.
>
> I look in user_indexes, and the information is correct for
> last_analyzed.
> I look in user_tab_partitions and the information is correct for
> num_rows and
> last_analyzed.
>
> I delete the statistics, and start fresh.
> Once I deleted the stats, the views were all empty for this table as
> they should be(and weird
> enough, some of the queries started using indexes again???!!!!!)
>
> So, I re-analyzed the table and again user_tables is showing incorrect
> information for this table....same date of July 31st.
> Because of this, I believe that Oracle is saying the statistics are
> stale, and reverting to RBO???
>
> I've tried adding hints to certain queries and even that doesn't work.
>
> Anyone have any ideas on what could cause this?
>
> :
>
>
>
Received on Wed Sep 12 2001 - 11:48:38 CDT
