Querying the Top 5 Timed Events

Querying the Top 5 Timed Events

In a previous life, I spent the better part of two years digesting other people's AWR reports.  Text, HTML, Statspack etc., all in an effort to show how slow their storage was an how much better their databases would perform on our all-flash storage array.

One of the frustrations of this job was that every problem generally only had one solution (a flash storage array, if you hadn't guessed) and along the way I encountered some pretty horribly tuned databases but of course could do nothing about that.

I did get to work with some excellent and talented people though, including the English duo of Chris Buckel and Ash Ubhi who have produced some sterling work in the field of AWR analysis - and parsing AWR reports and transforming them into lovely Excel charts...  All very useful sales stuff and actually quite essential for in-house DBA's for reporting purposes.  Most management like a pretty chart.

Writing my own AWR scripts to circumvent having to run a thousand reports led me down various rat holes - I was really looking for the top 5 timed events in query form.... and then I stumbled across the blog of my fellow Enkitecian, Alex Fatkulin.  In his blog entry (that's where the link goes), he provides a script to do just what I wanted.   Here's Alex's original script.

As you can see, it will give a nicely formatted output....

Top 5 Wait Events

Great for various purposes so utilizing the work Alex already did, I modified it slightly to give me statistics that I could turn into useful presentation materials.  Here's a snippet of the data I extracted:

More Top 5 Wait Events

In general, I only care about a few events - I/O or contention related for my purposes on this client engagement.  

Graphical wait event data

Not rocket science but it makes my life easier.  My code modifications to the original are minimal.

Have fun with your own AWR analysis.