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

What you need is detailed information over time. Start with SQL statements, into sessions, into the instance level. The problem is that this means that tons of data will be collected. So one has to be clever and reduce the amount of data collected. If you have data over time (SQL, session, instance), one can do some base lining and exceptions can be alerted on. If during monitoring a problem is detected one should have enough data to go back and analyze the problem without having to run the problem program again. And all of this has to be done with very little overhead. Performance problems can be repeatable or not repeatable (or at least difficult to predict when they will happen), you need to be able to fix both. SQL and Session data is probably the most valuable performance data, but collecting them can be expensive (more expensive in the case that you don't know when the performance problem will happen).

How ever these problem(s) have been fixed and addressed by companies like Precise, Quest and BMC (probably others). They offer SQL statement and session information that can be folded into instance information.

Anjo.

> Mogens Nørgaard wrote:
>
> 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.
>
> Mogens
>
> Boris Dali wrote:
>
> > 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: Anjo Kolk
  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).