RE: Analyze a table results in huge num_rows count

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Friday, September 30, 2005 7:48 AM To: adar76_at_inter.net.il
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

--
http://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_at_freelists.org, 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.


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