Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bpgsab$s4k$1$8300dec7_at_news.demon.co.uk...
>
> There may be a hidden flag somewhere defining
> the stats as user-created. I vaguely recall Wolfgang
> Breitling saying something like that once.

Maybe its the flag in sys.tab$.
A look in the view definition of dba_tables (in 9i) shows the column user_stats defined as "decode(bitand(t.flags, 256), 0, 'NO', 'YES')"

Which might be the flag you talk about here?

Anurag

>
> Try exporting the stats to a stats table, then re-importing
> them to see if that changes anything.
>
> Also try a 10053 trace for the two queries to see if
> there is any difference between the quoted stats
> and the stored stats.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> ____Belgium__November (EOUG event - "Troubleshooting")
> ____UK_______December (UKOUG conference - "CBO")
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "dias" <ydias_at_hotmail.com> wrote in message
> news:55a68b47.0311191236.10fcafd6_at_posting.google.com...
> > Hi,
> >
> > I've used only one query for the tests. This query is only single
> > partition, the where clause contains the partitioning key.
> >
> > I have tested these cases:
> > - dbms_stats. set table, index and column stats for the partition.
> > - dbms_stats. set table, index and column stats for the partition
> AND
> > global stats for table, index and column.
> >
> > The CBO generates wrong costs to access indexes
> >
> > The second case was to gather the stats for a partition A, and to
> use
> > GET stats procs (dbms_stats) from this partition and SET stats into
> a
> > partition B. The query against partition A is ok, against partition
> B,
> > the same wrong execution plan ...
> >
> > I think that setting stats do not populate all the stats needed by
> the
> > optimizer, but I cant see what is missing.
> >
> > Dias
> >
>
>
>
Received on Wed Nov 19 2003 - 21:40:10 CST