Memoria PGA frente a espacio de almacenamiento temporal en las bases de datos Oracle

Recientemente he recibido en este blogs sobre programación PL/SQL algunas preguntas referentes a las diferencias entre el tipo de operaciones que la base de datos Oracle realiza en la memoria del área global de programa (PGA - Program Global Area) frente a las que realiza en el espacio del almacenamiento temporal (TEMP space). Primero os comentaré que las versiones antiguas de las bases de datos Oracle solían utilizar los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE para controlar cuanta memoria PGA era posible utilizar antes de empezar a tener que usar el espacio de disco temporal (TEMP).

No obstante en las nuevas versiones de las bases de datos Oracle, la gestión de la memoria PGA se realiza de forma automática y mucho más dinámica.

Supongamos que ejecutamos algún tipo de sentencia SQL y que esta sentencia SQL tiene que realizar algún tipo de operación del tipo:

• Enlazar tablas usando un algoritmo de tipo hash (Hash join).
• Ordenar datos (operación de tipo SORT), por ejemplo cuando se aplican cláusulas como ORDER BY, DISTINCT o GROUP BY.
• Realizar cualquier operación que necesite la utilización de memoria.

En estas situaciones la base de datos Oracle asigna un área de trabajo, ya sea un área HASH, un área SORT o lo que sea, cuyo tamaño será determinado dependiendo de la carga de trabajo del sistema en ese momento concreto. Cuando este área de trabajo se llena y la operación SQL que está teniendo lugar no se ha completado, entonces la base de datos Oracle procede a llevar los datos de la operación en proceso al disco, es decir realiza una operación de escritura al espacio de almacenamiento temporal (TEMP). De tal manera que el espacio TEMP, aún no siendo tal cosa, funcionará como una memoria virtual, como si se tratase de una auténtica memoria PGA. Obviamente, para optimizar el rendimiento de la base de datos Oracle lo mejor es evitar la escrituras en el espacio de disco TEMP.

El objetivo de la gestión automática de la memoria PGA es no utilizar toda la memoria a la vez. La idea es reservar siempre algo de memoria previniendo la incorporación momentánea de usuarios adicionales y evitando situaciones en la que un determinado usuario acapare toda la memoria disponible.

De todas formas los DBA (analistas de bases de datos) pueden elegir entre una gestión manual de la memoria o una gestión automática. Yo personalmente me inclino por utilizar por defecto la gestión automática de la memoria PGA.

Por lo tanto, es posible asignar valores fijos a los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE, el problema es que los valores recomendables para estos parámetros van a variar a lo largo del tiempo, ya que dichos valores no pueden ser idénticos cuando la base de datos Oracle cuenta con, por ejemplo, cinco usuarios activos, frente a unas horas más tarde cuando el número de usuarios activos va a ser de cuatrocientos.

Por eso resulta muy recomendable asignar al parámetro WORKAREA_SIZE_POLICY el valor de AUTO, momento en el cual entra en juego el parámetro PGA_AGGREGATE_TARGET, parámetro que sirve para asignar la máxima cantidad de memoria PGA que la base de datos Oracle debe procurar utilizar. De esta manera, la base de datos Oracle distribuirá dicha memoria entre todas las sesiones activas. Además, desde la versión Oracle 9i Release 2, el paquete de estadísticas disponible a través de la vista de rendimiento dinámico V$, que es accesible desde Oracle Enterprise Manager, nos indica cual debe ser, en nuestro sistema, el valor óptimo del parámetro PGA_AGGREGATE_TARGET para minimizar las operaciones de entrada/salida en el espacio de alojamiento temporal. Esta información nos permitirá cambiar dinámicamente el tamaño de nuestra memoria PGA o tomar la decisión de aumentar el tamaño de la memoria RAM en nuestro servidor para conseguir un rendimiento óptimo de nuestra base de datos Oracle.

Situaciones en las puede ser necesario un uso extensivo de la memoria, por ejemplo, si necesitamos ejecutar diariamente un procedimiento PLSQL que realiza hash joins tremendamente grandes u otro tipo de acciones que requieren utilizar mucha memoria PGA, entonces puede ser recomendable crear un script SQL para ejecutar dicho procedimiento, script que utilizará el comando ALTER SESSION para que dicha sesión pueda hacer uso de todos los recursos de memoria PGA disponibles mediante la inicialización adecuada de los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE.

En conclusión, mi recomendación es hacer uso de la gestión automática de la memoria PGA para las sesiones de usuario y aplicaciones que se ejecutan en el día a día. La gestión manual de la memoria PGA sólo tiene sentido para aquellos procesos con mucha carga de trabajo, que requieren un uso extensivo de la memoria PGA y que se ejecutarán en periodos de tiempo durante los cuales sólo estarán en ejecución este tipo de procesos (generalmente serán trabajos programados que se lanzan en horas con poca o ninguna actividad de usuarios).