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.
-- --Original Message-- -- From: Mladen Gogala [mailto:gogala@(protected)] Sent: Thursday, September 08, 2005 4:47 PM To: Allen, Brandon Cc: Lou Fangxin; oracle-l@(protected) 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.
