RE: CBO problem

Alternatives I've used in this situation:

1. Analyze the smallest, least frequently used table involved in the target query. This will force the cursor to be invalidated and it will then be reparsed with new bind variable peeking the next time it is executed.
2. Use Stored Outlines (aka Plan Stability) to force the desired plan regardless of bind variables
3. Remove the histograms by analyzing the involved tables with "method_opt=>'for all columns size 1'", then lock those stats to preven the gather_stats_job from overwriting them and/or change your gather_stats_job to stop gathering histograms.

Regards,
Brandon  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nirmalya Das Sent: Monday, July 17, 2006 10:27 AM
To: Wolfgang Breitling
Cc: oracle-l_at_freelists.org
Subject: 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



Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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