The following excerpt is taken from the “Load Profile” section of an Oracle AWR report:

This data shows the database is processing 5,595.6 transactions per second, while also encountering 1,388.2 rollbacks per second. On the surface, this suggest that nearly a quarter of all transactions are being rolled back—seemingly a cause for concern. However, in the real-world Oracle environments, a high number of rollbacks doesn’t necessarily imply heavy or problematic rollback activity.

In Oracle AWR reports, the “Rollbacks” figure corresponds to the statistic user rollbacks. To properly assess whether rollback activity is abnormally high, it’s essential to consider this metric alongside two related Oracle statistics: transaction rollbacks and rollback changes – undo records applied.

To better understand the behavior of these statistics, several controlled experiments were conducted in a test environment.

In the initial experiment, an explicit ROLLBACK command was executed without any preceding DML operations.

SQL> rollback;

Rollback complete.

SQL> SELECT m.SID, s.name, m.VALUE -
  2  FROM v$mystat m -
  3  JOIN v$statname s ON m.STATISTIC# = s.STATISTIC# -
  4  WHERE s.name IN (
  5      'transaction rollbacks',
  6      'user rollbacks',
  7      'rollback changes - undo records applied'
  8* );

    SID                                       NAME    VALUE
_______ __________________________________________ ________
   4005 user rollbacks                                    1
   4005 rollback changes - undo records applied           0
   4005 transaction rollbacks                             0

The session-level statistics showed that the user rollbacks counter increased by one, reflecting the explicit rollback. However, both transaction rollbacks and rollback changes - undo records applied remained at zero, confirming that no actual transaction or undo operations occurred during this rollback.

In the second experiment, an INSERT operation was attempted that violated a unique constraint on the primary key.

SQL> INSERT INTO t1 (id, a) VALUES (1, 'B');

Error starting at line : 1 in command -
INSERT INTO t1 (id, a) VALUES (1, 'B')
Error report -
ORA-00001: unique constraint (YUAN.SYS_C009824) violated

SQL>
SQL> SELECT m.SID, s.name, m.VALUE -
  2  FROM v$mystat m -
  3  JOIN v$statname s ON m.STATISTIC# = s.STATISTIC# -
  4  WHERE s.name IN (
  5      'transaction rollbacks',
  6      'user rollbacks',
  7      'rollback changes - undo records applied'
  8* );

    SID                                       NAME    VALUE
_______ __________________________________________ ________
   4005 user rollbacks                                    1
   4005 rollback changes - undo records applied           1
   4005 transaction rollbacks                             1

This led to an error and an automatic rollback. A subsequent check of the statistics revealed that both transaction rollbacks and rollback changes – undo records applied had increased by one. Oracle first inserts the row into the table and obtains the rowid, then attempts to insert the primary key and rowid of the row into the index entry. Upon encountering a duplicate primary key, it reverts the inserted row in the table, and the single undo record applied corresponds to this reversal. The user rollbacks value remained unchanged, as this was an implicit rollback triggered by Oracle.

In the third experiment, a successful INSERT was executed, followed by an explicit ROLLBACK.

SQL> INSERT INTO t1 (id, a) VALUES (2, 'c');

1 row inserted.

SQL> rollback;

Rollback complete.

SQL> SELECT m.SID, s.name, m.VALUE -
  2  FROM v$mystat m -
  3  JOIN v$statname s ON m.STATISTIC# = s.STATISTIC# -
  4  WHERE s.name IN (
  5      'transaction rollbacks',
  6      'user rollbacks',
  7      'rollback changes - undo records applied'
  8* );

    SID                                       NAME    VALUE
_______ __________________________________________ ________
   4005 user rollbacks                                    2
   4005 rollback changes - undo records applied           3
   4005 transaction rollbacks                             2

All three counters increased: one user rollback, one additional transaction rollback, and two undo records applied—corresponding to the inserted row and the index entry.

In the final experiment, a bulk INSERT was performed, followed by an explicit ROLLBACK:

SQL> INSERT INTO t1
  2     SELECT LEVEL+1, 't'  FROM dual
  3*    CONNECT BY LEVEL <= 10000;

10,000 rows inserted.

SQL> rollback;

Rollback complete.

SQL> SELECT m.SID, s.name, m.VALUE -
  2  FROM v$mystat m -
  3  JOIN v$statname s ON m.STATISTIC# = s.STATISTIC# -
  4  WHERE s.name IN (
  5      'transaction rollbacks',
  6      'user rollbacks',
  7      'rollback changes - undo records applied'
  8* );

    SID                                       NAME    VALUE
_______ __________________________________________ ________
   4005 user rollbacks                                    3
   4005 rollback changes - undo records applied         132
   4005 transaction rollbacks                             3

This demonstrates that while the number of rollbacks remained small, the work involved in the rollback was significant—129 undo records were applied.

Conclusion

While user rollbacks and transaction rollbacks indicate how frequently rollbacks occur, rollback changes - undo records applied reflects how much data Oracle actually reversed. This statistic offers the most accurate representation of rollback effort within the database.

Superfluous user rollbacks are not uncommon in real-world systems. They often result from connection pool resets, client-side framework cleanups, precautionary rollbacks in scripts, or error-handling routines. These rollbacks may increment statistical counters but involve little actual undo work, meaning their impact on system performance is often negligible—even if they appear concerning in AWR reports.