Gravações lentas (latência de INSERT/UPDATE)

As operações de INSERT/UPDATE/DELETE estão mais lentas que o esperado. Primeiro determinamos pelo que a gravação está esperando (commit/redo, locks, disco ou overhead por linha) e depois descemos passo a passo até a causa raiz.

Pelo que a gravação está esperando?

Classifique o gargalo observando os wait events: predomina a sincronização de commit/redo (WAL), locks, latência de gravação em disco ou overhead de índices/triggers por linha?

SELECT event, total_waits, ROUND(time_waited/100,1) AS sec_waited
FROM v$system_event
WHERE event IN ('log file sync','log file parallel write','enq: TX - row lock contention','db file sequential read')
ORDER BY time_waited DESC;

• Esperando commit / redo (sincronização do WAL)
• Esperando um lock
• Suspeita de overhead por linha (índices/triggers)
• A latência de gravação em disco está alta

Padrão de commit ou fsync?

Um commit separado por linha (autocommit) dispara a sincronização de redo/log tantas vezes quanto há linhas. Por outro lado, se o número de commits for razoável, mas cada commit esperar pelo fsync, o problema é a latência do disco de log.

SELECT name, value FROM v$sysstat WHERE name IN ('user commits','redo writes','redo size');
SELECT ROUND(time_waited_micro/GREATEST(total_waits,1)/1000,2) AS ms_per_sync FROM v$system_event WHERE event='log file sync';

• Autocommit / um commit a cada linha
• O commit espera pela sincronização do log (fsync)

Quem está segurando o lock?

Se a gravação está esperando um lock: outra sessão pode estar segurando a mesma linha/índice, ou muitas sessões podem estar tentando gravar na mesma linha 'quente'.

SELECT s.sid, s.serial#, s.username, s.blocking_session, s.event, s.seconds_in_wait
FROM v$session s WHERE s.blocking_session IS NOT NULL;

• Outra sessão está segurando o lock
• Muitas sessões gravam na mesma linha quente

De onde vem o overhead por linha?

Todo INSERT/UPDATE também atualiza todos os índices da tabela e dispara eventuais triggers/constraints. Um número excessivo de índices ou um trigger pesado deixa as gravações significativamente mais lentas.

SELECT index_name, uniqueness FROM user_indexes WHERE table_name='T';
SELECT trigger_name, triggering_event FROM user_triggers WHERE table_name='T';

• A tabela tem índices demais / desnecessários
• Há um custo pesado de trigger ou constraint

Causa raiz: autocommit / commit por linha

Um commit separado para cada linha repete a sincronização de redo/log tantas vezes quanto há linhas. Agrupando as operações, você pode reduzir drasticamente o número de commits (e, portanto, de fsyncs).

Causa raiz: disco de redo/log lento (latência de fsync)

Cada commit espera até que o registro de redo/WAL seja gravado em armazenamento durável (fsync). Se o disco de log for lento (alta latência de gravação, IOPS insuficiente), cada commit paga essa latência.

Causa raiz: outra sessão está segurando o lock

A gravação está esperando um lock de linha/índice mantido por outra sessão. O bloqueador geralmente é uma transação longa ou uma cujo commit foi esquecido.

Causa raiz: contenção de linha quente

Muitas sessões tentam gravar na mesma linha (ex.: um contador, número de sequência, saldo); todas entram na fila do mesmo lock e as gravações são serializadas.

Causa raiz: overhead excessivo de índices

Todo índice da tabela é atualizado a cada gravação. Índices em excesso ou não utilizados deixam INSERT/UPDATE diretamente mais lentos.

Causa raiz: trigger / constraint pesado

Um trigger disparado a cada gravação ou uma constraint sendo validada (especialmente uma verificação de foreign key sem índice) adiciona custo extra a cada linha.