Oracle HowTo:如何快速杀死占用过多资源(CPU,内存)的数据库进程

  很多时候由于异常或程序错误会导致个别进程占用大量系统资源，需要结束这些进程，通常可以使用以下命令Kill进程:alter system kill session 'sid,serial#';

 但是此命令释放资源极为缓慢，具体可以参考:Oracle中Kill session的研究.
a`R nV Q%i)})i0为了更快速的释放资源，通常我们使用如下步骤来Kill进程:
a_
`$D&IIL%I01.首先在操作系统级kill进程51Testing软件测试网.Y;];wa#m
2.在数据库内部kill session51Testing软件测试网*n K][)?y\OA
这样通常可以快速中止进程，释放资源。

今天就遇到这样一个案例，其他朋友在数据库里kill session,可是长时间仍无效果:51Testing软件测试网jVi'^6A/w,uu7V
[oracle@danaly ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 27 11:09:50 200551Testing软件测试网BW(`a\m

^U5Da8Bf0Copyright (c) 1982, 2005, Oracle.  All rights reserved.51Testing软件测试网6`;ZT8C3fP#l j]jg

-PtU#V#H4{d0Connected to:
:C [g4UFo-@0Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
^'wRK s:U"v0With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options51Testing软件测试网!^$s{&K ~h2Q

n6L&Wll,{0SQL> select sid,username,status from v$session;

       SID USERNAME                       STATUS
d]g(b O0---------- ------------------------------ --------51Testing软件测试网/f"S!Icm[voC7c
....
}2v3Bzju E7?O[0       154 SCOTT                          KILLED51Testing软件测试网~*w$N7oLyzCm@
...51Testing软件测试网!xGh'M'eds(\3L

7W PLB6w{)\030 rows selected.51Testing软件测试网P%t?@8h;_J

那按照我前面提到的步骤，首先查询得到该session对应的OS进程号:51Testing软件测试网#zd/x(A#h!}#a.M
SQL> select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);
*{cM }2O7\@(kV*h0Enter value for sid: 154
S&a8oDEZ q*Z9L%^0old   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid)
m!|*oA6n}.v@+D0az'\g0new   1: select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=154)51Testing软件测试网 `2kzh7Dn(S*W4b

wJ;^7N%x:R0'KILL-9'||SPID
I!wz2X[w~!o0--------------------
Lea;ou0kill -9 22702

SQL> !51Testing软件测试网;NL jx'P8]yO

在操作系统级kill该进程:
.f$k(H"_W0[oracle@danaly ~]$ ps -ef|grep 22702
#M1?0UY(`a0oracle   22702     1  0 Oct25 ?        00:00:02 oracledanaly (LOCAL=NO)
/d+Tc8?8k0oracle   12082 12063  0 11:12 pts/1    00:00:00 grep 22702
%T!YJH]AFzB0[oracle@danaly ~]$ kill -9 22702
/d5t+i(d7Y_3b+M0[oracle@danaly ~]$ ps -ef|grep 2270251Testing软件测试网kyH#])[3N
oracle   12088 12063  0 11:12 pts/1    00:00:00 grep 2270251Testing软件测试网
czME1y`
[oracle@danaly ~]$ exit
@y2?:OLz:N0exit51Testing软件测试网c'W&nG+R$B

SQL> select sid,username,status from v$session;51Testing软件测试网%F(I o&o:[3l

       SID USERNAME                       STATUS51Testing软件测试网/Kuu]m
w\lQ
---------- ------------------------------ --------
2qJ1SgjY [w0...51Testing软件测试网(q'Xwx#chG
       154 SCOTT                          KILLED
yQJ9VFWWo5u0...

30 rows selected.51Testing软件测试网zL1Q;Hu;~

SQL> select sid,serial#,username from v$session where sid=154;

a(K4Nr+}0       SID    SERIAL# USERNAME
mP"ER2h~4yT*X9~0---------- ---------- ------------------------------51Testing软件测试网 pY#Hm/D y
       154      56090 SCOTT

L"q(Y3_B&}0再次在数据库中kill该session,并指定immediate选项:51Testing软件测试网KHv-H Rb_
w3_n.QY
SQL> alter system kill session '154,56090' immediate;51Testing软件测试网n7O%N9h{UmA;~-p

O@Z1C5C(t0System altered.

l%w-pm)a)Q$}0SQL> select sid,serial#,username from v$session where sid=154;51Testing软件测试网/y3}M&|#bK0m3E#^,DN.i

no rows selected51Testing软件测试网q!CHo;hh/S8m


e%J3Upk8M8`0此时该进程被迅速清除。51Testing软件测试网'B.f#} e
k