RE: Why we should use bind variables when we write code......

An archetypal example is "everyone fill out your on line labor distribution worksheet for last week" between 9 AM and 9:15 AM on Monday. And code that up with a literal for the employee id. Sure, they'll age out eventually, but of course it should have been bind variables given that there is certainly a unique index or primary key on employee id. Except that the application was written before there was a shared sql area, and literals parsed and executed just a wee bit faster back then. So now that application just overwhelms the shared sql area pushing everything else out and those nearly identical sqls are the most recently used. Sure, eventually that application will be retired, but if you've got an application no longer being maintained by the vendor and you don't have the source code you might be stuck with it for a while competing for budget and application selection. (Doesn't everyone hate the incumbent applications except when a change to something new is proposed?)  

I'm still not sure why there is no Unshared sql area and no session parameter to parse privately (skipping all the latches and the hash and search of shared sql). It is not as if that would be creating something new - that was all there used to be! True, that defeats the laudable purpose of the shared sql area, which scales much better if the application is written for bind variables, but it would handle antique applications that cannot be cost effectively changed. And the corresponding hint could be used for modern data warehouse queries designed for literals as well. Usually there is no point in keeping a copy of those parses either, but with a hint rather than a session parameter you could mix private and shared parsing as appropriate. And the session parameter solves the "I can't change the application" problem, so you need both.  

Sigh. The other improvement that would be easily added if there existed an unshared sql area is bailing out if the search in the shared area reaches either some limit of absolute time or some fraction of the average parse time. Of course that would help ameliorate latch storms, or any other pile up on the shared sql area. If it is being too expensive at the moment to use the shared area, parse me privately. Done. Thankyou very much. Next customer. Oh - and maybe optionally log the sql when you time out into a private parse and keep some stats so you know if you have a problem brewing. Because of course private parsing doesn't ultimately scale as well as shared parsing. But it could be very effective in the treating the symptoms while the doctors try to figure out whether there is a cure for the disease. Then we get into the whole issue of the cost of parsing. The reason we tolerate the cost of permutations is that we're expecting to get a big reduction in execution cost on average, and the scale is tipped in favor of spending time getting a good plan if you expect the shared sql to be re-used many times. If the sql in question is going to be pretty cheap to execute with pretty much any logical plan (cough, RBO, cough), then spending time on permutations quickly becomes a waste. (See nearly any of Cary's publications for the citation to Amdahl's Law (1967).) So *maybe* if the absolute cost underflows some epsilon it should stop trying to get better, and you'd tend to want epsilon to be higher if you bailed out to a private parse. That is way better than altering your session to a very low max permutations, because that affects expensive to execute queries as well.  

I really miss having Oracle VLDB meetings. Just hit delete if this was uninteresting to you.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
Sent: Wednesday, September 20, 2006 1:14 AM To: Mark.Bobak_at_il.proquest.com
Cc: oracle-l
Subject: Re: Why we should use bind variables when we write code......  

In addition to my warning about "inappropriate" use of bind variables I am wondering how the 3,424 "identical except for literals" sql could cause the shared pool to become fragmented. They should be eligible to be aged out for new sql, including "identical except for literals" ones. Unless, of course, the application doesn't close the corresponding cursor. But that would be a different issue.
As some of you know I am working with Peoplesoft applications which are not particularly knownfamous for their use of bind variables. But I rarely encounter ora-04031 errors and when then they are caused by some other application/add-on, often 3rd party monitoring or administrative "utilities".

At 08:37 AM 9/19/2006, Bobak, Mark wrote:

So, last week, one of my instances starts getting ORA-4031s, and after a few minutes, comes crashing down when a background process (lmd0, I think it was) catches an ORA-4031. So, with the instance down, it's a bit tough to see what happened. So, we start things up again, and I start watching closely over the next few days. Seems there's lots of code that doesn't bother with binds. In some cases, there are a dozen non-sharable SQLs that are identical except for literals, in other cases, up to hundreds. (Thanks to T.Kyte for the script that I'm using to identify non-sharable SQL.) After a few days, I find the smoking gun. One single SQL statement that has 3,424 copies that are identical except for literals. (No, that's not a typo.) This is taking up abour 75% of the 475M of shared pool that's dedicated to the sql area. One single SQL statement, 75%. Yikes!

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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