RE: High "transaction rollbacks" value in v$sysstat
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Mladen Gogala" <gogala@xxxxxxxxxxxxx>
- Date: Thu, 8 Sep 2005 16:58:24 -0700
Thanks, but I don't see how auditing would be helpful in this situation. I'm
trying to find the source of 'transaction rollbacks' w/o corresponding 'user
rollbacks'. If Oracle isn't even incrmenting the user rollbacks stat, then I
doubt it is writing anything to the audit trail either.
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
----------------------------------------------------------------------------
-----Original Message-----
From: Mladen Gogala [mailto:gogala@xxxxxxxxxxxxx]
Sent: Thursday, September 08, 2005 4:47 PM
To: Allen, Brandon
Cc: Lou Fangxin; oracle-l@xxxxxxxxxxxxx
Subject: Re: High "transaction rollbacks" value in v$sysstat
On 09/08/2005 07:19:00 PM, Allen, Brandon wrote:
> Not sure, I don't have good visibility into the application (Tririga Facility
> Center 8i). But if the application is rolling back, shouldn't I see 'user
> rollbacks' being incremented at least as much as 'transaction rollbacks'?
I have a very simple solution: look into DBA_AUDIT_TRAIL. Turn on auditing.
People often
forget to do that, but that is the only way to know what has someone executed
during the last
week or some other extended period of time.
--
Mladen Gogala
http://www.mgogala.com
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:
