RE: How to determine what session is using lot of Oracle resources

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Tue, 12 Dec 2000 16:17:52 -0000
Message-Id: <10708.124332@fatcity.com>

Rick,

The following should give you enough information to diagnose which is the problem session, and the info to kill that session.

select s.sid sid,
       s.username session_user,
       s.program program,
       s.osuser osuser,
       s.terminal terminal,
       p.pid pid,
       p.spid spid,
       p.username process_user,
       i.block_gets + i.consistent_gets logrds,
       i.physical_reads phyrds,
       i.block_changes + i.consistent_changes writes,
       1.0 * w.seconds_in_wait / 1.0 seconds_in_wait
  from v$session s,
       v$process p,
       v$sess_io i,
       v$session_wait w
 where s.paddr = p.addr
   and s.sid = i.sid

   and s.sid (+) = w.sid
   and s.type = 'USER'
 order by s.sid

HTH Mark

---

I have gotten suggestions to look at v$session,v$sess_io and v$sesstat. I have looked at these pretty
thouroughly and need some help in interpreting columns needed to answer original question.
Any help is appreciated. Received on Tue Dec 12 2000 - 10:17:52 CST