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); ||
