Menu de navegação

[sergiomsoParticipante]

Pessoal
Boa tarde

Queria ver se alguem pode me dar uma dica

Eu tenho a seguinte query que eu vejo os locks. Eu queria que me trousesse o sid e serial# de quem está bloqueando. A query abaixo ele me traz o alter system kill session do sid e serial# de que está sendo bloqueado.

SELECT S.SID,
S.SERIAL#,
S.LOGON_TIME,
S.USERNAME,
S.STATUS,
S.MODULE,
S.BLOCKING_SESSION ” BK “,
S.SQL_ID,
S.MACHINE,
S.OSUSER,
S.EVENT “EVENTO”,
S.BLOCKING_SESSION_STATUS,
S.PROGRAM,
‘alter system kill session ”’||S.SID||’,’||S.SERIAL#||”’ immediate;’ TO_KILL_DB
FROM V$SESSION S, V$PROCESS P
WHERE S.PADDR = P.ADDR(+)
AND S.STATUS in (‘ACTIVE’,’INACTIVE’)
AND S.USERNAME is not null
AND S.BLOCKING_SESSION <> 0
ORDER BY S.SID DESC;

[José Laurindo ChiappaModerador]

Isso depende da Crítica informação que vc ** não ** nos dá, ie, a versão de Oracle que vc está usando : SE for 10g vc tem na V$SESSION as colunas BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE e BLOCKING_SESSION que te dizem, respectivamente, se a sessão está bloqueada ou não, a instância e o SID da sessão que está causando o bloqueio…. Já se for 11g, vc continua tendo a opção das colunas extras da V$SESSION mas vc tem no 11g também uma nova view V$SESSION_BLOCKERS, que vc faz JOIN com a V$SESSION para encontrar os SIDs bloqueadores….

[]s

Chiappa

[sergiomsoParticipante]

jlchiappa
Obrigado pelo retorno

A versão do meu oracle é 10g. Eu queria que no mesmo script monta-se o alter system kill session com sid# e o serial de quem esta bloqueando. Eu consigo trazer o sid pegando o S.BLOCKING_SESSION ” BK “, mas o serial# ele me traz de quem está sendo bloqueado. Talvez teria que montar um join. Não sei……. Assim ficaria mais rapido para descobrir as contençoes de lock e library cache lock que acontece constantemente aqui no banco.

Se estiverem alguma sugestão fico agradecido

Obrigado

[José Laurindo ChiappaModerador]

Então : por definição, *** todas *** as sessões dedicadas (ie, que não estejam sendo gerenciadas por algum POOL de conexão : me refiro a casos em que o cliente abre uma conexão diretamente ao banco, envia seus SQLs e depois no final fecha a sessão, que atendeu/atende só a ele) criam registros na V$SESSION….
Realmente, então, se vc quer ler mais de um registro de uma tabela, a opção mais simples é fazer um JOIN : não é a única, nem de longe, e não é a mais performática (o ideal pra performance sempre é vc conseguir acessar a tabela uma só vez) mas como estamos falando aqui de tabela interna com poucas dezenas de registros, vamos de JOIN que é mais fácil de entender….
Vamos a um exemplo :

==> tenho uma sessão 1 que ao conectar no banco ganhou SID=52 e SERIAL#=9 :

SCOTT#1:SQL>select sid, serial# from v$session where audsid=userenv(‘sessionid’);

SID SERIAL#

---

52        9

SCOTT#1:SQL>

=> e tenho uma outra sessão no mesmo banco que ao conectar ficou com SID=58 e SERIAL#=5 :

SCOTT#2:SQL>select sid, serial# from v$session where audsid=userenv(‘sessionid’);

SID SERIAL#

---

58        5

SCOTT#2:SQL>

=> vou fazer a sessão 1 bloquear um registro de uma tabela qualquer :

SCOTT#1:SQL>update dept set dname=’Depto 33 alt’ where deptno=33;

1 row updated.

SCOTT#1:SQL>

==> aí vou pedir na sessão 2 para tentar mexer no mesmo registro que está lockado, ela automaticamente ficará bloqueada, esperando o lock do registro que ela quer mexer ser liberado pela sessão 1 :

SCOTT#2:SQL>delete dept where deptno=33;

==> vc não verá, mas o prompt não retorna na sessão 2, ela está “travada”, esperando o lock ser liberado (com COMMIT ou ROLLBACK), ou então a sessão ser morta (o que implica em ROLLBACK)…. Vou consultar (em uma terceira janela, com usuário que tem permissão para isso) a V$SESSION para os registros envolvidos :

SYSTEM:SQL> select sid, serial#,
LOCKWAIT ,
STATUS ,
BLOCKING_SESSION_STATUS ,
BLOCKING_INSTANCE ,
BLOCKING_SESSION ,
EVENT
from v$session where username=’SCOTT’;

SID SERIAL# LOCKWAIT STATUS BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION EVENT

---

52        9          INACTIVE NO HOLDER                                      SQL*Net message from client
58        5 3A6E3CC0 ACTIVE   VALID                       1               52 enq: TX - row lock contention

==> legal, a sessão 2 (SID=58) realmente está esperando por um lock ser liberado, E esse lock pertence à sessão 1 (SID=52)… Seria só ler a V$SESSION novamente solicitando o SID indicado como Bloqueador na cláusula de JOIN :

SYSTEM:SQL>select b.sid SID_BLOQUEADOR, b.serial# SERIAL#_BLOQUEADOR
from v$session a, v$session b
where a.blocking_session is not null
and b.sid = a.blocking_session ;

SID_BLOQUEADOR SERIAL#_BLOQUEADOR

---

        52                  9

SYSTEM:SQL>

==> Tranquilo até aqui ?? Antes de adaptar essa condição de JOIN no teu script, só uma obs a mais : o KILL SESSION é mais ‘educado’, digamos assim, ele comanda a sessão pra cometer suicídio e se matar – não é o que aconteceu no meu exemplo (veja acima, a sessão bloqueadora depois que bloqueou o registro ficou com STATUS=INACTIVE) mas muitas vezes, porém, a sessão depois de aplicar o lock tá trabalhando intensamente ainda, mandando outros e outros comandos pro banco, aí ela demora a obedecer e cometer suicídio e se matar…. Assim, via de regra é mais indicado ao invés de mandar a sessão bloqueadora se matar, vc ir lá e dar um tiro na cuca dela, isso seria OU matando no sistema operacional a task/processo que está controlando a sessão OU usando o comando que faz isso, que é o DISCONNECT SESSION (https://oracle-base.com/articles/misc/killing-oracle-sessions#disconnect_session documenta e explica a diferença), é o que vou fazer aqui por via das dúvidas… E não vou colocar todas as colunas adicionais de identificação das sessões só pra encurtar o exemplo, mas nada impede de vc colocar se quiser :

SYSTEM:SQL> SELECT
a.SID SID_WAITER,
a.SERIAL# SERIAL#_WAITER,
b.SID SID_BLOCKER,
b.SERIAL# SERIAL#_BLOCKER,
a.BLOCKING_SESSION ” BK “,
a.EVENT “EVENTO WAIT”,
b.EVENT “EVENTO do Blocker”,
a.BLOCKING_SESSION_STATUS,
a.PROGRAM,
‘alter system disconnect session ‘ || chr(39) ||b.SID || ‘,’ || b.SERIAL# || chr(39) || ‘ immediate;’ TO_KILL_DB
FROM V$SESSION a, V$SESSION b
WHERE a.BLOCKING_SESSION is not null
AND b.SID = a.BLOCKING_SESSION;

SID_WAITER SERIAL#_WAITER SID_BLOCKER SERIAL#_BLOCKER BK EVENTO WAIT EVENTO do Blocker BLOCKING_SE PROGRAM TO_KILL_DB
———- ————– ———– ————— — ——————————— ————————- ———– —————————– ————————————————
58 5 52 9 52 enq: TX – row lock contention SQL*Net message from client VALID sqlplus@localhost.localdomain alter system disconnect session ‘52,9’ immediate;

==> realmente ao executar o comando a sessão é matada :

SYSTEM:SQL>alter system disconnect session ‘52,9’ immediate;

System altered.

SYSTEM:SQL>

==> ó o que aconteceu lá na tela do programa que conectou na sessão 2 e estava bloqueado :

SCOTT#2:SQL>delete dept where deptno=33;

1 row deleted.

SCOTT#2:SQL>

==> liberou…. OKDOC ??? Antes porém que vc saia adaptando pras suas necessidades e aplicando esse script em produção, friso que :

a) as coisas mudam um pouco quando temos RAC/cluster, pois haverá N instâncias do mesmo database, diferentes comandos serão necessários neste caso

b) ao invés de só sair matando, vc ao inves tem que *** ENTENDER *** por que está acontecendo tão frequentemente LOCKs no teu sistema : não é incomum casos onde o sistema mal e porcamente programado deixa o usuário deletar ou alterar um registro na tela sem comitar, aí o usuário vai tomar café e deixa a tela do aplicativo aberta, com a transação pendente, bloquenado trocentas outras pessoas…. é ULULANTEMENTE ÓBVIO que a aplicação tá errada em deixar (pois se vc der margem o usuário *** vai *** abusar, isso é óbvio) mas TAMBÉM o procedimento do usuário tá incorreto/inadequado, um caso desses é facilmente SOLUCIONADO (ao invés de mascarado com kill) instruindo o usuário a proceder de forma diferente…. Ou até mesmo, em casos extremos, vc pode programar no banco para cair a conexão do usuário após x minutos inativo

c) o que falei acima e meus exemplos se referem à lock por TRANSAÇÃO, ie, usuários alterando/deletando/inserindo dados : entenda que LIBRARY CACHE LOCK é ** totalmente outra coisa ** , via de regra isso ocorre quando vc tem objetos sendo recriados/recompilados constantemente, sem aviso (caso típico, desenvolvedores que tem acesso á Produção e querem enfiar novas versões de rotinas, ou sair criando índice ou coisas assim estruturais sem nenhum tipo de gerenciamento de mudança, janela, nada de nada)…. SE este é teu cenário, antes de mais nada vc tem é que arrumar a casa, e ** expulsar ** desenvs do ambiente Prod…
Também ocorre muito isso com aplicação porquinha que fica alterando estruturas de objetos/recriando objetos on-the-fly, à quente…. NÂO TEM OUTRA solução nesse caso que não seja consertar a porquice, se não vc vai ficar ETERNAMENTE trabalhando nos sintomas ao invés de corrigir o problema…

[]s

Chiappa

[sergiomsoParticipante]

jlchiappa
Boa tarde

Muito obrigado pela ajuda.
Excelente Informações esclarecedores dada por você.

Fico grato pelo conhecimento.

[Autor]

Posts