Siguiendo el post anterior Ejecuciones que se pierden, vamos a continuar con la optimización de la consulta.
Los puntos que vamos a atacar van a ser principalmente intentar reducir la lectura de bloques que se producen en las operaciones nesteed loops, pero para ello tenemos que ver la causa.
Analizando la SQL encontramos clausulas exists, not exists o claulas join no-join como la siguiente:
Este tipo de operaciones impiden al CBO hacer su trabajo. Tenemos que restructurar la consulta para intentar corregir ese tipo de operaciones para que el CBO pueda hacer su trabajo. Restructuramos la consulta y generamos el tkproff de nuevo
Pasamos de una ejecución que leía 4.355.942 bloques a 12.744 bloques donde el número de filas devueltas es el mismo, es decir 147, y lo más importante ¡la consulta vuelva! El resultado es inmediato escribiendo correctamente el SQL.
Comparamos ahora la ejecución anual, a ver si mejoramos los tiempos
¡la noche al día! Casi 400.000.000 millones de bloques leídos frente a un 1.800.000 bloques leídos con la optimización, pasando de una ejecución de 24 minutos a 18 segundos.
Conclusión, no siempre el problema de tiempos es debido a la base de datos, sino que a veces el problema es el uso del propio lenguaje SQL, el uso incorrecto del SQL en este caso ha jugado malas pasadas, donde las malas practicas impedían al CBO hacer su trabajo.