Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: statspack snapshots cause 3-4 sec of 100% CPU utilization

Re: statspack snapshots cause 3-4 sec of 100% CPU utilization

From: Mogens Nørgaard <>
Date: Mon, 09 Jun 2003 15:04:38 -0700
Message-ID: <>

Good points, Boris. Thanks for that.

If all users are doing the same things, then I guess system-wide data could indicate what's wrong for the average user. You can also use system-wide data to see if something changes overall on the system, then try to dig deeper - but only if you are very experienced and have stared at 100's of Statspack collection outputs and your system is behaving in some constant, predictable manner.


Boris Dali wrote:
<blockquote type="cite"
  Mogens, Dennis,

I know I am going to catch flames for this, but here goes:

It seems to be quite fashionable to bash statspack, but help me out in understanding the alternatives to the StatsPack in the following cases:


At the client I am currently with, they have some 50+ applications. Performance is generally not an issue. Can things be optimized? Oh yeah. No doubt about it. Users don't complain though and 3-person DBA team focuses on operational/prod. support stuff.

3 weeks ago applcation support person took one of the applications "offline" and submitted a job to rebuild the indexes (this functionality is built into the app). DBA team wasn't obviously consulted as to whether or not it's of any benefit. In fact DBA team wasn't even aware that there's a maintenance activity going on :-(

Job dutifully dropped the indexes (as it doesn't seem to be aware of rebuilding, not to mention rebuilding online etc), but got stuck somewhere on re-creating them... so next morning app support person realized that there's a problem and recalled that there is a DBA team "that probably messed up his DB" (from his response to the ticket).

For DBA it didn't take long to see that what used to be a quick index lookup returning a single row, turned out to be a FTS on a 3mln row table as index wasn't re-created there. But...
If OS and StatsPack monitoring was in place DBA would know that long before app support person showed up as iostat -nmxzP on Solaris (or even iostat -d -x on Linux) with StatsPack data (or something as simple as query comparing current v$filestat with baseline one) would be self evident that things changed.

Utility to check execution plans against the baseline ones would be probably useful in this case, but I haven't heard about it before this thread :-)


Application benchmarking.
Last summer while with another client, DBA team was asked to monitor the newly purchased system in stress testing/sizing exercise. While not explicitely stated two additional objectves were to verify the scalability of the app with respect to the number of concurrent users as well as the data volumes. Mercury tools were used in colloboration with the StatsPack on the back-end. We couldn't get more than some 120 concurrent users and StatsPack clearly indicated the load profile w/ ~500,000 LIOs/sec, only ~50 KB/sec redo generation, 300:1 read:write ratio, with latch free being by far the most prominent wait event.
This case is probably an extreme, but when the support engineers of the product received our
Mercury/StatsPack report there was no doubt that "SQL needs to be revisited"

The point I am trying to make is that I think there's monitoring for the sake of tuning (and than I agree - system wide data shouldn't probably drive the tuning effort). But there's also other types of monitoring where StatsPack seems to be quite useful to me. It all depends on the objectives.

Boris Dali.

    for gathering lots
and lots of stats we can't use for anything anyway. When two experts can
look at the same summary data and get to different conclusions you're
not gathering data at the correct level. It's sort of like the
economists that will study the reams of data about GDP, GDI, money
supply 1 thru 6, and what have you - and arrive at complete opposite
conclusions. Same with bstat/estat, StatsPack, our own MirMon, etc.


DENNIS WILLIAMS wrote:          

      Boris - I'm not surprised in your results


Post your free ad now!    Received on Mon Jun 09 2003 - 17:04:38 CDT

Original text of this message