Re: Statistics contradiction can cause wrong cardinality estimation

  • From: Tomi Wijanto <restomi_w@xxxxxxxxx>
  • To: greg@xxxxxxxxxxxxxxxxxx
  • Date: Wed, 23 Jul 2008 08:26:24 -0700 (PDT)

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.


--- On Tue, 7/22/08, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
> Subject: Re: Statistics contradiction can cause wrong cardinality estimation
> To: restomi_w@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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
> --


Other related posts: