Oracle Scratchpad

July 10, 2009

Statspack Skills

Filed under: AWR,Oracle,Performance,Statspack,Troubleshooting — Jonathan Lewis @ 7:28 pm BST Jul 10,2009

I had a great time a couple of weeks ago at the  UKOUG meeting of the DBMS SIG (reported here by Coskan Gundogar). The range of presentations was good and I had a number of interesting conversations.

Of course, the exciting part for me was sitting down with a batch of Statspack and AWR reports that I had been supplied with in the previous couple of days and doing a “real-time” analysis of them.

I’m not sure that I’ve got the balance quite right doing this – the first time I did it for a SIG some of the feedback suggested that it would have been better to say less about each report and work through more reports; and that’s what I tried this time – but I felt that I barely touched on the information that was available in each report.   Next time, I think I’ll pre-select just three reports and give each one about 20 minutes.

When I do this type of thing on a client site, of course, it’s in front of a small group (usually about 6 to 15) of DBAs or developers, with their production system up on the big screen. This has three major benefits:

  • It’s their system, so they recognise the activity that the report is talking about, which makes it easier for them to relate to the type of problems and symptoms we see, and we can spend the time where it’s really helpful.
  • It’s their site, their database and their group (and it’s a small group), so it’s much easier to get a discussion going about where the problems are coming from, and how realistic different solutions might be.
  • It’s a live database so it’s possible to pursue the problem right down to the root cause (and sometimes fix it – or supply the correct fix – immediately). This is an activity that takes us through the whole range of analysis: from symptoms to SQL, to execution plan, to indexing, to data analysis, to optimising SQL, and so on.

If you’ve got a group of DBAs (or developers who want to get more involved with problem solving) this is a really good way to improve their trouble-shooting skills and sort out a few performance problems at the same time. Every time I’ve done an exercise like this the response from the technical group has been very positive, and quite often their management has reported back an increased enthusiasm for problem-solving from their teams.

I’ve even found that when I go on site for one or two days in a pure trouble-shooting role and spend time with just one or two DBAs, they often end up commenting that the ideas and insights that come up in our conversation about the problems are more instructive than seeing the solution itself.

If you’d like to get me on site for an open-ended day where you can really see how trouble-shooting can be done, then drop me an email.



  1. I would say something like ‘great minds …’ but the phrase probably doesn’t apply here ;-)

    It had occurred to me, entirely independently I assure you, to try doing this on the course I do for Oracle. The AWR chapter has a small number of slides on how to approach reading an AWR report based on personal experience and Connie Green’s excellent paper that you’ve mentioned in the past. I’d dig out a couple of example reports and go through them but it seemed a bit lifeless so last time I suggested some attendees mail be some reports. I was slightly overwhelmed by the demand and it was a fascinating experience. In the end, I agree it was a big help when the owners of the systems were there, so we could have a dialogue about what the reports were showing which included their system knowledge.

    I’ll emphasise that my treatment of the reports was *very* light and not the in-depth analysis you could do (in fact a couple of the reports are still in my Inbox for more detailed follow-up). So I’m not comparing our approach or touting for business – you’re the man for this particular job and I have enough of my current clients systems to look at! It was just funny we thought of something similar at similar times and wanted to share my experience of the process.

    Oh, in fact, I noticed you were planning for this presentation when I was receiving reports and thought of suggesting they might want to send them to you too ;-)

    Comment by Doug Burns — July 11, 2009 @ 8:27 am BST Jul 11,2009 | Reply

  2. […] — Jonathan Lewis @ 9:58 pm UTC Jul 21,2009 After I had described the way that I can do on-site, real-time, training in trouble-shooting for a group of DBAs I got a few email messages from American companies asking […]

    Pingback by Qualifications « Oracle Scratchpad — July 21, 2009 @ 9:58 pm BST Jul 21,2009 | Reply

  3. The feedback from my Statspack session arrived a few days ago and I’ve finally found time to read it. It seems that Coskan wasn’t the only one that found it useful – here are a few of the comments:

    “Very useful in taking away some of the mystique which has been created about interpreting statspack reports”

    “A very valuable insight to help assessment of statspack

    “For me the highlight of the event. Seeing Jonathan performing the analysis was quite fascinating and useful to learn from”


    I particularly liked this last one – it’s an accurate assessment and a good suggestion:

    “Very good idea. I feel statspack/awr analysis is a lot to do with experience. Maybe we can continue this as a theme for SIGS as a closing 30 mins for someone to do something similar each time.”

    Comment by Jonathan Lewis — August 7, 2009 @ 5:03 pm BST Aug 7,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

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

Powered by