One simple, and often helpful, option is to
query v$sess_io
Structure:
sid,
block_gets,
consistent_gets,
physical_reads,
block_changes,
consistent_changes
Look for any big numbers - if they are in
physical_reads, the user may have been
hammering the filesystem, the other figures
are indicators of CPU usage.
There is a package (at least, the Sep 1998 version
of it) on my website which I use quite often. It
takes snapshots of such statistics with a user-supplied
time-lag between them, and then reports the differences.
e.g.
set serveroutput on size 1000000 format wrapped
execute performance_snapshot.session_io(30)
This would report i/o related work done by any sessions
active in the last 30 seconds.
This gives a more immediate view of who is currently
doing the work, as it eliminates people who have done
lots of work in the past, but aren't currently doing much.
http://www/jlcomp.demon.co.uk/perf_p.html
is the starting point for the package.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 09 January 2003 16:07
>Newbie question time:
>
>We have 8.1.7 running on Win 2000 with about 50 users. The system has
>just slowed down dramatically for about 5 minutes, with lots of user
>complaints. I suspect one of the developers was loading data or doing
>some DML. But how do I find who it is? I found a small piece of code
on
>AskTom that returned a user whose SQL has been running longer that x
>seconds, but this wont necessarily be the user I'm looking for, will
>it?. Is there something in v$session I should be looking at?
>
>Any help appreciated
>
>Craig Healey
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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).
