ERROR ORA-04030 MEMORIA DE PROCESO INSUFICIENTE EN AIX

Que tal amigos!

Realizamos la instalación de Oracle 11.2.0.4 sobre plataforma AIX 1.7.  La base de datos se configuró para ser de tipo 'Datawarehouse'. En un inicio los usuarios no reportaron problemas, sin embargo, después de un mes de operaciones, se presento el siguiente error:

"ORA-04030: memoria de proceso insuficiente al intentar asignar 246296 bytes (QERGH hash-agg,kllcqas:kllsltba)

04030. 00000 -  "out of process memory when trying to allocate %s bytes (%s,%s)"

*Cause:    Operating system process private memory has been exhausted

*Action: "

Después de ejecutar el asistente 'Automatic Workload Repository' o 'AWR' se detectó un sentencia que realizaba consultas 'anidadas'  del tipo 'select campo, (select campo),' donde al final se realizaba un 'join' entre el resultado de estas subconsultas.  La sentencia realiza un 'full scan' y join de tipo 'hash' el cual utilizaba memoria privada o 'Private Global Area'  o 'PGA'. 

Todo indicaba que el origen del problema era  por memoria 'PGA' insuficiente, como lo indica el error '  Operating system process private memory has been exhausted' .   Al parecer la solución era muy sencilla: incrementar el tamaño de la 'PGA'.

Verificamos el tamaño actual de la memoria 'PGA', donde se valida que se tienen 10 GB asignados:

SQL> show parameter pga_aggregate_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 10G

Para validar si los 10gb de 'PGA'  son suficientes, ejecutamos una consulta sobre la vista 'pga_target_for_estimate' para validar cual es la recomendación de Oracle respecto al tamaño ideal. El 'advisor' de Oracle nos indica que un incremento mayor a los 12 GB, no representa un diminución de las lecturas a disco, por lo que el valor actual de 10gb es cercano al ideal.

SQL>SELECT PGA_TARGET_FOR_ESTIMATE/1024/1024/1024, ESTD_EXTRA_BYTES_RW/1024/1024/1024 FROM v$pga_target_advice

     ORDER BY 1;

       2

PGA_TARGET_FOR_ESTIMATE/1024/1024/1024 ESTD_EXTRA_BYTES_RW/1024/1024/1024

-------------------------------------- ----------------------------------

                                  1.25                         604.720174

                                   2.5                          604.713353

                                     5                           604.713353

                                   7.5                          604.713353

                                    10                         485.791062

                                    12                         473.302542

                                    14                         473.302542

Validamos si las consultas están consumiendo más de los 10gb asignados, por medio de la vista 'v$process'. Para nuestra sorpresa, verificamos que el máximo de 'PGA' en uso es de apenas 446 mb.


SELECT sum(pga_used_mem)/1024/1024, sum(pga_max_mem)/1024/1024 FROM v$process;

SUM(PGA_USED_MEM)/1024/1024 SUM(PGA_MAX_MEM)/1024/1024
--------------------------- --------------------------
                 446.813904                 877.627762

Ejecutamos una consulta en la vista 'v$pgastat'  y nos confirma el dato: apenas .4 gb de memoria en uso. Queda claro que no es un problema de memoria insuficiente en la 'PGA', por lo que en este momento tenemos más dudas que respuestas.

 SQL>   select name, value/1024/1024/1024 from v$pgastat where name = 'total PGA inuse';

NAME                                                             VALUE/1024/1024/1024
---------------------------------------------------------------- --------------------
total PGA inuse                                                            .440420151

Algunas notas en Internet nos indican que 'seguramente' es un problema de parámetros de 'kernel' del sistema operativo 'AIX'. Modificamos los valores a 'unlimited', reiniciamos la instancia de Oracle, ejecutamos nuevamente la consulta y el error ora-04030 ... se sigue presentando!

$ ulimit -a 
time(seconds) unlimited 
file(blocks) unlimited 
data(kbytes) unlimited 
stack(kbytes) unlimited 
memory(kbytes) unlimited 
coredump(blocks) unlimited 
nofiles(descriptors) unlimited 
threads(per process) unlimited 
processes(per user) unlimited 

Otra nota en Internet nos indica que la modificación del siguiente parámetro relacionado a accesos de tipo ´hash' es la solución a nuestro query problemático.  Aplicamos el 'alter system', ejecutamos el query y el error ora-04030 .... continúa ...

alter system set "_gby_hash_aggregation_enabled"=false scope=both;


Verficando el log '/u01/app/oracle/diag/rdbms/DWH/dwh/incident/incdir_120150'  por medio de la utilería 'adrci', nos muestra que, al parecer esta asignando segmentos de 111 MB en él ´HEAP MEMORY'


PRIVATE HEAP SUMMARY DUMP
111 MB total:
   108 MB commented, 594 KB permanent
  2819 KB free (0 KB in empty extents),
      74 MB,   1 heap:    "session heap   "            2188 KB free held
      30 MB,   2 heaps:   "callheap       "            130 KB free held 


Parece ser que Oracle está asignando segmentos de memoria muy pequeños. Según una nota del 'metalink' esto puede ser corregido con el siguiente parámetro del 'init.ora. Aplicamos el cambio, reiniciamos y la instancia ... y el error continúa!

ALTER SYSTEM SET "_use_realfree_heap"=false scope=spfile; 

Se empieza a escuchar entre los usuarios la frase temida por todo DBA .. 'traigan un experto ..'.  En vista del éxito obtenido, se levanta un 'service 'request'. Soporte de Oracle  nos solicita revisar el documento 'Doc ID 1260095.1'  relacionado al siguiente 'bug':

'11gR2/Aix - Dedicated Server Processes Have Large Usla Heap Segments Compared To Older Versions'

La nota de Oracle nos envía directamente al sitio de IBM donde se explica que es un ´bug'  que se presenta únicamente en plataforma 'AIX'. Oracle e IBM tuvieron que trabajar en conjunto para desarrollar una serie de parches, tanto a nivel operativo  como a nivel base de datos para solucionar este problema:

'Many customers have noted increased per-process memory consumption for the "USLA heap" segment with Oracle 11gR2. USLA stands for User-Space Loader Assistant and “heap” is an area of memory used for dynamic memory allocation. To fully resolve this, IBM and Oracle have collaborated to develop, test and release AIX enhancements and Oracle patches that reduce memory use to levels close to those previously experienced. 

 Nos solicitan verificar si tenemos instalados los siguientes parches  o 'APARS' a nivel sistema operativo :

AIX 7.1 TL-01 APAR IV09541, IV28925, IV21116 

El detalle de los parches para AIX se puede validar en las siguientes ligas:

http://www-01.ibm.com/support/docview.wss?uid=isg1IV28925 
http://www-01.ibm.com/support/docview.wss?uid=isg1IV21116 
http://www-01.ibm.com/support/docview.wss?uid=isg1IV09541 

Validando los parches de AIX ,  el comando 'instfix' solo nos muestra el parche 'APAR' IV09541 como instalado. La gente de soporte de sistema operatívo nos asegura que los parches faltantes ya están incluídos por ser un 'release' de AIX más nuevo.

Soporte de Oracle nos solicita instalar el siguiente parche desarrollado exclusivamente para la solución del problema de memoria en AIX:

Patch 10190759

Aplicamos el parche 10190759 con la utilería 'opatch'. Reiniciamos el servidor. Los usuarios empiezan a probar y el error de memoria se presenta de una forma esporádica, por lo que soporte de Oracle nos solicita realiza algunos ajustes en la sentencia de alto consumo de 'PGA'. Actualmente la instancia funciona de forma normal y no se ha vuelto a presentar el error ORA 04030, que se negaba a morir.

Gracias, saludos!

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 CDMX