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
-- //www.freelists.org/webpage/oracle-l
