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:
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:
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
Além de bom, artigo muito útil.
ResponderExcluirTks.
Márcio, obrigado pelo comentário!
ExcluirOlá Fábio,
ResponderExcluirSeria 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
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".
ExcluirSe 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