Vamos a contar la historia de como una consulta que se lanza por periodos anuales, pasa de no llegar a ejecutarse a tardar 8 segundos.
Lo primero que vamos a hacer es generar el tkprof. Con esto vamos a poder determinar qué está sucediendo internamente en el tiempo de ejecución. Al ser una consulta un poco especial, no respondía para un año, vamos a generar el tkprof por un mes, es decir, acotar el tiempo para ver si nos da una pista. Este es el resultado.
En este caso la BBDD es 19c, pero el parámetro optimizer_features_enable esta configurado con valor 11.2.0.4
Procedemos a ejecutar la consulta con optimizer_features_enable 19.1.0 para ver cómo se comporta la consulta
¿Cuál es nuestra sorpresa? Que no hay beneficio entre un optimizador en cuanto a tiempos.
Analizando en detalle el tkproff con optimizer_features_enable 19.1.0 vemos una operación nested loops con unos índices altos
Esta operación del row source, es la que más consume, con más de 900.000 bloques leídos en memoria. Otro punto más que nos llama la atención de este cruce es el tiempo.
Si miramos el árbol de ejecución, vemos que ese salto tanto en bloques leídos como en tiempo de ejecución se produce al acceder a la tabla particionada
En este punto, es donde deberíamos atacar para optimizar la consulta.
Corrigiendo la consulta para que se acceda de manera correcta a la partición la consulta a nivel mensual ¡vuela!
Y por último y más beneficioso, es que la consulta anual también termina con un resultados fantásticos, este es el resultado:
