Cómo habilitar trazas en el autostats job de Oracle

En ocasiones, necesitamos analizar con detalle el comportamiento del autostats job en Oracle, especialmente cuando detectamos problemas en la recogida de estadísticas o un comportamiento anómalo en la ejecución del Automatic Statistics Gathering.
Oracle ofrece la posibilidad de habilitar trazas a nivel de DBMS_STATS para entender qué está ocurriendo internamente durante estos procesos.

A continuación se muestra el procedimiento completo para activar y revisar las trazas del autostats job de manera controlada y segura.

---

1. Crear la tabla de log temporal

Primero, creamos una tabla en el esquema SYS que almacenará la información de trazas.
La estructura se basa en la tabla interna SYS.STATS_TARGET$:

create table sys.stats_target$_log
as
select t.*, rpad('X',30,'X') session_id, 1 state
from sys.stats_target$ t
where 1=0;

Esta tabla se crea vacía (where 1=0) y contendrá los registros generados por el proceso de autostats durante la traza.
El campo session_id se rellena con una cadena de prueba (rpad('X',30,'X')) y el campo state con valor 1 para simular la estructura de datos.

---

2. Habilitar la traza para DBMS_STATS

A continuación, habilitamos el tracing a nivel global.
El parámetro 'trace' de DBMS_STATS.SET_GLOBAL_PREFS permite activar diferentes niveles de detalle mediante la suma de bits.
En este caso, se activan múltiples niveles para obtener información completa, incluyendo la del autostats job:

exec dbms_stats.set_GLOBAL_PREFS('trace', 4+8+16+32+128+512+1024+2048+32768);

Esto incluye trazas para:

• Operaciones internas de DBMS_STATS

• Ejecución del autostats scheduler job

• Seguimiento detallado de las decisiones de estadísticas sobre objetos

• Procesamiento de targets y resultados de cálculo

---

3. Permitir la ejecución del autostats job

Una vez activada la traza, dejamos que el job de autostats se ejecute normalmente.
Es importante tener en cuenta que no es necesario ejecutar el job desde la misma sesión en la que se activó la traza.
El tracing se aplica de forma global mientras el parámetro esté activo.

---

4. Desactivar la traza

Cuando hayamos recopilado suficiente información, debemos desactivar las trazas inmediatamente para evitar un consumo excesivo de recursos o un log innecesariamente grande:

exec DBMS_STATS.SET_GLOBAL_PREFS('trace',0);

Y finalmente, eliminamos la tabla temporal que creamos al inicio:

drop table sys.stats_target$_log;

---

5. Localizar los archivos de traza

Podemos buscar los archivos generados por DBMS_STATS en el sistema de archivos con un comando similar a:

find /ruta_trace -type f | xargs grep 'DBMS_STATS'

Esto nos ayudará a identificar los ficheros de traza donde Oracle ha registrado la información de depuración.
Generalmente, los archivos se encuentran bajo el directorio de diagnóstico (ADR) del Oracle Base.

---

6. Analizar las tareas más lentas del autostats job

Además de las trazas, es posible identificar qué fases o tareas dentro del autostats job consumen más tiempo utilizando las vistas DBA_OPTSTAT_OPERATIONS y DBA_OPTSTAT_OPERATION_TASKS.
La siguiente consulta permite desglosar el detalle de cada tarea, su duración y tipo de objeto afectado:

SELECT 
    ta.opid,
    ta.TARGET_TYPE,
    ta.TARGET,
    ta.STATUS,
    TO_CHAR(ta.START_TIME, 'YYYY-MM-DD HH24:MI:SS') AS START_TIME,
    TO_CHAR(ta.END_TIME, 'YYYY-MM-DD HH24:MI:SS') AS END_TIME,
    ROUND((CAST(ta.END_TIME AS DATE) - CAST(ta.START_TIME AS DATE)) * 24 * 60, 2) AS DURATION_MINUTES,
    ta.ESTIMATED_COST,
    ta.notes
FROM 
    DBA_OPTSTAT_OPERATION_TASKS ta,
    DBA_OPTSTAT_OPERATIONS op
WHERE
    ta.start_time > (SYSDATE - 15)
    AND ta.opid = op.id
    AND op.operation = 'gather_database_stats (auto)'
ORDER BY 
    ta.START_TIME DESC;

Con esta consulta podemos identificar:

• Qué objetos están tardando más en el proceso de gather stats.

• Cuánto tiempo dura cada tarea.

• El tipo de objeto (tabla, índice, esquema, etc.).

• El coste estimado y notas del optimizador.

Esto resulta especialmente útil para detectar cuellos de botella o objetos que bloquean la ejecución del autostats job.

---

7. Recomendaciones

• Realiza este procedimiento solo bajo supervisión o en entornos controlados si se trata de producción.

• Desactiva siempre el tracing al finalizar para evitar logs innecesarios.

• Conserva los archivos de traza para su análisis conjunto con Oracle Support si estás trabajando en un SR.

• Usa la consulta de tareas para priorizar el ajuste de estadísticas en los objetos más costosos.

---

Conclusión

El uso del tracing en DBMS_STATS combinado con el análisis de las vistas de operaciones permite comprender en profundidad el comportamiento del autostats job en Oracle.
Gracias a estas herramientas, puedes detectar las fases más lentas del proceso, identificar qué objetos requieren atención y mejorar el rendimiento del mantenimiento de estadísticas en entornos críticos.