Mucho tiempo esperando la escritura del redo en el commit

Cada commit espera a que el LGWR escriba en disco el redo de la transacción. El tiempo crece de dos maneras: muchos commits con espera corta, o pocos commits con espera larga. La corrección de un caso no sirve para el otro.

Síntomas

En el Diagnóstico aparece el hallazgo Mucho tiempo esperando la escritura del redo en el commit (COMMIT). El texto dice cuánto del tiempo total quedó en la clase de espera Commit, en porcentaje y en segundos. Cuando el trace tiene commits, agrega cuántos fueron (La sesión hizo y el número) y termina con Ver perfil por clase.

El hallazgo aparece cuando la clase Commit pesa en el tiempo total. La gravedad es media y pasa a alta cuando la clase pesa más. En la clase Commit entra solo el evento log file sync.

El recuento de commits viene de las líneas XCTEND con rlbk=0 e incluye las transacciones que solo leyeron (rd_only=1), que terminan sin escribir redo. Por eso puede superar el número de esperas log file sync.

Causa

En el commit y en el rollback, la sesión espera a que el LGWR escriba en disco el redo de la transacción: es el log file sync. Compare Ocurrencias y Media (s) en la fila del evento en el perfil:

• Muchas esperas cortas: demasiados commits. El caso típico es un commit dentro de un bucle, uno por fila, o el driver en autocommit, que confirma cada sentencia.
• Media alta por espera: el redo tarda en llegar al disco. Almacenamiento del redo lento, LGWR sin CPU en un servidor ocupado, o transacciones que generan mucho redo de una vez.

La tarjeta del evento, que se abre al hacer clic en su nombre en el perfil, considera normal una espera por debajo de 1 a 2 ms en un buen almacenamiento. El texto del hallazgo recuerda que una media por encima de algunos ms por espera apunta al almacenamiento del redo.

Solución

Demasiados commits

• Haga el commit al final de cada unidad de trabajo del negocio, y no en cada fila. En PL/SQL, saque el commit del bucle; en una carga grande, confirme cada lote de filas.
• En Java, desactive el autocommit (setAutoCommit(false)) y envíe las filas en lotes (addBatch y executeBatch), con un commit por lote.
• Compare el número de commits del Resumen con lo que hace la operación: commits del orden del número de filas procesadas indican un commit dentro del bucle.

Media alta por espera

Compare el log file sync con el log file parallel write, que es la escritura del propio LGWR en 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');

La vista acumula desde el arranque de la instancia: para un período, tome dos lecturas y compare la diferencia. Si las dos medias son altas, el almacenamiento del redo es lento: lleve los redo logs a un disco más rápido, separado de los archivos de datos. Si el log file parallel write es rápido y el log file sync es lento, el LGWR espera CPU: revise la carga del servidor en el período.

Commit asíncrono

Oracle acepta un commit que no espera la escritura del redo (commit write batch nowait, o los parámetros commit_logging y commit_wait). La documentación de la sentencia COMMIT describe las opciones.

Cómo confirmar

1. Aplique un cambio por vez: commits agrupados o redo en un disco más rápido.
2. Active el trace de la misma operación con esperas (activar el trace con DBMS_MONITOR).
3. Envíe el archivo nuevo y abra el informe.
4. Compare el número de commits en el Resumen y la fila log file sync en el perfil.

Resultado: con commits agrupados, bajan los commits y las Ocurrencias del log file sync; con un redo más rápido, baja la Media (s). Cuando la clase deja de pesar en el tiempo total, el hallazgo desaparece.

En el informe

El informe de ejemplo no tiene esperas de la clase Commit; los enlaces abren las mismas secciones en él.

• Resumen: la tarjeta Sentencias distintas muestra, abajo, cuántos commits y rollbacks hizo la sesión (resumen).
• Diagnóstico: el hallazgo, con el enlace Ver perfil por clase.
• Por clase de espera: la fila Commit (clases de espera).
• Perfil del tiempo de respuesta: la fila log file sync, con Ocurrencias y Media (s). El nombre del evento abre la tarjeta Esperando el commit (perfil).
• Distribución de latencia: la tabla del log file sync muestra si son muchas esperas cortas o pocas largas (latencia).
• Sentencias principales por componente y por clase: las sentencias que más esperaron el commit (sentencias por componente).

Una transacción que termina en rollback también espera el log file sync; el número de rollbacks tiene su propia página en rollbacks en el trace.