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

From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Tue, 20 Nov 2012 15:16:32 +0100
Message-ID: <50AB90C0.2020903_at_mgm-tp.com>



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?

During the timeframe of the report hardly any DML was sent to the database (redo size per transaction is 246, Physical Writes per Transaction is 0.16).

Is there anything else apart from actually sending a ROLLBACK from the application that can cause such a high percentage of "Rollback per transaction"?

According to the application's vendor they are not sending any rollbacks (at least not by default) and the logfile of the application do not show any errors that might indicate failed transactions.

Here is the full "Load Profile" section of the AWR report:

                         Per Second       Per Transaction
Redo size:               1,640.61         246.86
Logical reads:          26,665.86       4,012.41
Block changes:              10.58           1.59
Physical reads:            242.28          36.46
Physical writes:             1.07           0.16
User calls:                438.49          65.98
Parses:                    276.59          41.62
Hard parses:                 0.10           0.02
Sorts:                      21.11           3.18
Logons:                      0.11           0.02
Executes:                  278.34          41.88
Transactions:                6.65

% Blocks changed per Read:    0.04  Recursive Call %:       83.69
Rollback per transaction %:  98.15  Rows per Sort:          12.39

Is there any item in the report that could tell me what causes this high rollback ratio?

And is this a problem that I should be concerned about at all?

Thanks in advance
Thomas Kellerer

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

Original text of this message