# Misterio en el SYSAUX Históricamente el tablespace SYSAUX es un tablespace al que hay que llevar un especial seguimiento debido a crecimientos incontrolados de los objetos que residen es éste. **Recomiendo encarecidamente realizar un seguimiento y control de los ocuppants** si no queremos llevar sorpresas. He realizado una búsqueda rápida como ejemplo en soporte y detallo **una muestra** de las entradas que he encontrado a grosso modo para poder tener una idea de la magnitud de incidir en su seguimiento: * Bug 16851194 - Growth of SYSAUX tablespace with incremental statistics without growth in table data - superseded(Doc ID 16851194.8) * Bug 6338357 - Expired Alerts not deleted - SYSAUX space growth / ORA-1653(Doc ID 6338357.8) * Bug 13901201 - wrh$\_sql\_plan not purged leading to excessive SYSAUX tablespace growth(Doc ID 13901201.8) * Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX(Doc ID 9910484.8) SYSAUX Tablespace can Become Full Due to wrh$\_latch\_children growth(Doc ID 874518.1) * Excessive AWR Growth From Partitioned Objects Such as SYS.wrh$\_event\_histogram Causing Sysaux to Grow(Doc ID 1912201.1) * Automatic Purge Job Fails with ORA-14758 After Dropping p\_permanent Partition Causing SYSAUX Tablespace Growth(Doc ID 1905788.1) * Troubleshooting Issues with SYSAUX Space Usage(Doc ID 1399365.1) * How to Delete Unwanted Incremental Partition Statistics Synopsis Information From wri$\_optstat\_synopsis$ in the SYSAUX Tablespace(Doc ID 1953961.1) * Sysaux Tablespace Size Increases Continuously Due to wrh$\_sql\_plan Table and wrh$\_sql\_plan\_pk Index Growth(Doc ID 1243058.1) * Large Growth of SYSAUX Tablespace with Table wrh$\_sql\_plan Taking Lot of Space(Doc ID 2475149.1) * Abnormal High Space Usage in Sysaux Tablespace - Unable to Purge(Doc ID 1360000.1) SYSAUX * Abnormal Growth with Error 'ORA-03233: unable to extend table SYS.wri$\_optstat\_synopsis$ in tablespace SYSAUX'(Doc ID 2472866.1) * Tips if Your SYSAUX Tablespace Grows Rapidly or Too Large(Doc ID 1292724.1) Can I Truncate Objects in SYSAUX if they are Taking up too much Space?(Doc ID 1928156.1) * How to Purge wrh$\_sql\_plan Table in AWR Repository, Occupying Large Space in SYSAUX Tablespace.(Doc ID 1478615.1) * Large Growth Of wri$*adv\_sqlt\_plans(Doc ID 2205210.1) CTXSYS.sys\_iot\_top*% Object Is Growing Steadily In SYSAUX Tablespace(Doc ID 1642172.1) * Bug 5974572 - wrh$\_service\_stat can grow very large in RAC(Doc ID 5974572.8) * NOTE:2305512.1 - SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor * NOTE:329984.1 - Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER * NOTE:2439129.1 - AUTO\_STATS\_ADVISOR\_TASK Not Purging the Old Date in 12.2 * NOTE:243246.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation. * NOTE:287679.1 - How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace * NOTE:427529.1 - EXTREME NUMBER OF ROWS ARE ACCUMULATED IN WRH$\_SERVICE\_STAT A raíz de una migración, nonCDB 11 a PDB 19c vemos que a la semana el SYSAUX ha aumentado casi 10Gb de espacio. Saltan las alarmas... Agregamos por los occupants: ```sql OCCUPANT_NAME OCCUPANT_DESC MB ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- SM/ADVISOR Server Manageability - Advisor Framework 9072,3125 AUDSYS AUDSYS schema objects 644,625 SQL_MANAGEMENT_BASE SQL Management Base Schema 625,75 SM/OPTSTAT Server Manageability - Optimizer Statistics History 594,1875 PL/SCOPE PL/SQL Identifier Collection 79,6875 SM/OTHER Server Manageability - Other Components 61 XDB XDB 53,875 JOB_SCHEDULER Unified Job Scheduler 18,125 WM Workspace Manager 6,5625 SMON_SCN_TIME Transaction Layer - SCN to TIME mapping 5,4375 SM/AWR Server Manageability - Automatic Workload Repository ,8125 AUTO_TASK Automated Maintenance Tasks ,5625 EM_MONITORING_USER Enterprise Manager Monitoring User ,1875 LOGSTDBY Logical Standby ,125 STREAMS Oracle Streams ,0625 ``` Vemos que el grueso se encuentra en el Server Manageability - Advisor Framework en la tabla WRI$\_ADV\_OBJECTS y sus índices asociados. ```sql SEGMENT_NAME GB -------------------------------------------------------------------------------------------------------------------------------- ---------- WRI$_ADV_OBJECTS_IDX_01 1,81933594 WRI$_ADV_OBJECTS 3,51757813 WRI$_ADV_OBJECTS_PK 1,25683594 WRI$_ADV_OBJECTS_IDX_02 1,50097656 ``` En dicha tabla se almacena la información de los objetos referenciados por todos los advisors de BBDD, y es tabla base de la vista dba\_advisor\_objects, por lo que nos vamos a centrar en analizar a nivel de datos cual, o cuales, son los más relevantes. > `DBA_ADVISOR_OBJECTS` displays information about the objects currently referenced by all advisors in the database. > > **Each row in the view pertains to an object instantiation.** ```sql SELECT TASK_NAME, COUNT(*) FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC; TASK_NAME CNT ----------------------------------- ---------- AUTO_STATS_ADVISOR_TASK 34296267 <<<----- SYS_AUTO_SPM_EVOLVE_TASK 6266 SYS_AUTO_SPCADV513002223122022 100 SYS_AUTO_SPCADV308011425122022 100 SYS_AUTO_SPCADV821041407012023 100 ``` Ya tenemos al culpable. Existe información de 34 millones de objetos relacionados con la tarea de auto optimizer stats collection. En primera instancia vemos una nota en soporte que puede encajar: **SM/ADVISOR "SM/ADVISOR" Taking Most of Space (Doc ID 2692726.1).** **No la doy por válida y genera desconfianza** por los siguientes motivos\*\*:\*\* * Indican: There are lot of executions for the Statistics Advisor task and consumes more space in SYSAUX tablespace. **No es así.** Ejecuciones hay una por día, la información se acumula en el **número de objetos referenciados por la tarea**. ```sql -- Número de ejecuciones totales SELECT e.task_id, COUNT(*) total FROM dba_advisor_executions e WHERE e.task_name = 'AUTO_STATS_ADVISOR_TASK' GROUP BY e.task_id; TASK_ID TOTAL ---------- ---------- 6 108 <<<----- Número de ejecuciones de la tarea select min(execution_start),max(execution_start) from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK'; -- Periodo de fechas de ejecución MIN(EXEC MAX(EXEC -------- -------- 19/12/22 30/05/23 <<<<<<<<<<<<<<<<<<------------------------------- ``` * En dicha nota se indica que la tarea se puede borrar y nos indican como y/o recrearla con otro nombre, pero no nos dan explicación de dicho comportamiento, opción e implicaciones de deshabilitarla o un análisis de las causas. Decido continuar analizando el detalle. Primer comportamiento que detectamos como anómalo es que no se están purgando en base a la retención establecida, la cual es de 30 días por defecto: ```sql SET TRIMSPOOL ON TAB OFF LINES 1000 TERMOUT ON VERIFY OFF PAGES 1000 col TASK_NAME format a25 col parameter_name format a35 col parameter_value format a20 set lines 120 select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE'; TASK_NAME PARAMETER_NAME PARAMETER_VALUE ----------------------------------- ----------------------------------- --------------- AUTO_STATS_ADVISOR_TASK EXECUTION_DAYS_TO_EXPIRE 30 <<<<<<<<<<<<<<<<<<------------------------------- ``` Voy a ver la distribución de datos, a ver si nos da una pista: ```sql SELECT e.task_name, e.execution_start, e.execution_end, e.execution_name, COUNT(*) total FROM dba_advisor_objects o, dba_advisor_executions e WHERE o.owner = e.owner AND o.task_id = e.task_id AND o.execution_name = e.execution_name AND o.task_name = 'AUTO_STATS_ADVISOR_TASK' GROUP BY e.task_name,e.execution_start,e.execution_end,e.execution_name order by 2 desc; TASK_NAME EXECUTION_START EXECUTION_END EXECUTION_NAME TOTAL AUTO_STATS_ADVISOR_TASK 30/05/2023 22:54:52 30/05/2023 23:14:30 EXEC_958 4565889 AUTO_STATS_ADVISOR_TASK 29/05/2023 22:16:59 29/05/2023 22:33:57 EXEC_938 3771938 AUTO_STATS_ADVISOR_TASK 28/05/2023 06:01:05 28/05/2023 06:11:53 EXEC_918 372290 AUTO_STATS_ADVISOR_TASK 27/05/2023 22:06:01 27/05/2023 22:20:09 EXEC_908 1776348 AUTO_STATS_ADVISOR_TASK 26/05/2023 22:27:23 26/05/2023 22:44:18 EXEC_888 4242431 AUTO_STATS_ADVISOR_TASK 25/05/2023 22:09:18 25/05/2023 22:25:26 EXEC_868 2965821 AUTO_STATS_ADVISOR_TASK 24/05/2023 22:46:52 24/05/2023 23:07:59 EXEC_849 1390 AUTO_STATS_ADVISOR_TASK 23/05/2023 22:52:41 23/05/2023 23:11:02 EXEC_838 4099001 AUTO_STATS_ADVISOR_TASK 23/05/2023 01:14:25 23/05/2023 01:41:13 EXEC_818 7919045 AUTO_STATS_ADVISOR_TASK 21/05/2023 09:10:24 21/05/2023 09:29:49 EXEC_788 4375134 AUTO_STATS_ADVISOR_TASK 19/05/2023 22:00:21 19/05/2023 22:04:45 EXEC_767 57 AUTO_STATS_ADVISOR_TASK 18/05/2023 22:16:16 18/05/2023 22:20:38 EXEC_766 59 AUTO_STATS_ADVISOR_TASK 17/05/2023 22:00:46 17/05/2023 22:04:58 EXEC_756 54 AUTO_STATS_ADVISOR_TASK 16/05/2023 23:54:23 16/05/2023 23:58:44 EXEC_765 274 AUTO_STATS_ADVISOR_TASK 15/05/2023 23:08:27 15/05/2023 23:12:04 EXEC_755 270 ``` La causa de no purgarse la información a los 30 días no es la causante del problema original de crecimiento (el número de registros anteriores es anecdótico). Vemos que el aumento ha sido en las últimas semanas, pudiendo discriminarse las anteriores por poco volumen de datos. Revisamos y se debe al bug Bug 34637351  AUTO\_STATS\_ADVISOR\_TASK Data Does Not Purge in PDB. Afecta a versiones inferiores a 23.1.0, por lo que la única opción de contención que tenemos es planificar un purgado manual. Aprovecho además para disminuir la retención. ```sql EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 7); BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'PURGE_AUTO_STATS_ADVISOR_TASK', job_type => 'PLSQL_BLOCK', job_action => 'prvt_advisor.delete_expired_tasks;', repeat_interval => 'FREQ=DAILY;BYHOUR=01'); END; / execute DBMS_SCHEDULER.ENABLE('PURGE_AUTO_STATS_ADVISOR_TASK'); ``` Me centro en el crecimiento, no en el purgado, y veo que no sigue un patrón, aún no encuentro lo que busco. Atendemos a una de las ejecuciones de la tarea con más objetos en la dba\_advisors\_objects, eligiendo la EXEC\_958 al tener un volumen elevado: ```sql select attr2,count(*) from dba_advisor_objects where task_name = 'AUTO_STATS_ADVISOR_TASK' and execution_name='EXEC_958' group by attr2 order by 2 desc; ATTR2 COUNT(*) TBL_EJEMPLO1 1521465 TBL_EJEMPLO2 988950 TBL_EJEMPLO3 684657 TBL_EJEMPLO4 380367 TBL_EJEMPLO5 304293 TBL_EJEMPLO6 152159 TBL_EJEMPLO7 152147 TBL_EJEMPLO8 152147 TBL_EJEMPLO9 152146 TBL_EJEMPL10 76076 TBL_EJEMPL11 17 TBL_EJEMPL12 17 TBL_EJEMPL13 15 ``` Hay 10 tablas son las que más ocurrencias tienen en dba\_advisors\_objects para la task con ID de ejecución EXEC\_958. Focalizamos en una de las tablas, TBL\_EJEMPLO1. En dba\_advisors\_objects tiene más ocurrencias, **distintos object\_id,** cuanto se cumple que ATTR9=20 y ATTR10=26. ```sql OWNER TYPE TYPE_ID TASK_ID TASK_NAME EXECUTION_NAME ATTR1 ATTR2 ATTR3 ATTR5 ATTR6 ATTR7 ATTR8 ATTR9 ATTR10 ATTR11 ATTR16 ATTR17 ATTR18 ADV_SQL_ID COUNT(*) SYS STATS OBJECT 28 6 AUTO_STATS_ADVISOR_TASK EXEC_958 HCIS CEX_ACTIVIDAD TABLE 1 16 22 159 1 SYS STATS OBJECT 28 6 AUTO_STATS_ADVISOR_TASK EXEC_958 HCIS CEX_ACTIVIDAD TABLE 0 20 26 0 1521460 SYS STATS OBJECT 28 6 AUTO_STATS_ADVISOR_TASK EXEC_958 HCIS CEX_ACTIVIDAD TABLE 1 16 21 181 1 SYS STATS OBJECT 28 6 AUTO_STATS_ADVISOR_TASK EXEC_958 HCIS CEX_ACTIVIDAD TABLE 1 16 23 182 1 SYS STATS OBJECT 28 6 AUTO_STATS_ADVISOR_TASK EXEC_958 HCIS CEX_ACTIVIDAD TABLE 1 16 21 183 1 SYS STATS OBJECT 28 6 AUTO_STATS_ADVISOR_TASK EXEC_958 HCIS CEX_ACTIVIDAD TABLE 1 16 23 180 1 ``` Reviso el resto de tablas y se cumple la misma casuística: acumula un gran número de registros, variando únicamente el object\_id, cuanto se cumple que ATTR9=20 y ATTR10=26. ```sql SELECT DISTINCT attr2, count(*) FROM dba_advisor_objects WHERE attr9=20 and attr10=26 and task_name = 'AUTO_STATS_ADVISOR_TASK' group by attr2; attr2 count(*) -------------- ----------- TBL_EJEMPLO1 548848 TBL_EJEMPLO2 297056 TBL_EJEMPLO3 298384 TBL_EJEMPLO4 75409 TBL_EJEMPLO5 75409 TBL_EJEMPLO6 75409 TBL_EJEMPLO7 1521460 TBL_EJEMPLO8 443712 TBL_EJEMPLO9 3563462 TBL_EJEMPLO10 2730506 TBL_EJEMPLO11 98813 TBL_EJEMPLO12 219357 TBL_EJEMPLO13 448784 TBL_EJEMPLO14 1055726 TBL_EJEMPLO15 98813 TBL_EJEMPLO16 573709 TBL_EJEMPLO17 1648536 TBL_EJEMPLO18 2047332 TBL_EJEMPLO19 73952 TBL_EJEMPLO20 76073 TBL_EJEMPLO21 1517384 TBL_EJEMPLO22 98813 ``` Estamos cerca, ya tenemos un patrón. Vemos en la [definición de la vista](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_ADVISOR_OBJECTS.html#GUID-8AA80907-9648-4983-90C2-76EE860D6AFE): ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1685647807354/a9526602-9416-4039-a274-ef92ab962e33.png align="center") > Note: > > The definition of the `ATTRn` columns depends on the advisors that are using the object. For example, the `SQL` object type defines the attribute columns as follows: > > * `ATTR1` contains the SQL ID > > * `ATTR2` contains the SQL address (in the cursor cache) > > * `ATTR4` contains the SQL text > Y siento que doy dos pasos atrás. No aclara ninguna de las dudas. ¿Qué información nos dan esos dos atributos? Intento buscar pistas sobre qué información albergan: ```sql SELECT NAME FROM DBA_SOURCE WHERE upper(text) LIKE '%ATTR9%' AND OWNER='SYS'; NAME -------------------- DBMS_ADVISOR ``` Reviso el código del paquete buscando referencias a dichos atributos: ```sql -- PROCEDURE DBMS_ADVISOR.CREATE_OBJECT -- PURPOSE: Creates a new task object -- ADVISOR SUPPORT: All advisors -- PARAMETERS: -- TASK_NAME -- Name of the task -- OBJECT_TYPE -- Type of Advisor Object being created. -- See dba_advisor_object_types -- ATTR1 -- Attribute of the object -- ATTR2 -- Attribute of the object -- ATTR3 -- Attribute of the object -- ATTR4 -- Attribute of the object -- ATTR5 -- Attribute of the object -- OBJECT_ID -- OUT Param: Generated ID for the object -- ATTR6 -- RAW attribute of the object -- ATTR7 -- Attribute of the object -- ATTR8 -- Attribute of the object -- ATTR9 -- Attribute of the object -- ATTR10 -- Attribute of the object PROCEDURE create_object(task_name IN VARCHAR2 , object_type IN VARCHAR2 , attr1 IN VARCHAR2 := NULL, attr2 IN VARCHAR2 := NULL, attr3 IN VARCHAR2 := NULL, attr4 IN CLOB := NULL, attr5 IN VARCHAR2 := null, object_id OUT NUMBER, attr6 IN RAW := NULL, attr7 IN NUMBER := NULL, attr8 IN NUMBER := NULL, attr9 IN NUMBER := NULL, attr10 IN NUMBER := NULL); ``` Vamos a ponerle cara a dichos atributos... ```sql select * from dba_advisor_object_types where object_type_id in (20,26); 20 DATABASE LATCH 26 STATS TASK ``` Bien, detallamos las conclusiones: * Hasta el momento tenemos un diagnóstico que nos indica que para algunas tablas, en el trabajo de estadística automático, se produce un número elevado de ejecuciones por objeto. * Suponemos que se trata de latches a la hora de ejecutar el proceso al haber un número elevado de llamadas sobre éstas. * Es necesario contener a nivel de PDB la información de los jobs antiguos de autostats planificando un purgado diario. Opto por contener el crecimiento estableciendo job de purgado en la PDB de los obsoletos mientras sigo investigando, estableciendo una una retención menor que la estandar(7 días). No es la mejor solución, pero en este punto no hay una "mejor". Se puede deshabilitar y planificar un trabajo de cálculo de estadísticas manual, por ejemplo. Como nota curiosa y de interés, animo a no suponer que la información de soporte es válida y homogénea siempre. Analizando dicho caso he visto información inconsistente entre notas sobre el mismo problema. La que más me ha llamado la atención ha sido la siguiente: **AUTO\_STATS\_ADVISOR\_TASK Running Outside of Maintenance Window (Doc ID 2387110.1)** En la cual una de las soluciones pasa por borrar el job de recolección automática de estadísticas. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1686354804593/e9a2fdcb-3577-402e-8f5e-19dc46598d3c.png align="center") Seguiré informando si avanzo en éste caso.