Sessão inativa (INACTIVE) segurando um row lock e travando as outras é comum em embientes oracle. Em vez de matar a sessão por fora do banco num loop de shell, o Oracle Resource Manager encerra esse bloqueador sozinho, com uma diretiva. Aqui está o passo a passo, a partir de um caso real em Oracle 19c multitenant.
O cenário
Ambiente Oracle 19c multitenant, RAC com duas instâncias, com o PDB de aplicação (APPPDB) em READ WRITE, ARCHIVELOG, FORCE LOGGING e FLASHBACK ativos. As sessões problemáticas vêm de um pool de aplicação, todas pelo schema APP_SCHEMA, com o mesmo usuário de SO (root) e o mesmo programa cliente (AppServer.exe). Os clientes interativos, que não devem ser tocados, usam outro programa (AppClient.exe).
O problema: a sessão inativa que segura o lock
Foi identificada contenção recorrente no evento enq: TX – row lock contention, sempre sobre o mesmo comportamento da aplicação. O padrão observado é o seguinte: o servidor de aplicação AppServer.exe executa um UPDATE no registro e não finaliza a transação com COMMIT ou ROLLBACK. A sessão fica INACTIVE, aguardando em SQL*Net message from client, mas continua segurando o row lock.
Com isso, outras sessões da mesma aplicação executam SELECT … FOR UPDATE na mesma chave e ficam ACTIVE, aguardando o enqueue de bloqueio.
O agravante é que o bloqueio se recompõe rapidamente. Ao encerrar a sessão inativa bloqueadora, o serviço abre uma nova conexão, executa novamente o mesmo UPDATE e o lock volta em poucos segundos.
A recomendação principal é que esse cenário seja corrigido na própria aplicação, pois normalmente a causa raiz está na falta de controle adequado da transação. Em muitos casos, uma exceção não tratada impede a execução do commit ou rollback, deixando a transação aberta e a sessão ociosa mantendo locks sobre os objetos.
Essa é a correção definitiva. Porém, enquanto o ajuste na aplicação não é implementado, ou caso ele não seja priorizado, é possível adotar uma camada de proteção no banco de dados. O Oracle Database Resource Manager, por meio da diretiva MAX_IDLE_BLOCKER_TIME, permite encerrar automaticamente sessões inativas que permanecem bloqueando outras sessões.
Dessa forma, o Resource Manager não substitui a correção na aplicação, mas funciona como uma mitigação para reduzir o impacto operacional de locks mantidos indevidamente.
Diagnóstico
Antes de mudar qualquer coisa, confirme a árvore de bloqueio e capture as strings exatas das sessões alvo. O PROGRAM e o OSUSER vão ser a base do mapeamento, e a comparação é case sensitive.
-- Em multitenant, comece sempre confirmando o container.
-- Se aparecer CDB$ROOT, pare e reconecte no PDB de aplicacao.
SHOW CON_NAME
--## CON_NAME
--## ------------------------------
--## APPPDB
-- Plano de RM ativo agora. Anote: o rollback precisa restaurar este valor.
SELECT name, is_top_plan FROM v$rsrc_plan WHERE is_top_plan = 'TRUE';
--## no rows selected
SHOW PARAMETER resource_manager_plan
--## NAME TYPE VALUE
--## --------------------- ----------- ------
--## resource_manager_plan string
-- Strings exatas de PROGRAM, OSUSER e MACHINE das sessoes alvo.
-- O mapeamento compara a string EXATA e e case sensitive.
SELECT inst_id, username, osuser, program, machine, status, COUNT(*) AS qtd
FROM gv$session
WHERE username = 'APP_SCHEMA'
GROUP BY inst_id, username, osuser, program, machine, status
ORDER BY status, program;
--## INST_ID|USERNAME |OSUSER |PROGRAM |MACHINE |STATUS |QTD
--## ----------|----------|-------|-------------|--------|--------|---
--## 1|APP_SCHEMA|root |AppServer.exe|appsrv01|ACTIVE | 14
--## 1|APP_SCHEMA|root |AppServer.exe|appsrv01|INACTIVE| 7
--## 2|APP_SCHEMA|root |AppServer.exe|appsrv02|ACTIVE | 11
--## 2|APP_SCHEMA|root |AppServer.exe|appsrv02|INACTIVE| 5
--##
--## 4 rows selected.
Para enxergar a árvore de bloqueio, quem segura e quem espera, no momento do incidente:
-- Linha do bloqueador: blocking_session nulo, status INACTIVE, idle alto.
-- Linhas dos esperadores: status ACTIVE, event = enq: TX - row lock contention.
SELECT s.inst_id, s.sid, s.serial#, s.status, s.osuser, s.program,
s.blocking_session, s.event, s.last_call_et AS idle_secs
FROM gv$session s
WHERE s.username = 'APP_SCHEMA'
AND (s.blocking_session IS NOT NULL
OR s.sid IN (SELECT blocking_session FROM gv$session WHERE blocking_session IS NOT NULL))
ORDER BY s.blocking_session NULLS FIRST, s.sid;
--## INST_ID| SID|SERIAL#|STATUS |OSUSER |PROGRAM |BLOCKING_SESSION|EVENT |IDLE_SECS
--## ----------|----|-------|--------|-------|-------------|----------------|-----------------------------|---------
--## 1|4065| 28531|INACTIVE|root |AppServer.exe| |SQL*Net message from client | 1840
--## 1| 273| 20132|ACTIVE |root |AppServer.exe| 4065|enq: TX - row lock contention| 11
--## 3|1561| 42459|ACTIVE |root |AppServer.exe| 4065|enq: TX - row lock contention| 30
--##
--## 3 rows selected.A versão completa, com níveis da árvore e o SQL_ID de cada sessão, é o locktree.sql da família g_gold (open source, no GitHub). Foi ele que gerou a evidência abaixo.
Observação: o bloco acima é carregado online do GitHub, então reflete sempre a última versão do locktree.sql. Para baixar, acesse github.com/dbasobrinho/g_gold/blob/master/locktree.sql.
Mostrando a sessão INACTIVE (LEVEL 1, bloqueadora) no topo e as várias sessões ACTIVE em SQL*Net message from client esperando o mesmo lock.
Por que o Resource Manager resolve a sessão inativa bloqueadora
O Oracle Database Resource Manager, também conhecido como DBRM, possui uma diretiva de plano chamada MAX_IDLE_BLOCKER_TIME. Essa diretiva define o tempo máximo, em segundos, que uma sessão pode permanecer INACTIVE enquanto bloqueia outras sessões.
Quando esse limite é atingido, o próprio banco encerra a sessão bloqueadora, executando o ROLLBACK da transação pendente e realizando o DISCONNECT da sessão.
Essa regra atua somente sobre sessões que estejam, ao mesmo tempo, ociosas e bloqueando outras sessões. Ou seja, uma conexão apenas ociosa, sem segurar lock necessário para outra sessão, não é afetada.
Da mesma forma, as sessões que estão aguardando o lock também não são encerradas por essa diretiva, pois normalmente estão ACTIVE, dentro de uma chamada ao banco, aguardando a liberação do enqueue.
Na prática, o DBRM age exatamente sobre o cenário crítico: uma sessão esquecida pela aplicação, parada em INACTIVE, mas ainda segurando um row lock que impede o andamento das demais sessões.
MAX_IDLE_BLOCKER_TIME contra MAX_IDLE_TIME
| Parâmetro | O que mede | Precisa estar bloqueando | Uso neste caso |
|---|---|---|---|
| MAX_IDLE_TIME | tempo ocioso (last_call_et) | Não | Nulo de propósito |
| MAX_IDLE_BLOCKER_TIME | tempo ocioso enquanto bloqueia | Sim | Ativo, é a solução |
Por que deixar MAX_IDLE_TIME nulo? Porque o servidor de aplicação mantém um pool de conexões persistentes que ficam legitimamente ociosas em SQL*Net message from client sem bloquear ninguém. Um MAX_IDLE_TIME agressivo derrubaria essas conexões saudáveis e provocaria tempestade de reconexão, piorando o cenário. Como o MAX_IDLE_BLOCKER_TIME só atua quando há bloqueio, ele é cirúrgico.
O encerramento não é instantâneo
O PMON avalia esses limites de ociosidade em ciclo, cerca de uma vez por minuto. Na prática, o encerramento efetivo fica entre o limite configurado e o limite mais o próximo ciclo do PMON. Com MAX_IDLE_BLOCKER_TIME = 100, espere o kill acontecer em algum ponto entre 100 e perto de 160 segundos. Por isso, não adianta configurar 10 segundos esperando reação sub minuto: a granularidade do PMON não entrega isso. Se você precisa de reação mais rápida que um minuto, o Resource Manager sozinho não é o mecanismo, e um paliativo leve em paralelo continua fazendo sentido. Esse comportamento está na documentação oficial do pacote (Oracle Database PL/SQL Packages and Types Reference, DBMS_RESOURCE_MANAGER): o PMON verifica max_idle_time e max_idle_blocker_time uma vez por minuto e, ao encontrar uma sessão que excedeu o limite, encerra a sessão à força e limpa todo o estado dela.
A decisão que sustenta tudo: como mapear a sessão
O Resource Manager coloca cada sessão em um consumer group olhando os atributos de login dela: programa, usuário do SO e usuário do banco.
O detalhe que pega muita gente é como o mapeamento nativo (SET_CONSUMER_GROUP_MAPPING) avalia isso. Cada regra é checada sozinha, no esquema OR. Basta uma bater.
Um exemplo deixa claro. Você cria três regras apontando para o mesmo grupo:
- uma por CLIENT_PROGRAM
- outra por CLIENT_OS_USER
- outra por ORACLE_USER
Se a sessão casar com qualquer uma das três, já cai no grupo. Não precisa casar com as três juntas.
E quando duas regras mandam a sessão para grupos diferentes? Aí entra a prioridade (SET_CONSUMER_GROUP_MAPPING_PRI) para decidir quem ganha, não é nosso caso.
O que o nativo não te dá é o AND. Não existe um “só entra no grupo quem bater em programa E usuário de SO E usuário de banco ao mesmo tempo”.
Nesse caso, não dá para errar o alvo. Uma regra só por ORACLE_USER varreria o schema inteiro e arrastaria junto os clientes interativos (AppClient.exe), que é exatamente quem a gente não quer no grupo.
O que precisamos é da interseção exata das três condições: programa E usuário de SO E usuário de banco, todos batendo ao mesmo tempo.
Como o AND não existe nativamente, a saída é um LOGON TRIGGER. Ele avalia as três condições juntas e, só quando todas casam, faz o switch explícito do consumer group.
| X -X | Opção A, mapping (OR) | Opção B, trigger (AND) |
|---|---|---|
| Lógica | OR (um atributo já basta) | AND (os três juntos) |
| Combina atributos | Não | Sim |
| Resultado | pega demais | só a tripla exata |
| Objeto extra | nenhum | 1 trigger no login |
| Comparação | case sensitive | case insensitive (UPPER) |
Escolha uma das duas, nunca as duas no mesmo plano. Se as duas ficarem ativas juntas, uma anula o propósito da outra.
Para este caso, que exige a tripla exata, a recomendada é a Opção B.
A Opção A fica como alternativa mais simples, para quando um único atributo já identifica suas sessões com segurança.
Implementação passo a passo
Cada etapa abaixo é um bloco isolado. Rode na ordem, conectado ao PDB de aplicação como SYS ou SYSDBA, sempre primeiro em homologação.
Etapa 1: abrir a pending area
A pending area é uma transação de configuração: toda alteração do Resource Manager é montada dentro dela e só passa a valer no submit.
BEGIN
-- clear_pending_area: descarta qualquer pendencia orfa de tentativa anterior
DBMS_RESOURCE_MANAGER.clear_pending_area;
-- create_pending_area: abre a area limpa para as proximas etapas
DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/
--## PL/SQL procedure successfully completed.Etapa 2: criar o plano
O plano é o conjunto de regras. Aqui ele existe só para hospedar a diretiva de ociosidade. São dois valores: plan, o nome do plano (RPLAN_JM), que vira a chave usada na ativação da etapa 8; e comment, texto livre que fica em DBA_RSRC_PLANS para documentar a intenção.
BEGIN
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'RPLAN_JM',
comment => 'Encerra holders de lock TX ociosos');
END;
/
--## PL/SQL procedure successfully completed.Etapa 3: criar o consumer group
O consumer group funciona como o grupo de controle onde serão direcionadas, via mapeamento, as sessões que precisam ser monitoradas.
Neste caso, são utilizados basicamente dois atributos:
consumer_group: nome do grupo, definido como GROUP_JM_KILL_INACTIVE_BLOCK, que será utilizado como alvo da diretiva e do mapeamento.
comment: descrição do grupo, exibida na view DBA_RSRC_CONSUMER_GROUPS.
BEGIN
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK',
comment => 'Sessoes candidatas a encerramento por bloqueio ocioso');
END;
/
--## PL/SQL procedure successfully completed.Etapa 4: criar as diretivas
A diretiva do grupo alvo é onde será configurado o MAX_IDLE_BLOCKER_TIME.
O parâmetro max_idle_blocker_time => 100 define o limite, em segundos, que uma sessão pode permanecer INACTIVE enquanto bloqueia outras sessões. Neste caso, a sessão poderá ficar aproximadamente 100 segundos em condição de bloqueio ocioso antes de ser encerrada automaticamente pelo banco.
O parâmetro max_idle_time => NULL é intencional. Com isso, o banco não encerra sessões apenas ociosas. A ação ocorre somente quando a sessão está, ao mesmo tempo, INACTIVE e bloqueando outras sessões.
A diretiva para OTHER_GROUPS também é obrigatória, porém permanece sem limite. Dessa forma, as demais sessões do banco não são afetadas pela regra.
BEGIN
-- Diretiva do grupo alvo
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'RPLAN_JM',
group_or_subplan => 'GROUP_JM_KILL_INACTIVE_BLOCK',
max_idle_blocker_time => 100, -- segundos (ajustar conforme o SLA)
max_idle_time => NULL, -- nulo de proposito: nao mata conexao so ociosa
comment => 'Encerra apenas sessao INACTIVE que esteja bloqueando outra');
-- Diretiva obrigatoria para todas as demais sessoes
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'RPLAN_JM',
group_or_subplan => 'OTHER_GROUPS',
comment => 'Demais sessoes: sem encerramento por ociosidade');
END;
/
--## PL/SQL procedure successfully completed.Etapa 5: validar e submeter
Até esse ponto, nenhuma das configurações realizadas nas etapas 2 a 4 entra em vigor imediatamente.
O procedimento validate_pending_area apenas valida a consistência das alterações pendentes no Resource Manager.
Já o procedimento submit_pending_area é o responsável por efetivar as alterações, aplicando de fato tudo o que foi configurado na área pendente.
BEGIN
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
--## PL/SQL procedure successfully completed.Etapa 6: conceder o privilégio de switch
Sem esse privilégio, o mapeamento ou a trigger não consegue mover a sessão para o consumer group definido.
Apesar de ser um passo discreto, ele é indispensável para que o controle funcione corretamente.
Os principais parâmetros são:
grantee_name: schema ou role que poderá ser movido para o grupo. Neste caso, APP_SCHEMA.
consumer_group: grupo de destino para onde a sessão poderá ser direcionada.
grant_option => FALSE: concede o direito de troca para o grupo, mas não permite que o schema repasse esse privilégio para terceiros.
Quando o mapeamento for feito por role, o privilégio deve ser concedido à própria role. Assim, todos os usuários membros dessa role herdam a permissão necessária.
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'APP_SCHEMA',
consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK',
grant_option => FALSE);
END;
/
--## PL/SQL procedure successfully completed.Etapa 7: o mapeamento (escolha A ou B)
Opção A: mapeamento nativo (OR)
Opção A: mapeamento nativo com lógica OR
Essa é a opção mais simples e costuma ser suficiente quando um único atributo já identifica corretamente as sessões que devem ser direcionadas para o grupo.
É importante lembrar que o mapeamento nativo trabalha com lógica OR. Ou seja, cada regra é avaliada de forma independente e qualquer uma delas, sozinha, já é suficiente para mover a sessão para o consumer group definido.
BEGIN
-- clear_pending_area: descarta qualquer pendencia orfa de tentativa anterior
DBMS_RESOURCE_MANAGER.clear_pending_area;
-- create_pending_area: abre a area limpa para as proximas etapas
DBMS_RESOURCE_MANAGER.create_pending_area;
END;
/
--## PL/SQL procedure successfully completed.
BEGIN
-- Por programa cliente (CASE SENSITIVE: 'AppServer.exe' <> 'APPSERVER.EXE')
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(
attribute => DBMS_RESOURCE_MANAGER.client_program,
value => 'AppServer.exe',
consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK');
-- Por usuario do SO no cliente
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(
attribute => DBMS_RESOURCE_MANAGER.client_os_user,
value => 'root',
consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK');
-- Por usuario Oracle (CUIDADO: pega o schema inteiro, ACTIVE e INACTIVE)
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(
attribute => DBMS_RESOURCE_MANAGER.oracle_user,
value => 'APP_SCHEMA',
consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK');
END;
/
--## PL/SQL procedure successfully completed.Em cada mapeamento, o parâmetro attribute define qual característica da sessão será avaliada, e o parâmetro value define o valor exato esperado.
Neste caso, podemos mapear por:
client_program: programa cliente, como AppServer.exe.
client_os_user: usuário de SO da máquina cliente, como root.
oracle_user: schema de conexão, como APP_SCHEMA.
A comparação é por igualdade exata. Nos atributos de cliente, também há diferença entre maiúsculas e minúsculas. Portanto, AppServer.exe é diferente de APPSERVER.EXE. Já o oracle_user normalmente é armazenado em maiúsculas.
O consumer_group é o grupo de destino para onde a sessão será direcionada.
A prioridade só serve para desempate quando a sessão atende a regras que apontam para grupos diferentes. Como aqui todas apontam para o mesmo grupo, ela não muda o destino. Menor número significa maior prioridade.
BEGIN
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping_pri(
explicit => 1, -- switch explicito tem sempre a maior prioridade
client_program => 2,
client_os_user => 3,
oracle_user => 4,
service_name => 5,
client_machine => 6,
module_name => 7,
module_name_action => 8,
service_module => 9,
service_module_action => 10);
END;
/
--## PL/SQL procedure successfully completed.
BEGIN
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
--## PL/SQL procedure successfully completed.Opção B: logon trigger com AND das três condições
Com a logon trigger, a sessão só será movida para o consumer group se as três condições forem atendidas ao mesmo tempo: SESSION_USER, OS_USER e PROGRAM.
Esse é o recorte mais seguro, pois evita que sessões parecidas, mas que não fazem parte do problema, sejam afetadas.
Ao usar essa opção, é importante garantir que nenhum mapeamento nativo da Opção A esteja ativo apontando para o mesmo grupo. Caso contrário, a lógica OR do mapeamento nativo volta a valer e a precisão da trigger é perdida.
CREATE OR REPLACE TRIGGER trg_map_kill_idle_blocker
AFTER LOGON ON DATABASE
DECLARE
v_program VARCHAR2(48);
v_old VARCHAR2(128);
BEGIN
-- Condicao 1 (usuario Oracle) E Condicao 2 (usuario do SO).
-- Comparacoes com UPPER nos dois lados, para nao escorregar em caixa.
IF UPPER(SYS_CONTEXT('USERENV','SESSION_USER')) = 'APP_SCHEMA'
AND UPPER(SYS_CONTEXT('USERENV','OS_USER')) = 'ROOT'
THEN
-- Condicao 3 (programa): o PROGRAM nao esta em USERENV.
-- E lido de v$session pela SID atual, por isso o owner precisa de SELECT em v$session.
BEGIN
SELECT program INTO v_program
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV','SID')
AND rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN v_program := NULL;
END;
-- AND final: so troca de grupo se as TRES condicoes baterem
IF UPPER(v_program) = 'APPSERVER.EXE' THEN
DBMS_SESSION.switch_current_consumer_group(
new_consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK',
old_consumer_group => v_old,
initial_group_on_error => FALSE);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL; -- trigger de logon que falha pode IMPEDIR o login; nunca propagar o erro
END;
/
--## Trigger created.O EXCEPTION WHEN OTHERS THEN NULL é proposital. Um trigger de logon que levanta erro pode barrar a conexão (ORA-00604). O mapeamento nunca pode impedir alguém de logar.
Etapa 8: ativar o plano no PDB
Este é o passo que aplica a configuração de fato, por isso deve ser executado em janela controlada.
O comando ALTER SYSTEM ativa o plano do Resource Manager:
resource_manager_plan = ‘FORCE:RPLAN_JM’ ativa o plano usando o prefixo FORCE:.
SCOPE = BOTH aplica a alteração imediatamente na memória e também grava no spfile, mantendo a configuração após restart.
SID = ‘*’ aplica a alteração em todas as instâncias do RAC, e não apenas na instância local.
-- SCOPE = BOTH aplica imediatamente e persiste no estado do PDB.
-- sid = '*' aplica em todas as instancias do RAC.
-- O prefixo FORCE: trava o plano: impede que uma janela de manutencao do Scheduler
-- troque o resource_manager_plan para o DEFAULT_MAINTENANCE_PLAN e desligue a protecao.
ALTER SYSTEM SET resource_manager_plan = 'FORCE:RPLAN_JM' SCOPE = BOTH SID = '*';
--## System altered.
--##
SHOW PARAMETER resource_manager_plan
--## NAME TYPE VALUE
--## --------------------- ------ --------------
--## resource_manager_plan string FORCE:RPLAN_JM
--##
SELECT name, is_top_plan FROM v$rsrc_plan WHERE is_top_plan = 'TRUE';
--## NAME |IS_TOP_PLAN
--## --------|-----------
--## RPLAN_JM|TRUE
--##
--## 1 row selected.Sem o FORCE:, durante a janela noturna de manutenção o Oracle troca o plano ativo pelo DEFAULT_MAINTENANCE_PLAN, e os bloqueadores ociosos deixariam de ser encerrados justamente de madrugada. Com o FORCE:, só um ALTER SYSTEM manual muda o plano.
Validação
Confira as diretivas, em que grupo as sessões caíram e os contadores de encerramento.
-- Formatacao para a saida nao quebrar linha
SET LINESIZE 240
SET PAGESIZE 100
COLUMN username FORMAT A24
COLUMN osuser FORMAT A10
COLUMN program FORMAT A15
COLUMN status FORMAT A8
COLUMN resource_consumer_group FORMAT A28 HEADING 'CONSUMER_GROUP'
COLUMN idle_secs FORMAT 99999999 HEADING 'IDLE'
COLUMN name FORMAT A28 HEADING 'CONSUMER_GROUP'
COLUMN plan FORMAT A28 HEADING 'PLAN'
COLUMN group_or_subplan FORMAT A28 HEADING 'GROUP_OR_SUBPLAN'
COLUMN max_idle_time FORMAT 999999999 HEADING 'MAX_IDLE_TIME'
COLUMN max_idle_blocker_time FORMAT 999999999 HEADING 'MAX_IDLE_BLOCKER_TIME'
-- Diretivas do plano (esperado: CG com 100 e OTHER_GROUPS sem limite)
SELECT plan, group_or_subplan, max_idle_time, max_idle_blocker_time
FROM dba_rsrc_plan_directives
WHERE plan = 'RPLAN_JM'
ORDER BY group_or_subplan;
--## PLAN |GROUP_OR_SUBPLAN |MAX_IDLE_TIME|MAX_IDLE_BLOCKER_TIME
--## ----------------------------|----------------------------|-------------|---------------------
--## RPLAN_JM |GROUP_JM_KILL_INACTIVE_BLOCK| | 100
--## RPLAN_JM |OTHER_GROUPS | |
--##
--## 2 rows selected.
--##
-- Em que grupo as sessoes alvo cairam (mapping so vale para sessoes novas).
-- Filtrar pelo grupo deixa a lista curta; SUBSTR encurta osuser e program;
-- o NOT LIKE tira a sua propria sessao interativa (DBeaver, SQL Developer) da lista.
SELECT inst_id, username, sid, serial#,
SUBSTR(osuser,1,10) AS osuser,
SUBSTR(program,1,15) AS program, status,
resource_consumer_group, blocking_session, last_call_et AS idle_secs
FROM gv$session
WHERE resource_consumer_group = 'GROUP_JM_KILL_INACTIVE_BLOCK'
AND program NOT LIKE '%DBeaver%'
ORDER BY inst_id, status, sid;
--## INST_ID|USERNAME | SID|SERIAL#|OSUSER|PROGRAM |STATUS |CONSUMER_GROUP |BLOCKING_SESSION|IDLE
--## ----------|----------|----|-------|------|-------------|--------|----------------------------|----------------|----
--## 1|APP_SCHEMA|4065| 28531|root |AppServer.exe|INACTIVE|GROUP_JM_KILL_INACTIVE_BLOCK| |1840
--## 1|APP_SCHEMA| 273| 20132|root |AppServer.exe|ACTIVE |GROUP_JM_KILL_INACTIVE_BLOCK| 4065| 11
--## 3|APP_SCHEMA|1561| 42459|root |AppServer.exe|ACTIVE |GROUP_JM_KILL_INACTIVE_BLOCK| 4065| 30
--##
--## 3 rows selected.
--##
-- Contador por instancia (RAC): idle_blkr_sessions_killed sobe a cada bloqueador encerrado
SELECT inst_id, name, idle_sessions_killed, idle_blkr_sessions_killed
FROM gv$rsrc_consumer_group
WHERE name IN ('GROUP_JM_KILL_INACTIVE_BLOCK','OTHER_GROUPS')
ORDER BY inst_id, name;
--## INST_ID|NAME |IDLE_SESSIONS_KILLED|IDLE_BLKR_SESSIONS_KILLED
--## ----------|----------------------------|--------------------|-------------------------
--## 1|GROUP_JM_KILL_INACTIVE_BLOCK| 0| 9
--## 1|OTHER_GROUPS | 0| 0
--## 3|GROUP_JM_KILL_INACTIVE_BLOCK| 0| 2
--## 3|OTHER_GROUPS | 0| 0
--##
--## 4 rows selected.O sinal de sucesso é observar as sessões do servidor de aplicação com resource_consumer_group = GROUP_JM_KILL_INACTIVE_BLOCK.
Com o passar do tempo, o contador idle_blkr_sessions_killed deve aumentar, indicando que o Resource Manager encerrou sessões ociosas que estavam bloqueando outras sessões.
Importante: o Resource Manager não deixa a sessão marcada como KILLED. O encerramento ocorre por meio de ROLLBACK da transação pendente e DISCONNECT da sessão.
Por isso, o acompanhamento deve ser feito pelo contador agregado idle_blkr_sessions_killed, e não pela busca de sessões com status KILLED.
Para ver, naquele instante, quem está prestes a ser encerrado (no grupo, INACTIVE e com gente esperando):
-- Candidatos agora: sessao do grupo, INACTIVE e bloqueando alguem (waiters > 0)
SELECT s.sid, s.serial#, s.osuser, s.program, s.status,
s.last_call_et AS idle_secs,
(SELECT COUNT(*) FROM gv$session w WHERE w.blocking_session = s.sid) AS waiters
FROM gv$session s
WHERE s.resource_consumer_group = 'GROUP_JM_KILL_INACTIVE_BLOCK'
AND s.status = 'INACTIVE'
AND EXISTS (SELECT 1 FROM gv$session w WHERE w.blocking_session = s.sid)
ORDER BY s.last_call_et DESC;
--## SID|SERIAL#|OSUSER |PROGRAM |STATUS |IDLE_SECS|WAITERS
--## ----|-------|-------|-------------|--------|---------|-------
--## 4065| 28531|root |AppServer.exe|INACTIVE| 1840| 3
--##
--## 1 row selected.Se as esperas continuarem altas, reduza o limite sem recriar nada (lembrando do piso prático de um minuto do PMON):
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.update_plan_directive(
plan => 'RPLAN_JM',
group_or_subplan => 'GROUP_JM_KILL_INACTIVE_BLOCK',
new_max_idle_blocker_time => 60); -- novo limite em segundos
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
--## PL/SQL procedure successfully completed.Teste controlado
Para validar o mecanismo sem depender do incidente em produção, use duas sessões conectadas como APP_SCHEMA e uma tabela de teste isolada. Se você adotou o trigger (Opção B), conecte de fato com o usuário de SO e o programa esperados, porque o AND exige as três condições.
-- Preparacao
CREATE TABLE app_schema.t_dbrm_test (id NUMBER PRIMARY KEY, v VARCHAR2(10));
--## Table created.
--##
INSERT INTO app_schema.t_dbrm_test VALUES (1,'X');
--## 1 row created.
--##
COMMIT;
--## Commit complete.
--##
-- Sessao A (vira o bloqueador ocioso): rodar, NAO commitar e deixar parada
UPDATE app_schema.t_dbrm_test SET v='A' WHERE id=1;
--## 1 row updated.
--##
-- Sessao B (esperador): fica em enq: TX, ACTIVE, aguardando A
UPDATE app_schema.t_dbrm_test SET v='B' WHERE id=1;
--## (B fica em enq: TX; so completa quando o RM encerra a sessao A bloqueadora)
--## 1 row updated.Numa terceira sessão, confirme que A está no grupo, INACTIVE e bloqueando B. Espere acima do MAX_IDLE_BLOCKER_TIME mais a granularidade do PMON. O esperado: A recebe ORA-00028 na próxima chamada, B prossegue, e o contador idle_blkr_sessions_killed incrementa.
-- Limpeza
DROP TABLE app_schema.t_dbrm_test PURGE;
--## Table dropped.Riscos e avisos
O holder recebe ORA-00028 (your session has been killed) e precisa reconectar e reprocessar. No carrossel descrito isso acontece com frequência, então valide antes que o servidor de aplicação trata a reconexão sem efeito funcional. Aplicação que não reconecta vira incidente novo.
O mapeamento só reclassifica sessões novas, no login. Sessões já abertas mantêm o grupo anterior. Se precisar reclassificar uma sessão existente sem esperar reconexão, use DBMS_RESOURCE_MANAGER.switch_consumer_group_for_sess.
E o mais importante de tudo: isso é mitigação, não cura. A contenção de hot row continua existindo. O Resource Manager troca um workaround externo e frágil por uma política nativa, mais limpa e mais segura, mas a correção definitiva mora na aplicação: commit ou rollback imediato após o UPDATE.
Rollback
-- 1) desativar (ou restaurar o plano anterior anotado no diagnostico)
ALTER SYSTEM SET resource_manager_plan = '' SCOPE = BOTH;
--## System altered.
--##
-- 2) remover o trigger de mapeamento (se usou a Opcao B)
DROP TRIGGER trg_map_kill_idle_blocker;
--## Trigger dropped.
--##
-- 3) revogar o switch
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.revoke_switch_consumer_group(
revokee_name => 'APP_SCHEMA',
consumer_group => 'GROUP_JM_KILL_INACTIVE_BLOCK');
END;
/
--## PL/SQL procedure successfully completed.
--##
-- 4) apagar diretivas, plano e consumer group dentro de uma pending area
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.delete_plan_directive('RPLAN_JM','GROUP_JM_KILL_INACTIVE_BLOCK');
DBMS_RESOURCE_MANAGER.delete_plan_directive('RPLAN_JM','OTHER_GROUPS');
DBMS_RESOURCE_MANAGER.delete_plan('RPLAN_JM');
DBMS_RESOURCE_MANAGER.delete_consumer_group('GROUP_JM_KILL_INACTIVE_BLOCK');
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
--## PL/SQL procedure successfully completed.Se você usou a Opção A em vez do trigger, troque o passo 2 por três chamadas de set_consumer_group_mapping passando consumer_group => NULL para cada atributo, removendo os mapeamentos nativos.
Referências oficiais
- Managing Resources with Oracle Database Resource Manager (19c) (doc oficial): conceitos de plano, consumer group, diretivas e limite de tempo ocioso, inclusive restrito a sessões que bloqueiam.
- DBMS_RESOURCE_MANAGER (PL/SQL Packages and Types Reference) (doc oficial): assinatura de CREATE_PLAN_DIRECTIVE, SET_CONSUMER_GROUP_MAPPING, SET_CONSUMER_GROUP_MAPPING_PRI e a checagem do PMON uma vez por minuto.
- Managing Database Resources (19c) (doc oficial): obrigatoriedade da diretiva para OTHER_GROUPS e limites de consumer groups em planos de PDB.
Conclusão
O MAX_IDLE_BLOCKER_TIME transforma um workaround externo, um shell matando sessões num loop, em uma política nativa do banco. Só toca quem está ocioso e bloqueando. A ressalva continua ele trata o sintoma, não a doença. Enquanto a correção definitiva não chega na aplicação, é a contenção mais limpa e segura disponível. No fim, encerrar a sessão inativa que está bloqueando deixa de ser plantão manual e vira política do próprio banco.
Quer o passo a passo pronto para executar? Deixei o runbook completo, comentado linha a linha, no GitHub: runbook_kill_sessao_inativa_rm.sql.
