Re: SQL: different execution plans at different times

From: Nirmalya Das <nirmalya_at_hln.com>
Date: Wed, 1 Nov 2006 14:46:21 -0800
Message-ID: <20061101144621.e7dy2oogsok88s8c@www.hln.com>

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

begin

    dbms_stats.gather_schema_stats (

           ownname=>'ACCT_OWNER_01',
           options=>'GATHER',estimate_percent=>dbms_stats.auto_sample_size,
           method_opt=>'for all columns size 1',
           degree=>15,
           cascade => TRUE);

end;

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

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
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

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
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_at_centrexcc.com>:

> 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.
>
> Quoting Nirmalya Das <nirmalya_at_hln.com>:
>
>
>
> --
> regards
>
> Wolfgang Breitling
> Oracle 7,8,8i,9i OCP DBA
> Centrex Consulting Corporation
> www.centrexcc.com

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

Received on Wed Nov 01 2006 - 16:46:21 CST