Essa semana eu precisei monitorar alguns locks que estavam ocorrendo em uma base de produção.
Só que os locks eram esporádicos, não dava para ficar na frente de uma tela de OEM esperando acontecer, além disso eu precisava descobrir o SQL que estava bloqueando algum recurso e o SQL que estava esperando pelo recurso.
Eu criei um job que roda de 2 em 2 minutos , ele verifica se existe alguma sessão com evento de espera tipo o : enq: TX - row lock contention.
Caso exista e o tempo de espera seja maior que X ele loga os SQLSs que estao bloqueando e esperando por um recurso,o SID das sessões,a data e o tempo de espera da sessão em uma tabela que criei para isso.
O Mais legal nisso foi que descobri a coluna Prev_sql_id da V$SESSION, com ela conseguimos saber o SQL que esta bloqueando algum recurso.
Abaixo segue o comando de criação do JOB e da Tabela para logar.
UPDATE:
Legal olhar um post de quase 4 anos atrás, vendo agora não gosto muito desse código, então decide realizar algumas pequenas modificações , graças ao comentário do Mauro rafael, modifiquei na hora mesmo, só por capricho,rs.
Tabela:
CREATE TABLE LOG_LOCK (SQL_BLOCKER VARCHAR2(1000),
SID_BLOCKER INT, SQL_BLOCKED VARCHAR2(1000),
SID_BLOCKED INT,DATA_LOG DATE,WAIT INT);
Job:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Monitor_Lock',
job_type => 'PLSQL_BLOCK',
job_action => '
DECLARE
V_BLOCKER INT;
V_BLOCKED INT;
V_SQL VARCHAR2(1000);
V_SQL2 VARCHAR2(1000);
V_TIME INT;
V_CTR INT;
BEGIN
SELECT COUNT(*) INTO V_CTR
FROM v$session s
WHERE event LIKE ''enq: %'';
FOR CRS IN (select s1.sid BLOCKER, s2.sid BLOCKED
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2)
LOOP
IF V_CTR >= 1 THEN
V_BLOCKED:=CRS.BLOCKED;
V_BLOCKER:=CRS.BLOCKER;
SELECT SQL_TEXT INTO V_SQL FROM V$SQL S
INNER JOIN V$SESSION V ON (V.Prev_sql_id=S.SQL_ID)
WHERE V.SID=V_BLOCKER;
SELECT SQL_TEXT ,V.SECONDS_IN_WAIT INTO V_SQL2,V_TIME FROM V$SQL S
INNER JOIN V$SESSION V ON (V.sql_id=S.SQL_ID)
WHERE V.SID=V_BLOCKED;
IF V_TIME > 30 THEN
INSERT INTO LOG_LOCK (SID_BLOCKER,SQL_BLOCKER,SID_BLOCKED,SQL_BLOCKED,DATA_LOG,WAIT) VALUES
(V_BLOCKER,V_SQL,V_BLOCKED,V_SQL2,SYSDATE,V_TIME);
COMMIT;
END IF;
END IF;
END LOOP;
END;
',
start_date => trunc(sysdate)+18/24,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
