Re: Which session is killing performance?

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).