Re: Mutating (SQL) execution plan!?...is that possible...stranges t thing I have every seen

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 10 Mar 2005 11:27:52 -0700
Message-ID: <423091A8.6030401@centrexcc.com>

John Kanagaraj wrote:
> Chris,
>
>

>>>>I have the feeling you are setting yourself up for the=3D20 bind=20 
>>>>variable peeking trap and appear to be falling into it.

>
>
> I am NOT a fan of using hidden parameters, but just be aware of the hidden
> parameter '_optim_peek_user_binds' (which is set to TRUE by default and
> enables bind peeking), that when set to FALSE in this case would take care
> of your "problem". Histograms *are* a useful tool when used appropriately,
> and can be a great help (and also a hindrance as you have seen).
>

While that is correct, you would already be at at least two layers of band-aids. First using cursor_sharing=force to patch up the application not using bind variables correctly ( being written in perl there is really no excuse for that ) and then use an underscore parameter to patch up the bind variable peeking trap because of the histogram created   by the broad "for all indexed columns ...". There are too many broad assumptions at work here.
I hate to point it out because it could delay or avoid fixing the real problem, but another possible workaround would be to use cursor_sharing=similar - and that is at least a documented parameter.

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 10 2005 - 13:31:13 CST