RE: High "transaction rollbacks" value in v$sysstat
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 9 Sep 2005 11:44:35 -0700
Upon further examination, I don't think the description provided in the
Metalink note below could explain what I'm seeing, because if that were the
case, then I would expect a large number of 'enqueue waits' accompanying the
'transaction rollbacks', but I'm not seeing that. Here are the stats from the
worst one hour period yesterday:
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
enqueue waits 295 0.1 0.0
transaction rollbacks 389,303 108.1 2.1
rollback changes - undo records a 394,309 109.5 2.1
user commits 189,364 52.6 1.0
user rollbacks 0 0.0 0.0
Can anyone think of an explanation for this? How can I have 389,303
transaction rollbacks with 0 user rollbacks???
I'll open a TAR and let y'all know if/when I figure it out, but if you have any
ideas, please let me know.
Thanks!
-----Original Message-----
From: Allen, Brandon
Sent: Thursday, September 08, 2005 4:58 PM
To: 'Mladen Gogala'
Cc: Lou Fangxin; oracle-l@xxxxxxxxxxxxx
Subject: RE: High "transaction rollbacks" value in v$sysstat
I found this explanation on Metalink, which might explain it:
----------------------------------------------------------------------------
From: Andrew Allen 26-Apr-05 15:29
Subject: Re : User rollbacks higher then user commits
Could be blocked updates causing the rollbacks. I do not know the source of the
stat in the statspack report, but I will bet that this is it.
You see, when you do an update -- especially when more than one row is
affected, your transaction could rollback and restart (possibly several times)
before it completes. Oracle does not take locks on every row before it does the
update. Instead it takes the row lock as it does the update on that row, so if
you are updating more than one row in a query, you may successfully change the
first few rows, then encounter a row that is locked. In this case your update
will rollback and then attempt to do the update again once the lock is cleared.
Then next pass may update all the rows or it may encounter another lock further
down the line and rollback/restart again. All this happens behind the scenes so
you never know about it.
HTH,
Andrew
----------------------------------------------------------------------------
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
