Re: dbms_stats / 9204

Jonathan

Thanks for your answer. I did not know about 919 block limit. It makes sense in my case that when i sample 5 , 10 , 20 , 40 or compute the index stats is gathered only for a certain percentage of blocks. I have 2 tables of 40 and 90 mil rows where gather stats samples less than 1% . I am on oracle 9204 and sun solaris 32 bit version.
I have another prod server where the estimate % is what i asked for. When i sample 5 , 10 ,20 , 40 or compute it exactly does 5,10,20, 40 or compute. This is the same version and same os. Incase of index on not null columns only if 1million rows are indexed out of 20 million rows then it sample 5% of 1 million. This puzzles me. It works on one server and not on other.
Also i dont know how many people are using dbms_stats in 9204. I see quite a few bugs logged against this package.
One more bug is dbms_stats is slower when using parallelism >1 which i feel is critical when you can actually make use of parallelism to gather stats faster on big tables.
I hope somebody who is using it on 9204 posts some more information about their experiences.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:cqc4rt$980$1_at_hercules.btinternet.com...
>
>
> 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 ......
> >
> >
>
>
Received on Wed Dec 22 2004 - 21:42:58 CST