Cada nueva versión del optimizador cada vez es más dependiente de las estadísticas, la calidad de ellas puede ser suficiente para una versión anterior pero para la actual no.
Como regla general se ha de analizar una tabla (en casacada, es decir incluyendo índices) cuando se realizan modificaciones importantes sobre ella. Por ejemplo:
- truncates
- Insert into .. select …
- Cualquier inserción, actualización y borrado masivo (se entiendo por masivo que afecte a más de un 20% del total de los registros)
Las estadísticas se pueden obtener de muchas maneras, algunas más rápidas que otras (estimaciones parciales, completas, etc…) y con más o menos generación de redologs.
Por ejemplo si usamos el paquete DBMS_STATS (https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS059) concretamente el subprograma GATHER_TABLE_STATS (https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582), la sintaxis es muy similar para los subprogramas:
- DBMS_STATS.GATHER_DATABASE_STATS
- DBMS_STATS.GATHER_SCHEMA_STATS
Es posible realizar estadísticas de forma rápida y que generen poca actividad en redolog por ejemplo con:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SCOTT', tabname=>'EMP',estimate_percent=> 10, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1', cascade=>TRUE);
END;
/En la llamada anterior solo se computan estadísticas en de columnas indexadas e histogramas de 1 solo bucket (que es lo mismo que no tener histogramas, el optimizador los necesita para funcionar de forma óptima), con una estimación del 10%.
Cualquier consulta que incluya un campo (en las condiciones) de una tabla sin analizar puede ser un desastre respecto a rendimiento, el optimizador no tiene la información del histograma y además el porcentaje de estimación es demasiado bajo (un 10%, un buen valor es un 25% o mejor dejarlo en automático).
Un análisis de mayor calidad sería con una llamada:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SCOTT', tabname=>'EMP',estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', cascade=>TRUE);
END;
/En este caso se analizan todos los campos de la tabla y se deja al servidor la elección del tamaño del histograma (buckets), es más lento porque tiene que analizar más campos y genera como 4 veces más actividad en redolog debido a los histogramas.