Re: Guidelines for avoid Bind Variable Peeking behavior

From: Kerry Osborne <>
Date: Thu, 11 Jun 2009 13:46:46 -0500
Message-Id: <>


On the future hint question, I assume you're referring to this:

   Q: Are you planning a hint to mark statements as bind-aware ?

  1. Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

I didn't read to much into that quote. For two reasons.

First, I expect that the "startup cost" they are referring to has to do primarily with internal house keeping, not with the penalty that is incurred by executing the statement badly at least once, before creating a new plan. Unless they add a way to persist the data associated each cursor (i.e. the association between values of bind variables and plans), it seems to me that the penalty of at least one sub-optimal execution is unavoidable.

Second, and more importantly, my thinking is that if you have to modify your code to tell Oracle that you have an issue (via a hint), you might as well just modify it to use literals appropriately, so that the optimizer doesn't need to do any bind variable peeking in the first place. By the way, it might be interesting if they provided a way to make every statement bind aware (via a parameter) effectively causing bind peeking to happen for every execution. This may actually work pretty well for DW type systems where parse times are not really much of a concern.

Kerry Osborne

On Jun 10, 2009, at 6:04 PM, Allen, Brandon wrote:

> Kerry,
> Thatís a very interesting blog entry. I searched the internet for
> any other mention of the 14 bind variable limit and couldnít find
> any so it appears youíre the first to discover it Ė good work!
> Did you catch the mention at the bottom of the optimizermagic blog
> about adding a hint in future releases to inform the CBO that a
> query should be bind aware so that it will know up front without
> having to go through the pain of figuring it out?
> This reminded me that we had some discussion along the same lines on
> this list back in 2006:
> Iím looking forward to digging into Adaptive Cursor Sharing and SQL
> Plan Management a lot more once I wrap up this Oracle Application
> Server project Iíve been working on and I get a chance to do an 11g
> upgrade.
> Thanks,
> Brandon
> From: Kerry Osborne []
> We've been supporting a production cluster for some time
> and just recently migrated a 10g system (which had bind variable
> peeking issues) to I just did a post on my blog this
> morning based on my observations of this migrated system. The bottom
> line for this system was that ACS didn't really give us the results
> we were hoping for.
> 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.

Received on Thu Jun 11 2009 - 13:46:46 CDT

Original text of this message