Re: Statistics contradiction can cause wrong cardinality estimation

Hi Greg,

for the first case where NUM_DISTINCT=1 but LOW_VALUE <> HIGH_VALUE, this problem occurred because the way we manipulate the stats in partition table. Every time rolling out new partition, we copy the stats from previous partition, and then after data loading will adjust the HIGH_VALUE with the new value (mostly increasing date), but we forget to adjust the NUM_DISTINCT. In case we have weekly partition, but also create seperate partition for end-of-month, copying stats from eom partition into normal partition will cause problem as NUM_DISTINCT for date column never get increased.

For the second case when NUM_NULLS is greater than NUM_ROWS, the cardinality=1 seems because the way Filtering Factor is computed. If NUM_NULLS > NUM_ROWS, FF will be (1 - NUM_NULLS/NUM_ROWS) which is NEGATIVE value, so seems to be always rounded to 1.

NUM_NULLS > NUM_ROWS on this particular table was triggered by mistake. Someone manually gathered the stats on all columns, and later gathered the stats only for indexed column. So the table stat got updated but non-indexed columns stat was obsolete.

regards,
tomi

• On Tue, 7/22/08, Greg Rahn <greg_at_structureddata.org> wrote:

> From: Greg Rahn <greg_at_structureddata.org>
> Subject: Re: Statistics contradiction can cause wrong cardinality estimation
> To: restomi_w_at_yahoo.com
> Cc: oracle-l_at_freelists.org
> Date: Tuesday, July 22, 2008, 2:54 PM
> A couple comments:
>
> I would recommend to always use YYYY as RRRR/RR was
> introduced as a
> Y2K workaround.
> This should yield a number value for Pstart/Pstop vs. a
> KEY/KEY plan.
>
> Based on your note that recollecting stats resolved the
> issue, what
> command was used to gather stats the produced the bad
> plans? What
> command was used to produce the good plans? What
> differences in the
> stats did you observe?
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
> --
> http://www.freelists.org/webpage/oracle-l
      

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