Re: SQL: different execution plans at different times

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Nirmalya Das <nirmalya@xxxxxxx>
  • Date: Wed, 01 Nov 2006 22:31:06 -0700

What are some of the values for the bind variables? According to the stats, status and visible have only two values each. Any possibility that the sql right after the gather job uses values that are untypical and outside the known range for the column(s). Any possibility for a type mismatch in some of the executions? 


I'd try to enable a 10046 level 4 or a 10053 trace.

At 03:46 PM 11/1/2006, Nirmalya Das wrote:

I disabled the original "automatic statistics" gathering job and created a new
job that runs every other day as:

and by checking ---

SQL> SELECT column_name, num_distinct, num_buckets, histogram
  FROM DBA_TAB_COL_STATISTICS
  2    3   WHERE table_name = 'CONTACTVERSION';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
COMPANYNAME                          577329           1 NONE
MRMS                                     37           1 NONE
FIRSTNAME                              5692           1 NONE
LASTNAME                              44916           1 NONE
MI                                      656           1 NONE
PRINTAS                              670726           1 NONE
PHONE1                               166713           1 NONE
PHONE2                                10321           1 NONE
CELLPHONE                              2466           1 NONE
PAGER                                   198           1 NONE
FAX                                   13615           1 NONE
EMAIL1                                 9984           1 NONE
EMAIL2                                  550           1 NONE
URL1                                   4435           1 NONE
URL2                                    147           1 NONE
MAILADDRKEY                           38133           1 NONE
NAME                                 816604           1 NONE
TAXGROUPKEY                              33           1 NONE
TAXABLE                                   2           1 NONE
VISIBLE                                   2           1 NONE
OEPRICESCHEDKEY                           1           1 NONE
DISCOUNT                                  0           0 NONE
OEPRCLSTKEY                               3           1 NONE
CNY#                                   2230           1 NONE
RECORD#                               34831           1 NONE
STATUS                                    2           1 NONE
FAX                                   13615           1 NONE

These are simple "HEAP" tables :)

Quoting Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>:


Did you disable the automatic statistics gathering job in 10g? Can you verify
that there are no histograms on any of the predicate columns. I know, you quote your stats gathering job as using " method_opt=>'for all columns size 1' " but 
double-check anyways.
Is any of the tables by any change partitioned on any of the predicate columns? CNY# would be the most likely candidate. I hope no one would partition on status 
or visible.


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2006