RE: Analyze a table results in huge num_rows count
- From: "Arnon, Yuval" <Yuval.Arnon@xxxxxxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Sep 2005 10:31:13 -0400
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
Other related posts:
