Resolviendo las limitaciones del Auto Optimizer Stats Collection en Oracle
En el mundo de la administración de bases de datos, las estadísticas son la ruta de acceso que el optimizador (CBO) elegirá para ejecutar una sentencia SQL. Sin ellas, hasta la consulta más simple puede convertirse en una pesadilla de rendimiento. Oracle nos ofrece por defecto el Auto Optimizer Stats Collection, un job diseñado gestionar la recolección de estadísticas de forma integral y desatendida. Sin embargo, en entornos de Data Warehouse con volúmenes masivos de datos esto suele ser insuficiente.
Recientemente, en una de las bases de datos que administro, me enfrenté a un escenario crítico: el mantenimiento nativo de Oracle no lograba concluir dentro de la ventana programada de 4 horas. Esto derivaba en la presencia de estadísticas obsoletas (STALE) y el bloqueo de estadísticas en objetos específicos, provocando una degradación progresiva en el rendimiento global de la base de datos.
El proceso se interrumpía dejando gran parte de los objetos sin procesar.
Las estadísticas quedaban en un estado inconsistente, impidiendo actualizaciones manuales y disparando errores como el
ORA-20005.Sin datos frescos, el optimizador elegía rutas de acceso ineficientes, aumentando drásticamente la latencia global del sistema.
Para romper este ciclo, decidimos tomar el control total mediante una automatización personalizada. Nuestra lógica aplica reglas de ingeniería de datos claras:
- El procedimiento identifica si el esquema contiene tablas particionadas o normales para aplicar el método de recolección correcto.
- Priorizamos la última partición vigente, asegurando que el optimizador (CBO) siempre tenga datos frescos de la carga reciente sin desperdiciar recursos en datos históricos estáticos.
- Implementamos una tabla de control para registrar cada inicio, fin y duración, permitiendo una auditoría que el job nativo no ofrece de forma tan detallada.
Pero antes verificamos el estado actual de la tarea de recolección de estadísticas.
select client_name,status from dba_autotask_client where client_name='auto optimizer stats collection';
Procedemos a desactivar la tarea de recolección automática de Oracle.
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
Verificamos nuevamente el estado actual de la tarea de recolección de estadísticas.
He compartido la solución completa (DDL de bitácora, procedimiento PL/SQL y configuración del Job) en mi repositorio de GitHub para que la comunidad pueda adaptarlo:
Puedes consultar el estado de tus estadísticas y el rendimiento del job mediante la tabla de bitácora:
select * from sys.gbd_bitacora_estat where grupo=5 order by 1,2 asc;
Esta solución fue desarrollada y validada con éxito en un entorno Oracle Database 12c, demostrando una estabilidad total tanto en la gestión de objetos particionados como en la integración con el Oracle Scheduler. Aunque el enfoque es estándar, se recomienda realizar pruebas previas en entornos de desarrollo antes de pasar a producción.
