All, Just to let you know I've figured what the problem is. This table is being audited using FGA for one of the columns and that is what causes the huge discrepancy. Once I drop the policy (using execute dbms_fga.drop_policy), the gather_table_stats works ok. I am going to open a TAR wih Oracle. Thanks for the input. Yuval. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling Sent: Friday, September 30, 2005 7:48 AM To: adar76@xxxxxxxxxxxx Cc: ORACLE-L Subject: 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 -- //www.freelists.org/webpage/oracle-l This transmission may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you, oracle-l@xxxxxxxxxxxxx, are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. -- //www.freelists.org/webpage/oracle-l