Why we should use bind variables when we write code......
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Sep 2006 10:37:20 -0400
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!
So, now I'm on an education kick. "This is the way we do things when we
don't want to kill the Oracle database server!"
So, my question is, is there a resource, online or otherwise, that has
examples of proper bind variable usage in various languages? I can
cover C/Pro*C, PL/SQL, SQL*Plus, but what about Java, Perl, Python,
etc,etc? I'm not much of a coder lately, and I want proper examples
that can be shown to developers, in whatever is the language of choice.
Thanks,
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"A human being should be able to change a diaper, plan an invasion,
butcher a hog, conn a ship, design a building, write a sonnet, balance
accounts, build a wall, set a bone, comfort the dying, take orders, give
orders, cooperate, act alone, solve equations, analyze a new problem,
pitch manure, program a computer, cook a tasty meal, fight efficiently,
die gallantly. Specialization is for insects." --Robert A. Heinlein
Other related posts:
