Matando Sessões KILLED

6 Replies

Algumas vezes sessões ficam “presas” no banco de dados com status KILLED. Elas estão/são mortas e mesmo assim não desaparecem da gv$session, ficam como “zumbis”. Algumas podem até impedir o shutdown do banco de dados. Aqui vamos ver como resolver isso.

Felizmente resolver este problema não é complicado, mas temos que verificar algumas coisas antes de sair matando os processos do Oracle. De forma resumida (e bem resumida) toda a conexão feita pelo usuário ao banco de dados cria um processo no servidor que fica responsável por fazer a comunicação com o kernel do banco de dados. Ao finalizarmos este processo a conexão do usuário é terminada e o Oracle libera a sessão do usuário.

No exemplo abaixo temos uma lista de sessões que estão marcadas como “killed” na gv$session, todas estas sessões foram finalizadas com “alter system kill session” mas mesmo assim continuam aparecendo.

SQL> SELECT s.inst_id, s.sid, s.SERIAL#, s.STATUS, s.USERNAME, s.MACHINE
  2  FROM gv$session s
  3  WHERE s.status = 'KILLED'
  4  /

   INST_ID        SID    SERIAL# STATUS   USERNAME                       MACHINE
---------- ---------- ---------- -------- ------------------------------ ----------------------------------------------------------------
         3         84      44325 KILLED   DJE                            lpb6-opg32t
         2        198      51965 KILLED   DJE                            lpb6-opg32t
         1        233      34653 KILLED   DJE                            lpb6-opg32t
         1        257      15969 KILLED   DJE                            lpb6-opg32t

SQL>

Mesmo a sessão aparecer listada como “killed” e demorar para sumir da gv$session não significa problema, muitas vezes a sessão pode estar realizando rollback. Para verificarmos isso, podemos usar a consulta abaixo, caso tenha algum retorno (de alguma sessão com status “killed”) não podemos finalizar o seu processo no sistema operacional.

SQL> select t.INST_ID
  2         , s.sid
  3         , s.program
  4         , t.status as transaction_status
  5         , s.status as session_status
  6         , s.lockwait
  7         , s.pq_status
  8         , t.used_ublk as undo_blocks_used
  9         , decode(bitand(t.flag, 128), 0, 'NO', 'YES') rolling_back
 10  from
 11      gv$session s
 12      , gv$transaction t
 13  where s.taddr = t.addr
 14  and s.inst_id = t.inst_id
 15  and s.STATUS = 'KILLED'
 16  order by t.inst_id;

   INST_ID        SID PROGRAM                                          TRANSACTION_STATUS SESSION_STATUS LOCKWAIT         PQ_STATUS UNDO_BLOCKS_USED ROLLING_BACK
---------- ---------- ------------------------------------------------ ------------------ -------------- ---------------- --------- ---------------- ------------

SQL>

Como visto, não existe nenhuma sessão fazendo rollback e podemos continuar na tentativa de remover as sessões. Como disse acima, toda a sessão do usuário cria um processo no servidor do banco de dados. Você pode verificar estes processos através da gv$process e fazer uma relação com a gv$session para mapear as sessões dos usuários com os processos do servidor. Se consultarmos os processos das sessões marcadas como “killed” não temos retorno. Isso é interessante, pois nenhuma sessão fica sem processo associado no Oracle.

SQL> SELECT s.username, s.osuser, s.machine, s.sid, s.serial#, p.spid
  2  FROM gv$session s, gv$process p
  3  WHERE s.inst_id = p.inst_id
  4  AND s.paddr = p.addr
  4  AND s.username IS NOT NULL
  5  AND s.STATUS = 'KILLED'
  6  /

USERNAME                       OSUSER                         MACHINE                                                                 SID    SERIAL# SPID
------------------------------ ------------------------------ ---------------------------------------------------------------- ---------- ---------- ------------------------

SQL>

Podemos verificar o que estas sessões estão esperando, uma simples consulta na gv$session_wait pode ajudar. Abaixo com o retono da consulta identificamos que as sessões marcadas como “killed” estão esperando retorno do usuário, a coluna “wait_time” mostra que o Oracle está esperando o retorno do cliente (wait_time = 0 significa que o Oracle está aguardando o cliente). Provavelmente a sessão recebeu o kill e tentou comunicar isso ao cliente, como não conseguiu e ficou no limbo.

SQL> SELECT sw.INST_ID, SW.SID, SW.SEQ#, SW.EVENT, SW.STATE, sw.WAIT_TIME
  2  FROM gv$session_wait sw
  3  WHERE sw.sid IN (SELECT sid FROM gv$session s WHERE s.status = 'KILLED' AND s.inst_id = sw.INST_ID)
  4  ORDER BY sw.inst_id
  5  /

   INST_ID        SID       SEQ# EVENT                                                            STATE                WAIT_TIME
---------- ---------- ---------- ---------------------------------------------------------------- ------------------- ----------
         1        233       6288 SQL*Net message from client                                      WAITING                      0
         1        257      39818 SQL*Net message from client                                      WAITING                      0
         2        198       1422 SQL*Net message from client                                      WAITING                      0
         3         84      33615 SQL*Net message from client                                      WAITING                      0

SQL>

Possivelmente após a receber o kill e tentar comunicar o cliente o Oracle modificou o “paddr” da sessão. Assim, a ligação entre a gv$session e a gv$process foi “interrompida”. Partindo disso, podemos localizar todos os processos sem relação com sessões. A consulta abaixo retorna os processos sem sessões, como estamos em um ambiente RAC observe as instâncias de cada sessão.

SQL> SELECT p.INST_ID, p.pid, p.serial#, p.spid, p.program
  2  FROM gv$process p
  3  WHERE p.spid is not null
  4  AND NOT EXISTS (SELECT 1 FROM gv$session s WHERE s.inst_id = p.inst_id and s.paddr = p.addr)
  5  AND p.pname is null
  6  ORDER BY p.inst_id
  7  /

   INST_ID        PID    SERIAL# SPID                     PROGRAM
---------- ---------- ---------- ------------------------ ------------------------------------------------
         1        115        210 24011                    oracle@exadb01.tjsc.jus.br
         1         79        134 4533                     oracle@exadb01.tjsc.jus.br
         2        110        150 29153                    oracle@exadb02.tjsc.jus.br
         3         53        214 24512                    oracle@exadb03.tjsc.jus.br

SQL>

A coluna “spid” nos dá o número do processo do sistema operacional, para finalizar ele basta verificar se está como “LOCAL=NO”. Assim, sabemos que é uma conexão do usuário e nenhum processo interno do Oracle.

Verificando o processo e fazendo o “kill -9” no sistema operacional a sessão com status “killed” é removida do Oracle. Abaixo, observe a instância (servidor), o tipo do processo e seu número; relacione com o resultado da última consulta.

[root@exadb01 ~]# ps -ef |grep 24011
root     14628 14154  0 14:24 pts/4    00:00:00 grep 24011
oracle   24011     1  0 Aug28 ?        00:00:05 oracleopg51 (LOCAL=NO)
[root@exadb01 ~]# ps -ef |grep 4533
oracle    4533     1  0 Aug28 ?        00:00:00 oracleopg51 (LOCAL=NO)
root     14826 14154  0 14:24 pts/4    00:00:00 grep 4533
[root@exadb01 ~]#
[root@exadb01 ~]#
[root@exadb01 ~]# kill -9 24011 
[root@exadb01 ~]# kill -9 4533
[root@exadb01 ~]#
[root@exadb01 ~]# ssh exadb02
Last login: Mon Jun 24 17:27:12 2013 from 10.17.42.21
[root@exadb02 ~]#
[root@exadb02 ~]# ps -ef |grep 29153
root     25421 25226  0 14:25 pts/0    00:00:00 grep 29153
oracle   29153     1  0 Aug28 ?        00:00:17 oracleopg52 (LOCAL=NO)
[root@exadb02 ~]# kill -9 29153
[root@exadb02 ~]#
[root@exadb02 ~]#
[root@exadb02 ~]# ssh exadb03
Last login: Mon Jun 24 17:27:47 2013 from 10.17.42.21
[root@exadb03 ~]#
[root@exadb03 ~]# ps -ef |grep 24512
root     22948 22815  0 14:25 pts/0    00:00:00 grep 24512
oracle   24512     1  0 Aug28 ?        00:00:12 oracleopg53 (LOCAL=NO)
[root@exadb03 ~]# kill -9 24512
[root@exadb03 ~]#

Se verificarmos, não existe mais nenhum processo na gv$process sem relação na gv$session, bem como nenhum sessão marcada como “killed”. Algumas vezes o Oracle demora alguns segundos até remover a sessão após o kill do sistema operacional.

SQL> SELECT p.INST_ID, p.pid, p.serial#, p.spid, p.program
  2  FROM gv$process p
  3  WHERE p.spid is not null
  4  AND NOT EXISTS (SELECT 1 FROM gv$session s WHERE s.inst_id = p.inst_id and s.paddr = p.addr)
  5  AND p.pname is null
  6  ORDER BY p.inst_id
  7  /

   INST_ID        PID    SERIAL# SPID                     PROGRAM
---------- ---------- ---------- ------------------------ ------------------------------------------------

SQL> 
SQL> SELECT s.inst_id, s.sid, s.SERIAL#, s.STATUS, s.USERNAME, s.MACHINE
  2  FROM gv$session s
  3  WHERE s.status = 'KILLED'
  4  /

   INST_ID        SID    SERIAL# STATUS   USERNAME                       MACHINE
---------- ---------- ---------- -------- ------------------------------ ----------------------------------------------------------------

SQL>

Por fim, este exemplo foi feito em uma ambiente Linux (funcionaria em uma ambiente Unix também) e em um banco Oracle configurado em modo dedicado (versões 9 em diante). Não fiz qualquer teste no ambiente Windows ou Oracle configurado como shared.  Este artigo foi baseado em um que encontrei aqui e em problemas do dia a dia.

6 thoughts on “Matando Sessões KILLED”

  2. Renata

    Fernando, boa noite!

    Fiz este select que vc recomendou:
    SELECT sw.INST_ID, SW.SID, SW.SEQ#, SW.EVENT, SW.STATE, sw.WAIT_TIME
    2 FROM gv$session_wait sw
    3 WHERE sw.sid IN (SELECT sid FROM gv$session s WHERE s.status = ‘KILLED’ AND s.inst_id = sw.INST_ID)
    4 ORDER BY sw.inst_id

    E o meu retorno foi esse:
    1 10 154 cell single block physical read WAITED SHORT TIME -1

    Não consigo matar essa sessão… pode me ajudar?

    Reply ↓
    1. Simon Post author

      Olá,
      Eu escrevi o post tendo como base um Oracle 10g em AIX, mas ja usei em outros ambientes com sucesso.
      No seu caso, você pode fazer um crosscheck se a sessão não está com blocks de UNDO e REDO em uso. Acredito que não (mas teria que verificar com mais detalhes, e ão mataria caso estivesse), mas continue com o resto do procedimento para ver se o processo ainda existe no Oracle e no S.O.

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *