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
|—||Per Second||Per Transaction|
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
|—||Total||per Second||per Trans|
|rollback changes – undo records applied||
|data blocks consistent reads – undo records applied||
|transaction tables consistent reads – undo records applied||
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…