Wednesday, October 11, 2017

ODC Appreciation Day : AWR

#ThanksODC seems like a good time to knock some rust off my blog.

I would like to direct a thank you to my favorite feature of the Oracle Database, AWR. I did a quick search through my computer for AWR reports, currently I have 720 reports from more than 50 customers, I use this tool all the time. For me, the AWR report is a window into the workload of the database. Here you can see what it is spending most of its time on, if there are any large problems occuring, resource utilization, underlying HW, response time from the disksystem and the list goes on. As long as the customer has the diagnostic license, you can always ask the DBA for an AWR report, it is the easiest way to get detailed information on what the database is doing.

A few use-cases where I think AWR is invaluable:

Sizing, replacement HW and consolidation

An AWR will easily give you information on CPU usage, how fast the current disksystem is and if the database(s) are struggling on current HW. How big will the impact be if I move this database to an Exadata? The Top timed events together with the wait event histogram section will give a clue on how the system will behave with a faster disk system. Also, a graph of CPU usage is good to have in addition to the AWR reports, but the graph will never tell you what the database is spending CPU cycles on.

Troubleshooting, performance problems

This is a given, but often I start asking for an AWR when customers have problems with performance. Is it perfect? No! You only see averages over usually an hour and all info on one session might be hidden. But it's a good starting point for further investigation,.

RAT analysis

For the past year I've been involved with several upgrade project using Real Application Testing when testing their application on 12c. For this we have used RAT to replay the workload from 11g production onto a 12c RAC test database, and for analysis we have used RAT. No automated tools, just plain simple single-node AWR snapshots of 30 minutes. After a few tests you immediately spot when something is off.

So, #ThanksODC for making my life easier with AWR!