EFECTO DEL PARAMETRO PGA_AGGREGATE_TARGET

Manejo de memoria PGA 

La documentación de Oracle, nos muestra que la memoria de la instancia se encuentra dividida en dos áreas: ‘SGA’ (System Global Area) y’ PGA’ (Private Global Area).- En teoría es muy fácil distinguir estos dos tipos de memoria.  La ‘SGA’ se encuentra dedicada a recibir la información proveniente de los ‘datafiles’, entre otros, en un área llamada ‘DATABASE BUFFER CACHE’, que normalmente ocupa el mayor tamaño de la memoria física de la instancia. La memoria ‘PGA’, es una memoria privada, donde se tienen información de las sesiones residentes en la base de datos y también es utilizada para operaciones de ‘sort’ y ‘hash’. Sin embargo, en el momento de realizar un monitoreo de las sentencias ‘SQL’, no es tan fácil distinguir en cuál de estas dos memorias se está ejecutando una consulta. Se va a realizar el ejercicio con los siguientes valores de memoria asignada: Para la memoria ‘SGA’ se tiene un valor máximo de 400 mb y 40 mb para la ‘PGA’, siendo este valor un poco más bajo de lo recomendable.  El objetivo del ejercicio es que el  ‘query’ utilice únicamente memoria ‘PGA’ y el efecto del parámetro PGA_AGGREGATE_TARGET en la asignación de los recursos de memoria:

La siguiente pantalla muestra el valor de la memoria que pretendemos que utilice Oracle, que es la 'PGA'. Incialmente se tienen asignados 40mb por medio del parámetro 'PGA_AGGREGATE_TARGET'

 

Una vez identificado el valor asignado a la memoria ‘PGA’, se va a realizar un ejercicio utilizando las tablas ‘test1’ y ‘test2’. La tabla ‘test1’ tiene un solo campo de tipo númerico llamado ‘campo1’ con 30 millones de registros, con valores únicos. La tabla ‘test2’ es una copia exacta de la tabla ‘test1’. El siguiente ‘query’ va a generar un ‘hash join’ entre las dos tablas. Una vez ejecutado,  deberá realizar un barrido completo de todos los registros de las dos tablas, que en teoría deberán ser alojados en memoria ‘PGA’. El ‘query’ realizó el conteo de los 30 millones de registros por cada tabla. El valor inicial de la memoria PGA de únicamente 40 MB, parece no ser suficiente para alojar los 345 MB que inicialmente requirió el ‘HASH JOIN’, como se muestra en el plan de ejecución:

La siguiente pantalla muestra la memoria ‘SGA’ ocupada. Se  tenían 87.42 MB libres previos a la ejecución del ‘query’. Una vez ejecutado este, la memoria ‘SGA’ disponible disminuyo a 81.37 MB. El ‘query’ ocupo únicamente 6 MB de esta memoria, como lo muestra la vista dinámica ‘v$sgastat’.

Ahora vamos a verificar si realmente se ocupo la memoria ‘PGA’ por medio del ‘HASH JOIN’ del ‘query’. Previo a su ejecución, se habían procesado 20.19 MB de memoria ‘PGA’, como se muestra en la columna ‘bytes processed’ de la vista dinámica ‘V$PGASTAT’

Posterior a la ejecución del query, se tuvo un importante incremento en el consumo de memoria PGA con 828.91 MB !. Esto nos indica que el ‘HASH JOIN’ si ocupo memoria ‘PGA’ y que el valor inicial de 40MB asignados por el parámetro PGA_AGGREGATE_TARGET fue insuficiente. Sin embargo a Oracle no  le  ‘importo’ mucho el valor definido, ya que asigno aproximadamente 800 MB más de los declarados.

Qué efecto tiene en el manejador que el valor asignado a la PGA sea insuficiente?  La consulta fue ejecutada en 3.56 minutos y requirió 235,138 de lecturas a disco.

Vamos a modificar los valores tanto de las memorias ‘SGA’ y ‘PGA’ para verificar si existe una mejora en la ejecución del ‘query’. Se va a incrementar el valor de la memoria ‘SGA’ de 400mb a 1800mb, reiniciando la instancia:

De le misma forma vamos a incrementar la memoria ‘PGA’ (que en este caso es utilizada principalmente por el ‘select count(*) de los 30 millones de registros). Oracle recomienda asignar el 80% de memoria física a la  ‘SGA’ y el 20% restante para la memoria ‘PGA’ en ambientes transaccionales. Para ambientes Datawarehouse se recomienda valores de 70% para la memoria ‘SGA’ y 30 % de memoria para la ‘PGA’. Continuando con el ejercicio, vamos a configurar la memoria ‘PGA’ un poco más alta de lo normal, con 800 mb y 1800 mb asignados a la ‘SGA’.

Una vez incrementada la memoria, se ejecuto nuevamente el ‘query’ con la sentencia ‘select count’ de los 30 millones de registros. La ejecución del ‘query’ observó el siguiente comportamiento (aqui les debo la imagen con el  plan de ejecución :-( ):

1. El tiempo de ejecución disminuyo de 3.56 a 1.50 minutos
2. Las lecturas a disco disminuyeron de 235,138 a 190,220
3. Los 'sorts' en memoria se incrementaron de 4 a 12 lo que ayudo a mejorar el tiempo de ejecución

 Conclusiones

El parámetro 'PGA_AGGREGATE_TARGET' no impone un límite en cuanto a la memoria ´PGA´asignada. Sin embargo, si este se encuentra configurado correctamente, el manejador podrá realizar de forma más eficiente las recuperaciones de disco a memoria. Para asignar un valor correcto a este parámetro, se pueden utilizar como insumo los 'advisors' contenidos en los reportes 'STATSPACK' y 'AWR' y la vista dinámica del sistema 'v$pga_target_advice'.  En la recien liberada versión Oracle 12c se introduce un nuevo parámetro que al parecer va a limitar la memoria 'PGA':  pga_aggregate_limit'.



José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México