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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx,<don@xxxxxxxxx>
  • Date: Wed, 20 Jun 2007 05:37:45 -0600

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


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


Other related posts: