Introducción a la

Optimización de Consultas.

Francisco Moreno

Introducción al afinamiento (tuning) de Introducción al afinamiento (tuning) de SQLSQL

• Mejorar el desempeño de SQL es generalmente la forma más efectiva de mejorar el desempeño de las aplicaciones

• Afinar SQL no es sencillo• Beneficios al realizar tuning:

• Mejorar el tiempo de respuesta de las aplicaciones online

• Mejorar el tiempo de las aplicaciones batch (puede llegar el momento en que traspasen los límites permisibles ¿+ 12 horas?)

• Garantizar la escalabilidad de la aplicación

• Reducir la carga del sistema liberar recursos para otros propósitos

• Evitar actualizaciones innecesarias (e inútiles muchas veces) de hardware

Volumen de Volumen de DatosDatos

Tpo. de Tpo. de RtaRta..

Tipo de Degradación de RendimientoTipo de Degradación de Rendimiento

“Bottleneck”

Exponencial

Lineal

Afinado

Objeciones comunes para realizar tuning:Objeciones comunes para realizar tuning:

“El optimizador automáticamente afina las sentencias SQL”

“Afinar SQL no está dentro de mi área de especialidad”

“Yo escribo SQL, otra persona lo debe afinar” “Afinaré el SQL más tarde” “No podemos darnos el lujo de dedicar tiempo a

afinar el SQL”

¿Cuándo se debe afinar?¿Cuándo se debe afinar?

Idealmente SQL debería ser afinado en el momento en que se escribe.

Mientras más avanzado esté el proyecto más difícil será realizar el tuning:– Cambiar algunos aspectos implican cambiar

muchas otras cosas– Una vez que SQL entra en producción, la

simple adición de un índice sobre una tabla “grande” puede ser complejo (tiempo, restricciones corporativas etc.)

Costo-Beneficio del tuning durante el ciclo Costo-Beneficio del tuning durante el ciclo de vida de un sistemade vida de un sistema

Diseño Desarrollo Pruebas Producción

Costo de Realizar Tuning

Mejora delDesempeño

ImpactoImpacto del Tuningdel Tuning

Posible MejoraPosible Mejora

Diseño de la BD

Tuning SQL

Compra de nuevo hardware

Tuning del Servidorde BD

Tuning del SistemaOperativo

Tuning de la Aplicación(sin incluir SQL)

El proceso de afinamiento de SQL:El proceso de afinamiento de SQL:

Generar plan de

Ejecución

Afinar SQL

Reescribir la

Sentencia SQLUsar Hints Adicionar o Quitar

índices

¿Se ha logrado la optimización deseada?

Formular un nuevo plan de Ejecución

Sentencia SQL

inicial

No

Si

Terminar

El tuningEs un proceso

iterativo

Rediseño de tablas

Condiciones para realizar tuning:Condiciones para realizar tuning:

• Volúmenes de datos reales: Realizar tuning contra tablas vacías o con pocos registros es prácticamente inútil. Alternativas:• Probar en el ambiente real antes de entrar en

producción• Trabajar en un ambiente con tablas a escala de

las reales, por ejemplo un 25% del tamaño de las tablas “grandes” y un 100% de las tablas “pequeñas” (tablas de referencias)

• Documentación de los modelos disponibles• Los requerimientos del sistema han sido

expuestos• ¡Si el diseño está mal, el tuning puede ser

inútil!• Aunque el SQL esté afinado, si el servidor no

lo está, esto podría impedir el logro de las expectativas… Afinar el servidor de la BD

Condiciones para realizar tuning:Condiciones para realizar tuning:

Herramientas de Oracle para Herramientas de Oracle para realizar Tuning de Sentencias realizar Tuning de Sentencias

SQLSQL

EXPLAIN PLANEXPLAIN PLAN

• El plan de ejecución 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 ejecución (y otros datos valiosos) de una sentencia específica

• El EXPLAIN PLAN muestra los planes de ejecución escogidos por el optimizador de Oracle para las sentencias SELECT, UPDATE, INSERT y DELETE

EXPLAIN PLANEXPLAIN PLAN

Los componentes del plan de ejecución de una sentencia incluyen:

• El orden de acceso a las tablas utilizadas en la sentencia

• Un método de acceso para cada tabla utilizada en la sentencia

• Un método de acceso a las tablas para operaciones binarias:

- Reunión (join)

- Unión

- Intersección etc.

EXPLAIN PLANEXPLAIN PLAN

• Aunque la salida del EXPLAIN PLAN muestra cómo ejecuta Oracle una sentencia SQL, estos resultados por si solos no son suficientes para diferenciar entre sentencias bien optimizadas y las que no lo están

• 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 PLANEXPLAIN PLAN

¿Entonces por qué se debe utilizar el EXPLAIN PLAN?

• El EXPLAIN PLAN permite determinar por ejemplo si un índice está siendo usado, el método de join que está siendo utilizado etc.

• Es posible instruir a Oracle para que modifique el plan (ver Hints) y luego a través de pruebas (tipo TKPROF*), determinar cuál es más eficiente

• Al poder visualizar el plan de ejecución de una consulta se puede determinar dónde puede haber problemas potenciales de rendimiento

*Ver más adelante

EXPLAIN PLANEXPLAIN PLAN

• Cuando se evalúa 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 PLANEXPLAIN 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 PLANEXPLAIN PLAN

Especifica variantes para la operación ejecutada. Más adelante se observarán sus posibles valores.

EXPLAIN PLANEXPLAIN PLAN

Utilizado para consultas distribuidas. OTHER contiene el texto SQL que es ejecutado en un nodo remoto.

Información adicional para consultas distribuidas y paralelas.

a tablas.está

Número estimado de filas accesadas por la operación

Número estimado de bytes retornados por la operación