Re: Analyze table compute statistics suddenly setting wrong values in user_tables

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?
>
> :

Did you change any of your init.ora parameters just between the last two analyzes? Several of them affect the optimizer particularly the multi-block read count, sort area size, and the setting for PQO. And another poster recommended looking at outline to lock the query in the way you want. Make sure nobody played with outline and messed up the query.

Since an analyze can take 4X the table size worth of temp to run are you sure the analyze is actually completing successfully. It may be worth trying an estimate using a sample size of around 5% and see what the stats and explain plans come out looking like. I remember reading a couple of optimizer bug numbers posted by Oracle support for 8.1 but I think there were for a version prior to 8.1.6.

If you know how the query should run I would think that hinting the code to run that way would be your best solution if you have access to the SQL.
To get help from the true tuning experts you will probably need to post the explain plan and list of available indexes.

• Mark D Powell --