Re: CBO problem

My queries do include bind variables....

Because of the problem I stated, I flush the shared pool once in a while and the
problem goes away.....

But this is a very expensive operation and of course NOT a permanent fix....

What alternatives do we have.....

Is Column Histograms creating the trouble? Should I turn it off?

Quoting Wolfgang Breitling <breitliw_at_centrexcc.com>:

> My guess is that it has nothing to do with IOT and only indirectly with
> automatic statistics gathering. Unless you changed the default
> method_opt, the
> 10g automatic statistics gathering job will gather histograms on all columns
> used in predicates (at least that is an improvement over the "for all indexed
> columns" nonsense). If your queries include bind variables you could see the
> result of different plans being generated due to bind variable
> peeking at parse
> time - and then that plan being used for all other BV until the plan
> ages out or
> gets invalidated.
>
> Quoting Nirmalya Das <nirmalya_at_hln.com>:
>
>>
>> Some queries (which involves some Index Organized Tables) runs like a charm
>> for
>> some time and once in a while will a get a bad execution plan and will
>> exceed
>> the CPU limit assigned in the user profile.
>>
>> We use the "Automatic Statistics Gathering" to collect statistics.
>>
> --
> regards
>
> Wolfgang Breitling
> Oracle 7,8,8i,9i OCP DBA
> Centrex Consulting Corporation
> www.centrexcc.com

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