Re: How to cope with nasty side effects of bind variable peeking
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Thu, 24 Aug 2006 15:54:56 -0500
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@xxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *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
Other related posts:
