Anuncios en tutorial de programación PLSQL

viernes, 24 de noviembre de 2017

Tuning o puesta a punto de consultas SELECT COUNT(*) en PL/SQL

Tuning o puesta a punto de consultas SELECT en PL/SQLDe vez en cuando recibo consultas sobre cómo sería posible mejorar el rendimiento de sentencias PL/SQL concretas. En la mayoría de los casos contestar a estas preguntas puede ser poco menos que imposible, más que nada porque realizar el tuning de una consulta PL/SQL sin conocer el contexto en que se ejecuta dicha consulta resulta muy complicado. Cada vez que esto ocurre siempre me asaltan preguntas como: ¿por qué se ejecuta dicha consulta?, ¿puede eliminarse la consulta y ser incluida en otro proceso?, ¿está la consulta dentro de un bucle LOOP y realmente debe formar parte del bucle?, ¿están creados todos los índices que podrían acelerar su ejecución? Por si esto fuera poco, una vez que tenemos la respuesta a preguntas como las antes mencionadas, sin duda, surgirán nuevas preguntas.

No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.

La consulta SQL era la siguiente:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id(+)
AND  t2.otro_id = v_otro_id;

Lo primero que se me vino a la mente es que había muchas posibilidades de que realmente no hiciera falta ejecutar dicha sentencia, y no existe ninguna posibilidad de ejecutar de forma más rápida una sentencia SELECT que no tener que ejecutarla. El caso es que siempre que me encuentro una cláusula COUNT(*) en una consulta SQL, tiendo a intentar eliminarla. La razón es que en la mayoría de los casos el procedimiento PL/SQL que la contiene, utiliza dicho COUNT(*) para hacer lo siguiente:

SELECT COUNT(*)
INTO   v_count
FROM   ...;

IF v_count > 0 THEN
ejecutar_algo();
ENF IF;

En mi opinión, en estos casos lo mejor para el rendimiento de la base de datos Oracle es reescribir el código antes mencionado y reemplazarlo por un código tan simple como este:

ejecutar_algo();

Si el procedimiento PLSQL ejecutar_algo() lo que hace es analizar un conjunto de resultados dentro de un bucle, terminando cuando dicho conjunto ha sido analizado en su totalidad, entonces llamar a dicho procedimiento PLSQL cuando no hay datos para procesar, significará que el conjunto de resultados inicial estará vacío y la rutina ejecutar_algo() no hará nada. Por otro lado, si existen datos para procesar, entonces el procedimiento PL/SQL se ejecutará más rápido porque habremos eliminado la primera consulta SELECT realmente innecesaria. Y si lo pensamos detenidamente, es más que probable que incluso no habiendo datos para procesar, también el rendimiento de nuestro proceso mejore.

De cualquier forma, si asumimos que por cualquier motivo no hay forma de eliminar la consulta SELECT y que ésta tiene que ser realmente ejecutada, entonces deberemos pensar en qué podemos hacer para ponerla a punto o "tunearla". En el caso que os he planteado, algo que me resultó evidente nada más ver la consulta fue que el outer join podía eliminarse sin problemas, no en vano la primera consulta SQL es equivalente a esta otra:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id
AND  t2.otro_id = v_otro_id;

El outer join desde la tabla t1 a la t2 lo que hace es devolver resultados de la tabla t1 cuando no existe ninguno asociado por el mismo id en la t2, pero en este caso el campo t2.otro_id será NULL, y el valor NULL nunca es igual a nada, por lo que el último límite de la consulta nunca se cumplirá cuando la tabla t2 no devuelve ningún registro asociado.

No obstante, realizar este cambio en el código PLSQL no implicará ninguna mejora en el rendimiento de nuestro proceso, y esto es debido a que el optimizador de la base de datos Oracle es lo suficientemente "inteligente" como para darse cuenta de que puede eliminar sin problemas el outer join, y así lo hará al escribir el plan de ejecución correspondiente. Lo único que realmente habremos hecho es conseguir que nuestra consulta SELECT esté escrita de forma correcta.

Por lo tanto, llegado a este punto tuve que preguntarme si verdaderamente se podía hacer algo más para mejorar el rendimiento de esa sentencia SELECT tan simple, y me encontré con que sabiendo lo que sabía no era posible hacer nada. Sin conocer nada acerca del esquema de la base de datos Oracle, no podía hacer ninguna suposición, ni llegar a ninguna conclusión. ¿Realmente se podría hacer algo más conociendo el esquema y la relación existente entre las tablas t1 y t2? La respuesta es sí, pero esto será materia de otro artículo.

Artículos relacionados:
Puesta a punto de sentencias SQL.
Bucles y problemas de rendimiento.
Dinamic sampling o muestreo dinámico.
La cláusula CONSTRAINT.
Subqueries o joins.

5 comentarios:

Adrián Gil "El Tigre" dijo...

hola, dando mi contribucion al tema:

Nunca uso count(*), prefiero count(1) o count('a') o sea una constante, excepto el caso que se quieran contar las filas con una determinada columna no nula en dicho caso uso count(nombre_columna).
Por otro lado si lo que se desea es saber si existe algun registro, o sea count >0 lo que estilo es abrir un cursor del tipo:
cursor c_existe is
select 1
from
where and rownum<=1;
Luego open c_existe;
fetch c_existe into v;
if c_existe%found then

else

end if;
close c_existe;

esto usualmente es muy eficiente comparado con contar los registros.

saludos y muchas gracias por el blog, soy asidua lectora

Pepelu dijo...

Muy buen apunte Adrián (extraño nombre si eres lectora). Desde luego tu propuesta es mucho más eficiente, pero hasta ese tipo de chequeo en muchos casos resulta innecesario.

compempos dijo...

Hola Pepelu.
No he encontrado por donde escribirte directamente.
Tengo una inquietud y por tu publicaciones, espero que me puedas ayudar.
Como hago para compartir funciones creadas en diferentes bases de datos por diferentes usuarios?

Gracias

Pepelu dijo...

Hola compempos, en mi blog personal www.pepelu.com.es puedes encontrar mi email.

Una función en otra base de datos debe llamarse a través de un database link (dblink). Puedes mirar este artículo: Acceso remoto a una base de datos Oracle mediante DBLINK.

Una vez creado el database link puedes llamar a la función de la siguiente manera:

SELECT nombre_esquema.nombre_funcion@nombre_dblink(parametro_1,...) FROM dual;

Jacqueline Gil Tapia-Ruano dijo...

Hola pepelu,
En realidad salio mi comentario anterior con el nombre de mi hermano, jeje, soy Jacqueline y si soy lectora, jeje
saludos