Analyze table compute statistics suddenly setting wrong values in user_tables

  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:02:11 CDT