Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Large Tables, Bad Indexes and Fake Statistics

Re: Large Tables, Bad Indexes and Fake Statistics

From: Don Seiler <>
Date: Tue, 19 Jun 2007 17:24:00 -0500
Message-ID: <>

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
> (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:
Received on Tue Jun 19 2007 - 17:24:00 CDT

Original text of this message