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

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Mon, 09 Jun 2003 09:20:40 -0700
Message-ID: <F001.005ADA50.20030609085440@fatcity.com>

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:

Case1:

---

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 :-)

Case2:

---

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.

Cheers,
Boris Dali.

• Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > Couldn't agree more. We need to stop using StatsPack
  > 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.
  >
  > Mogens
  >
  > DENNIS WILLIAMS wrote:
  > >Boris - I'm not surprised in your results
  ....

  ---

Post your free ad now! http://personals.yahoo.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boris Dali
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jun 09 2003 - 11:20:40 CDT