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

if the ora-04301 comes from sql literals then it should leave traces in the evanescent x$ksmlru. did you checked this ? I uses this script for the purpose. note the spool mendatory good practice on this view:  

 SPOOL=$SBIN/tmp/KMSLRU_${ORACLE_SID}_`date +%m%d%H%M%S`.log  

set linesize 124 pagesize 66 head on
col ksmlrcom format A20 head "Namespace|affected" col ksmlrsiz format 99999 head "Size |requested" col ksmlrnum format 99999 head "Num Object|Flushed out" col ksmlrhon format A32 head "What is loaded" col ksmlrohv format 9999999999999 head "Hash_value" col username format a20 head "Username"
col sid format 9999 head "Sid"

select

    ksmlrcom, ksmlrsiz, ksmlrnum, ksmlrhon, ksmlrohv,     sid,username
from x\$ksmlru a,v\$session b where a.addr=b.saddr (+) /
spool off  

You should find the last ora-0431 failure size value
(last_failure_size from v$shared_pool_reserved)
some where in the column ksmlrsiz. if not then your 4031 comes from a session allocation.  

Note that I don't see anymore ora 4031 these last years. last I am surprise that you never mentioned the the case of the parameter 'CURSOR_SHARE' set to similiar or force. Any reason?  

B. Pokarski

From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Wednesday, 20 September, 2006 7:14 AM To: Mark.Bobak_at_il.proquest.com
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

--
http://www.freelists.org/webpage/oracle-l