Re: Analyze a table results in huge num_rows count

In general, gather_table_stats is able to rather accurately estimate the number of rows even from very small samples ( < .01 even ). I have not seen such a huge discrepancy. Is the mis-estimation consistent? If it is due to severe skew in the row length I would expect rather wild fluctuations. I'd be curious to see a 10046 trace of the gather_table_stats.
I would do (possibly) two things:
Try the auto_sample_size (and again run it with a 10046 trace to observer what sample percent it eventually uses). Open a TAR

At 02:03 AM 9/30/2005, Yechiel Adar wrote:
>Hello Yuval
>
>I hope you are aware that your sample is 1/3 of one percent only.
>
>My GUESS is that you have very different distribution of data in the blocks.
>If you have heavy insert/delete activity and some of the blocks are
>full and some are partially empty, if Oracle visits a few blocks
>that are full, it will think that all blocks are full and that will
>increase the num_rows.
>
>Adar Yechiel
>Rechovot, Israel

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l