Re: dbms_stats / 9204

Jonathan Lewis wrote:
>
> I haven't followed this through, but it looks as if it's
> related to the issue that Oracle apparently wants a
> minimum sample of 919 leaf blocks when sampling
> an index. So the theory is that if you do something like:
> estimate_percent=>10,
> Oracle can work out if this meets the 919 block limit,
> and change the sample size if necessary. But it's only
> supposed to change the sample size upwards. When
> (as your bug note shows) you set 100% as the sample
> size, Oracle still adjusted it down to about 54% then
> got a small sampling error that resulted in a num_rows
> that was a little off.
>
> In reply to your earlier question about sample_size - for
> indexes, this is the number of index entries that existed
> in the leaf blocks that were sampled.
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated Sept 19th
>
> "Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
> news:0S3yd.237219$V41.231021_at_attbi_s52...
> > Are you using dbms_stats on 9204? Did you come across a bug 3301328 ?
> > Index stats being collected is not accurate when compared to analyze
> > commands.
> > How about analyze commands using partition tables ? Any issues ......
> >
> >

On 9205 I've still noticed a number of hassles with dbms_stats when it comes to index usage. One killer thats hurting us is the num_distinct being vastly undercalculated on many occasions.

At this current site, we've taken the "backward" step of setting event 38019 at level 1 to force calls to dbms_stats for indexes to revert to analyze.

:-(

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------