Recolección de estadísticas de alta frecuencia en Oracle 19c+
Manteniendo el optimizador actualizado durante el día.
Introducción
En Oracle 19c aparece una nueva funcionalidad, la recolección de estadísticas de alta frecuencia, diseñada para actualizar las estadísticas que se quedan obsoletas sin esperar a la ejecución de la tarea nocturna diaria mitigando por tanto la influencia de la obsolescencia en los planes de ejecución en entornos con alto DML. Su ejecución es mucho más rápida ya que omite las tareas adicionales de la recolección nocturna.
Funcionamiento
Esta funcionalidad está disponible para Enterprise Edition en Engineered Systems ( Exadata y ODA) y en OCI, no está disponible para instalaciones on‑premise ni Standard Editions.
La recolección de estadísticas de alta frecuencia está desactivada por defecto pero viene con una configuración inicial que en caso de activarse, se ejecutaría cada 15 minutos recalculando las estadísticas que estén obsoletas. Es una tarea ligera (lightweight task) porque no realiza ninguna de las operaciones adicionales que realizan la tarea nocturna (auto optimizer stats collection), como por ejemplo invocar a el Optimizer Statistics Advisor y purgar estadísticas de objetos inexistentes.
Las estadísticas se recolectarán para todos aquellos objetos que las tengan obsoletas, STALE, La High-Frequency Automatic Optimizer Statistics Collection funciona como un disparador ante estadísticas obsoletas, no modifica la forma de recolección que se tuviera ya configurada respetando el bloqueo de estadísticas, el sampling, la configuración de histogramas y de estadísticas incrementales.
Se marcan como STALE aquellas estadísticas cuando el DML acumulado desde la última recoleción supera el umbral definido para el objeto, por defecto un 10%.
Configuración
Se verifica si está desactivada y su configuración con la siguiente query:
SET LINESIZE 300
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
COLUMN task_status FORMAT A15 HEADING 'TASK_STATUS'
COLUMN task_interval FORMAT A20 HEADING 'INTERVAL (segs)'
COLUMN max_run_time FORMAT A20 HEADING 'MAX_RUN_TIME (segs)'
SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') AS task_status, DBMS_STATS.GET_PREFS('AUTO_TASK_INTERVAL') AS task_interval, DBMS_STATS.GET_PREFS('AUTO_TASK_MAX_RUN_TIME') AS max_run_time FROM dual;
SET FEEDBACK ON
Se observa que el valor de TASk_STATUS es OFF, Para activar la recoleccion de estadísticas de alta frecuencia se cambia a ON, en este ejemplo se limitará el tiempo máximo de ejecución a 3 minutos:
BEGIN
-- Habilitar la recolección de estadísticas de alta frecuencia DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
-- Configurar el tiempo máximo de ejecución en segundos, por defecto 3600s
DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','180');
-- Configurar el intervalo de ejecución en segundos, por defecto 900s DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','900');
END;
/
Esta consulta muestra las tablas que tienen las estadísticas obsoletas, son las que se recalcularán :
SELECT t.owner,
t.table_name,
t.partition_name,
t.stale_stats,
NVL(p.preference_value, DBMS_STATS.GET_PREFS('STALE_PERCENT') || ' (GLOBAL)') AS stale_percent,
TO_CHAR(t.last_analyzed, 'DD/MM/YYYY HH24:MI:SS') AS last_analyzed, t.global_stats,
t.user_stats,
NVL(t.stattype_locked, 'NONE') AS stattype_locked
FROM
dba_tab_statistics t, dba_tab_stat_prefs p
WHERE
t.owner = p.owner(+) AND
t.table_name = p.table_name(+) AND
p.preference_name(+) = 'STALE_PERCENT' AND
t.stale_stats = 'YES' AND
t.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'OUTLN')
ORDER BY t.owner, t.table_name, t.partition_name;
Caso práctico
En el punto anterior se ha configurado la ejecución de las estadísticas de alta frecuencia, se comprueba con esta query: chk_auto_task_executions.sql
Se usará una copia de la tabla scott.emp para forzar unas estadísticas obsoletas:
Se insertan los registros de la tabla emp:
Son 14 registros:
Inicialmente no tiene estadísticas:
SELECT table_name, num_rows, last_analyzed,stale_stats
FROM dba_tab_statistics
WHERE owner = 'SCOTT'
AND table_name = 'EMP_2';
Se le calculan explicitamente:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP_2');
Ahora se repite la insercion de registros hasta que se queden las estadísticas obsoletas,
Con 70 registros ya están obsoletas ya que supera la configuración por defecto del 10% de cambios en los registros de la tabla:
Han pasado al menos 15 minutos despues de la insercción de los registros, ya han corrido las las estadísticas de alta frecuencia:
( verificación con chk_auto_task_executions.sql)
Por lo tanto, han sido recalculadas las estadísticas en emp_2:
Conclusión
La recopilación automática de estadísticas de alta frecuencia, es una opción muy interesante en entornos donde existen tablas en las que su tasa de cambios está por encima del definido para que salten las estadísticas.
Es una tarea ligera, diseñada para ejecutarse cada 15 minutos, pero configurable.
No sustituye a las estadísticas nocturnas dentro de la ventana de mantenimiento.
No corren cuando está corriendo la tarea de estadísticas nocturnas.
Permite mantener estadísticas actualizadas sin tener que esperar a la tarea nocturna, mejorando la estabilidad y calidad de los planes de ejecución tras operaciones masivas DML.
Respeta la configuración para el cálculo automático de estadísticas: lass estadísticas bloqueadas, el cálculo de histogramas y las estadísticas incrementales.
Desactivada por defecto.
Disponible solo en Enterprise Edition para Engineered Systems (Exadata y ODA) y OCI.
Debe implementarse de forma controlada, revisando la posible regresión en planes de ejecución y aplicando los mecanismos correctivos necesarios.
Queries útiles y documentación oficial
Detección de estadísticas obsoletas:
Verificación de la ejecución del autotask:
Las referencias a la documentación oficial
Best Practices for Gathering Optimizer Statistics with Oracle Database 19c
Understanding Optimizer Statistics With Oracle Database 19c
Licencias y disponibilidad
Las High-Frequency Statistics, solo están disponibles en Enterprise Edition para Engineered Systems (Exadata, Oracle Database Appliance) y en servicios nativos de Oracle Cloud:
No requiere licencia adicional para ser usada.
Consideraciones
Las consultas que aparecen en el artículo se han elaborado a partir de la documentación oficial de Oracle y se ofrecen como ejemplos y herramientas de apoyo al diagnóstico. Se facilitan para su uso y adaptación, pero su aplicación en entornos productivos debe validarse previamente por el responsable del sistema o un DBA; cualquier modificación que afecte a la producción es responsabilidad del usuario. Para casos críticos o dudas, consulte la documentación oficial de Oracle y/o Oracle Support (MOS). Si detecta errores o desea proponer mejoras, agradeceremos sus informes o contribuciones mediante issues o pull requests.
Los problemas son reales, los datos no.
