Re: SQL: different execution plans at different times

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_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.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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