miércoles, 20 de febrero de 2008

Tunning (optimización)

El objetivo de tunning (optimización de sentencias sql) es el evitar búsquedas del tipo FULL TABLE SCAN. Principalmente se consigue creando indices adecuados y fomentando que sean usados por las sentencias. Podemos examinar el comportamiento de una sentencia SQL con el comando EXPLAIN PLAN.
Es importante:

1) Utilizar índices sobre columnas lo más selectivas posibles (aquellas que reducen al máximo el espacio de búsqueda), y en el caso de los índices compuestos, el orden en el que se declaran estas columnas deberá ser de la más a la menos selectiva (siempre que sea posible). En algunos casos es conveniente sustituir índices compuestos por varios índices simples.

2) En el caso de los Joins entre múltiples tablas, considerar que hay distintas opciones para obtener el mismo resultado, teniendo en cuenta además el coste de las operaciones que pueden involucrar según se aplique NESTED LOOPS, MERGE JOINS o HASH JOIN. Incluso se pueden considerar en algunos casos alternativas al Join (consultas anidadas, cláusula exists subconsulta , outer-join etc...). El orden de las tablas en el Join es importante.

3) Gestión de las sentencias SQL que contienen vistas. Si una consulta contiene una vista, el optimizador tiene dos formas de actuar: resolver primero la vista y después la consulta o integrar la vista en el texto de la misma. Si se resuelve primero la vista, el resultado completo de la vista se determina en primer lugar y, el resto de las condiciones de la consulta se aplican como filtro. Dependiendo del tamaño de las tablas involucradas puede resultar conveniente hacerlo de un modo u otro. Para ello se debe tener en cuenta que, si una vista contiene una operación de conjunto (GROUP BY, SUM, COUNT o DISTINCT), no podrá ser integrada en la consulta.

4) Se deben optimizar las subconsultas.

5) Utilizar las cláusulas start with y connect by en el caso de consultas sobre datos relacionados por alguna relación de herencia.

6) Limitar los accesos a tablas remotas.

7) Utilizar la cláusula UNION ALL antes que UNION siempre que sea posible.

8) Evitar el uso de llamadas a funciones PL/SQL en sentencias SQL: cuando se incluye una función PL/SQL dentro de una consulta SQL.

Oracle la divide en dos componentes: una consulta SQL con “bind variables” en el lugar en el que se encuentra la función y un bloque anónimo por cada llamada a una función que son precisamente los que tienen un mayor coste de ejecución. No obstante, en algunos casos puede ser útil el uso de packages o funciones PL/SQL almacenados en la base de datos pero usándolos en lugar de una sentencia SQL (hay casos en los que la sentencia SQL puede resultar mucho más compleja de lo que lo sería incluyendo un cursor en el procedimiento o función de base de datos).

9) Gestionar el uso de las "bind variables": Las sentencias pueden recoger los parámetros por valor (where salario>1000) o una vez compilada la sentencia haciendo uso de Bind Variables (where salario>:b1). La ventaja de la segunda opción es que Oracle compila un única vez la sentencia y reutiliza el código compilado para cada uno de los valores para los parámetros. En este segundo caso, Oracle no puede calcular el grado de selectividad de una consulta y, en su lugar, aplica un grado de selectividad por defecto (asociado a cada tipo de operación), lo cual puede dar lugar a decisiones “equivocadas”. Por lo tanto, trabajando por costes es desaconsejable el uso de Bind Variables, salvo que trabajemos con sentencias que se van a ejecutar repetidas veces y que no ofrezcan muchas dudas en cuanto a los posibles planes de acceso que puede generar.

10) Uso de Hints: es una facilidad de Oracle para modificar o influenciar las decisiones del optimizador. Se ponen en forma de comentarios en una consulta con el formato Select /*+Hint */ from... Algunos de estos hints se han visto en el punto anterior como son: rule, choose, first_rows etc... Hay que tener en cuenta que: si no es posible efectuar lo que se indica con el hint, Oracle lo ignorará, los Hints fuerzan el uso del Optimizador por costes (a excepción de rule) y no afectan a subconsultas en la misma sentencia SQL.

11) Revisar las consultas peridodicamente, puesto que puede haber dejado de estar construidas de la forma más óptima debido al constante cambio en el tamaño de las tablas, la distribución de los valores, el esquema etc...