Recently one of my customers asked to investigate a database performance problem on a system where for multiple reasons I wasn’t having direct access.

To troubleshoot the performance, I started asking few AWR reports; at the first glance I didn’t spot any bottleneck, but while re-reading the statistics I found a strange ration between number of Transactions and Rollbacks.

Extract of AWR Load Profile

Because all others OS and database statistics were quite good, I decided to follow the trail of the high percentage of transactions rolled back.

Before any fast conclusion I checked the nature of the rollbacks:

• transaction rollbacks: Oracle is automatically executing a rollback, this happens for example in case of constraint violation (i.e. Primary Key violation).

• user rollbacks: number of times users manually issue the ROLLBACK statement or an error occurs during users’ transactions.

Extract of AWR Activity Stats

From the AWR analysis has emerged the following data:

• 87% of the user’s transactions ended with a rollback.

• While rolling back, Oracle applies 25.6 undo records/sec. which means that at least a certain percentage of user rollbacks generates “real work“.

• Even more important in term of performance analysis
  • number of data block consistent reads 235.14 records/sec.
  • number of consistent changes 3109.48 times/sec.

Because the database load profile is 90% SELECT and 10% DML, and there are important values about data block consistent reads and consisten changes, we can assume that most of the activity is concentrated in a small number of user objects.

Next step: I have to gain access to the system and continue the investigation…