Olá,
Uma das tarefas de um DBA é monitorar o banco de dados de forma que seja identificado possíveis problemas que possam vir a comprometer a performance ou a disponibilidade do mesmo. Dentre alguns problemas que podem ser identificados antecipadamente, estão aos bloqueios (locks), em especial, aqueles onde existam sessões bloqueadoras e sessões bloqueadas. Dependendo da situação, descobrir de forma rápida a sessão (SID) no banco de dados que está bloqueando por muito tempo um recurso, é fundamental para se evitar maiores problemas. Dependendo do recurso bloqueado, (tabela, linha, etc.) várias sessões ficarão bloqueadas até a liberação do bloqueio do recurso pela sessão bloqueadora. O problema em si não é o bloqueio, mas por quanto tempo a sessão fica bloqueada. Em determinados casos, por falhas na aplicação, entre outros, o bloqueio pode ficar eternamente e, em casos extremos, somente matando a sessão bloqueadora (kill session ...) irá liberar o recursos e resolver o problema.
Enfim, o objetivo deste artigo será demonstrar algumas formas de identificar as sessões que estão bloqueando outras sessões. Serão apresentadas algumas views e consultas SQL que poderão ser úteis para este fim. Irei simular abaixo um ambiente na qual uma sessão no Oracle irá bloquear outras sessões. Segue demonstração abaixo:
SESSÃO 1
C:\>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Seg Mai 4 10:17:20 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select distinct sid from v$mystat;
SID
----------
278
SQL> create table t1 (id number);
Tabela criada.
SQL> insert into t1 values (1);
1 linha criada.
SQL> commit;
Commit concluído.
SQL> update t1 set id=10;
1 linha atualizada.
Acima, a sessão de SID 278 realizou um UPDATE na tabela T1 e não efetuou COMMIT.
SESSÃO 2
SQL> select distinct sid from v$mystat;
SID
----------
165
SQL> create table t2 (id number);
Tabela criada.
SQL> insert into t2 values (1);
1 linha criada.
SQL> commit;
Commit concluído.
SQL> update t2 set id=100;
1 linha atualizada.
SQL> update t1 set id=20;
-- Aguardando
Na sessão de SID 165 acima, tanto na tabela T2 quanto na tabela T1 foram realizados UPDATEs. No entanto, ao realizar o UPDATE na tabela T1, a sessão ficou bloqueada, ou seja, aguardando que a sessão de SID 278 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T1.
SESSÃO 3
SQL> select distinct sid from v$mystat;
SID
----------
41
SQL> update t2 set id=30;
-- Aguardando
A sessão de SID 41 efetuou um UPDATE na tabela T2 e ficou bloqueada, ou seja, aguardando que a sessão de SID 165 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T2. Enfim, diante da situação das sessões de banco de dados acima, poderemos ver abaixo, consultando a view dinâmica de desempenho V$SESSION, quais sessões estão bloqueadas. Caso deseje ver por quanto tempo a sessão está bloqueada, basta adicionar a coluna SECONDS_IN_WAIT na instrução SELECT a seguir.
SQL> select sid,
2 serial#,
3 status,
4 username,
5 osuser,
6 program,
7 blocking_session blocking,
8 event
9 from v$session
10 where blocking_session is not null;
SID SERIAL# STATUS USERNAME PROGRAM BLOCKING EVENT
------ -------- -------- ---------- ------------ -------- -----------------------------
41 17090 ACTIVE SYS sqlplus.exe 165 enq: TX - row lock contention
165 6770 ACTIVE SYS sqlplus.exe 278 enq: TX - row lock contention
Pelo resultado acima, é possível verificar que a sessão de SID 41 está sendo bloqueada pela sessão de SID 165, que está sendo bloqueada pela sessão de SID 278. Uma outra forma de ver essa situação é consultar as views DBA_WAITERS e DBA_BLOCKERS. Caso estas views não estejam criadas no banco de dados, as mesmas poderão ser criadas através do script $ORACLE_HOME/rdbms/admin/catblock.sql. Segue o resultado da view DBA_WAITERS conforme demonstrado abaixo.
SQL> select waiting_session,holding_session from dba_waiters;
WAITING_SESSION HOLDING_SESSION
--------------- ---------------
41 165
165 278
Uma outra forma que temos para visualizar situações com esta é executar o script $ORACLE_HOME/rdbms/admin/utllockt.sql. Este script é muito útil quando existem várias sessões bloqueadoras e bloqueadas e precisamos saber qual sessão iniciou todo o processo de bloqueio. A visualização do resultado é em forma de uma Tree View, que demonstra em uma hierarquia, as sessões bloqueadoras e as sessões bloqueadas.
SQL> @utllockt.sql
Tabela criada.
Tabela criada.
2 linhas criadas.
Commit concluído.
Tabela eliminada.
1 linha criada.
Commit concluído.
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- -------------- -------------- ------------ ---------- ---------
278 None
165 Transaction Exclusive Exclusive 393227 454
41 Transaction Exclusive Exclusive 524312 454
Tabela eliminada.
Acima, é possível determinar que a sessão de SID 278 iniciou o processo de bloqueio que fez com que a sessão de SID 165 ficasse bloqueada. Como consequência, a sessão de SID 165 também bloqueou a sessão de SID 41. Em relação ao objeto (tabela) que está sofrendo bloqueio, é possível determiná-lo consultando as views DBA_OBJECT, V$LOCKED_OBJECT, V$LOCK. O código PL/SQL abaixo retorna um resultado na qual é possível ver o SID da sessão bloqueadora, o SID da sessão bloqueada e o recurso (objeto) que detém o bloqueio (lock).
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.enable (1000000);
3
4 FOR do_loop IN (SELECT session_id,
5 a.object_id,
6 xidsqn,
7 oracle_username,
8 b.owner owner,
9 b.object_name object_name,
10 b.object_type object_type
11 FROM v$locked_object a, dba_objects b
12 WHERE xidsqn != 0 AND b.object_id = a.object_id)
13 LOOP
14 DBMS_OUTPUT.put_line ('.');
15 DBMS_OUTPUT.put_line ('Blocking Session : ' || do_loop.session_id);
16 DBMS_OUTPUT.put_line (
17 'Object (Owner/Name): '
18 || do_loop.owner
19 || '.'
20 || do_loop.object_name);
21 DBMS_OUTPUT.put_line ('Object Type : ' || do_loop.object_type);
22
23 FOR next_loop
24 IN (SELECT sid
25 FROM v$lock
26 WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
27 LOOP
28 DBMS_OUTPUT.put_line (
29 'Sessions being blocked : ' || next_loop.sid);
30 END LOOP;
31 END LOOP;
32 END;
33 /
.
Blocking Session : 278
Object (Owner/Name): SYS.T1
Object Type : TABLE
Sessions being blocked : 165
.
Blocking Session : 165
Object (Owner/Name): SYS.T1
Object Type : TABLE
Sessions being blocked : 41
.
Blocking Session : 165
Object (Owner/Name): SYS.T2
Object Type : TABLE
Sessions being blocked : 41
Procedimento PL/SQL concluído com sucesso.
4 comentários:
Olá Eduardo,
Ótimo post, realmente o Script nativo da Instância utllockt.sql lhe retorna a informação exata, outra forma rápida e fácil de identificar a sessão principal que gerou o Lock é realizando a consulta no Synonym v$lock:
select *
from v$lock
where block <> 0;
Ela irá retornar o Sid que originou a consulta, algo bem mais simples e resumido porém rápido no caso de não se lembrar do caminho e nome do Script.
Parabéns.
Olá Wender,
A view dinâmica de desempenho V$LOCK realmente é uma mão na roda :-)
As views DBA_WAITERS e DBA_BLOCKERS, ultimamente, tem sido as minhas preferidas.
Abraços e até mais,
Legatti
Parabéns pelo post !!!! Muito Bom
Olá Francois,
Obrigado pela visita e pelo comentário!
Abraços,
Legatti
Postar um comentário