RE: How to cope with nasty side effects of bind variable peeking
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Charles Schultz" <sacrophyte@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Aug 2006 14:56:20 -0700
Thanks Charles. Please see comments in-line:
________________________________
From: Charles Schultz [mailto:sacrophyte@xxxxxxxxx]
Sent: Thursday, August 24, 2006 1:55 PM
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.
[Allen, Brandon] Yes, you're right - the more I think about it,
I guess what I described would be about the same as just eliminating
bind variables and going back to literals instead (which is actually a
good idea in some cases, e.g. in DSS). It seemed like a good idea as I
was writing it, but I obviously didn't think it all the way through. It
seems like there has to be some way to have our cake and eat it too,
i.e. peek at the bind variables and share cursors, but only when
appropriate. I guess maybe the best solution is to build it into the
application logic, e.g. instead of sending a query like "select * from
sales_orders where order# >= :v1 and order# <= :v2" and then setting
v1=1000 and v2=1000 (this is what Baan does), the application should be
smart enough to realize that this query should instead be written as
"select * from sales_orders where order# = :v1" - then Oracle would be
able to optimize both queries appropriately and there would be less
probability of inappropriate explain plans being shared.
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.
[Allen, Brandon] In one case, just today - bind variable peeking
was the difference between hours vs. 5 minutes on the runtime for a
single query - much more significant than just trying to squeeze out
microseconds.
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.
[Allen, Brandon] I've seen these same problems in 9i databases
with no histograms, and 10g with all the histograms gathered by the auto
stats job - so yes, histograms may contribute to the headache, but doing
away with them doesn't eliminate the problem.
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.
Other related posts:
