Re: Causes for high "Rollback per transaction %" in AWR report

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 20 Nov 2012 14:37:38 -0000
Message-ID: <6A324620FEAD411D8ADDEB6276CA60C3_at_Primary>


  • Original Message ----- From: "Thomas Kellerer" <thomas.kellerer_at_mgm-tp.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, November 20, 2012 2:16 PM Subject: Causes for high "Rollback per transaction %" in AWR report

| Hello,
|
| I am analyzing some performance problems of an Oracle 10.2.0.5.0 2-node
RAC system and one figure in the AWR report caught my attention:
|
| Rollback per transaction %: 98.15
|
| What exactly does this figure indicate, and what factors contribute to
this?
|

The first things to check are the stats:

    rollback changes - undo records applied     user commits
    user rollbacks
    transaction rollbacks

If the first figure is very small it doesn't really matter how many rollbacks you are doing, they are "no work" rollbacks. It is a common feature of web app servers to issue a (redundant) rollback after every single statement sent to the database - you may be seeing the effects of such a WAS.

If transaction rollbacks is much larger than user rollbacks then there may be an internal problem of some sort - e.g. a loader program is constantly trying to insert duplicate keys which are rolled back by Oracle, raising an error that should be trapped (but might be ignored) by the application.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 20 2012 - 15:37:38 CET

Original text of this message