Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

Thanks again,
I (he) really appreciated your help.

Dias

Wolfgang Breitling <John.Doe_at_aol.com> wrote in message news:<Xns943AEE698253breitliwcentrexcccom_at_198.80.55.250>...
> I have been communicating with Dias directly and he (why do I assume he's a
> he?) provided me with the 10053 traces and more details on the dbms_stats
> calls used. From that I could create a testcase and reproduce the problem.
> It turns out it has nothing to do with the fact that the tables are
> partitioned. I could get the same behaviour using just one non-partitioned
> table. The cause for the problem lies with the omission of a reference to a
> filled-in srec structure in the set_column_stats call to provide the min
> and max values for the column, if not an entire histogram array. The Oracle
> 8i optimizer obviously didn't mind, but the Oracle 9i cbo does.
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:bpe6d7$11r$1
> $830fa7a5_at_news.demon.co.uk:
>
> >
> > I think there was a change of strategy between 8 and 9
> > on multi-partition queries.
> >
> > In Oracle 8, Oracle would examine the statistics of
> > all relevant partitions. On Oracle 9, it uses the stats
> > of the base table.
> >
> > Are you setting the base table stats, and are you
> > running multi-partition queries, or single partition
> > queries.
> >
Received on Sun Nov 23 2003 - 14:27:02 CST