AWR report is by far one of the best and superior Oracle diagnostic tool. No other database diagnostic tool comes even close to AWR reports. I will discuss one of our recent experiences of using AWR to resolve a complex issue. I consider this as an complex issue as the issue was identified with AWR even though it was not an database issue.
.Note: Our SLA’s are in milliseconds and every change to the database has to be performance tested. You will surprised how sensitive the database/OS/App becomes when you have millisecond SLA’s
.Problem : Response times are really very bad in our performance environment.
What could be the most likely culprits?.
- Newly applied AIX patch
- Application Code
- Unusual Storage activity/SRDF
- Network connectivity issues.
- Database itself
Top wait events from Oracle.
“log file sync” was the top wait event with average wait of 17ms. Normally this wait event is around 8-9ms. We also noticed unusual number of SQL*net break/reset to client. I think important most part of troubleshooting is to try and relate wait events. Since “log file sync” doubled , the obvious culprit becomes our EMC storage which was not the actual case.
But the key to resolve our issue was load profile
From the load profile, the redo size increased from 169 to 1394, the number of block changes increased from 1207 to 15437 which lead to conclusion that some additional unusual activity is occurring in the database. This became more intriguing and complex since there was no change to apps/database. With this clue, we drilled down further into AWR reports and identified few more more major variations.
Let me summarize the above data.
- “transaction rollbacks” increased from 281 to 322,439.
- “undo change vector size” increased from 62.2 to 330.7
- “user commits” increase from 93K to 679K.
- “user rollbacks” remained the same.
Here are some of the key definitions for wait events discussed in this blog
- SQL*Net break/reset : A wait event indicating an error/unhandled exception during execution.
- User Rollbacks: Rollback issued by application/user
- Transaction Rollbacks: Rollbacks performed by Oracle like a transaction could not be completed because of some constraint violation or so.