Re: USER_BIND_PEEK_MISMATCH...

On 27 Jun., 22:09, amitpodd..._at_gmail.com wrote:
> > There is some discussion on my blog:
> > http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/
>
> > --
> > Regards
>
> > JonathanLewishttp://jonathanlewis.wordpress.com
>
> > Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> > The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> The above blog mentioned by jonathan discusses a issue which will show
> up as BIND_MISMATCH and not as
> USER_PEEK_BIND_MISMATCH. I sent an email to jonthan with this
> information and he replied back with the following
>
> "My error - I failed to think carefully enough about
> the post. Feel free to post the correction to the
> newsgroup.
>
> I wonder if it could also happen with cursor_sharing
> set to force if there was a size mismatch; or would
> that still show as a BIND_MISMATCH.
>
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com"
>
> The USER_PEEK_BIND_MISMATCH generally happens which you have
> cursor_sharing = similar. and oracle decides to optimize the query
> again
> as a new child cursor since the peeked bind value can result in a
> different execution plan. This can happen when there is a histogram on
> the column.
>
> amit

Hello,

thank you for the answers!
I see, that my update is loosed, so I try again... We use cursor_sharing=force, not similar. And we have a query with user defined binds...
I am wondering, if the number of the child cursors depend from the shared pool size. I mean: small shared pool - many child cursors? As we had the problem on one database, restart with bigger shared pool did help. But I don't know, if this was the "solution" or just luck...

Regards
Elena Received on Sun Jul 01 2007 - 04:18:39 CDT