Cuando apareció Oracle 11gR1 se introdujo una mejora que permite reducir drásticamente el tiempo que toma calcular las estadísticas de las tablas particionadas: las estadísticas incrementales.
Con cada nueva versión de Oracle Server se ha ido mejorando esta funcionalidad, pero por temas de compatibilidad no se aplican por defecto a las tablas que ya teníamos configuradas con anterioridad a cada upgrade, por lo que es probable que actualmente tengas tablas que hacen uso de todo su potencial y también algunas que no.
¿Será que es tu caso? Sigue leyendo y averígualo!
Los requisitos
En primer lugar, para poder hacer uso de estadísticas incrementales, se deben fijar algunos parámetros para la tabla particionada:
| Propiedad | Valor |
|---|---|
| incremental | true |
| publish | true |
| estimate_percent | dbms_stats.auto_sample_size |
| granularity | auto |
La magia
En lugar de tener que procesar todas las particiones para el cálculo de estadísticas, ahora se calculan las estadísticas por cada partición, y estas son agregadas para poder tener las estadísticas globales de la tabla.
Para lograrlo se hace uso de un nuevo elemento: SYNOPSIS, que viene a ser un resumen de los datos estadísticos de cada partición, como por ejemplo el número de valores distintos de las columnas (NDV).
Estos datos son almacenados en 2 tablas: wri$_optstat_synopsis_head$ y wri$_optstat_synopsis$.
El problema
Si bien todo funciona bien tal y como está, el algoritmo inicial—llamado ADAPTIVE SAMPLING—tiene como efecto secundario que se registren millones de filas en la tabla wri$_optstat_synopsis$, pudiendo llegar a ser el primer consumidor de espacio en el tablespace SYSAUX.
Dependiendo de la cantidad de particiones, el número de filas, el número de columnas, etc., esta tabla puede llegar a ocupar decenas y hasta cientos de gigas!
SQL> @?/rdbms/admin/awrinfo.sql
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT SYS 25,317.4 MB <<<-------
| SM/AWR SYS 420.1 MB
| SM/ADVISOR SYS 351.2 MBLa solución
Es con Oracle 12.2 que Oracle decide introducir un nuevo algoritmo: HYPERLOGLOG, el cual ofrece un menor tiempo para el procesamiento de SYNOPSES, pero lo que es mejor aún: ya no es necesario usar la tabla wri$_optstat_synopsis$, y con ello todos los millones de filas y decenas de gigas para almacenarlos.
Este nuevo algoritmo solo se usará con nuevas implementaciones de estadísticas incrementales para tablas particionadas. Todas las tablas que ya tenías configuradas hasta antes de hacer upgrade a Oracle 12.2 seguirán usando el algoritmo anterior, con todas sus desventajas.
Lo más probable es que quieras empezar a usar el nuevo algoritmo ahora mismo, y resulta que no es nada complicado, pero va a requerir de un recálculo de las estadísticas, y eso ya implica tiempo y uso de recursos, por lo que es mejor una buena planificación.
En base a mi experiencia, te recomiendo seguir estos pasos y lo lograrás con el menor impacto posible:
- Si no tienes problemas en recalcular las estadísticas de golpe para toda la tabla y sus particiones:
Fijar propiedades
BEGIN
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'APPROXIMATE_NDV_ALGORITHM',
pvalue => 'HYPERLOGLOG'
);
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'INCREMENTAL_STALENESS',
pvalue => 'NULL'
);
END;
/
1
2
Borrar synopses
BEGIN
DBMS_STATS.delete_table_stats(
ownname => 'SH',
tabname => 'SALES',
stat_category => 'SYNOPSES'
);
END;
/Recalcular estadísticas
BEGIN
DBMS_STATS.gather_table_stats (
ownname => 'SH',
tabname => 'SALES'
);
END;
/
3
- Si lo tuyo es ir con calma e ir recalculando poco a poco:
Fijar propiedades
BEGIN
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'APPROXIMATE_NDV_ALGORITHM',
pvalue => 'HYPERLOGLOG'
);
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'INCREMENTAL_STALENESS',
pvalue => 'ALLOW_MIXED_FORMAT'
);
END;
/
ALLOW_MIDEX_FORMAT permite que existan, de forma simultánea, particiones con SYNAPSES creadas con el algoritmo ADAPTIVE SAMPLING y otras creadas con el algoritmo HYPERLOGLOG.
1
2
Recalcular partición por partición
BEGIN
FOR part IN ( SELECT partition_name
FROM dba_tab_partitions
WHERE table_owner = 'SH'
AND table_name = 'SALES'
ORDER BY
partition_name )
LOOP
DBMS_STATS.delete_table_stats(
ownname => 'SH',
tabname => 'SALES',
partname => part.partition_name,
stat_category => 'SYNOPSES'
);
DBMS_STATS.gather_table_stats(
ownname => 'SH',
tabname => 'SALES',
partname => part.partition_name,
granularity => 'PARTITION'
);
END LOOP;
END
/Acá ejecuto el recálculo partición tras partición, pero bien se podría procesar un grupo de particiones hoy, otro mañana y así sucesivamente, hasta que no quede ninguna sin recalcular.
Recalcular estadísticas globales
BEGIN
DBMS_STATS.gather_table_stats (
ownname => 'SH',
tabname => 'SALES'
);
END;
/
3
Conclusiones
La introducción de estadísticas incrementales para las tablas particionadas nos ha permitido tener las estadísticas actualizadas con un bajo consumo de recursos.
Con el tiempo se han ido presentando mejoras en los algoritmos usados para las estadísticas incrementales, que por defecto se aplican a los nuevos objetos.
No pierdas tiempo y verifica la situación de tus tablas particionadas, y de ser el caso ve planificando el recálculo de sus estadísticas con el nuevo algoritmo HYPERLOGLOG, y nos comentas como te fue.
Para complementar lo aquí expuesto, te recomiendo la lectura de las notas:
| 1417133.1 | How To Collect Statistics On Partitioned Table |
| 814710.1 | SYSAUX Tablespace Fills Rapidly due to Increased WRI$_OPTSTAT_SYNOPSIS$ Size |
| 2916829.1 | How to rebuild Incremental Statistics with HYPERLOG |
Una respuesta
Información muy útil. Gracias por compartir!.