Re: Causes for high "Rollback per transaction %" in AWR report
From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Tue, 20 Nov 2012 15:46:32 +0100
Message-ID: <50AB97C8.6070501_at_mgm-tp.com>
Jonathan Lewis, 20.11.2012 15:37:
> | 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.
>
Date: Tue, 20 Nov 2012 15:46:32 +0100
Message-ID: <50AB97C8.6070501_at_mgm-tp.com>
Jonathan Lewis, 20.11.2012 15:37:
> | 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.
>
Thanks for the quick respsonse
It indeed seems to be that "no work rollback" type of thing:
Total per Second per Trans
rollback changes - undo records applied 6 0.00 0.00
transaction rollbacks 3 0.00 0.00
user rollbacks 23,511 6.52 0.98
I already requested to get the configuration of the connection pools to find out if e.g. a "validation query" or something like that is configured to be a ROLLBACK statement.
So, seeing the low number of "rollback changes", I assume the bottomline is that I don't need to worry and concentrate on the other bottlenecks that we have already identified.
Regards
Thomas
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 20 2012 - 15:46:32 CET