Ao depurar problemas de desempenho em um banco de dados Oracle, uma situação comum é uma sessão ou processo consumindo recursos excessivos e, consequentemente, impactando a aplicação principal. Este comportamento pode frequentemente levar a bloqueios (locks) e deadlocks, fazendo com que as operações pendurem indefinidamente sem concluir ou reportar erros. A seguir, descreve-se o processo de diagnóstico e resolução.

Sintomas e Diagnóstico Inicial

Quando uma operação DML (como UPDATE ou DELETE) é executada em uma linha e a transação correspondente não é commitada, ela retém um bloqueio naquela linha. Se outra sessão tentar modificar a mesma linha, ficará em estado de espera, resultando em um deadlock aparente. O processo de diagnóstico envolve identificar sessões bloqueadas e as instruções SQL responsáveis.

Para verificar sessões com bloqueios ativos, consulta-se as visões v$session e v$locked_object.

SELECT ses.sid,
       ses.username,
       ses.lockwait,
       ses.status,
       ses.machine,
       ses.program
FROM v$session ses
WHERE ses.sid IN (SELECT obj.session_id FROM v$locked_object obj);

Os campos a serem analisados incluem: username (usuário da sessão), lockwait (preenche-se se estiver em espera por um lock), status (se 'ACTIVE', indica bloqueio) e machine/program (origem da sessão).

Para encontrar a instrução SQL específica que causou o bloqueio, pode-se correlacionar com a visão v$sql.

SELECT sql_text
FROM v$sql
WHERE hash_value IN (
    SELECT ses.sql_hash_value
    FROM v$session ses
    WHERE ses.sid IN (SELECT obj.session_id FROM v$locked_object obj)
);

Procedimentos de Resolução

A solução principal para liberar o bloqueio é encerrar a sessão responsável. Isso pode ser feito com o comando ALTER SYSTEM KILL SESSION. Primeiro, é necessário obter o identificador da sessão (sid) e o número serial (serial#).

SELECT b.username,
       b.sid,
       b.serial#,
       b.logon_time
FROM v$locked_object a
JOIN v$session b ON a.session_id = b.sid
ORDER BY b.logon_time;

Com os valores obtidos, a sessão é terminada. Por exemplo, para uma sessão com sid 12 e serial# 73:

ALTER SYSTEM KILL SESSION '12,73';

Em casos mais graves, onde a sessão não responde ao comando KILL, pode ser necessário encerrar o processo do sistema operacional subjacente. Essa ação, no entanto, requer análise cuidadosa e, se aplicado ao processo principal do banco, pode levar a uma reinicialização instável.

Visões Úteis para Monitoramento

Diversas visões do dicionário de dados do Oracle são ferramentas essenciais para esse tipo de análise:

Para identificar a tabela específica que sofreu bloqueio e o modo do bloqueio, uma junção entre v$locked_object e dba_objects é útil.

SELECT obj.owner,
       obj.object_name,
       loc.session_id,
       loc.locked_mode
FROM v$locked_object loc
JOIN dba_objects obj ON obj.object_id = loc.object_id;