Introduccin a la Optimizacin de Consultas.

Francisco Moreno

Introduccin al afinamiento (tuning) de SQLMejorar el desempeo de SQL es generalmente la forma ms efectiva de mejorar el desempeo de las aplicacionesAfinar SQL no es sencilloBeneficios al realizar tuning:Mejorar el tiempo de respuesta de las aplicaciones onlineMejorar el tiempo de las aplicaciones batch (puede llegar el momento en que traspasen los lmites permisibles + 12 horas?)Garantizar la escalabilidad de la aplicacinReducir la carga del sistema liberar recursos para otros propsitosEvitar actualizaciones innecesarias (e intiles muchas veces) de hardware

Volumen de DatosTpo. de Rta.Tipo de Degradacin de RendimientoBottleneckExponencialLinealAfinado

Objeciones comunes para realizar tuning:El optimizador automticamente afina las sentencias SQLAfinar SQL no est dentro de mi rea de especialidadYo escribo SQL, otra persona lo debe afinarAfinar el SQL ms tardeNo podemos darnos el lujo de dedicar tiempo a afinar el SQL

Cundo se debe afinar?Idealmente SQL debera ser afinado en el momento en que se escribe.Mientras ms avanzado est el proyecto ms difcil ser realizar el tuning:Cambiar algunos aspectos implican cambiar muchas otras cosasUna vez que SQL entra en produccin, la simple adicin de un ndice sobre una tabla grande puede ser complejo (tiempo, restricciones corporativas etc.)

Costo-Beneficio del tuning durante el ciclo de vida de un sistemaDiseoDesarrolloPruebasProduccinCosto de Realizar TuningMejora delDesempeo

Impacto del TuningPosible MejoraDiseo de la BDTuning SQLCompra de nuevo hardwareTuning del Servidorde BDTuning del SistemaOperativoTuning de la Aplicacin(sin incluir SQL)

El proceso de afinamiento de SQL:Generar plan de EjecucinAfinar SQLReescribir la Sentencia SQLUsar HintsAdicionar o Quitar ndicesSe ha logrado la optimizacin deseada? Formular un nuevo plan de EjecucinSentencia SQLinicialNoSiTerminarEl tuningEs un proceso iterativoRediseo de tablas

Condiciones para realizar tuning:Volmenes de datos reales: Realizar tuning contra tablas vacas o con pocos registros es prcticamente intil. Alternativas:Probar en el ambiente real antes de entrar en produccinTrabajar en un ambiente con tablas a escala de las reales, por ejemplo un 25% del tamao de las tablas grandes y un 100% de las tablas pequeas (tablas de referencias)

Condiciones para realizar tuning:Documentacin de los modelos disponiblesLos requerimientos del sistema han sido expuestosSi el diseo est mal, el tuning puede ser intil!Aunque el SQL est afinado, si el servidor no lo est, esto podra impedir el logro de las expectativas Afinar el servidor de la BD

Herramientas de Oracle para realizar Tuning de Sentencias SQL

EXPLAIN PLAN El plan de ejecucin de una sentencia SQL es la secuencia de operaciones que el motor de Oracle realiza para ejecutar una sentencia El EXPLAIN PLAN es una herramienta proporcionada por Oracle que permite observar el plan de ejecucin (y otros datos valiosos) de una sentencia especfica El EXPLAIN PLAN muestra los planes de ejecucin escogidos por el optimizador de Oracle para las sentencias SELECT, UPDATE, INSERT y DELETE

EXPLAIN PLANLos componentes del plan de ejecucin de una sentencia incluyen: El orden de acceso a las tablas utilizadas en la sentencia Un mtodo de acceso para cada tabla utilizada en la sentencia Un mtodo de acceso a las tablas para operaciones binarias: - Reunin (join) - Unin- Interseccin etc.

EXPLAIN PLAN Aunque la salida del EXPLAIN PLAN muestra cmo ejecuta Oracle una sentencia SQL, estos resultados por si solos no son suficientes para diferenciar entre sentencias bien optimizadas y las que no lo estn Por ejemplo, si la salida muestra que una sentencia usa un ndice, esto no significa que la sentencia ejecuta eficientemente. En algunas ocasiones los ndices pueden ser extremadamente ineficientes(ver luego ndices)

EXPLAIN PLANEntonces por qu se debe utilizar el EXPLAIN PLAN?

El EXPLAIN PLAN permite determinar por ejemplo si un ndice est siendo usado, el mtodo de join que est siendo utilizado etc.

Es posible instruir a Oracle para que modifique el plan (ver Hints) y luego a travs de pruebas (tipo TKPROF*), determinar cul es ms eficiente

Al poder visualizar el plan de ejecucin de una consulta se puede determinar dnde puede haber problemas potenciales de rendimiento

*Ver ms adelante

EXPLAIN PLAN

Cuando se evala un plan se debe examinar adicionalmente el consumo actual de recursos de la sentencia Lo anterior se logra mediante el uso de las herramientas TRACE y TKPROF para examinar el rendimiento de las sentencias SQL

EXPLAIN PLAN Los resultados del EXPLAIN PLAN quedan guardados en una tabla la cual puede ser creada utilizando un script proporcionado por Oracle (UTLXPLAN.SQL). Dicha tabla posee las siguientes columnas:Es el identificador de la sentencia.

EXPLAIN PLANEspecifica variantes para la operacin ejecutada. Ms adelante se observarn sus posibles valores.

EXPLAIN PLANUtilizado para consultas distribuidas. OTHER contiene el texto SQL que es ejecutado en un nodo remoto.Informacin adicional para consultas distribuidas y paralelas. a tablas.estNmero estimado de filas accesadas por la operacin

Nmero estimado de bytes retornados por la operacin