"Put away your scripts and let Oracle automatically resize your memory pools."
"Haz a un lado tus scripts, y permite que Oracle reconfigure de forma automática los pools de memoria".
Anteriormente, en versiones menores a 9.2.0.X, un cambio en la estructura de memoria iba acompañado de un bounce de la base de datos.
Ya en 9iR2 lo que solíamos hacer era sobredimensionar el SGA_MAX_SIZE, y aumentar las estructuras de memoria como fueran necesarias. En forma personal, siempre veo esto como mala práctica; quizás se justifica para una base de datos completamente nueva, en la cual se desconoce la carga que vas a tener, y que difícilmente se permitirá hacer un bounce.
Siempre he tenido mis dudas sobre el manejo automático de lo que sea en Oracle (Quedé sorprendido con el buen funcionamiento del UNDO_MANAGEMENT), e incluso recuerdo, que cuando tuve la oportunidad de preguntarle a Ken Jacobs (Dr. Dba) sobre el manejo automático de memoria, no se animó a decir que funcionaba a la perfección, simplemente dijo que detrás de toda esta tecnología, había mucha experiencia de DBAs, y que Oracle estaba haciendo su mejor esfuerzo para facilitar la administración.
De alguna forma estoy de acuerdo, pero es un tanto difícil lograr que las cosas funcionen bien. Doy algunos ejemplos claros.
Si se tiene una aplicación mal diseñada, que no usa bind variables, el sistema estará pidiendo a gritos aumentar el tamaño del shared_pool a costa del buffer_cache.
Otro ejemplo que me ha tocado vivir, es: cuando llega una compañía tipo QUEST o BMC Software ofreciendo productos que ayudan a hacer "Tuning Mágico" eligiendo "n" posibilidades de reescritura de una sentencia SQL. Cada uno de los "n" planes de ejecución se van contra el shared_pool, y de esta forma volvemos a pegarle a los tamaños de las estructuras.
Una carga masiva de información, o recreación de objetos, pudiera incrementar el tamaño del buffer_cache y disminuir alguna otra estructura, etc...
Recientemente en un ambiente productivo, se reportó una lentitud generalizada del sistema, generamos un reporte de AWR en un lapso de una hora y nos encontramos con lo siguiente:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
Event Waits Time (s)
------------------------------ ------------ -----------
CPU time 103,907
cursor: pin S wait on X 1,881,946 35,170
...
El problema generalizado de la base de datos era relacionado al CPU. A nivel sistema operativo, "sar" nos mostraba lo siguiente:
prod$ sar 1 10
%usr %sys %wio %idle
92 8 0 0
95 5 0 0
96 4 0 0
96 4 0 0
97 3 0 0
92 8 0 0
92 8 0 0
94 6 0 0
93 7 0 0
96 4 0 0
94 6 0 0
La cantidad de uso de CPU era muy alta, y a nivel base de datos las esperas más altas eran "cursor: pin S wait on X", ¿pero exáctamente a qué se debía?
Lo primero que se revisó fue Metalink, y se encontró la nota 731233.1 Revisando las operaciones de Resize que se estaban ejecutando, se observó que cada 30 segundos había operaciones similares a las siguientes:
SELECT component,
oper_type ,
final_size ,
TO_CHAR(start_time ,'mm/dd/yyyy') started
FROM v$sga_resize_ops
WHERE status ='COMPLETE'
ORDER BY started DESC,
component;
COMPONENT OPER_TYPE FINAL_SIZE STARTED
--------------------- ------------- ------------ -----------
DEFAULT buffer cache SHRINK 7482638336 11/18/2008
shared pool GROW 3070230528 11/18/2008
DEFAULT buffer cache GROW 7616856064 11/18/2008
shared pool SHRINK 2936012800 11/18/2008
DEFAULT buffer cache SHRINK 7482638336 11/18/2008
shared pool GROW 3070230528 11/18/2008
DEFAULT buffer cache GROW 7616856064 11/18/2008
shared pool SHRINK 2936012800 11/18/2008
DEFAULT buffer cache SHRINK 7482638336 11/18/2008
shared pool GROW 3070230528 11/18/2008
DEFAULT buffer cache GROW 7616856064 11/18/2008
shared pool SHRINK 2936012800 11/18/2008
DEFAULT buffer cache GROW 7482638336 11/18/2008
shared pool SHRINK 3070230528 11/18/2008
DEFAULT buffer cache SHRINK 7331643392 11/18/2008
shared pool GROW 3221225472 11/18/2008
DEFAULT buffer cache SHRINK 7482638336 11/18/2008
shared pool GROW 3070230528 11/18/2008
DEFAULT buffer cache GROW 7616856064 11/18/2008
shared pool SHRINK 2936012800 11/18/2008
Así que ¿qué se puede hacer?, creo que 3 cosas.
1) Deshabilitar por completo el Automatic Memory Management (creo yo que es la mejor opción) SGA_TARGET=0.
2) Dejar valores por default más elevados, es decir, si sabemos que como mínimo requerimos el shared_pool de 900m, incluir en el archivo de inicialización shared_pool=900m
3) Aparentemente aplicar un par de parches (7189722 y 6528336) para evitar los crecimientos y decrementos de estructuras tan frecuentemente (no lo he probado).
El problema con el cliente, en esta ocasión se debió a un mantenimiento programado, la instancia usa un init y no un spfile, dentro del init, el shared_pool estaba definido muy pequeño, el db_cahce_size y otros pools no estaban definidos, y el sga_target en mas de 10GB.
Ahora, debido a que no suelo usar manejo automático de memoria, quizás lo que diga a continuación pudiera no ser válido (simplemente así creo que funcionaría).
Cuando uno tiene un spfile, y manejo automático de memoria, Oracle usa parámetros como:
*.__db_cache_size=58720256
*.__java_pool_size=8388608
*.__large_pool_size=4194304
*.__shared_pool_size=159383552
*.__streams_pool_size=50331648
Estos se pueden ver cuando creamos un pfile a partir de un spfile. Si se reiniciara oracle, por lo menos se tiene un registro de cómo se comportan de forma "optima" las estructuras de memoria en la instancia. Usando los valores con "__" como valores iniciales, y si existen valores predefinidos sin "__" se usan como cota inferior, o valor mínimo.
Debido a que se usa un archivo de init en esta instancia, al hacer el bounce de la base de datos, las estructuras vuelven a su tamaño original (no hay parámetros "__" en el init), y esto nos lleva a una gran cantidad de cambios en las estructuras de memoria.
Cuando el problema se presentó, el shared_pool estaba creciendo cerca de los 3gb, y el db_cache_size muy cerca de los 7gb.
Este tipo de problemas me ha tocado verlos sólo en instancias muy concurridas y con SGAs muy grandes. De manera personal creo que el activar el manejo automático no siempre es la mejor opción, con los advisors activados, podemos darnos cuenta de qué valores son los óptimos, y programar los cambios en estructuras durante periodos de mantenimiento.
2 comentarios:
Hola Hugo,
A mi me queda la duda sobre las operaciones en la SGA, en particular las anotadas en las lineas 12 y 14:
10) shared pool GROW 3070230528 11/18/2008
11) DEFAULT buffer cache GROW 7616856064 11/18/2008
12) shared pool SHRINK 2936012800 11/18/2008
13) DEFAULT buffer cache GROW 7482638336 11/18/2008
14) shared pool SHRINK 3070230528 11/18/2008
Tenemos en la linea 12 un SHRINK hacia un valor menor que en la linea 10,eso esta bien; posteriormente en la linea 14 tenemos un SHRINK(!?) hacia un valor mayor que en la linea 12 ¿Como es eso?
Saludos, Nacho
Hola Nacho, hay veces en que las operaciones no se completan, por lo cual si el valor actual es de 40, y se hace un shrink a 20 y no se completa, la siguente instrucción puede ser un shrink a 30 y es completamente válido.
Publicar un comentario