RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Nice one, John!
And quite portable. Runs on Solaris and Linux alike.
On HP-UX 11.0 I had to modify it slightly, but it
looks good too:
if [ $# -eq 1 ] ;then
UNIX95= ps -eo pid,pcpu,ruser,time,etime,args | grep
$1 | sort -nr +1 | awk '{if (NR = 20) print
substr($0,1,80)}'
else
same as above wo/ grep
fi
I usually used BSD stile of ps on Linux. Something
like:
ps aufx | egrep '(USER|oracle)'
with exporting COLUMNS=200 prior to running this and
stiking the puppy into an alias. But sorting by %CPU
looks cool. (Solaris doesn't seem to understand the f
flag and it should be /usr/ucb/ps, not default
/usr/bin/ps)
Ok, back to the original issue :-)
Since snapshot causes 100% CPU util only for some 3
sec it was quite a challenge to catch it. The best I
got so far is 72% for the shadow process taking the
snapshot (after some 20 tries). I should probably put
it in an infinite loop and spool (or tee) it to a
file. Might as well try to catch /proc/pid/status (as
I don't have the luxury of pmap here on Linux) for
some memory stuff.
One thing, John. Since it doesn't split CPU
utilization into user and kernel buckets - how does
this help me? Wouldn't it be nice to get output
similar to ptime [or at least time(x)] in ps output?
I am thinking of taking Dennis's advice and see what
happens with level=0 or simply start commenting out
code in the package and see when high CPU utilization
drops (and I was hopping to go with level=7 to get
some segement stats - he-he)
Thanks, John.
Cheers,
Boris Dali.
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Boris,
Nice to discuss this with someone who understands
the numerous (and various)
options of 'sar' :) I use 'sar -r' to cross verify
the 'rate of need for
swap' - a sudden increase may mean either bursts of
I/O (eating up File
buffer space), memory leaks or a sudden rush of
programs Could you take
a quick snapshot of the top 20 CPU consumers using
the script below when the
snapshot runs? It takes the SID as a parameter to
grep out only Oracle
processes for that SID. The interesting part is that
the CPUTIME *and*
ELAPSED time is shown - you should run the snapshot
as a script (as in
sqlplus perfstat/ @snapshot.sql) where
snapshot.sql has an execute,
followed by an exit. This way, one has a crude set
of CPU and Elapsed time
for that process as it runs...
I use this to quickly point out processes that are
heavy and consistent CPU
consumers, allowing me to rap some knuckles ;-)
#!/bin/ksh
#
# Name: top20.ksh
# Purpose: Display the top 20 CPU consumers.
Specify a SID to collect
# only those top procs related to that
SID in a multi-db system
# Author:John Kanagaraj, DBSoft Inc/ Aug 2001
# Notes: Tested and works on Solaris - may need
adjustment for other OS
#
uptime
echo PID %CPURUSER CPUTIME ELAPSED
COMMAND
if [ $# == 1 ]; then
ps -eo pid,pcpu,ruser,time,etime,args | grep
$1 | sort -nr +1 |
head -20 | awk '{print substr($0,1,80)}'
else
ps -eo pid,pcpu,ruser,time,etime,args | sort
-nr +1 | head -20 |
awk '{print substr($0,1,80)}'
fi
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Great, uplifting music - http://www.klove.com
** The opinions and statements above are entirely my
own and not those of my
employer or clients **
__
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).
Re: statspack snapshots cause 3-4 sec of 100% CPU utilization
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).
Re: statspack snapshots cause 3-4 sec of 100% CPU utilization
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_Nrgaard [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
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
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Boris, The default statspack snapshot is at level 5, which collects Top SQL (by buffer and Phys reads, etc.) from the Shared pool, and that would cause significant latching for a large shared pool which in turn results in a high CPU usage. You could try a level 0 snapshot and look at the CPU utilization at that time... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Boris Dali [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 2:05 PM To: Multiple recipients of list ORACLE-L Subject: statspack snapshots cause 3-4 sec of 100% CPU utilization 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: John Kanagaraj 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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John. Any insight as to why is it primarily the system mode CPU? I've seen high system mode CPU utilization in non-Oracle stuff (like NFS), but for Oracle I thought it should be primarily user mode? Does Oracle's CPU used by this session represents user-, kernel-mode or both? And what about c in the raw traces? Thanks John, Boris Dali. --- John Kanagaraj [EMAIL PROTECTED] wrote: Boris, The default statspack snapshot is at level 5, which collects Top SQL (by buffer and Phys reads, etc.) from the Shared pool, and that would cause significant latching for a large shared pool which in turn results in a high CPU usage. You could try a level 0 snapshot and look at the CPU utilization at that time... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Boris Dali [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 2:05 PM To: Multiple recipients of list ORACLE-L Subject: statspack snapshots cause 3-4 sec of 100% CPU utilization 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: John Kanagaraj 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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
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 SLW 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
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, Dennis. I am with you on your point about GUI tools... --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: 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 SLW when the system is about belly up. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] __ 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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John. No there's no paging/swapping going on (1GB real memory for a single 200MB SGA and just a couple of users). Out of curiousity, John. I usually measure paging via vmstat (si/so columns on Linux and pi/po everywhere else - everywhere else being HP-UX, Solaris and AIX), as well as via sar -w (swpin/s, swpot/s) on HP-UX/Solaris and sar -W on Linux (pswpin/s, pswpot/s). Is sar -r a better way? Quick check shows that on Linux it seems to report memory and swap utilization (but not in terms of rates, rather absolute numbers). On HP-UX it doesn't seem to be covered by man pages, but effectively the output is the same as -w. On Solaris it shows unused memory pages and disk blocks. And I don't currently have any IBM boxes around As for the wrong bucket... well, I'll be able to verify it in the next couple of weeks on Solaris and for sure on HP-UX. One thing I know is that both vmstat and sar -u agree here on Mandrake that it is the kernel-mode that chews up most of the CPU for this 3-4 sec snapshot time. Thanks, Boris Dali. --- John Kanagaraj [EMAIL PROTECTED] wrote: Boris, I missed the second part of your question... apologies. If your SGA/Shared pool was partly swapped out, I would assume that you might see an increased 'system' utilization. Did you check 'sar -q' and 'sar -r' at the same time to check? I haven't used mandrake - just wondering if the CPU cycles used for memory access are being counted against the wrong pigeonhole.. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve; Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** __ 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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Boris,
Nice to discuss this with someone who understands the numerous (and various)
options of 'sar' :) I use 'sar -r' to cross verify the 'rate of need for
swap' - a sudden increase may mean either bursts of I/O (eating up File
buffer space), memory leaks or a sudden rush of programs Could you take
a quick snapshot of the top 20 CPU consumers using the script below when the
snapshot runs? It takes the SID as a parameter to grep out only Oracle
processes for that SID. The interesting part is that the CPUTIME *and*
ELAPSED time is shown - you should run the snapshot as a script (as in
sqlplus perfstat/ @snapshot.sql) where snapshot.sql has an execute,
followed by an exit. This way, one has a crude set of CPU and Elapsed time
for that process as it runs...
I use this to quickly point out processes that are heavy and consistent CPU
consumers, allowing me to rap some knuckles ;-)
#!/bin/ksh
#
# Name: top20.ksh
# Purpose: Display the top 20 CPU consumers. Specify a SID to collect
# only those top procs related to that SID in a multi-db system
# Author:John Kanagaraj, DBSoft Inc/ Aug 2001
# Notes: Tested and works on Solaris - may need adjustment for other OS
#
uptime
echo PID %CPURUSER CPUTIME ELAPSED COMMAND
if [ $# == 1 ]; then
ps -eo pid,pcpu,ruser,time,etime,args | grep $1 | sort -nr +1 |
head -20 | awk '{print substr($0,1,80)}'
else
ps -eo pid,pcpu,ruser,time,etime,args | sort -nr +1 | head -20 |
awk '{print substr($0,1,80)}'
fi
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Great, uplifting music - http://www.klove.com
** The opinions and statements above are entirely my own and not those of my
employer or clients **
-Original Message-
From: Boris Dali [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John.
No there's no paging/swapping going on (1GB real
memory for a single 200MB SGA and just a couple of
users).
Out of curiousity, John. I usually measure paging via
vmstat (si/so columns on Linux and pi/po everywhere
else - everywhere else being HP-UX, Solaris and
AIX), as well as via sar -w (swpin/s, swpot/s) on
HP-UX/Solaris and sar -W on Linux (pswpin/s,
pswpot/s).
Is sar -r a better way?
Quick check shows that on Linux it seems to report
memory and swap utilization (but not in terms of
rates, rather absolute numbers). On HP-UX it doesn't
seem to be covered by man pages, but effectively the
output is the same as -w. On Solaris it shows unused
memory pages and disk blocks. And I don't currently
have any IBM boxes around
As for the wrong bucket... well, I'll be able to
verify it in the next couple of weeks on Solaris and
for sure on HP-UX. One thing I know is that both
vmstat and sar -u agree here on Mandrake that it is
the kernel-mode that chews up most of the CPU for this
3-4 sec snapshot time.
Thanks,
Boris Dali.
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Boris,
I missed the second part of your question...
apologies. If your SGA/Shared
pool was partly swapped out, I would assume that you
might see an increased
'system' utilization. Did you check 'sar -q' and
'sar -r' at the same time
to check? I haven't used mandrake - just wondering
if the CPU cycles used
for memory access are being counted against the
wrong pigeonhole..
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Grace - Getting something we don't deserve; Mercy -
NOT getting something we
deserve
Click on 'http://www.needhim.org' for Grace and
Mercy that is freely
available!
** The opinions and statements above are entirely my
own and not those of my
employer or clients **
__
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: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
Re: statspack snapshots cause 3-4 sec of 100% CPU utilization
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. 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Boris,
I missed the second part of your question... apologies. If your SGA/Shared
pool was partly swapped out, I would assume that you might see an increased
'system' utilization. Did you check 'sar -q' and 'sar -r' at the same time
to check? I haven't used mandrake - just wondering if the CPU cycles used
for memory access are being counted against the wrong pigeonhole..
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Grace - Getting something we don't deserve; Mercy - NOT getting something we
deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and statements above are entirely my own and not those of my
employer or clients **
-Original Message-
From: Boris Dali [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 7:45 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John.
Any insight as to why is it primarily the system mode
CPU? I've seen high system mode CPU utilization in
non-Oracle stuff (like NFS), but for Oracle I thought
it should be primarily user mode?
Does Oracle's CPU used by this session represents
user-, kernel-mode or both? And what about c in the
raw traces?
Thanks John,
Boris Dali.
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Boris,
The default statspack snapshot is at level 5, which
collects Top SQL (by
buffer and Phys reads, etc.) from the Shared pool,
and that would cause
significant latching for a large shared pool which
in turn results in a high
CPU usage. You could try a level 0 snapshot and look
at the CPU utilization
at that time...
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Disappointment is inevitable, but Discouragement is
optional!
** The opinions and statements above are entirely my
own and not those of my
employer or clients **
-Original Message-
From: Boris Dali [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 2:05 PM
To: Multiple recipients of list ORACLE-L
Subject: statspack snapshots cause 3-4 sec of 100%
CPU utilization
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: John Kanagaraj
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
