Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to cope with nasty side effects of bind variable peeking

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

From: Allen, Brandon <>
Date: Thu, 24 Aug 2006 14:56:20 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1D97@NT15.oneneck.corp>

Thanks Charles. Please see comments in-line:

	From: Charles Schultz [] 
	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.

Received on Thu Aug 24 2006 - 16:56:20 CDT

Original text of this message