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.



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: