RE: Why we should use bind variables when we write code......
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>, <Mark.Bobak@xxxxxxxxxxxxxxx>
- Date: Wed, 20 Sep 2006 02:50:25 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfgang Breitling
Sent: Wednesday, September 20, 2006 1:14 AM
To: Mark.Bobak@xxxxxxxxxxxxxxx
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
Other related posts:
