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.sqldocumented in this post. Gets all the plans for a given sql_id.

optimizerstatisticsdocumented 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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Working on Performance Toolkit

  1. Pingback: Toastmaster Talk – DBA Toolkit | Bobby Durrett's DBA Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.