RE: Why we should use bind variables when we write code......
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <breitliw@xxxxxxxxxxxxx>,<Mark.Bobak@xxxxxxxxxxxxxxx>
- Date: Thu, 21 Sep 2006 14:04:48 +0200
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@xxxxxxxxxxxxx]
Sent: Wednesday, 20 September, 2006 7:14 AM
To: Mark.Bobak@xxxxxxxxxxxxxxx
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
Other related posts:
