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
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+
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
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,
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
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
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
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
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
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
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
[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
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
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
14 matches
Mail list logo
