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

Mark, thanks.
I do have an alert sent if the analyze did not complete, I get a spooled log of the output, and I haven't seen any temp segment problems at all.
The biggest concern I have is the fact that the data dictionary view user_tables is reverting back to old statistics, while all others for this table are. I agree that compute statistics is a heavy resource user, but we haven't been able to get an estimate to work...have tried up to 45% (over 50 it just computes anyhow doesn't it?). I just think the optimizer is seeing the statistics as being stale. I do have access to most of the queries, and I have tried adding hints....to no avail. It's very strange behavior...never seen anything like it.
Once I do get things stabilized, I will definitely be taking advantage of using outlines. I'll post if it's found to be a bug. Wish me luck :-).

Mark D Powell wrote:

> 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 --
Received on Wed Sep 12 2001 - 22:13:37 CDT