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:
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.
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_OBJECTSdisplays information about the objects currently referenced by all advisors in the database.Each row in the view pertains to an object instantiation.
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.
-- 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:
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:
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.
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:
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.
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.
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:
Note:
The definition of the
ATTRncolumns depends on the advisors that are using the object. For example, theSQLobject type defines the attribute columns as follows:
ATTR1contains the SQL ID
ATTR2contains the SQL address (in the cursor cache)
ATTR4contains 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:
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:
-- 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...
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.
Seguiré informando si avanzo en éste caso.