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