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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nirmalya Das
Sent: Monday, July 17, 2006 10:27 AM
To: Wolfgang Breitling
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx>:

> 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@xxxxxxx>:
>
>>
>> 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


Other related posts: