Introducción a la
Optimización de Consultas.
Francisco Moreno
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
Tipo de Degradación de Rendimiento
“Bottleneck”
Exponencial
Tpo. de
Rta.
Lineal
Afinado
Volumen de
Datos
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?
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
de vida de un sistema
Costo de Realizar
Tuning
Mejora del
Desempeño
Diseño
Desarrollo
Pruebas
Producción
Impacto del 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
El proceso de afinamiento de SQL:
Sentencia SQL
Generar plan de
inicial
Ejecución
Si
¿Se ha logrado la
optimización deseada?
Terminar
El tuning
Es un proceso
iterativo
Reescribir la
Afinar SQL
No
Formular un nuevo
plan de Ejecución
Usar Hints
Sentencia SQL
Rediseño de tablas
Adicionar o Quitar
índices
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)
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
Herramientas de Oracle para
realizar Tuning de Sentencias
SQL
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 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 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 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 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 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 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 PLAN
Especifica variantes para la operación ejecutada.
Más adelante se observarán sus posibles valores.
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
Descargar

optimizacion_consultas1