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

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 19 2006 - 09:37:20 CDT