Re: Subject: Large Tables, Bad Indexes and Fake Statistics

At 01:05 AM 6/20/2007, FreeLists Mailing List Manager wrote:
>The problem:
>A partition of FOO is bulk loaded around 10 times a month. The
>partition is created a few days before it will first be loaded into.
>We have a recurring problem when, after the partition is loaded into
>(2-3 million records per load), queries on FOO that have A, B, C, D in
>the WHERE clause will start using the second index. The fact that
>this second index doesn't use column B means it is walking over a HUGE
>set of records, practically a complete partition scan.
>
>It seems like an out-of-whack statistics issue. After I gather stats
>on the table (takes 2.5 hours) the queries then use the PK as desired.
> To fight this, we had just this month started calling
>dbms_stats.set_table_stats and set_index_stats to indicate a full
>month's worth of stats before loading. Then for the rest of the day
>we could fool Oracle, until the nightly stats job then set the real
>stats. However this didn't work yesterday. I'm not sure why, but I
>did notice that the dba_tab_statistics table showed a sample_size for
>my user-set stats, when I don't believe there was one before.

Since the partition is pre-created it get its statistics gathered (all zero rows) and therefore dynamic sampling at level 2 (all tables without statistics) won't get called.
The setting of statistics ought to work. Do/did you also set the column statistics? You should find out why it didn't work "yesterday". I've been seeding partition statistics without problems. Usually I clone them from an existing partition - replacing the low/high values of the partitioning column(s).

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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