08 octubre 2017

Qué hacer cuando tenemos una alerta de aumento de registros espectacular en el tablespace SYSAUX

Este artículo vamos a mostrar diferentes casuísticas que puedes tener cuando tienes un aumento espectacular de ocupación de tablespace SYSAUX.
A partir de Oracle 10g en adelante, hay lo que Yo llamaría un error grave (para Oracle, es una característica 💀), debido a que el tablespace SYSAUX crecerá continuamente. Sólo hay forma de limitar este crecimiento y no está claramente documentado en ninguna parte

¿Para que demonios vale ese tablespace?

El espacio de tablas SYSAUX se instaló como un tablespace auxiliar en el tablespace SYSTEM cuando creó la base de datos, apareció a partir de la versión 10g. Algunos componentes de base de datos que anteriormente creaban y utilizaban espacios de tabla independientes ahora ocupan el espacio de tablas SYSAUX.

Si el espacio de tablas de SYSAUX no está disponible, la funcionalidad de la base de datos central permanecerá operativa. Las funciones de base de datos que utilizan el espacio de tablas SYSAUX podrían fallar o funcionar con capacidad limitada.


Estos componentes pueden utilizar el espacio de tablas SYSAUX y su instalación proporciona los medios para establecer su ocupación del espacio de tablas SYSAUX. Puede controlar los ocupantes del espacio de tablas SYSAUX mediante la vista V $ SYSAUX_OCCUPANTS. Esta vista enumera la siguiente información sobre los ocupantes del espacio de tablas SYSAUX:
  • Nombre del ocupante
  • Descripción del ocupante
  • Nombre del esquema
Configuración básica del tablespace SYSAUX

CASO #1: Uno de los ocupantes de SYSAUX, el optimizador de estadísticas, crece exponencialmente.


Tengo una base de datos de Oracle 11g R2 (11.2.0.1) donde advertí que el tablespace de SYSAUX crecía cada día más y más grande. Busqué en Metalink (perdón, Oracle My Support) y encuentro dos notas:
  • Doc ID 1292724.1
  • Doc ID 552880.1 
que fueron útiles.

Después de ejecutar el script awrinfo.sql, localizado en $ORACLE_HOME/rdbms/admin/, encontré que el consumidor más grande era SM / OPTSTAT en 3 GB que es más grande y no típico en comparación con mis otras bases de datos Oracle 10g R2 y Oracle 11g R1.

Investigando por la documentación encuentro lo siguiente:
"Siempre que se modifiquen las estadísticas del diccionario, las versiones antiguas de estadísticas se guardan automáticamente para futuras restauraciones. Las estadísticas antiguas se depuran automáticamente a intervalos regulares en función de la configuración de retención de historial de estadísticas y de la hora de la recopilación de estadísticas recientes realizada en el sistema. La retención se puede configurar mediante el procedimiento ALTER_STATS_HISTORY_RETENTION. El valor predeterminado es 31 días."
Para ayudar a reducir la cantidad de espacio causada por datos históricos, fijé la retención de las viejas estadísticas a 14 días.

exec dbms_stats.alter_stats_history_retention (14);

Para verificar la nueva configuración, ejecuté la siguiente declaración.

select dbms_stats.get_stats_history_retention from dual;

Procedí a depurar incrementalmente las estadísticas de 31 días a 14 días. Comenzó con 28 días, luego se trasladó a 21 días.

exec dbms_stats.purge_stats (sysdate-28);

Entonces intenté 14 días, sin embargo encontré un error del espacio de la UNDO que requiere que cambie mis incrementos usando 18, 16, y finalmente 14 para terminar la tarea de la purga.

Ahora, para recuperar el espacio utilizado por los objetos mostrados en la sección 4 del informe AWRINFO, usé el comando ALTER TABLE MOVE.

CASO #2: Otra forma de resolver el crecimiento desmesurado del optimizador de estadísticas.

A partir de 10gR1 en adelante, Oracle mantendrá la copia de seguridad de las estadísticas del optimizador automáticamente, incluso si no le pide que lo haga.

Esta información de copia de seguridad se almacena en el tablespace SYSAUX y las tablas implicadas en esto son las siguientes:
  • WRI$_OPTSTAT_OPR                         
  • WRI$_OPTSTAT_AUX_HISTORY                 
  • WRI$_OPTSTAT_TAB_HISTORY                 
  • WRI$_OPTSTAT_IND_HISTORY                 
  • WRI$_OPTSTAT_HISTGRM_HISTORY             
  • WRI$_OPTSTAT_HISTHEAD_HISTORY  

La retención predeterminada de esta información se obtiene con el siguiente comando:

NO se escribirá ningún mensaje en el registro de alertas ni en ningún otro lugar para indicar que no se ha producido la purga. Con cada día que pasa, la probabilidad de falla aumenta y alcanzará una etapa, donde la purga automática nunca será completada por MMON con éxito.

Alternate proporcionado por Oracle para esto es ejecutar, programa DBMS_STATS.PURGE_STATS para purgar esta información de estadísticas. Esto se puede hacer manualmente y es seguro. No hay tiempos muertos involucrados aquí. sólo una terminación manual o un fallo anormal detendrá la purga.

¿Qué más me falta aquí?

A pesar de que publicamos la retención, puede suceder que debido a la falta de purga, vamos a acumular gran cantidad de historial de estadísticas en SYSAUX, haciendo que crezca continuamente.

Como el fallo de purga no está escrito en ninguna parte, podemos perder el hecho de que hay un proceso que consume el espacio con regularidad.

El proceso PURGE_STATS suprimirá estas tablas. Por lo tanto, el espacio libre no se devuelve al espacio de tablas, lo que resulta en una fragmentación de espacio libre dentro de esta tabla.

¿Cómo sé que tengo este problema?

Compruebe su valor de retención actual utilizando la siguiente sentencia
select dbms_stats.get_stats_history_retention from dual;

Vea el historial de estadísticas más antiguo usando la siguiente declaración:

select dbms_stats.get_stats_history_available from dual;
Comparar los dos anteriores y y deberías saber qué hacer

¿Qué debería hacer ahora?

Identifique la retención del sistema tan pronto como sea posible y cambie a un valor apropiado. 30 días es el valor predeterminado. Sin embargo, los valores más pequeños no harían mucho daño.

Deshabilite el Autoextend en SYSAUX tan pronto como sea posible para asegurarse de que el espacio de tablas no crece fuera de límites y, finalmente, se convierte en inmanejable.

Comience a programar el PURGE_STATS tan pronto como sea posible y mantenga el tamaño del espacio de tablas SYSAUX en control.

Realice la reorganización de tablas relacionadas para recuperar el espacio.

¿Por qué debería importarme?

Con cada día que pasa, el tamaño de la tabla crece más grande y más grande, finalmente convirtiéndose en un consumidor superior del espacio. Los esfuerzos que se harán para la limpieza aumentará exponencialmente. HACERLO diariamente en 1hr o hacerlo en 5hr mañana.

El procedimiento PURGE_STATS realiza una supresión de comando simple con una cláusula where específica. Esto deja mucho desorden a medida que el proceso se completa completamente o no hace nada en absoluto. No se engancha a intervalos regulares en este código.

Para una base de datos promedio de 150G, en 6 meses, puede terminar con 100M filas en esta tabla. La limpieza de estos datos es siempre un dolor.

¿Algún consejo?

Identifique la información más antigua que tenga en el historial de estadísticas usando el siguiente comando.

select dbms_stats.get_stats_history_availability from dual;

Si la información es demasiado antigua, comience a purgar la información en trozos más pequeños. recuerde que si cancela el proceso en el medio, usted hará una reversión completa antes de salir.

Utilice el siguiente comando para saber en qué fechas había generación de estadísticas enormes.
select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;

Comienza con la fecha más antigua y se invoca bajo el comando
exec dbms_stats.purge_stats (to_date ('01 -JAN-2010 ',' DD-MON-YYYY '));

Una vez que la cantidad de datos se reduce a un límite de tiempo razonable en las horas punta, como una actividad de mantenimiento regular.

NOTA: Tenga en cuenta que este proceso requiere muchos recursos. No lo hagas durante horas de oficina.


No hay comentarios:

Publicar un comentario

Por favor deja tu comentario, es valioso.