Cada commit espera o LGWR gravar no disco o redo da transação. O tempo cresce de dois jeitos: muitos commits com espera curta, ou poucos commits com espera longa. A correção de um caso não serve para o outro.
Sintomas
No Diagnóstico, aparece o achado Muito tempo esperando a gravação do redo no commit (COMMIT). O texto diz quanto do tempo total ficou na classe de espera Commit, em percentual e em segundos. Quando o trace tem commits, acrescenta quantos foram (A sessão fez e o número) e termina com Ver perfil por classe.
O achado aparece quando a classe Commit pesa no tempo total. A severidade é média e passa a alta quando a classe pesa mais. Na classe Commit entra só o evento log file sync.
A contagem de commits vem das linhas XCTEND com rlbk=0 e inclui as transações que só leram (rd_only=1), que terminam sem gravar redo. Por isso ela pode passar do número de esperas log file sync.
Causa
No commit e no rollback, a sessão espera o LGWR gravar no disco o redo da transação: é o log file sync. Compare Ocorrências e Média (s) da linha do evento no perfil:
- Muitas esperas curtas: commits demais. O caso típico é commit dentro de laço, um por linha, ou o driver em autocommit, que confirma cada comando.
- Média alta por espera: o redo demora para chegar ao disco. Armazenamento do redo lento, LGWR sem CPU num servidor ocupado, ou transações que geram muito redo de uma vez.
O cartão do evento, que abre ao clicar no nome dele no perfil, considera normal uma espera abaixo de 1 a 2 ms em armazenamento bom. O texto do achado lembra que média acima de alguns ms por espera aponta para o armazenamento do redo.
Resolução
Commits demais
- Faça o commit ao fim de cada unidade de trabalho do negócio, e não a cada linha. Em PL/SQL, tire o
commitde dentro do laço; em carga grande, confirme a cada lote de linhas. - Em Java, desligue o autocommit (
setAutoCommit(false)) e mande as linhas em lote (addBatcheexecuteBatch), com um commit por lote. - Compare o número de commits do Resumo com o que a operação faz: commits na casa do número de linhas processadas indicam commit dentro do laço.
Média alta por espera
Compare o log file sync com o log file parallel write, que é a gravação do próprio LGWR no disco:
select event, total_waits,
round(time_waited_micro / nullif(total_waits, 0) / 1000, 2) as media_ms
from v$system_event
where event in ('log file sync', 'log file parallel write');A visão acumula desde a subida da instância: para um período, tire duas leituras e compare a diferença. Se as duas médias são altas, o armazenamento do redo está lento: leve os redo logs para disco mais rápido, separado dos arquivos de dados. Se o log file parallel write é rápido e o log file sync é lento, o LGWR espera CPU: confira a carga do servidor no período.
Commit assíncrono
O Oracle aceita commit que não espera a gravação do redo (commit write batch nowait, ou os parâmetros commit_logging e commit_wait). A documentação do comando COMMIT descreve as opções.
Com commit assíncrono, uma transação confirmada pode se perder se a instância cair antes da gravação do redo. Use só para dado que pode ser refeito, e só depois de reduzir o número de commits.
Como confirmar
- Aplique uma mudança por vez: commits agrupados ou redo em disco mais rápido.
- Ligue o trace da mesma operação com esperas (ligar o trace com o DBMS_MONITOR).
- Envie o arquivo novo e abra o relatório.
- Compare o número de commits no Resumo e a linha
log file syncno perfil.
Resultado: com commits agrupados, caem os commits e as Ocorrências do log file sync; com redo mais rápido, cai a Média (s). Quando a classe deixa de pesar no tempo total, o achado some.
No relatório
O relatório de exemplo não tem espera da classe Commit; os links abrem as mesmas seções nele.
- Resumo: o cartão Comandos distintos mostra, embaixo, quantos commits e rollbacks a sessão fez (resumo).
- Diagnóstico: o achado, com o link Ver perfil por classe.
- Por classe de espera: a linha Commit (classes de espera).
- Perfil do tempo de resposta: a linha
log file sync, com Ocorrências e Média (s). O nome do evento abre o cartão Esperando o commit (perfil). - Distribuição de latência: a tabela do
log file syncmostra se são muitas esperas curtas ou poucas longas (latência). - Principais comandos por componente e por classe: os comandos que mais esperaram o commit (comandos por componente).
Transação que termina em rollback também espera o log file sync; o número de rollbacks tem página própria em rollbacks no trace.