Optimización Básica de SQL

Una de las tareas más importantes de las propias de un desarrollador de bases de datos es la de optimización, ajuste, puesta a punto o //tunning//. Hay que tener en cuenta que las sentencias SQL pueden llegar a ser muy complejas y conforme el esquema de base de datos va creciendo, las sentencias son más complejas y confusas. Por es difícil escribir la sentencia correcta a la primera. Por todo ello después de tener cada uno de los procesos escrito, hay que pasar por una etapa de //tunning //en la que se revisan todas las sentencias SQL para poder optimizarlas conforme a la experiencia adquirida.

Tanto por cantidad como por complejidad, la mayoría de las optimizaciones deben hacerse sobre sentencias SELECT, ya que son (por regla general) las responsables de la mayor pérdida de tiempos.

===== Normas básicas de optimización =====

A continuación se dan unas normas básicas para escribir sentencias SELECT optimizadas.

~- Las condiciones (tanto de filtro como de //join//) deben ir siempre en el orden en que esté definido el índice. Si no hubiese índice por las columnas utilizadas, se puede estudiar la posibilidad de añadirlo, ya que tener índices extra sólo penaliza los tiempos de inserción, actualización y borrado, pero no de consulta.

~- Al crear un restricción de tipo PRIMARY KEY o UNIQUE, se crea automáticamente un índice sobre esa columna.

~- Para chequeos, siempre es mejor crear restricciones (//constraints) //que disparadores (//triggers)//.

~- Hay que optimizar dos tipos de instrucciones: las que consumen mucho tiempo en ejecutarse, o aquellas que no consumen mucho tiempo, pero que son ejecutadas muchas veces.

~- Generar un plan para todas las consultas de la aplicación, poniendo especial cuidado en los planes de las vistas, ya que estos serán incluidos en todas las consultas que hagan referencia a la vista.

~~- Generar y optimizar al máximo el plan de las vistas. Esto es importante porque el SQL de una vista, no se ejecuta mientras que la vista no es utilizada en una consulta, así que

~~- todas las consultas de esa vista se ven afectadas por su plan. Hay que tener especial cuidado de hacer //joins// entre vistas.

~- Si una aplicación que funcionaba rápido, se vuelve lenta, hay que parar y analizar los factores que han podido cambiar. Si el rendimiento se degrada con el tiempo, es posible que sea un problema de volumen de datos, y sean necesarios nuevos índices para acelerar las búsquedas. En otras ocasiones, añadir un índice equivocado puede ralentizar ciertas búsquedas. Cuantos más índices tenga una tabla, más se tardará en realizar inserciones y actualizaciones sobre la tabla, aunque más rápidas serán las consultas. Hay que buscar un equilibrio entre el número de índices y su efectividad, de tal modo que creemos el menos número posible, pero sean utilizados el mayor número de veces posible.

~- Utilizar siempre que sea posible las mismas consultas. La segunda vez que se ejecuta una consulta, se ahorrará mucho tiempo de //parsing// y optimización, así que se debe intentar utilizar las mismas consultas repetidas veces.

~- Las consultas más utilizadas deben encapsularse en procedimientos almacenados. Esto es debido a que el procedimiento almacenado se compila y analiza una sola vez, mientras que una consulta (o bloque PL/SQL) lanzado a la base de datos debe ser analizado, optimizado y compilado cada vez que se lanza.

~- Los filtros de las consultas deben ser lo más específicos y concretos posibles. Es decir: es mucho más específico poner WHERE campo = 'a' que WHERE campo LIKE '%a%'. Es muy recomendable utilizar siempre consultas que filtren por la clave primaria u otros campos indexados.

~- Hay que tener cuidado con lanzar demasiadas consultas de forma repetida, como por ejemplo dentro de un bucle, cambiando una de las condiciones de filtrado. Siempre que sea posible, se debe consultar a la base de datos una sola vez, almacenar los resultados en la memoria del cliente, y procesar estos resultados después. También se pueden evitar estas situaciones con procedimientos almacenados, o con consultas con parámetros acoplados (//bind//).

~- Evitar la condiciones IN ( SELECT…) sustituyéndolas por joins: cuando se utiliza un conjunto de valores en la clausula IN, se traduce por una condición compuesta con el operador OR. Esto es lento, ya que por cada fila debe comprobar cada una de las condiciones simples. Suele ser mucho más rápido mantener una tabla con los valores que están dentro del IN, y hacer un join normal. Por ejemplo, esta consulta:

SELECT * FROM datos WHERE campo IN ('a', 'b', 'c', 'd', ... , 'x', 'y', 'z');

se puede sustituir por la siguiente consulta, siempre que la tabla "letras" contenga una fila por cada valor contenido en el conjunto del IN:

SELECT * FROM datos d, letras l WHERE d.campo = l.letra;

También hay que tener cuidado cuando se mete un SELECT dentro del IN, ya que esa consulta puede retornar muchas filas, y se estaría cayendo en el mismo error. Normalmente, una condición del tipo "WHERE campo IN (SELECT...)" se puede sustituir por una consulta con //join//.

~- Cuando se hace una consulta multi-tabla con //joins//, el orden en que se ponen las tablas en el FROM influye en el plan de ejecución. Aquellas tablas que retornan más filas deben ir en las primeras posiciones, mientras que las tablas con pocas filas deben situarse al final de la lista de tablas.

~- Si en la cláusula WHERE se utilizan campos indexados como argumentos de funciones, el índice quedará desactivado. Es decir, si tenemos un índice por un campos IMPORTE, y utilizamos una condición como WHERE ROUND(IMPORTE) > 0, entonces el índice quedará desactivado y no se utilizará para la consulta.

~- Siempre que sea posible se deben evitar las funciones de conversión de tipos de datos e intentar hacer siempre comparaciones con campos del mismo tipo. Si hay que hacer algún tipo de conversión, intenta evitar el uso del //cast// y aplica siempre la función de conversión sobre la constante, y no sobre la columna.

~- Una condición negada con el operador NOT desactiva los índices

~- Una consulta cualificada con la cláusula DISTINCT debe ser ordenada por el servidor aunque no se incluya la cláusula ORDER BY.

~- Para comprobar si existen registros para cierta condición, no se debe hacer un SELECT COUNT(*) FROM X WHERE xxx, sino que se hace un SELECT DISTINCT 1 FROM X WHERE xxx. De este modo evitamos al servidor que cuente los registros.

~~- Si vamos a realizar una operación de inserción, borrado o actualización masiva, es conveniente desactivar los índices, ya que por cada operación individual se actualizarán

~~- los datos de cada uno de los índices. Una vez terminada la operación, volvemos a activar los índices para que se regeneren.

~- La mejor optimización es rediseñar y normalizar la base de datos. Las bases de datos relacionales están diseñadas para funcionar lo más rápidamente posible para un buen diseño relacional, pero con diseños erróneos, se vuelven muy lentas. La mayoría de los problemas de rendimiento tienen un problema de fondo de mal diseño, y muchos de ellos no podrán ser optimizados si no se rediseña el esquema de base de datos.

Toda consulta SELECT se ejecuta dentro del servidor en varios pasos. Para la misma consulta, pueden existir distintas formas para conseguir el mismo resultados, por lo que el servidor es el responsable de decidir qué camino seguir para conseguir el mejor tiempo de respuesta. La parte de la base de datos que se encarga de estas decisiones se llama Optimizador. El camino seguido por el servidor para la ejecución de una consulta se denomina “Plan de ejecución” En //Oracle8// existen dos optimizadores para la decisión del plan de ejecución:

===== Optimizador basado en reglas (RULE) =====

Se basa en ciertas reglas para realizar las consultas. Por ejemplo, si se filtra por un campo indexado, se utilizará el índice, si la consulta contiene un ORDER BY, la ordenación se hará al final, etc. No tiene en cuenta el estado actual de la base de datos, ni el número de usuarios conectados, ni la carga de datos de los objetos, etc. Es un sistema de optimización estático, no varía de un momento a otro.

===== Optimizador basado en costes (CHOOSE) =====

Se basa en las reglas básicas, pero teniendo en cuenta el estado actual de la base de datos: cantidad de memoria disponible, entradas/saludas, estado de la red, etc. Por ejemplo, si se hace una consulta utilizando un campo indexado, mirará primero el número de registros y si es suficientemente grande, entonces merecerá la pena acceder por el índice, si no, accederá directamente a la tabla. Para averiguar el estado actual de la base de datos se basa en los datos del catálogo público, por lo que es recomendable que esté lo más actualizado posible (a través de la sentencia ANALYZE), ya que de no ser así, se pueden tomar decisiones a partir de datos desfasados (la tabla tenía 10 registros hace un mes pero ahora tiene 10.000).

//Oracle8// recomienda que todas las consultas se hagan por costes, aunque hay ciertos casos en los que una consulta no se resuelve (o tarda mucho) por costes y por reglas es inmediata.

¿Y cómo hacer para que una consulta se ejecute por reglas o por costes? Pues hay dos modos de forzar a Oracle a que utilice un optimizador concreto. La primera es modificando la sesión activa para que todas las consultas sean optimizadas de una manera:

ALTER SESSION SET OPTIMIZER_GOAL = [RULE|CHOOSE];

Con esto todas las consultas se ejecutarán utilizando el optimizador indicado. La otra manera es forzando a Oracle a que utilice un optimizador en una consulta concreta. Esto se hace a través de los “hints” o sugerencias.

===== Sugerencias o //hints // =====

Un //hint// es un comentario dentro de una consulta SELECT que informa a Oracle del modo en que tiene que trazar el plan de ejecución. Los //hint// deben ir junto detrás de la palabra SELECT:

SELECT /*+ HINT */ . . .

A continuación se muestra una lista de algunos de los //hints// posibles:

|| Hint || || Descripción ||

|| /*+ CHOOSE */ || || Pone la consulta a costes. ||

|| /*+ RULE */ || || Pone la consulta a reglas. ||

|| /*+ ALL_ROWS */ || || Pone la consulta a costes y la optimiza para que devuelva todas las filas en el menor tiempo posible. Es la opción por defecto del optimizador basado en costes. Esto es apropiado para procesos en masa, en los que son necesarias todas las filas para empezar a trabajar con ellas. ||

|| /*+ FIRST_ROWS */ || || Pone la consulta a costes y la optimiza para conseguir que devuelva la primera fila en el menor tiempo posible. Esto es idóneo para procesos online, en los que podemos ir trabajando con las primeras filas mientras se recupera el resto de resultados. Este //hint// se desactivará si se utilizan funciones de grupo como SUM, AVG, etc. ||

|| /*+ INDEX( tabla índice ) */ || o || Fuerza la utilización del índice indicado para la tabla indicada. Se puede indicar el nombre de un índice (se utilizará ese índice), de varios índices (el optimizador elegirá uno entre todos ellos) o de una tabla (se utilizará cualquier índice de la tabla). ||

|| /*+ ORDERED */ || || Hace que las combinaciones de las tablas se hagan en el mismo orden en que aparecen en el join. ||

Para más información sobre los //hints// consultar el //Oracle 8 Tunning. //

Una misma consulta puede generar distintos planes de ejecución en las siguientes situaciones: ƒ Cambios en las estadísticas de las tablas (COMPUTE STATISTICS) si se utiliza un

optimizador basado en costes. ƒ Uso de los //hints //en si se utiliza el optimizador basado en reglas. ƒ Añadir o eliminar índices de una tabla, si se utiliza el optimizador basado en reglas.

===== Calcular el coste de una consulta =====

Para calcular el coste de una consulta, el optimizador se basa en las estadísticas almacenadas en el catálogo de Oracle, a través de la instrucción:

ANALYZE [TABLE,INDEX] [COMPUTE, ESTIMATE] STATISTICS;

Si no existen datos estadísticos para un objeto (por ejemplo, porque se acaba de crear), se utilizarán valores por defecto. Además, si los datos estadísticos está anticuados, se corre el riesgo de calcular costes basados en estadísticas incorrectas, pudiendo ejecutarse planes de ejecución que a priori pueden parecer mejores. Por esto, si se utiliza el optimizador basado en costes, es muy importante analizar los objetos periódicamente (como parte del mantenimiento de la base de datos). Como las estadísticas van evolucionando en el tiempo (ya que los objetos crecen o decrecen), el plan de ejecución se va modificando para optimizarlo mejor a la situación actual de la base de datos. El optimizador basado en reglas hacía lo contrario: ejecutar siempre el mismo plan, independientemente del tamaño de los objetos involucrados en la consulta. Dentro de la optimización por costes, existen dos modos de optimización, configurables desde el parámetro OPTIMIZER_MODE:

ƒ FIRST_ROWS: utiliza sólo un número determinado de filas para calcular los planes de ejecución. Este método es más rápido pero puede dar resultados imprecisos.

ƒ ALL_ROWS: utiliza todas las filas de la tabla a la hora de calcular los posibles planes de ejecución. Este método es más lento, pero asegura un plan de ejecución muy preciso. Si no se indica lo contrario, este es el método por defecto.

===== Plan de ejecución =====

Aunque en la mayoría de los casos no hace falta saber cómo ejecuta Oracle las consultas, existe una sentencia especial que nos permite ver esta información. El plan de ejecución nos proporciona muchos datos que pueden ser útiles para averiguar qué está ocurriendo al ejecutar una consulta, pero principalmente, de lo que nos informa es del tipo de optimizador utilizado, y el orden y modo de unir las distintas tablas si la instrucción utiliza algún //join//.

Para obtener un plan de ejecución, debemos rellenar una tabla especial (llamada PLAN_TABLE) con un registro para cada paso en el plan de ejecución. En Oracle8, la tabla PLAN_TABLE debe tener la siguiente estructura, aunque cambia en cada versión. Puedes encontrar la definición de la tabla en el //script// UTLXPLAN.SQL, dentro del directorio ORACLE_HOME/rdbms/admin

|| CREATE TABLE PLAN_TABLE( ||

|| STATEMENT_ID || VARCHAR2(30), ||

|| TIMESTAMP || DATE, ||

|| REMARKS || VARCHAR2(80), ||

|| OPERATION || VARCHAR2(30), ||

|| OPTIONS || VARCHAR2(30), ||

|| OBJECT_NODE || VARCHAR2(128), ||

|| OBJECT_OWNER || VARCHAR2(30), ||

|| OBJECT_NAME || VARCHAR2(30), ||

|| OBJECT_INSTANCE || NUMERIC, ||

|| OBJECT_TYPE || VARCHAR2(30), ||

|| OPTIMIZER || VARCHAR2(255), ||

|| SEARCH_COLUMNS || NUMERIC, ||

|| ID || NUMERIC, ||

|| PARENT_ID || NUMERIC, ||

|| POSITION || NUMERIC, ||

|| COST || NUMERIC, ||

|| CARDINALITY || NUMERIC, ||

|| BYTES || NUMERIC, ||

|| OTHER_TAG || VARCHAR2(255), ||

|| OTHER || LONG); ||