Re: How to cope with nasty side effects of bind variable peeking

I have had a couple sidebars on a similar topic, and have come to some conclusions:
1) The end-users like plan stability. Sometimes even more than apparent run times. (not all the time, but in my limitted experience, a majority of the time)
2) Lots of things can upset plan stability, making some queries faster, other slower.
3) Among other things, the "peek-once-and-form-a-plan" idea helps stabilize the plans, even though you can get really unlucky.

The concept you describe is loosely implemented via shared cursors (I learned a lot looking at v$shared_cursors). You can find multiple plans for one sql_id just because.... because the bind metadata does not match, you have reached a roll limit (still do not know much about that one), or.... a few other things. So the actual functionality to support multiple hash plans for a given cursor is already there, but extending it to actually peek each bind variable on every parse is not quite - this would be close to the opposite of shared cursors. Still a lot of peeking and parsing going on.

From my point of view, it comes down to where your priorities are, and where your bottleneck is. If you are already overburderned with latch contention, peeking every bind variable is probably not the best thing in the world. However, if your end-users are micromanaging and want to squeeze every microsecond of performance out of the database engine, than maybe that is a good idea.

There have been some ideas proferred about how to reduce problematic situations where the first parse is not very representative of the rest of your queries. For instance, pre-parse specific queries for which you know might exhibit this behavior. You already mentioned outlines. In some ways, you just have to bite the bullet, I think.

We thought about turning off the peek function (_optim_peek_user_binds), especially in regards to optimizing our histograms (we are getting more proactive on that end). Histograms + peeking = headache.

Credit goes to Wolfgang Breitling, Christian Antogningi, John Kanagaraj, Joel Wittenmeyer and Jonathan Lewis (via his CBO book). All mistakes and misrepresentations are my own.

On 8/24/06, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
>
> What if Oracle could evaluate the incoming bind variable values for a
> query and compare them to the bind values used at parse time for all
> previously cached versions of the same query and if they don't match, then
> the CBO peeks at the new bind variables and comes up with the best plan.
> If the new plan is different than the existing cached plan (quick check on
> plan_hash_value), then it loads it as a new child cursor, but if it's the
> same as the plan that was already loaded for the previous bind variables,
> then the new query just uses that plan. Oracle could store
> multiple bind_hash_values associated with a given plan and always do a
> comparison on new queries to determine which queries can use the existing
> cached plan(s) and which ones need to be optimized for a new set of
> variables.
>
> Anyone else think this would be a good idea?
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Allen, Brandon
> *Sent:* Thursday, August 24, 2006 11:52 AM
>
> Ever since moving to 9i and 10g databases in the last couple years, the
> most common performance problem I see is queries that are optimized by the
> CBO based on one set of bind variables, but subsequently executed with a
> drastically different set of bind values, resulting in a terrible mismatch
> between the query and the execution plan . . .
>
> 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.
>

-- 
Charles Schultz

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