RE: High "transaction rollbacks" value in v$sysstat

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Thu, 22 Sep 2005 12:06:56 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C4502361634@NT15.oneneck.corp>

Just an update in case anyone still cares - I found the source of my high 'transaction rollbacks' stats. I captured a 10046 trace on a session that was generating some transaction rollbacks and I found the following in the trace file:

---

PARSING IN CURSOR #1 len=96 dep=0 uid=48 oct=2 lid=48 tim=4021023302 hv=3246794128 ad='65b2b754' insert into REPORT_INDEX_TEMP(REPORT_ID, RECORD_ID, PROJECT_ID, MODULE_ID) values (:1,:2,:3,:4) END OF STMT
EXEC #1:c=0,e=2873,p=0,cr=9,cu=11,mis=0,r=0,dep=0,og=2,tim=4021026271 ERROR #1:err=1 tim=380526050

This same sequence appeared 229 times, which matched exactly with the increase I saw for the value of 'transaction rollbacks' in v$sesstat for this session.

So it appears the problem is due to a flaw in the application causing it to attempt 30 duplicate key inserts every second!

/mnt1/oracle/ ->oerr ora 1
00001, 00000, "unique constraint (%s.%s) violated"

Regards,
Brandon

-----Original Message-----
From: Allen, Brandon
Sent: Monday, September 12, 2005 4:13 PM To: 'Lex de Haan'; oracle-l_at_freelists.org Subject: RE: High "transaction rollbacks" value in v$sysstat

Now to try and figure out exactly what statements are failing and why . . .

>>Re: High "transaction rollbacks" value in v$sysstat
>>From: Phil Jones <phillipjones_at_xxxxxxxxx>
>>To: lex.de.haan_at_xxxxxxxxxxxxxx
>>Date: Sat, 10 Sep 2005 12:55:57 +0000
>>See his recent blog entries:

On 10/09/05, Lex de Haan <lex.de.haan_at_xxxxxxxxxxxxxx> wrote: > yep -- the famous (but less well known) Oracle feature "write consistency"

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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

Received on Thu Sep 22 2005 - 14:10:17 CDT