Boris - I think John has an excellent point (as always). I just remembered that one vendor (can't recall which) has some sort of "stealth" method to directly sample the SQL buffer. They make a big deal about how it doesn't impact your system, so I would infer as John says that with a large shared pool this might be significant. My point is to just collect the information that is of value to you. If a level 0 gets you everything you need, go with that. When I suggest writing your own routines, I'm not proposing that you could collect all the information STATSPACK collects more efficiently, but if you only use one or two pieces of information and you need a level 5 snapshot to get it, then you might consider a quick script to collect just what you need. Also if you need frequent snapshots to capture certain critical data, you can avoid some snapshots. CPU cycles are meant to be used, so if snapshots aren't affecting your overall system, then what is the problem? Well, unfortunately you would like to collect statistics when the system is the busiest. I find STATSPACK to be the most useful when the system appears "hung". With the GUI tools you are still clicking screens when the problem clears itself up. You can take a couple of STATSPACK snapshots and do damage control on the people side in between. But man are those snapshots SLOOOOW when the system is about belly up.
Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Friday, June 06, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Thanks, Dennis. I've been using statspack for quite some time now, but I've never bothered to ask myself an obvious question, namely what overhead does statspack impose on the system (taking about Heisenberg's principle of uncertainty, he-he) I guess part of the reason is the fact that statspack.snap returns prompt almost immediately so I sort of subconsciously assumed that it's ... "light" in terms of resource consumption. Thanks for the suggestion to write my own routines, but I don't think I'll go down this route. It's true that it's probably not too difficult as the statspack schema is pretty much self explanatory with RI constarints in place and besides (supprisingly) statspack package is not wrapped, but ... With every new release/feature you'll need to keep pace, which doesn't sound like fun to me. With standard out-of-the-box statspack you get it for free (stuff like segment stats in 9i statspack) Thanks again, Boris Dali. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Boris - I'm not surprised in your results. I > wouldn't describe STATSPACK as > "brutal", but it is a significant hit, so you > wouldn't want to start doing > snaps at 1 second intervals. STATSPACK does collect > a LOT of data, and you > can adjust the amount of data collected with the > level if you feel the need > to reduce the brutality. If you find you only need a > few pieces of > information, you could write your own routines to > collect just what you > need. > I have no idea why your system mode sees an > impact. Perhaps someone who > has more systems experience can venture a guess. You > might try several > measurements just in case you caught the system at a > bad moment. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -----Original Message----- > Sent: Thursday, June 05, 2003 4:05 PM > To: Multiple recipients of list ORACLE-L > > > As subject line indicates standard (level 5) > snapshots > make "vmstat 1" or "sar -u 1 100" show 100% CPU > utilization (75% system mode) for about 3 seconds. > > Is this normal? Is statspack that brutal on CPU? And > why would that be a system mode primarily? > > Environment: > > Oracle 9.2.0.2 on Mandrake 9.0 > (2.4.19-16mdkenterprise) > 2.4GHz uniprocessor P4 box, 1GB SDRAM > > TIA, > Boris Dali. > > > ______________________________________________________________________ > > 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). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > 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). ______________________________________________________________________ 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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).
