Introducción a la Optimización de Consultas. Francisco Moreno
-
Upload
kylynn-salinas -
Category
Documents
-
view
20 -
download
1
description
Transcript of Introducción a la Optimización de Consultas. Francisco Moreno
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