Re: Oracle processes running wild

From: Bib Endum <gd-newsgroups_at_spamex.com>
Date: Tue, 24 Feb 2004 08:03:14 -0500
Message-ID: <MYH_b.10488$Mo4.316606@news20.bellglobal.com>

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> a écrit dans le message de news:c1f3qd$1hh8m3$1_at_ID-93924.news.uni-berlin.de...
>
> Do not kill the processes on UNIX level, this is not safe.
> It can make Your database unusuable. The sessions are marked
> as "killed" as long as the sessions are "cleaned up" by the processes.
> So the only advice I cound give is: wait until the killed seesions are
gone.
> This may take a while, but this is the safest way.
>
> Maybe others have more ideas ?
>

We have a PL/SQL web application that regularily creates Spinning CPU hogs for no apparent reason, we have to kill those process in order to get the machine back. Those are dedicated server process and we've been killing the processes on the UNIX side automatically for almost 2 years now, without a single problem. Why would it be more dangerous to the database when you kill a UNIX process than when a Windoze machine sporadically reboots or crashes ? And God know, this happens a lot in the M$FT world!

You just have to make sure that you are killing the right UNIX process.

We use the following SQL*Plus script here to determine "who does what"

set trim on
set trimsp on
spool c:\temp\who.txt
set SHOW OFF
set verify off
set linesize 200
set pagesize 500
col username format a20
col osuser format a8
col sid format a4
col serial format a6
col client format a20
col CLI_PID format a10
col SRV_PID format a10
col PROGRAM format a32 truncated
col module format a81
select

   s.username,
   s.osuser,
   substr(s.sid,1,4) sid,
   substr(s.serial#, 1, 8) serial,

   s.machine "CLIENT",
   s.process "CLI_PID",
   s.program,
   p.spid "SVR_PID",

   decode(nvl(s.action, 'NULL'), 'NULL', s.module, s.module||'-'||s.action) "MODULE"
from v$session s, v$process p
where s.paddr = p.addr
order by s.username, s.machine, s.process, s.sid; spool off
host c:\temp\who.txt" Received on Tue Feb 24 2004 - 07:03:14 CST