Lentidão geral: Um owner com mais de 450 locks no banco

[Róli]

Olá!

Em um ambiente bancário, com considerável carga no banco, um determinado owner apresenta mais de 450 locks de mais de 2horas na execução de vários de seus processos. Isso acaba onerando a performance dos demais owners no banco, causando uma lentidão geral.

=>Os locks são de SQLs diferentes, não foi possível pegar o SQL gerador do lock.
DÚVIDA: Teoricamente, o SQL que está a mais tempo em execução seria o gerador do lock?
No final, está o SQL utilizado para buscar os locks.

=> Os locks gerados são do tipo locks TX onde mais transações estão buscando atualizar um mesmo registro e este está locado.

=> Um dos SQLs que está nas sessões com lock, chama uma package de 2 mil linhas. Essa package possui DMLs de update, delete e insert. O commit é realizado a cada 1000 linhas retornadas no contador do código.

=> Gerado plano de execução dos SQLs envolvidos nas sessões com locks e o plano é bom.

=> Ao verificar as Foreign keys não indexadas para esse owner, foram 546 listadas. Há necessidade de indexadas todas as Foreign keys ou somente as que possuirem ON DELETE CASCADE?

=> A coleta de estatísticas está atualizada para esse owner, feita full diariamente.

=> Gerado relatório AWR referente ao período da geração de locks: Em Segments by Row Lock Waits outro owner estava apresentando contenção. Seria porque esse outro owner representa a metade do banco em bytes? Ou seja, o volume de dados desse owner é muito maior que do owner que está apresentando os locks a baixo.

SET LINESIZE 130
SET PAGESIZE 999
COLUMN Object FORMAT a30 HEADING 'Object'
COLUMN Type FORMAT a4 HEADING 'Type'
COLUMN UserID FORMAT a20 HEADING 'OS/Oracle'
COLUMN Hold FORMAT a10 HEADING 'Hold'
COLUMN Program FORMAT a15 HEADING 'Program'
COLUMN usercode FORMAT a12 HEADING 'SID/Serial#'
COLUMN WaitMin FORMAT 999,999 HEADING 'Wait Time (minutes)'
SELECT
FROM
WHERE
a.osuser || ':' || a.username UserID
, a.sid || ',' || a.serial# usercode
, b.lock_type Type, b.mode_held Hold
, c.owner || '.' || c.object_name Object
, a.program Program
, ROUND(d.seconds_in_wait/60,2) WaitMin
gv$session a
, dba_locks b
, sys.dba_objects c
, gv$session_wait d
a.sid = b.session_id
AND b.lock_type IN ('DML','DDL')
AND b.lock_id1 = c.object_id
AND b.session_id = d.sid
ORDER BY 7
/



OS/Oracle SID/Serial# Type Hold Object Program Wait Time(minutos)
-------------------- ------------ ---- ---------- ------------------------------ --------------- ------------------
usuarioX:ownerX 252,46365 DML Row-X (SX) ownerX.tablex PCSIS316.EXE 31
usuarioX:ownerX 252,46365 DML Row-X (SX) ownerX.tabley PCSIS316.EXE 50
usuarioX:ownerX 252,46365 DML Row-X (SX) ownerX.tablef PCSIS316.EXE 45
usuarioX:ownerX 252,46365 DML Row-X (SX) ownerX.tablex PCSIS316.EXE 92


=> Como identificar a sessão geradora de lock e a sessão que está sofrendo lock?


Desde já, agradeço por toda atenção

[dpreviatti]

Tive uma situação parecida uma vez e usei uma query simples pra solucionar.
Na época, tive que ligar para alguns usuários fecharem o sistema, pois pensei que poderia trazer implicações fazer um kill.

Code: Select all

SELECT s1.username || ' - ' || s1.machine || ' ( SessionID=' || s1.sid || ' )  bloqueia ' || s2.username || ' - ' || s2.machine || ' ( SessionID=' || s2.sid || ' ) ' AS RESULTADO
       ,s1.sql_hash_value, s2.sql_hash_value
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

Coloquei tb o hash value da query utilizada pelo usuário, para posteriormente tentar corrigir com os desenvolvedores (tarefa árdua, rsrs).
Espero ter ajudado.
Mês que vem estarei realizando o curso de performance! ANSIOSO!

[fabiosegato]

QUERY não gera lock, logo o problema não é query, problema são os DMLS, principalmente DELETE e UPDATE. Geralmente esse tipo de situação é gerada por aplicações mal construídas. Uma aplicação bem construída por exemplo verifica antes de fazer um delete ou update se o registro está lockado ou não, utilizando UPDATE NOWAIT, isso evita que várias sessões fiquem lockadas esperando um processo terminar.

[portilho]

Uma Query pode gerar Lock (TX) se for FR UPDATE.
Não é esse o caso?

Pelo AWR será difícil e duvidoso você pegar que SQL causa estes Locks.

Sugiro que você colete o conteúdo deste SQL pela cron, guardando m um log, mostrando o horário:

SET PAGESIZE 1000;
SET LINESIZE 210;
COL EVENT FORMAT A40;
COL SQL_TEXT FORMAT A70;
-- 10gR1 ou superior
SELECT W.SID, W.EVENT, W.SECONDS_IN_WAIT, SQL.SQL_TEXT FROM V$SESSION_WAIT W, V$SESSION S, V$PROCESS P, V$SQLTEXT SQL WHERE W.SID = S.SID AND S.PADDR = P.ADDR AND SQL.ADDRESS = S.SQL_ADDRESS AND SQL.HASH_VALUE = S.SQL_HASH_VALUE AND W.WAIT_CLASS != 'Idle' ORDER BY W.SECONDS_IN_WAIT, W.SID, SQL.PIECE;

[Róli]

Boa noite a todos,

Obrigada pelas dicas.

Quanto a query gerar lock, temos rotineiras situações com locks em query FOR UPDATE sim.

O "monitoramento" pela cron identificou o lock e já estamos trabalhando para resolução.

Obrigada pela atenção de todos.

[Róli]

Olá,

Gostaria de levantar uma possibilidade de melhoria para resolução de Locks, conforme a seguir:


SQL> ALTER TABLE t initrans 8; --- máximo é 100

Tabela alterada.

SQL> select ini_trans de user_tables onde table_name = 'T';
INI_TRANS
----------
8

alter table <table name> initrans 16;
alter index <index name> rebuild initrans 16;

Alterar o initrans, o número inicial de transações no bloco da tabela. Qual o risco de incosistência nos dados ou perda de integridade das operações?

Essa alteração é recomendada?

Encontri pouca documentação da Oracle referente a essa possibilidade de recurso.

Obrigada.