ORA-04036 Encontrando la causa
Ampliar la PGA no siempre es la mejor solución, un caso particular de ORA-04036
Introducción
En este artículo analizamos un ORA-04036 — «La memoria de PGA que utiliza la instancia supera PGA_AGGREGATE_LIMIT» — que recibimos como alerta nocturna desde Cloud Control, esto suele ser un indicativo de que la PGA es pequeña, pero veamos que la solución no tiene por qué ser aumentar la PGA_AGGREGATE_TARGET o PGA_AGGREGATE_LIMIT. En este caso concreto, veremos el peligro de no gestionar las sesiones inactivas.
El entorno es una base de datos Enterprise Edition 19c con Diagnostic y Tuning Pack.
Configuración y trazas
Tenemos configurados 6 GB de PGA_AGGREGATE_TARGET y 18 GB de PGA_AGGREGATE_LIMIT
La alerta se recibió de noche, cuando la aplicación que corre en esa base de datos es un 8x5, por lo que ya sospechamos de algún job nocturno
Si miramos el alert y la traza creada veremos los detalles del inicident creado, y la pdb donde se ha producido:
El fragmento del alert.ora donde se registra el ORA-04036:
Nos lleva a analizar la primera traza a la que se hace referencia. Vemos que es una traza relativa a una sesión que comenzó varias horas antes y que dio varios ORA-01555 previamente al fallo de PGA:
La línea signalling ORA-4036 interrupt de la traza, apunta a que la sesión ha sido matada por la base de datos:
Al final del fichero de traza aparecen los dos ficheros de incident creados, son realmente la continuación del error, vamos a verlos con detenimiento:
Vemos los detalles de qué sesión fue matada cuanta PGA no ajustable estaba consumiendo.
Evidentemente es el proceso CURRENT, es el que disparó la traza y el incident. Posiblemente llevaba consumiendo PGA desde mucho antes, pero la entrada de Jobs nocturnos (ver los procesos J00X) sumaron consumo de PGA para que entre todos superaran el valor de la PGA_AGGREGATE_LIMIT.
Consultas y análisis
Voy a ver la historia de esa sesión en awr, tirando de la DBA_HIST_ACTIVE_SESS_HISTORY (NECESITA LICENCIA DIAGNOSTIC PACK)
Uso la consulta chk_sess_hist_pga.sql, que nos da las veces que el awr ha tomado muestras de esa sesión, saca el sql_id, el evento, el programa, la máquina y la PGA usada por esa sesión. Datos suficientes para localizar que efectivamente es un forms.
Muestro el principio y el final de la salida:
Dejó de registrase en el awr a las 16:41…justo cuando dio el ORA-1555 snaphsot too old. Pero la sesión continuó en la base de datos hasta que fue eliminada por la congestión de la PGA…con mas de 5G de PGA reservados.
Si volvemos al directorio de trazas, encontramos ordenando por fecha, justo debajo de la traza de la sesión, vemos una traza del pmon en la que hace referencia a la sesión que acaba de matarse.
Está claro por tanto que el sistema mata a la sesión que más memoria asignada no ajustable tiene cuando se da un ORA-4036, de la documentación oficial;
“Oracle Database ensures that the PGA size does not exceed this limit. If the database exceeds the limit, then the database terminates calls from sessions that have the highest untunable PGA memory allocations”
Vamos a confirmar el consumo de PGA la pasada noche hasta que saltó el ORA-4036, para ello vuelvo a usar dos vistas del awr, la dba_hist_active_sess_history y la dba_hist_pgastat, ambas nos darán una versión aproximada, sabemos que el AWR es un agregado y pierde detalle por el camino, pero nos ayudará a entender el problema
De la dba_hist_pgastat saco el consumo por snap de awr:
(uso chk_daily_pga.sql)
Con un pico en la máxima pga allocated justo en el snap del ORA-4036.
De la dba_hist_active_sess_history saco las sesiones que hicieron crecer el consumo de pga y que dispararon el ORA-4036.( uso la query chk_top10_pga_per_snap.sql)
Conclusión
Al saltar los Jobs nocturnos se disparó el consumo de pga…Pero la sesión que fue eliminada no es una que empezara con los Jobs, ni siquiera era una sesión activa, el awr no reportaba nada de ella desde hacía varias horas, era una sesión de forms que no hacía nada desde que devolvió un ORA-01555, muy seguramente la persona que estaba trabajando en ese forms durante la jornada de ayer, ya no estaba en la oficina y había dejado la aplicación abierta por si había suerte y terminaba durante la tarde noche.
Al no haberse cerrado la sesión, la PGA seguía retenida. Este suele ser un problema frecuente en forms, usuarios que no cierran la sesión y sesiones que no se desconectan por inactividad y por lo tanto no liberan los recursos que consumen. La solución será instaurar profiles para los usuarios de aplicación de tal forma que se limite el número de sesiones por usuario y el tiempo de inactividad a 9h, de tal forma que si se dejase a las 15h una sesión abierta, esta no llegase a las 01h.
Resumen
Hemos identificado la sesión que causante de un colapso de la PGA.
Hemos verificado que ha sido eliminada por el sistema.
Se ha propuesto el uso de profiles para evitar que sesiones inactivas vuelvan a provocar falta de memoria PGA.
Se han creado varias vistas que consultan el awr para extraer la información necesaria, la explotación del framework awr requiere de licencia diagnostic pack.
Consultas generadas y documentación oficial
Las consultas usadas son:
La documentación oficial consultada:
Bonus track
He construido una nueva consulta que me da las 20 sesiones en base de datos que mas PGA tienen reservada:
Se puede ver que algunos están inactivos con un LAST_CALL_ET muy grande, esos no generarán registros en el AWR pero si están consumiendo PGA, es una muy buena razón para activar un control de sesiones inactivas.
Nota:
Este es un problema real en un entorno real, todos los datos han sido anonimizados y las trazas editadas.
