Aumento en esperas log file sync

Se puede encontrar mucha "blografía" acerca de este clásico problema de tunning. El parámetro log file sync está directamente ligado al proceso interno LGWR que sincroniza el log buffer (área de memoria interna de la SGA) y redo logs en disco (archivos donde son registradas las transacciones), y puede revelar uno de dos problemas (sino ambos): un sub dimensionamiento de la base de datos, o un uso inadecuado de transacciones en las aplicaciones que la usan. En ambos casos, el tiempo de respuesta puede experimentar picos, deteriorando la performance.

Si bien alguien puede pensar en alterar la configuración de Oracle, es mejor comenzar a buscar posibles sesiones ofensoras. Es sabido que el 98% de las veces las aplicaciones son las principales reponsables de los problemas de performance que los usuarios reclaman, y no la base de datos.

Recientemente tuve la oportunidad de ver un problema de performance en una base de datos de producción que comenzó a paralizarse sin explicación aparente. He notado una tendencia de los gerentes a pensar los problemas de performance como problemas de hardware, y que adquiriendo nuevos recursos el problema se soluciona. Hay veces que puede ser cierto -no descartemos posibilidades- pero antes de cualquier conjetura y gasto innecesario, un poco de investigación es primordial. Para eso, Oracle nos provee de algunas herramientas de gran utilidad para identificar comportamientos anómalos, y causas que los producen.

En este caso, fue la herramienta de diagnóstico Statspack quien reveló la verdadera causa del problema.

El primer indicio en el reporte fue en los Top 5 wait events: log file sync al tope de la lista.
Eso significaba que Oracle estaba generando mucho más REDO, y tan agresivamente que la instancia dificilmente podía procesarlo. Existen errores típicos de programación PL/SQL que generan más REDO del necesario, uno de ellos es el clásico commit dentro del loop:

FOR rec IN (SELECT * from TABLA_GRANDE)
-- ..
-- procesamiento del registro rec
COMMIT;
END LOOP;
-- no es aquí donde debería hacerse el commit?

Analisando los resultados del reporte de Statspack, se pudo percibir que no había habido un aumento de COMMITs sino de ROLLBACKs.

Extracto de Statspack:

Load Profile
-----------



% Blocks changed per Read:    4.09    Recursive Call %:                92.21
Rollback per transaction %:   64.85       Rows per Sort:                14.35

....
user commits                                  96,732          161.2          0.4
user rollbacks                               175,390          292.3          0.6
...

El valor de Rollback per transaction indicaba que un 65% de las transacciones terminaban con rollback, y más adelante en el reporte se pudo verificar que efectivamente estaban ocurriendo el doble de rollbacks que de commits. Esto no es normal prácticamente en ninguna base de datos, al menos las que yo he visto. Leyendo reportes anteriores con actividad normal, se comprobó que efectivamente estos números había cambiado (anteriormente el índice era 4%). Sirvió para enfocarse y analisar de lleno las aplicaciones que se estaban ejecutando, y descubrir a través de las sesiones activas que una de ellas generaba mucho rollback. Sucede que el código que esta ejecutaba, había sido levemente modificado (3 líneas) y estaba generando errores inesperados con rollbacks de forma indiscriminada.

Las bases de datos deben servir a las aplicaciones y usuarios de la mejor forma posible. Para eso, no basta simplemente con ampliar recursos de memoria ni ajustar parámetros. Las aplicaciones que afectan la base de datos deben ser las primeras en ser tuneadas, y ni que hablar testeadas antes de colocarlas en producción. Ya sea en proyectos de desarrollo o mantenimiento, los tests de carga son necesarios. Un test de funcionalidad con una única sesión no revela este tipo de defectos, los cuales sumados en un ambiente real se convierten en un gran problema.

Haciendo las veces de investigador, el DBA debe recurrir a las herramientas de diagnóstico como Statspack, tkprof y vistas dinámicas. En caso de tratarse de código malicioso, será fundamental trabajar en equipo junto a los desarrolladores para hallar la fuente del problema.