Re: Large Tables, Bad Indexes and Fake Statistics

A few clarifications:
* The table is range partitioned on two number fields (year, month),
not a single date field.  These fields are two of the four fields that
are most commonly used in query predicates.
* Both the PK and second index are LOCAL.

My replies are inline below.


1. Dynamic Sampling

Do you use dynamic sampling? What level? Could it be the case that the
missing partition statistics gathered using dynamic sampling are inaccurate
due to extremely small sample size (small number of blocks red in sampling)?


We are using level 2 dynamic sampling (the default in 10g, iirc).


2. BV Peek

Additionally to the well-known histogram trap of the BV peek there is a less
known data warehouse analogy. It comes in case of fact tables with loaded
and preallocated partitions. The problem appears if the peek uses the
statistics of one type of partition (e.g. of the empty one) but the
statement run on the partition of the other type (e.g. on the full one).
If I understand you correctly that your problem disappears if the statistics
of all partitions are more or less the same (and the statement uses bind
variable on the partition key), I'd also check this possibility.
There is a rather funny example of this kind of trap documented on
http://www.jlcomp.demon.co.uk/faq/bind_peek.html
(The details probably differs from your case)


I've considered this possibility.  Jonathan's text there certainly
hits close to my mark.  Rolling window, pre-allocated empty
partitions, etc.

Although, the problem also affects test queries using literals as
well.  And once statistics are up-to-date, there is no problem.  e.g.
after the first market is loaded, that "current" partition will have
1-to-3 million rows, the other 3 partitions will have ~30 million rows
each.

--
Don Seiler
oracle blog: http://ora.seiler.us
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l

Other related posts: