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

Other related posts: