Con este espacio pretendo crear un rincón de información relacionda con las bases de datos Oracle, sus funcionalidades, características, noticias.... etc. Interaré hacer pequeños artículos con ejemplos demostrativos para que resulte más fácil leerlos. No dudéis en mandar vuestros comentarios, críticas, aclaraciones... etc.

Información del Autor

Llevo más de diez años trabajando como DBA Oracle en diferentes compañías y desde el 2004 lo hago de forma freelance a través de una sociedad creada por mí, Infor Consult Soluciones (http://www.inforconsult.es). Si tienes cualquier duda, comentario o propuesta que hacerme, no lo dudes.

viernes, 23 de noviembre de 2007

Gestión automática de la memoria PGA

¿Qué es la PGA?
Las siglas provienen de Program/Private Global Area, y es la memoria privada de cada proceso servidor. En esta memoria cada proceso almacena información que sólo es necesaria para su propio funcionamiento como por ejemplo sus variables globales, el estado actual de cada cursor (SQL) que se ejecuta... etc.

La PGA se compone de:
  • Área SQL privada: cada SQL que se ejecuta necesita de este espacio para poder llevar el control de las operaciones propias de la sentencia. Se asigna cuando se abre el cursor y se libera completamente cuando se cierra. Esta parte de memoria se subdivide en dos: a) area persistente: perdura durante toda la vida del cursor. Guarda las bind variables además de otras cosas; b) area en tiempo de ejecución: se libera cuando finaliza la ejecución de la sentencia SQL (aunque no se haya cerrado el cursor ). Constituyen las áreas de trabajo (working areas) que se explican más adelante. El número máximo de cursores, y por tanto, el número máximo de áreas SQL privadas, que un usuario puede tener abiertos al mismo tiempo se controla con el parámetro OPEN_CURSORS. También hay que tener en cuenta que esta área SQL privada se almacena en la PGA si la Instancia está configurada como servidores dedicados (dedicated servers). En caso de servidors compartidos (shared servers) se almacena en la SGA.
  • Memoria de las sesiones: guarda información relativa a la sesión como el login, variables de sesión... etc. En servidores compartidos (shared servers) este área pasa a ser pública ya que diferentes usuarios comparten los mismos procesos servidores.
¿Qué son las áreas de trabajo (working areas)?
Las área de trabajo (working areas) son el espacio usado por el proceso servidor para realizar ordenaciones y clasificaciones como los ORDER BY, GROUP BY o HASH JOIN. Esta área es la de mayor tamaño y forma parte del área en tiempo de ejecución.

Su tamaño es determinante para obtener buenos tiempos de respuesta en operaciones de este tipo, ya que si el espacio se queda pequeño para realizar estas tareas es necesario liberar espacio usando el tablespace temporal del usuario. Ésta actividad de swapping afecta drásticamente al rendimiento y recibe el nombre de "extra pass".

¿Dónde se almacena la PGA?
La PGA se almacena en la memoria del servidor pero fuera de la SGA. Por tanto, para dimensionar ambas áreas hay que tener en cuenta que la suma de las dos no supere los límites específicos del servidor y la plataforma (tamaño máximo memoria RAM, 2 GB en Windows sin /3GB, 3 GB en Windows con /3GB... etc.).

¿El tamaño de la PGA es configurable?
Sí, pero únicamente es configurable el tamaño de las áreas de trabajo. Desde Oracle9i el DBA puede fijar el tamaño máximo del cónjunto de todas las PGAs de la Instancia a través del parámetro PGA_AGGREGATE_TARGET. De esta forma, Oracle dinámicamente gestiona el tamaño de cada área de trabajo para que la suma de todas las PGAs (no de todas las áreas, que es un parte de la PGA como ya se ha indicado) no exceda del límite impuesto. En versiones anteriores la forma de controlar el tamaño de cada área de trabajo era a través de los parámetros SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE y CREATE_BITMAP_AREA_SIZE.

Para que la Instancia tenga el cuenta el parámetro PGA_AGGREGATE_TARGET, el parámetro WORKAREA_SIZE_POLICY se debe asignar a AUTO (por omisión). Con MANUAL, se indica que se trabaja de la forma anterior y se fija cada parámetro *_AREA_SIZE por separado.

¿Cuándo se crea la PGA?
La crea el Listener durante la creación del proceso servidor. Si esta memoria no puede ser asignada o reservada la creación del proceso servidor falla y el Listener devuelve un ORA-12500: TNS:listener failed to start a dedicated server process. Igualmente, si durante la ejecución de alguna sentencia es necesario incrementar el tamaño de la PGA y el sistema operativo no tiene más memoria para darle se devuelve un error indicándolo.

¿Cuándo se destruye la PGA?
Al cerrar el proceso servidor su PGA asociada se libera y se devuelve al sistema operativo el espacio reservado. El proceso servidor se cierra cuando el usuario ejecuta EXIT o DISCONNECT, lanza un Ctrl+C, se pierde la comunicación con el servidor, se produce un ORA-600 que desconecta la sesión, el DBA lanza un KILL SESSION, etc.

¿Cuánto ocupa cada PGA?
En cada versión de Oracle, e incluso en cada release, el tamaño mínimo cambia. No obstante, como el tamaño es cambiante en función del área de trabajo, lo mejor que se puede hacer para comprobar cuánto ocupa o cómo ocupa es monitorizarlas.

¿Cómo se monitoriza la PGA?
Hay diferentes vistas para saber cuánto ocupa cada PGA individual o para saber cuánto ocupan todas las PGAs o incluso para saber si está bien configurado el parámetro PGA_AGGREGGATE_TARGET.

Con V$PROCESS se puede saber el tamaño de la PGA de cada proceso de la Instancia (el proceso puede ser background o foreground -también llamado shadow-). Si es un proceso background se sabé rápidamente qué proceso es porque lleva el nombre del proceso en la columna PROGRAM. Si es un proceso shadow se sabe el usuario que hay detrás cruzando las columnas ADDR.V$PROCESS con PADDR.V$SESSION. Veamos un ejemplo:

SQL> select sid, serial#, username, command, status, machine from v$session where paddr='BE103FA4';

SID SERIAL# USERNAME COMMAND STATUS MACHINE
---- ---------- -------- ------- -------- -------
1386 9 TEST_USR 0 INACTIVE SATURNO


Para obtener unas estadísticas de la PGA se puede usar la vista V$PGASTAT:

SQL> select * from v$pgastat;

NAME VALUE UNIT
---------------------------------------- ---------- -------
aggregate PGA target parameter 256901120 bytes
aggregate PGA auto target 58300416 bytes
global memory bound 51380224 bytes
total PGA inuse 192188416 bytes
total PGA allocated 283714560 bytes
maximum PGA allocated 548391936 bytes
total freeable PGA memory 0 bytes
process count 343
max processes count 404
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 78736384 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 530432 bytes
over allocation count 15599
bytes processed 8,1983E+11 bytes
extra bytes read/written 1,4539E+10 bytes
cache hit percentage 98,25 percent
recompute count (total) 208640


Aggregate PGA target parameter indica el valor del parámetro PGA_AGGREGGATE_TARGET.
Aggregate PGA auto target indica la cantidad de PGA que Oracle puede usar como áreas de trabajo. Este valor varía en función de la carga de trabajo. Total PGA allocated indica el valor total de PGA asignado. Este es el valor que Oracle intenta mantener por debajo de PGA_AGGREGGATE_TARGET. Este valor puede ser más alto que el indicado por el parámetro de inicialización en momentos puntuales de mucha carga o de forma constante si PGA_AGGREGGATE_TARGET está pobremente dimensionado. Over allocation count indica el número de veces que ha habido que sobrepasar el límite impuesto por PGA_AGGREGGATE_TARGET, lo que es síntoma de que hay que aumentar el parámetro.
Cache hit percentage indica el número de veces que se ha podido realizar una operación intensiva de ordenación/clasificación sin incurrir en un "extra pass". Si este valor no es cercano al 100% indica que hay que aumentar el parámetro PGA_AGGREGGATE_TARGET.

La vista V$PGA_TARGET_ADVICE es muy útil para predecir cómo cambiaría el comportamiento de la Instancia si modificamos le valor de PGA_AGGREGATE_TARGET. Para que esta vista recopile información el parámetro STATISTICS_LEVEL debe estar configurado a TYPICAL o ALL:

SQL> select pga_target_for_estimate, pga_target_factor,
estd_pga_cache_hit_percentage, estd_overalloc_count
from v$pga_target_advice;

PGA_TGT_FE PGA_TGT_F ESTD_HIT_PCTGE ESTD_OVRALL_COUNT
---------- --------- -------------- -----------------
32112640   ,125      67             67303
64225280   ,25       67             67303
128450560  ,5        67             66960
192675840  ,75       74             40785
256901120  1         97             4652
308281344  1,2       98             73
359661568  1,4       98             0
411041792  1,6       98             0
462422016  1,8       98             0
513802240  2         98             0
770703360  3         98             0
1027604480 4         100            0
1541406720 6         100            0
2055208960 8         100            0

La columna pga_target_for_estimated indica la estimación para las que las demás columnas están dando información. La columna pga_target_factor indica la relación entre el valor de PGA_AGGREGGATE_TARGET actual y el valor de estimación de esa fila. Cuando este factor está a 1 indica la relación 1 a 1 -el tamaño actual-. Si es 2, es 1 a 2 -el doble-, y si es 0,125 es 1 a 0,125 -una octava parte-. La columna estd_pga_cache_hit_percentage indica el porcentage de éxito, operación resueltas sin "extra pass" para ese pga_target_for_estimated. Por último, la columna estd_overalloc_count indica el número de veces que habría que sobrepasar el valor indicado por PGA_AGGREGGATE_TARGET para ese pga_target_for_estimated.

Para este caso el valor óptimo sería subir el valor del parámetro 1,4 veces porque nos da una estimación de 0 "extra passes". Seguir aumentándolo no aportaría ninguna mejora. Si estamos justos de memoria y 73 "extra passes" son asumbles se puede empezar aumentándolo 1,2 veces en vez 1,4 y después de un tiempo de trabajo volver a chequear los valores.

Por último decir que las vistas V$PGASTAT y V$PGA_TARGET_ADVICE guardan información de la Instancia en tiempo real. Cuando se reinicia la Instancia estas vistas empiezan a recopilar datos nuevos. Para ver el histórica entre reinicios están las vistas DBA_HIST_PGASTAT y DBA_HIST_PGA_TARGET_ADVICE respectivamente.

Espero que te haya sido útil. Por favor, deja tus comentarios, correciones o sugerencias.

Juan Lorenzo Arellano
Oracle DBA
Infor Consult Soluciones
www.inforconsult.es

8 comentarios:

Anónimo dijo...

Fenomenal, claro y conciso.

Gracias

Anónimo dijo...

Muy bueno.

Ysi Chas dijo...

Magnífico!
ya había leido rápidamente porque necesitaba algo específico, y me ayudó mucho. El día de hoy lo leí todo y me pareció muy completo y de mucha ayuda!

Unknown dijo...

perfecto tio

Unknown dijo...

Muy buen artículo. Una pregunta? yo podría configurar diferentes tamaños de PGA en una misma instancias y permitir que un grupo de usuarios tome una de ellas y otro grupo otra.

Anónimo dijo...

Un post genial. Muy útil. Gracias.

Peter dijo...

Gracias por publicar y compartir. Es la primera vez que me sucede que no puedo hacer que llegue al 100% de aciertos.

Por qué no puedo alcanzar el 100% de aciertos (HIT_PORCENTAGE), en carga grande baja hasta 75%
Oracle 11G
Memoria RAM total servidor 80Gb
sga_max_size 16G
sga_target 16G
PGA_AGGREGATE_TARGET 32G
VALORES EN MB
PGA_ESTIMADO FACTOR BYTES_PROCESS ESTD_TIME RAM_ADICIONAL HIT_PORCENTAGE
------------ ---------- ------------- ---------- ------------- --------------
4096 .125 166081.615 80916 8750.36816 95
8192 .25 166081.615 80596 8057.34766 95
16384 .5 166081.615 80596 8057.34766 95
24576 .75 166081.615 80596 8057.34766 95
32768 1 166081.615 79865 6478.94629 96
39321.5996 1.2 166081.615 79865 6478.94629 96
45875.1992 1.4 166081.615 79865 6478.94629 96
52428.7998 1.6 166081.615 79865 6478.94629 96
58982.3994 1.8 166081.615 79865 6478.94629 96
65536 2 166081.615 79865 6478.94629 96
98304 3 166081.615 79865 6478.94629 96
131072 4 166081.615 79865 6478.94629 96
196608 6 166081.615 79865 6478.94629 96
262144 8 166081.615 79865 6478.94629 96

Peter dijo...

aunque adicione mas memoria no consigo llegar al 100% de aciertos

NAME VALUE UNIT
---------------------------------------------------------------- ---------- -----
aggregate PGA target parameter 3.4360E+10 bytes
aggregate PGA auto target 3.0661E+10 bytes
global memory bound 1073741824 bytes
total PGA inuse 291690496 bytes
total PGA allocated 538419200 bytes
maximum PGA allocated 2676378624 bytes
total freeable PGA memory 39256064 bytes
process count 93
max processes count 123
PGA memory freed back to OS 2.3577E+11 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 2035450880 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 1.7611E+11 bytes
extra bytes read/written 1.0078E+10 bytes
cache hit percentage 94.58 percent
recompute count (total) 42098