Re: Who is running a long-run job....

From: Ruth Gramolini <rgramolini_at_tax.state.vt.us>
Date: Tue, 23 May 2000 09:41:51 -0400
Message-Id: <10506.106363@fatcity.com>

Here is a script I got from one of the lists to get cpu per session.

/*******************************************************************/
/* FILE         : j_cpusess.sql                                    */
/* PURPOSE      : To display the sessions and their sqltext for cpu*/
/*                intensive processes.                             */
/* AUTHOR       : Jayant R Kulkarni (WIPRO ORACLE SUPPORT).        */
/* DATE         : 14 Jan 1999                                      */
/*******************************************************************/

cl scr
prompt Displaying The CPU Intensive Oracle Processes ... prompt
prompt
!ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
column username format a10
column terminal format a9
column sql_text format a30
prompt
prompt
prompt
prompt Enter The UNIX PID :
accept PID

SELECT a.username, a.terminal, a.program, b.sql_text FROM v$session a, v$sqlarea b, v$process c WHERE ( c.spid = &PID OR a.process = &PID )

AND     a.paddr = c.addr
AND     a.sql_address = b.address

/

HTH,
Ruth
> "Aldi Barco" <ipal_at_hotmail.com> on 05/22/2000 12:26:20 PM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc: (bcc: Lisa Koivu/GELCO)
>
>
>
>
> Hi list..
>
> Users have just complained about dropped performance, more than 10 times
> slower. This was due to one user was running a long-run job i.e importing
a
> big table.
>
> Is there a way to know who is currently running a long-run job ?
> Can we know how much resource (percentage of cpu, mem, etc) is being
> allocated for each user ?
>
> Tia for any clue.
>
> Aldi
> Oracle DBA
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
> --
> Author: Aldi Barco
> INET: ipal_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
>
>
>
>
>
>
> --
> Author:
> INET: Lisa_Koivu_at_gelco.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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
Received on Tue May 23 2000 - 08:41:51 CDT