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 StatsPackfor 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
