| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Which session is killing performance?
Once you know the thread id (ex. 2496) you can do the following
SELECT s.osuser,s.program,s.terminal /* whatever you want to see */
FROM v$session s, v$process p
WHERE p.spid = '2496'
AND p.addr = s.paddr;
Rick
"Igor Neyman"
<ineyman_at_perce To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
ptron.com> cc:
Sent by: Subject: Re: Which session is killing performance?
root_at_fatcity.c
om
01/09/2003
04:25 PM
Please respond
to ORACLE-L
True, but how you associate thread# in PerformanceMonitore with Oracle's spid?
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
>
> I believe you can if you use performance monitor and chart by thread.
>
> Rick
>
>
>
>
> "Igor Neyman"
> <ineyman_at_perce To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> ptron.com> cc:
> Sent by: Subject: Re: Which session
is
killing performance?
> root_at_fatcity.c
> om
>
>
> 01/09/2003
> 02:54 PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Ference,
>
> Oracle on Windows runs as one multithreaded process, so you wouldn't get
> specific session id sorting list of processes by CPU.
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, January 09, 2003 1:57 PM
>
>
> > Since you are on Weenie-doze:
> > go to task manager (3 finger salute [alt+ctrl+delete]), and order by
CPU
> > descending, then look at the PID, make a note of it, and run this
script,
> > entering the PID:
> >
> > column log_tim format a18
> > col username format a12
> > col osuser for a12
> > set linesize 132
> > set verify off
> > set pagesize 80
> > col program for a20
> > accept id prompt 'Enter shadow process ID : '
> > select osuser, a.program, sid, a.serial#, sql_address, prev_sql_Addr,
> > taddr,
> > to_char(logon_time,'DD:HH24:MI:SS') log_tim, spid from v$session a,
> > v$process b
> > where b.spid=('&&id') and a.paddr = b.addr(+) and a.type !='BACKGROUND'
> > order by logon_time desc ;
> >
> > Once you have SQL_ADDRESS, then run THIS script, copying and pasting
the
> > SQL_ADDRESS value:
> >
> > set pagesize 200
> > set verify off
> > accept adr char prompt "Enter SQL_ADDRESS for session whose SQL you
wish
> to
> > see : "
> > select ltrim(rtrim(sql_text)) from v$sqltext where address = '&&adr'
> order
> > by piece;
> >
> >
> > Or if you have the PID, launch Toad, and get the Trace/Kill view and
> filter
> > it for the PID, it will show you the SQL in the bottom pane. Much
quicker
> > with TOAD.
> >
> > Regards :
> >
> > Ferenc Mantfeld
> >
> > -----Original Message-----
> > From: Craig Healey [SMTP:C.Healey_at_hhsuk.com]
> > Sent: Friday, January 10, 2003 1:40 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Which session is killing performance?
> >
> > 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: Igor Neyman INET: ineyman_at_perceptron.com 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Rick_Cale_at_teamhealth.com 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).Received on Fri Jan 10 2003 - 07:09:10 CST
 |
 |