Re: Which session is killing performance?

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