Muito obrigado Alessandro, irá ajudar muito!!!
Em Quarta-feira, 13 de Abril de 2016 11:42, "Alessandro Lúcio Cordeiro da
Silva [email protected] [oracle_br]" <[email protected]>
escreveu:
Bom dia Rafael,
Problemas de Lock, em geral é problema da aplicação, mas temos que pelo menos
provar isso, o que não é tão fácil devido não conhecemos os meandros de um
sistema.
Bom, dizer que a sessão A esta bloqueando a sessão B não traz muita luz, então
o que eu normalmente faço é criar uma rotina que disparava por um JOB em 10 a
10 segundos, por exemplo, me traga quais são os objetos estão sendo locados, e
se for uma tabela me traga o ROWID da linha em questão. E se o programa for
escrito em packages/function/procedures PLSQL me traga o programa e a linha que
ocorreu o lock.
Neste cenário eu crio um schema chamado monitora, e crio a uma tabela que terá
os LOCKS e a procedure, espero que ajude:
TABELA :
create table ROWLOCK
( RLODATE DATE, SID_BLOCK NUMBER(6), SERIAL_BLOCK
NUMBER(6), USER_BLOCK VARCHAR2(30), MODULE_BLOCK VARCHAR2(50),
PROGRAM_BLOCK VARCHAR2(50), TERMINAL_BLOCK VARCHAR2(50), SID_WAIT
NUMBER(6), SERIAL_WAIT NUMBER(6), USER_WAIT VARCHAR2(30),
MODULE_WAIT VARCHAR2(50), PROGRAM_WAIT VARCHAR2(50), TERMINAL_WAIT
VARCHAR2(50), SECONDS_IN_WAIT NUMBER(6), EVENT_WAIT VARCHAR2(64),
OBJ_LOCADO VARCHAR2(60), ROWID_WAIT VARCHAR2(30), OBJETO_PLSQL
VARCHAR2(60), OBJETO_TYPE VARCHAR2(30), TEXTO_SQL CLOB);
create index INDX_SIDSERIAL on ROWLOCK (SID_BLOCK, SERIAL_BLOCK, SID_WAIT,
SERIAL_WAIT);
procedure registra_espera_por_lock is /* create table monitora.rowlock
(rlodate date default sysdate, -- DataHora que foi detectado o LOCK
SID_BLOCK number(6), -- SID do usuario que esta bloqueando o
acessso serial_block number(6), -- Seria do usuario que esta
bloqueando o acessso user_block varchar2(30), -- Usuario que
esta bloqueando o acessso sid_wait number(6), -- SID de que
esta esperando acessar a tabela serial_wait number(6), --
Seria de que esta esperando acessar a tabela user_wait varchar2(30),
-- USuário que esta esperando acessar a tabela seconds_in_wait
number(6), -- Tempo sem Segundos de espera tabela_wait
varchar2(60), -- Nome da Tabela que esta em LOCK rowid_wait
varchar2(30), -- ROWID da tabela que esta em LOCK objeto_plsql
varchar2(60), -- Se o SQL de espera esta dentro de uma rotina PLSQL,
irar retornar o nome. objeto_type varchar2(30), -- Se é Pacote,
trigger, função procedure; modulo varchar2(50), -- Nome do
modulo que esta esperando, se a sessão cliente setou o contexto programa
varchar2(50), -- Nome do programa que esta esperando, se a sessão
cliente setou o contexto terminal varchar2(50), -- Nome da
maquina onde ocorre a espera. texto_sql clob -- SQL
que esta aguardado o LOCK event varchar2(64) -- Nome do
Evento de Espera ); */ cursor c_espera_por_lock is select k.sid
sid_block, k.serial# seria_block, k.username
user_block, k.module module_block, k.program
program_block, k.terminal terminal_block,
---------------------------- v.sid sid_wait,
v.serial# seria_wait, v.username user_wait,
v.module module_wait, v.program
program_wait, v.terminal terminal_wait,
v.seconds_in_wait seconds_wait, v.event event_wait,
v.sql_id, do.owner||'.'||do.object_name obj_locado,
do.OBJECT_TYPE OBJECT_TYPE_locado, do2.object_type
OBJETO_PLSQL_type, do2.owner||'.'||do2.object_name OBJETO_PLSQL,
--, k.PLSQL_OBJECT_ID 'alter system kill session
'''||v.blocking_session||','||substr(ltrim(to_char(k.serial#,'99999')),1,5)||''';'
KILL_EM_BLOCK, v.row_wait_obj#, v.row_wait_file#,
v.row_wait_block#, v.row_wait_row# from v$session v, v$session k,
dba_objects do, dba_objects do2 where v.blocking_session = k.sid
and v.row_wait_obj# = do.object_id(+) and
v.PLSQL_ENTRY_OBJECT_ID = do2.object_id(+) and v.blocking_session is
not null --and v.event = 'enq: TX - row lock contention' and (
( ( k.WAIT_TIME = 0 and k.seconds_in_wait >= 5
) or ( k.WAIT_TIME <> 0 and (k.SECONDS_IN_WAIT - k.WAIT_TIME /
100) >= 5) ) OR --- OU (
( v.WAIT_TIME = 0 and v.seconds_in_wait >= 5 ) or (
v.WAIT_TIME <> 0 and (v.SECONDS_IN_WAIT - v.WAIT_TIME / 100) >= 5)
) ); v_rowid_wait varchar2(30); v_sysdate
date := sysdate; v_qtde integer; begin
for c in c_espera_por_lock loop
if c.OBJECT_TYPE_locado in ('TABLE', 'MVIEW', 'VIEW') then begin
v_rowid_wait := dbms_rowid.rowid_create ( 1, c.row_wait_obj#,
c.row_wait_file#, c.row_wait_block#, c.row_wait_row# ); exception
when others then v_rowid_wait := null; end; end if;
/*if c.seconds_in_wait >= 300 then -- Se for mais de 5 minutos de espera,
mata a sessão que esta bloqueando o registro... execute immediate
c.KILL_EM_BLOCK; continue; end if;*/
insert into monitora.rowlock (RLODATE,
SID_BLOCK, SERIAL_BLOCK,
USER_BLOCK, MODULE_BLOCK,
PROGRAM_BLOCK,
TERMINAL_BLOCK, SID_WAIT,
SERIAL_WAIT, USER_WAIT,
MODULE_WAIT,
PROGRAM_WAIT, TERMINAL_WAIT,
SECONDS_IN_WAIT,
EVENT_WAIT, OBJ_LOCADO,
ROWID_WAIT, OBJETO_PLSQL,
OBJETO_TYPE,
TEXTO_SQL) values (v_sysdate, c.sid_block,
c.seria_block, c.user_block, c.module_block,
c.program_block, c.terminal_block,
c.sid_wait, c.seria_wait, c.user_wait,
c.module_wait, c.program_wait,
c.terminal_wait, c.seconds_wait, c.event_wait,
c.obj_locado, v_rowid_wait,
c.objeto_plsql, c.objeto_plsql_type, (select
s.SQL_FULLTEXT from v$sql s where sql_id = c.sql_id and rownum =1));
commit;
end loop; end registra_espera_por_lock;
Alessandro Lúcio Cordeiro da Silva
Analista de Sistema
þ http://alecordeirosilva.blogspot.com/
Porque esta é a vontade de Deus, a saber, a vossa
santificação: que vos abstenhais da prostituição.
(1º Tessalonicenses 4:3)
Em Quarta-feira, 13 de Abril de 2016 9:37, "Rafael Mendonca
[email protected] [oracle_br]" <[email protected]> escreveu:
Senhores, bom dia.
Cenário:
Oracle Enterprise Edition 11.2.0.4 - ASM Single Instance - RH 6.0
Problema:
Um determinado sistema que atende diversos estados do Brasil (cada um com seu
database individual com as mesmas características citadas acima) está tendo um
grande problema de LOCK de transação há uns 4 meses. Em conversa com a equipe
de desenvolvimento nada mudou. No banco de dados também nada foi alterado. O
engraçado é que esse mesmo sistema em outros estados não está ocorrendo esse
problema de Lock TX, somente em um estado o problema de LOCK está muito
agravante.
Monitoramento:
O que fiz foi monitorar vários aspectos do database. Primeiramente a
V$SESSION_WAIT, depois a V$SESSION_EVENT e a V$SYSTEM_EVENT. Realmente a campeã
dos eventos em espera é a enq: TX - row lock contention.
Após a primeira análise, verifiquei as sessões ativas dos usuários que estavam
sendo locados. Detectei que é sempre um UPDATE em DIVERSAS tabelas do sistema.
Não é em um uma tabela ou específica ou duas, são em várias. Passei as
informações para a equipe de desenvolvimento verificar a possibilidade de
colocar o COMMIT; logo após algumas instruções (no caso os UPDATES detectados),
mesmo assim não adiantou.
Expliquei para o cliente que o grande problema de lock de transação é
justamente o DESIGN da aplicação. Que isso é um comportamento normal do RDBMS
Oracle, entre outros RDBMS. Mas o que o cliente questiona, é que antes não
existia esse problema de lock, e que o mesmo sistema atende outros estados e o
problema não se replica nesses estados.
Portanto, peço ajuda aos senhores para que possam me ajudar com suas
expertises. Não sei mais o que fazer em relação a esse problema.
#yiv0991738973 #yiv0991738973 -- #yiv0991738973ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0991738973
#yiv0991738973ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0991738973
#yiv0991738973ygrp-mkp #yiv0991738973hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv0991738973 #yiv0991738973ygrp-mkp #yiv0991738973ads
{margin-bottom:10px;}#yiv0991738973 #yiv0991738973ygrp-mkp .yiv0991738973ad
{padding:0 0;}#yiv0991738973 #yiv0991738973ygrp-mkp .yiv0991738973ad p
{margin:0;}#yiv0991738973 #yiv0991738973ygrp-mkp .yiv0991738973ad a
{color:#0000ff;text-decoration:none;}#yiv0991738973 #yiv0991738973ygrp-sponsor
#yiv0991738973ygrp-lc {font-family:Arial;}#yiv0991738973
#yiv0991738973ygrp-sponsor #yiv0991738973ygrp-lc #yiv0991738973hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0991738973
#yiv0991738973ygrp-sponsor #yiv0991738973ygrp-lc .yiv0991738973ad
{margin-bottom:10px;padding:0 0;}#yiv0991738973 #yiv0991738973actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0991738973
#yiv0991738973activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0991738973
#yiv0991738973activity span {font-weight:700;}#yiv0991738973
#yiv0991738973activity span:first-child
{text-transform:uppercase;}#yiv0991738973 #yiv0991738973activity span a
{color:#5085b6;text-decoration:none;}#yiv0991738973 #yiv0991738973activity span
span {color:#ff7900;}#yiv0991738973 #yiv0991738973activity span
.yiv0991738973underline {text-decoration:underline;}#yiv0991738973
.yiv0991738973attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv0991738973 .yiv0991738973attach div a
{text-decoration:none;}#yiv0991738973 .yiv0991738973attach img
{border:none;padding-right:5px;}#yiv0991738973 .yiv0991738973attach label
{display:block;margin-bottom:5px;}#yiv0991738973 .yiv0991738973attach label a
{text-decoration:none;}#yiv0991738973 blockquote {margin:0 0 0
4px;}#yiv0991738973 .yiv0991738973bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0991738973
.yiv0991738973bold a {text-decoration:none;}#yiv0991738973 dd.yiv0991738973last
p a {font-family:Verdana;font-weight:700;}#yiv0991738973 dd.yiv0991738973last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0991738973
dd.yiv0991738973last p span.yiv0991738973yshortcuts
{margin-right:0;}#yiv0991738973 div.yiv0991738973attach-table div div a
{text-decoration:none;}#yiv0991738973 div.yiv0991738973attach-table
{width:400px;}#yiv0991738973 div.yiv0991738973file-title a, #yiv0991738973
div.yiv0991738973file-title a:active, #yiv0991738973
div.yiv0991738973file-title a:hover, #yiv0991738973 div.yiv0991738973file-title
a:visited {text-decoration:none;}#yiv0991738973 div.yiv0991738973photo-title a,
#yiv0991738973 div.yiv0991738973photo-title a:active, #yiv0991738973
div.yiv0991738973photo-title a:hover, #yiv0991738973
div.yiv0991738973photo-title a:visited {text-decoration:none;}#yiv0991738973
div#yiv0991738973ygrp-mlmsg #yiv0991738973ygrp-msg p a
span.yiv0991738973yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0991738973
.yiv0991738973green {color:#628c2a;}#yiv0991738973 .yiv0991738973MsoNormal
{margin:0 0 0 0;}#yiv0991738973 o {font-size:0;}#yiv0991738973
#yiv0991738973photos div {float:left;width:72px;}#yiv0991738973
#yiv0991738973photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv0991738973
#yiv0991738973photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0991738973
#yiv0991738973reco-category {font-size:77%;}#yiv0991738973
#yiv0991738973reco-desc {font-size:77%;}#yiv0991738973 .yiv0991738973replbq
{margin:4px;}#yiv0991738973 #yiv0991738973ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv0991738973 #yiv0991738973ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv0991738973
#yiv0991738973ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv0991738973
#yiv0991738973ygrp-mlmsg select, #yiv0991738973 input, #yiv0991738973 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv0991738973
#yiv0991738973ygrp-mlmsg pre, #yiv0991738973 code {font:115%
monospace;}#yiv0991738973 #yiv0991738973ygrp-mlmsg *
{line-height:1.22em;}#yiv0991738973 #yiv0991738973ygrp-mlmsg #yiv0991738973logo
{padding-bottom:10px;}#yiv0991738973 #yiv0991738973ygrp-msg p a
{font-family:Verdana;}#yiv0991738973 #yiv0991738973ygrp-msg
p#yiv0991738973attach-count span {color:#1E66AE;font-weight:700;}#yiv0991738973
#yiv0991738973ygrp-reco #yiv0991738973reco-head
{color:#ff7900;font-weight:700;}#yiv0991738973 #yiv0991738973ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv0991738973 #yiv0991738973ygrp-sponsor
#yiv0991738973ov li a {font-size:130%;text-decoration:none;}#yiv0991738973
#yiv0991738973ygrp-sponsor #yiv0991738973ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv0991738973
#yiv0991738973ygrp-sponsor #yiv0991738973ov ul {margin:0;padding:0 0 0
8px;}#yiv0991738973 #yiv0991738973ygrp-text
{font-family:Georgia;}#yiv0991738973 #yiv0991738973ygrp-text p {margin:0 0 1em
0;}#yiv0991738973 #yiv0991738973ygrp-text tt {font-size:120%;}#yiv0991738973
#yiv0991738973ygrp-vital ul li:last-child {border-right:none
!important;}#yiv0991738973
