Working on Performance Toolkit

I’m working on a performance toolkit with a coworker of mine. It is great to have someone else to use the tools I’ve been using, streamline them, and document them for the rest of our team.

To figure out what to include in our toolkit I reviewed the performance problems I had worked on for the past couple of years. I keep a folder on a network drive for each incident. I found three scripts (or groups of scripts) that I used more than any other.  So, we decided to document these for the team. I use a bunch of other scripts so it is interesting to think about why these three are the top of the list.  Here is what they are:

AWR report – this is just the standard Oracle delivered AWR report $ORACLE_HOME/rdbms/admin/awrrpt.sql.  Shows the difference between two snapshots of the V$ tables.

getplans.sql – documented in this post. Gets all the plans for a given sql_id.

optimizerstatistics – documented in my SEOUC presentation. Dumps out all the statistics for tables used by a query you are tuning.

So, the process boils down to using the AWR report to find a problem SQL query, using getplans.sql to get the plan(s) for that query, and using the querytuning scripts to dump out the stats for the tables used by that problem query.

But, I use a bunch of other scripts and tools besides these so why are these the top three?  I think it must be because much of the time performance problems boil down to SQL tuning or at least the need for SQL tuning needs to be explored.  But it remains to be seen how well my coworker and I can communicate this to our team and how helpful it would be to just have these three tools as an initial information gathering tool when a performance problem comes in.

– Bobby