Re: Which session is killing performance?

True, but how you associate thread# in PerformanceMonitore with Oracle's spid?

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 3:59 PM

>
> 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
> >
> >
> > ************************************************************************
> > **********
> >
> > This email and any files transmitted with it are confidential and
> intended
> > solely
> > for the use of the individual or entity to whom they are addressed and
> may
> > contain
> > confidential and/or privileged material. Any review, retransmission,
> > dissemination
> > or other use of, or taking of any action in reliance upon, this
> information
> > by
> > persons or entities other than the intended recipient is prohibited.
> > Statements
> > and opinions expressed in this e-mail may not represent those of the
> > company.
> >
> > If you have received this email in error please notify
> > system.administrator_at_hhsuk.com
> >
> > This footnote also confirms that this email message has been swept by
> > MIMEsweeper
> > for the presence of computer viruses (www.mimesweeper.com)
> >
> > ************************************************************************
> > ***********
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Craig Healey
> > INET: C.Healey_at_hhsuk.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: mantfield
> > INET: mantfield_at_connexus.net.au
> >
> > 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: 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).
>

-- 
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).