(14)应急常备的KILL SQL

       在项目出现程序处理不当或死锁等原因,造成一个session长期占了大量的资源不释放, 导致Oracle中进程不停的

大量占有操作系统资源,进而让应用处理效率变得很低,甚至有若假死,估计做数据库的都碰到过。因为通常不能重启实例,

所以最有效的治标方法就是kill掉有问题的session. 我在这弄了个非常方便的代码介绍给大家使用。

    首先把这篇的重点,SQL贴出来: 

  select s.inst_id,s.status,s.program,s.terminal,
         'alter system kill session '||''''||s.sid||','||s.serial#||',@'||s.inst_id ||''';' as oracle_kill,
         'kill -9 '||p.spid as unix_kill,
         'orakill '||i.instance_name||' '||p.spid as win_kill,
         s.sid,s.serial#,p.spid,
         s.last_call_et as exec_seconds,t.sql_text as curr_sql
  from gv$session s,v$process p,v$instance i,v$lock k, v$sqltext t
  where s.paddr = p.addr
  and s.type != 'BACKGROUND'
  and s.lockwait = k.kaddr
  and s.sql_hash_value = t.hash_value
  and s.username is not null

简单说明下:

     oracle_kill :  在Oracle下杀掉指定的会话，注意@后面的，代表Oracle RAC的实例序号，单实例默认为1.

        unix_kill :  UNIX/Linux下，杀进程的命令

         win_kill :   Windows下Oracle官方提供的杀死死锁线程的命令

        exec_seconds :  死锁了多长时间

        curr_sql :  当前死锁对应的SQL语句

熟悉的看完上面就够了，不了解的，我下面来解释。

  

先弄个死锁出来，方便后面看效果，同时也让你知道，死个锁太容易了。 哈哈。

 1. 开个会话1.

SQL> create table demo.test_lock(id number,name varchar2(10));
 
Table created
 
SQL> set serveroutput on
SQL> insert into demo.test_lock(id,name) values(1,'a');
 
1 row inserted
 
SQL> insert into demo.test_lock(id,name) values(2,'b');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 

SQL> update demo.test_lock set name='aa' where id = 1;

2. 新开会话2

SQL> update demo.test_lock set name='bb' where id = 2;
 
1 row updated

3. 回到会话1

SQL> update demo.test_lock set name='bb2' where id = 2;
 
update demo.test_lock set name='bb2' where id = 2
 
ORA-00060: deadlock detected while waiting for resource

4. 回到会话2,执行下面更新，光荣的死锁了。

SQL> update demo.test_lock set name='aa2' where id = 1;

手工kill常分两步走:

   1. 列出所有session 找到需要kill掉的会话ID和序列号 

  -- 数据库用户名,操作系统用户名,会话ID,序列号和系统进程ID(SPID)
    select s.username, s.osuser, s.sid, s.serial#, p.spid, s.program,s.STATUS
    from v$session s,v$process p
    where s.paddr = p.addr and s.username is not null;

 2. 执行kill命令   

     --sid,serial#分别对应 会话ID和序列号
     alter system kill session  'sid,serial#';
   --你要碰上RAC,
    alter system kill session 'sid,serial#,@inst';

         但执行完这些,你会发现并不会立竿见影地出效果. 只是session状态变成了KILLED,资源还是被占用着. 原来, Oracle认为,

在分配给这个进程的锁和资源在session完全超时前是不会被释放的. 这意味着如果你要等Oracle PMON后台进程把资源回收,还要一段时间.

      但实际情况往往不能等.  可以在操作系统中清理掉这个对应的spid,Oracle会马上收到进程中断的通知,立即启用来PMON来清除 session.只是要小心,别把Oracle 自带的那些核心进程kill了. 搞错的后果有可能是实例崩掉.呵呵.

  分别介绍下Unix/Linux和Windows下怎么弄:

   Unix/Linux下:

    前面SQL中可以查出"系统进程ID(SPID)"

      kill -9 系统进程ID(SPID)即可.

    或 ps -ef 去找到对应的后台进程,再kill -9也一样.

   Windows下,痛苦点.

       Oralce在Windows下就一个进程 Oracle.EXE,需要用第三方的工具(PsTools之类),查到对应的线程,再杀掉.

       除了这种方法,Oracle官方也提供了OraKill.exe程序.放在$ORACLE_HOME/bin目录下. 在cmd.exe中打orakill可以看到其帮助.

C:\Users\XCL>orakill

Usage:  orakill sid thread

  where sid    = the Oracle instance to target
        thread = the thread id of the thread to kill

  The thread id should be retrieved from the spid column of a query such as:

        select spid, osuser, s.program from
        v$process p, v$session s where p.addr=s.paddr

分开步骤:

  a.得到实例sid: 

     select * from v$instance;

  b.得到orakill命令,过滤 v$bgprocess ,排除掉Oracle本身的核心后台线程         

        select spid as thread_id, osuser, s.program,
           ' orakill 实例sid '||spid as execmd
         from v$process p, v$session s
         where p.addr=s.paddr
           and not exists ( select b.paddr from v$bgprocess b
                where p.addr = b.paddr )

 上面说的都是数据库还可以连的情况. 不过有时已经搞得很慢很慢,连都可能快连不上了.

   那就直接去操作系统下kill吧.

    Unix/Linux下:

       直接去ps然后kill -9 .

   Windows下   

     去下载一个功能强大的 PsTools 包吧.解压后即可使用.

     查看windows线程:     

        --显示Oracle的进程信息
        pslist -e oracle
         --显示Oracle进程的线程信息
         pslist -e oracle -d

         不过痛苦的事,这个不像ps -ef|grep oracle 那样全,这里不会显示线程名称.

         只能依资源消耗来大致判断是哪个Tid.

         再用 orakill 实例sid 线程id

          或 pskill 杀掉线程.

         要是觉得命令行不爽,可以下个 ProcessExplorer 图形用户界面.

         PsTools 包下载地址

         http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx

         ProcessExplorer 下载地址

         http://technet.microsoft.com/zh-cn/sysinternals/bb896653.aspx

 最后说一句, 这都是治标不治本的方法,找出真正问题所在,才是根本.

 MAIL: xcl_168@aliyun.com

BLOG: http://blog.csdn.net/xcl168