Introducción a la Optimización de Consultas. Francisco Moreno

1. Introducción a la Optimización de Consultas. Francisco Moreno

2. Introducción al afinamiento (tuning) de SQL • 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

3. Tipo de Degradación de Rendimiento “Bottleneck” Exponencial Tpo. de Rta. Lineal Afinado Volumen de Datos

4. 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”

5. ¿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.)

6. Costo-Beneficio del tuning durante el ciclo de vida de un sistema Costo de Realizar Tuning Mejora del Desempeño Diseño Desarrollo Pruebas Producción

7. Impactodel Tuning Diseño de la BD Tuning SQL Compra de nuevo hardware Tuning del Servidor de BD Tuning del Sistema Operativo Tuning de la Aplicación (sin incluir SQL) Posible Mejora

8. El proceso de afinamiento de SQL: ¿Se ha logrado la optimización deseada? Sentencia SQL inicial Generar plan de Ejecución Si Terminar No El tuning Es un proceso iterativo Afinar SQL Formular un nuevo plan de Ejecución Reescribir la Sentencia SQL Adicionar o Quitar índices Usar Hints Rediseño de tablas

9. 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)

10. Condiciones para realizar tuning: • 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

11. Herramientas de Oracle para realizar Tuning de Sentencias SQL

12. EXPLAIN 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 EXPLAINPLAN es una herramienta proporcionada por Oracle que permite observar el plan de ejecución (y otros datos valiosos) de una sentencia específica • El EXPLAINPLAN muestra los planes de ejecución escogidos por el optimizador de Oracle para las sentencias SELECT, UPDATE, INSERT y DELETE

13. EXPLAIN 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.

14. EXPLAIN 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)

15. EXPLAIN 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

17. EXPLAIN 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

18. 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.

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

20. EXPLAIN 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. está a tablas. Número estimado de filas accesadas por la operación Número estimado de bytes retornados por la operación