Social Icons

17 de out. de 2016

Matando sessões bloqueadoras automaticamente



Olá pessoal,

    No post de hoje quero compartilhar com vocês algo que criei há alguns anos atrás para resolver problemas com sessões bloqueadas no Banco de Dados Oracle.

     Antes de começar, sugiro porém, que você leia o artigo Matando sessões no Oracle Database, para saber mais sobre as formas possíveis de matar sessões no Oracle Database, pois talvez você precise alterar o código do stored procedure que estou compartilhando abaixo, se o seu Banco de Dados (BD) estiver usando, por exemplo, conexões compartilhadas.

     Comecemos agora... quem é que nunca teve problemas no Oracle Database com sessões bloqueadas ou sessões bloqueadoras causando um grande enfileiramento no BD? Em um dos BDs que administro existe  uma aplicação que atualmente é a mais utilizada pelos usuários deste BD, e que executa transações e SQLs muito ruins, causando diariamente muitos locks "longos" e até mesmo deadlocks. Há uns 2 anos atrás, umas 2 X por semana, nós DBAs éramos acionados para verificar o que estava ocorrendo no BD, pois as sessões de muitos usuários desse sistema ficavam congeladas, aguardando algo que parecia que nunca iria terminar. Descobrimos quais eram as transações que causavam os problemas, mas não havia nada que pudéssemos fazer no nível do BD para evitá-los, então a solução corriqueira era identificar e matar as sessões bloqueadoras. 




     Como esse trabalho era muito repetitivo, automatizei a tarefa criando um stored procedure (SP) e um scheduler job que executa esse SP de 15 em 15 minutos. Este processo, além de matar a sessão bloqueadora, envia um e-mail informando alguns detalhes do SQL e da sessão eliminada, para que o DBA possa depois investigar com mais calma o que estava ocorrendo naquele momento no BD. Seguem abaixo o código do SP e do job. Implemente-os fazendo as alterações necessárias (código em cor vermelha) referentes ao seu ambiente:

CREATE OR REPLACE PROCEDURE SP_Matar_Sessoes_Bloqueadoras AS
      v_instance VARCHAR2(8);
      V_msg VARCHAR2(1000);
      v_encontrou_registro boolean:=false;
BEGIN
-- --------------------------------------------------------------------
-- Autor : Fábio Prado -- Criação : 17/12/2014
-- Sumário : Matar sessões bloqueadoras no BD
-- Entradas : NÃO HÁ
-- Saídas :
-- Detalhes : Essa proc é chamada a partir de um schjob p/ matar sessões bloqueadoras
-- --------------------------------------------------------------------    
     SELECT  UPPER(instance_name) into v_instance
    from    v$instance; 
    
    -- inicializa var para enviar msg
    V_msg := 'Dados da(s) sessões(ão) eliminada(s):' || chr(13) || chr(10);
    
    FOR LINHA IN (with s as (select * from v$session WHERE TYPE = 'USER'),
                  l as (select * from v$lock)
                  SELECT  'ALTER system DISCONNECT SESSION '''||s.sid||', '||s.serial#||''' IMMEDIATE' AS instrucao,
                          S.MACHINE, S.PROGRAM, S.OSUSER, S.USERNAME, s.sid, q.SQL_FULLTEXT
                  FROM    s
                  inner join v$sql q on q.sql_id = s.sql_id
                  where   (s.USERNAME, s.SID) in (select s1.username, s1.sid                  
                                                  from    l l1, s s1, l l2, s s2
                                                  where   s1.sid=l1.sid and s2.sid=l2.sid
                                                  and     l1.BLOCK=1 and l2.request > 0
                                                  and     l1.id1 = l2.id1
                                                  and     l1.id2 = l2.id2)
                  )
      LOOP
          v_encontrou_registro := true;
          
          EXECUTE IMMEDIATE LINHA.instrucao;
          
          V_msg := V_msg || 'SID: ' || linha.sid || ' | USERNAME: ' || linha.username || ' | PROGRAM: ' || linha.program ||
                    ' | MACHINE: ' || linha.machine || ' | OSUSER: ' || linha.osuser || chr(13) || chr(10) || 'SQL: ' || linha.SQL_FULLTEXT || chr(13) || chr(10) || chr(13) || chr(10);
      END LOOP;
      
      IF v_encontrou_registro THEN
          -- envia email p/ interessados informando qual sessão do BD foi eliminada
          UTL_MAIL.Send('remetente', 'destinatário', NULL, NULL, '[oracle] Sessão(ões) bloqueadora(s) eliminada(s) no BD ' || v_instance, V_msg, 'text/plain; charset=iso-8859-1');
      END IF;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
END;
/
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'SCHEMA.JOB_NAME',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN SP_Matar_Sessoes_Bloqueadoras();  END;',
            number_of_arguments => 0,
            start_date => SYSTIMESTAMP,
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
            end_date => NULL,
            enabled => true,
            comments => 'Job para matar sessoes bloqueadoras');
END;

     Para que o envio de e-mails funcione é necessário efetuar algumas configurações prévias, que eu indico no artigo Enviando e-mails com PL/SQL em Bancos de Dados Oracle.

    Teste aí os procedimentos. Qualquer dúvida que tiver, é só deixar um comentário.


A maior parte dos DBAs trabalham mais com infraestrutura, e por isso tem dificuldades de escrever código PL/SQL, como o da simples SP que você viu neste artigo. Se você precisa ou quer aprender a programar em PL/SQL, envie um e-mail com o assunto "DESC PLSQL 20 OFF" para contato@fabioprado.net e ganhe um desconto de 20% nos cursos em videoaulas PL/SQL Essentials (opção com suporte, que inclui o módulo PL/SQL Tuning)

 

Por hoje é só, espero que o artigo lhe seja útil!
Até a próxima!


[]s
  

4 comments:

  1. Além de bom, artigo muito útil.

    Tks.

    ResponderExcluir
  2. Olá Fábio,

    Seria possível realizar algo em relação a quem está matando as sessões? preciso identificar quem está fazendo um "alter system kill session"

    artigo muito útil, obrigado

    ResponderExcluir
    Respostas
    1. Hudson, para auditar ALTER SYSTEM de usuários não-sys é bem simples, basta habilitar previamente a auditoria (parâmetro audit_trail) e depois executar o comando: "audit alter system".

      Se você quiser auditar também o sys, execute por exemplo, o que está escrito no post: https://uhesse.com/2010/02/02/how-to-audit-sys-into-an-os-file-owned-by-root/.

      []s

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)