Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL...
-
Upload
david-ortiz-villanueva -
Category
Documents
-
view
237 -
download
0
Transcript of Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL...
![Page 1: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/1.jpg)
Optimización
Francisco Moreno
![Page 2: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/2.jpg)
Introducción al afinamiento (Introducción al afinamiento (tuningtuning) de ) de SQLSQL
El afinamiento de SQL es: • un aspecto clave para mejorar el desempeño de las
aplicaciones• una labor exigente:
• Implica conocer los fundamentos teóricos de la optimización
• Implica pensar en el tamaño de la base de datos hacia el futuro (con pocos datos, usualmente todo es muy rápido)
• Depende mucho del producto y de sus versiones: lo que en un producto o versión puede ser recomendable podría ser inadecuado, obsoleto o inexistente en otro
• Se requiere un gran conocimiento de SQL
![Page 3: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/3.jpg)
Beneficios al realizar afinamiento:Beneficios al realizar afinamiento:
• Mejorar el tiempo de respuesta de las aplicaciones online
• Mejorar el tiempo de las aplicaciones batch (puede llegar el momento en que se traspasen los límites permisibles “más de h horas”)
• Garantizar la escalabilidad de la aplicación
• Reducir la carga del sistema liberar recursos para otras tareas
• Evitar actualizaciones innecesarias (e inútiles muchas veces) de hardware
![Page 4: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/4.jpg)
Volumen de Volumen de DatosDatos
Tiempo Tiempo de de
respuestarespuesta
Tipos de degradación del rendimientoTipos de degradación del rendimiento
Bottleneck
Exponencial
Lineal
Afinado
![Page 5: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/5.jpg)
Objeciones comunes para no afinar:Objeciones comunes para no afinar:
“El optimizador automáticamente afina las sentencias SQL”
“El afinamiento de SQL no está dentro de mi especialidad”
“Yo escribo las sentencias SQL, otra persona las debe afinar”
“Afinaré el SQL más tarde” “No puedo darme el lujo de dedicar tiempo a
afinar el SQL”
![Page 6: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/6.jpg)
¿Cuándo se debe afinar?¿Cuándo se debe afinar?
Idealmente, las sentencias SQL se deberían afinar en el momento en que se escriben
Mientras más avanzado esté el proyecto, más difícil será realizar el afinamiento ya que:– El cambio de algunos aspectos puede implicar el
cambio de muchos otros– Una vez que la aplicación entra en producción, la
simple adición, por ejemplo, de un índice a una tabla “grande” (millones de filas) puede ser complejo (tiempo, restricciones corporativas, etc.)
![Page 7: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/7.jpg)
Costo-Beneficio del afinamiento durante el Costo-Beneficio del afinamiento durante el ciclo de vida de una aplicaciónciclo de vida de una aplicación
Diseño Desarrollo Pruebas Produccion
Costo de realizar afinamiento
Beneficio deldesempeño
![Page 8: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/8.jpg)
ImpactoImpacto del afinamiento en una sentencia SQLdel afinamiento en una sentencia SQL
Posible MejoraPosible Mejora
Diseño de la BD
Afinamiento SQL
Compra de nuevo hardware
Afinamiento del servidorde BD
Afinamiento del sistemaoperativo
Afinamiento de la aplicación
(sin incluir SQL)
![Page 9: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/9.jpg)
El proceso de afinamiento de SQLEl proceso de afinamiento de SQL
Generar plan de
ejecución
Afinar SQL
Reescribir la sentencia SQL
Usar hints Adicionar o quitar índices
¿Se lograron los objetivos?
Formular un nuevo plan de ejecución
Sentencia SQL
inicial
No
Sí
Terminar
El afinamientoes un proceso
iterativo
Rediseñar las tablas Generar estadísticas Usar paralelismo
![Page 10: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/10.jpg)
Condiciones para afinarCondiciones para afinar
• Volúmenes de datos reales: afinar contra tablas vacías o con pocos datos 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”
![Page 11: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/11.jpg)
• Se dispone de la documentación de los modelos
• Se conocen los requisitos del sistema• Si el diseño de la BD está mal, el afinamiento
puede ser inútil• Aunque el SQL esté afinado, si otros aspectos
no lo están (ver diapositiva 8), esto podría impedir el logro de los objetivos
Condiciones para afinarCondiciones para afinar
![Page 12: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/12.jpg)
Herramientas en Oracle para afinar sentencias
SQL (aspectos esenciales)
![Page 13: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/13.jpg)
EXPLAIN PLANEXPLAIN PLAN
• El plan de ejecución de una sentencia SQL es la secuencia de operaciones que un SGBD hace para ejecutar la 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 SQL
• muestra el plan de ejecución escogido por el optimizador de Oracle para las sentencias DML (SELECT, UPDATE, INSERT y DELETE )
![Page 14: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/14.jpg)
EXPLAIN PLANEXPLAIN PLAN
El plan de ejecución de una sentencia incluye:
• El orden de acceso a las tablas usadas en la sentencia
• Un método de acceso para cada tabla usada en la sentencia
• Un método de acceso para operaciones binarias:
- Reunión (join) En la práctica es la principal
- Unión
- Intersección
- Etc.
![Page 15: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/15.jpg)
EXPLAIN PLANEXPLAIN PLAN
• Aunque el EXPLAIN PLAN muestra como ejecuta Oracle una sentencia SQL, estos resultados por sí solos no son suficientes para diferenciar entre sentencias bien optimizadas y las que no lo están: Por ejemplo, si el EXPLAIN PLAN muestra que una sentencia usa un índice, esto no significa que la sentencia sea eficiente En algunas ocasiones los índices pueden ser perjudiciales para el rendimiento…
![Page 16: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/16.jpg)
EXPLAIN PLANEXPLAIN PLAN
¿Entonces para qué sirve el EXPLAIN PLAN?
• El EXPLAIN PLAN permite determinar, por ejemplo, si un índice es usado, el método de join usado, etc.
• Al visualizar el plan de ejecución de una consulta se puede vislumbrar donde puede haber problemas de rendimiento• Es posible “aconsejar” a Oracle para que modifique el plan de ejecución (ver hints luego) y a través de pruebas determinar cual plan es más eficiente
Las pruebas con volúmenes reales de datos son un aspecto fundamental
Se explican en detalle más adelante
![Page 17: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/17.jpg)
Definir nueva estrategia
EjecuciónPlan actual
Comparación de resultados
![Page 18: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/18.jpg)
EXPLAIN PLANEXPLAIN PLAN
• Los resultados del EXPLAIN PLAN quedan guardados en una tabla llamada plan_tableplan_table
• Algunas de las columnas de esta tabla son:
Es el identificador de la sentencia.
Columna Tipo de dato Descripción
STATEMENT_ID
VARCHAR2(30) Es el identificador de la sentencia, es el valor especificado al ejecutar el EXPLAIN PLAN. Si no se especifica queda en NULL.
TIMESTAMP DATE La fecha y hora en la que se ejecutó el EXPLAIN PLAN
REMARKS VARCHAR2(4000)
Comentario asociado con un paso del plan de ejecución.
![Page 19: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/19.jpg)
EXPLAIN PLANEXPLAIN PLAN
Columna Tipo de dato Descripción
OPERATION VARCHAR2(30) Nombre de la operación hecha en este paso. Más adelante se ven algunos de sus posibles valores.
OPTIONS VARCHAR2(255)
Especifica variantes para la operación de la columna anterior. Más adelante se ven algunos de sus posibles valores.
OBJECT_OWNER
VARCHAR2(30) Nombre del usuario, dueño del esquema que contiene al objeto (tabla, índice, etc.)
OBJECT_NAME
VARCHAR2(30) Nombre del objeto involucrado en el paso.
OBJECT_INSTANCE
NUMBER(38) Un número correspondiente a la posición ordinal del objeto en la sentencia (si hay subconsultas, vistas se puede generar un orden impredecible).
OBJECT_TYPE VARCHAR2(30) Da información sobre un objeto, por ejemplo, NON-UNIQUE para un índice.
![Page 20: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/20.jpg)
EXPLAIN PLANEXPLAIN PLAN
Columna Tipo de dato Descripción
OPTIMIZER VARCHAR2(255) Indica el modo del optimizador (all_rows, first_rows, first_rows_n) *
ID NUMBER(38) Un número asignado a cada paso del plan de ejecución.
PARENT_ID NUMBER(38) Número del próximo paso de ejecución con respecto al paso actual.
POSITION NUMBER(38) Indica el orden de procesamiento para los pasos que tienen el mismo PARENT_ID
OTHER LONG Usado para consultas distribuidas. Contiene el texto SQL que es ejecutado en un nodo remoto.
OTHER_TAG VARCHAR(255) Información adicional para consultas distribuidas y paralelas.
*Para cambiarlo, por ej.: ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS; Ver también en el PL/SQL Developer.
![Page 21: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/21.jpg)
Columna Tipo de dato Descripción
COST NUMBER(38) Costo de la operación. El valor de esta columna no tiene una unidad de medida en particular.
CARDINALITY NUMBER(38) Número estimado de filas accedidas por la operación
BYTES NUMBER(38) Número estimado de bytes retornados por la operación
EXPLAIN PLANEXPLAIN PLAN
![Page 22: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/22.jpg)
EjemploSean las tablas: DROP TABLE emp; DROP TABLE dep; CREATE TABLE dep( depno NUMBER(3) PRIMARY KEY, dnom VARCHAR2(20)); CREATE TABLE emp( ced NUMBER(8) PRIMARY KEY, enom VARCHAR2(10), depno NUMBER(3) REFERENCES dep);
![Page 23: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/23.jpg)
Ejemplo
INSERT INTO dep VALUES(1,'Admin'); INSERT INTO dep VALUES(2,'Pintura'); INSERT INTO dep VALUES(3,'Lavado');
INSERT INTO emp VALUES(101,'Lisa',1); INSERT INTO emp VALUES(201,'Kirsty',1); INSERT INTO emp VALUES(304,'Bjork',3);
![Page 24: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/24.jpg)
Ejemplo 1: DELETE plan_table;
EXPLAIN PLAN SET STATEMENT_ID = 'P1' FORSELECT *FROM emp;
Se generan dos filas en la tabla plan_table con id = 0 e id = 1.
Un posible* contenido de la tabla plan_table es:
* El contenido puede variar dependiendo de aspectos como la versión del SGBD, número de filas en la tabla, entre otros.
![Page 25: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/25.jpg)
ID STATEMENT_ID TIMESTAMP REMARKS
0 P1 15/02/2015
1 P1 15/02/2015
ID OPERATION OPTIONS OBJECT_OWNER
0 SELECT STATEMENT
1 TABLE ACCESS
FULL FJMORENO
![Page 26: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/26.jpg)
ID OBJECT_
NAME
OBJECT_
INSTANCE
OBJECT_TYPE
0
1 EMP 1 TABLE
ID PARENT_ID OPTIMIZER
0 ALL_ROWS
1 0
![Page 27: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/27.jpg)
ID POSITION OTHER OTHER_TAG
0 5
1 1
ID COST CARDINALITY BYTES
0 5 3 99
1 5 3 99
![Page 28: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/28.jpg)
Ejemplo 2: DELETE plan_table;
EXPLAIN PLAN SET STATEMENT_ID = 'P1' FORSELECT *FROM dep, empWHERE emp.depno = dep.depno;
Se generan cuatro registros en la tabla plan_table con id = 0,1,2 y 3.Un posible contenido de la tabla plan_table es:
![Page 29: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/29.jpg)
ID STATEMENT_ID TIMESTAMP REMARKS
0 P1 15/02/2015
1 P1 15/02/2015
2 P1 15/02/2015
3 P1 15/02/2015
![Page 30: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/30.jpg)
ID OPERATION OPTIONS OBJECT_OWNER
0 SELECT STATEMENT
1 HASH JOIN
2 TABLE ACCESS
FULL FJMORENO
3 TABLE ACCESS
FULL FJMORENO
![Page 31: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/31.jpg)
ID OBJECT_NAME
OBJECT_
INSTANCE
OBJECT_TYPE
0
1
2 DEP 1 TABLE
3 EMP 2 TABLE
![Page 32: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/32.jpg)
ID PARENT_ID OPTIMIZER
0 ALL_ROWS
1 0
2 1
3 1
![Page 33: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/33.jpg)
ID POSITION OTHER OTHER_TAG
0 8
1 1
2 1
3 2
![Page 34: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/34.jpg)
ID COST CARDINALITY BYTES
0 8 3 174
1 8 3 174
2 2 3 75
3 5 3 99
![Page 35: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/35.jpg)
El costo total estimado está dado por el primer paso de la consulta
Una buena forma para visualizar las operaciones que se hacen en un EXPLAIN PLAN es usar la siguiente consulta:
EXPLAIN PLANEXPLAIN PLAN
![Page 36: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/36.jpg)
SELECT LPAD(' ', 2*LEVEL) || OPERATION || ' '
|| OPTIONS || ' ' || OBJECT_NAME AS query_planFROM PLAN_TABLEWHERE STATEMENT_ID = 'P1'CONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 0;
EXPLAIN PLANEXPLAIN PLAN
![Page 37: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/37.jpg)
QUERY_PLAN-----------------------------SELECT STATEMENT HASH JOIN TABLE ACCESS FULL DEP TABLE ACCESS FULL EMP
El resultado será algo similar a esto:
EXPLAIN PLANEXPLAIN PLAN
![Page 38: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/38.jpg)
Sin embargo, en las versiones recientes de Oracle se puede usar el paquete DBMS_XPLAN así:
SELECT * FROM table(DBMS_XPLAN.DISPLAY); Otra alternativa es usar en SQL*Plus:
SET AUTOTRACE ON
EXPLAIN PLANEXPLAIN PLAN
![Page 39: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/39.jpg)
Compare el EXPLAIN PLAN de la consulta anterior con:
SELECT *FROM dep INNER JOIN emp ON (emp.depno = dep.depno);
SELECT *FROM dep NATURAL JOIN emp;
A continuación se explican algunas de las operaciones que suelen aparecer en un plan de ejecución y su significado.
EXPLAIN PLANEXPLAIN PLAN
![Page 40: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/40.jpg)
EXPLAIN PLANEXPLAIN PLAN
• Las operaciones que suelen aparecer en un plan de ejecución se clasifican como:
- Row: Ejecutan una fila a la vez. El usuario puede “ver” el primer resultado antes de que la última fila sea recuperada. Útiles para aplicaciones online - Set: Ejecutan sobre un conjunto de filas. Los resultados solo se pueden ver hasta cuando el conjunto ha sido procesado completamente. Útiles para aplicaciones en batch
![Page 41: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/41.jpg)
Ejemplo:
Un join se puede hacer mediante el método
nested loops (que es de tipo row) o mediante
un hash (que es de tipo set).
EXPLAIN PLANEXPLAIN PLAN
Los algoritmos para estos métodos se explicarán en detalle posteriormente
![Page 42: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/42.jpg)
Operación Set o row
Uso
AND-EQUAL* row Operación AND entre dos o más columnas con un índice no único.
CONCATENATION** row Operación OR (usualmente sobre columnas
indexadas)
CONNECT BY row/set Operación CONNECT BY
COUNT set Operación SELECT ROWNUM
COUNT STOPKEY row ROWNUM <= constante
* En Oracle 9i, obsoleto en versiones posteriores.
** Usar hint: USE_CONCAT Los hints se ven más adelante.
![Page 43: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/43.jpg)
FILTER row Una condición hecha sobre filas que son retornadas por una operación de conjunto (tal como HAVING)
FOR UPDATE row Cuando se usa la cláusula FOR UPDATE (bloqueo de filas)
HASH JOIN set Método de join basado en hashing.
INDEX FULL SCAN row Puede aparecer cuando se usa un ORDER BY sobre una columna indexada
INDEX RANGE SCAN row Puede aparecer cuando se usa BETWEEN, >, <, >=, <=, etc.
Nota: Se puede requerir un hint que fuerce el uso del índice sobre el atributo, ya que el optimizador podría usar un full table scan. Ver ejemplo 1.
![Page 44: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/44.jpg)
INDEX UNIQUE SCAN
row Búsqueda sobre todas las columnas de un índice único.
INTERSECTION set Operación INTERSECT
MERGE JOIN set Método de join usado normalmente cuando las tablas no tienen índices sobre las columnas de join.
MINUS set Operación MINUS
NESTED LOOPS row Método de join usado normalmente cuando al menos una de las tablas tiene un índice sobre una de las columnas de join.
![Page 45: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/45.jpg)
OUTER row/set Puede aparecer en la columna Options e indica un outer join (NESTED LOOPS OUTER, MERGE JOIN OUTER, HASH JOIN OUTER)
REMOTE row/set Una operación que involucra database links (recuperar datos de una BD remota)
SEQUENCE row Uso de una secuencia, por ejemplo,
NombreSequence.NEXTVAL
SORT AGGREGATE set Suele aparecer con funciones de agregación COUNT, MIN, MAX, etc.
![Page 46: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/46.jpg)
HASH GROUP BY set Cláusula GROUP BY
SORT JOIN set Una operación que hace parte de un SORT MERGE JOIN (ordena las filas antes de hacer el join)
SORT ORDER BY set Cláusula ORDER BY
HASH UNIQUE set Cláusula DISTINCT
TABLE ACCESS BY USER ROWID
row Acceso a una tabla por medio de un ROWID
![Page 47: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/47.jpg)
TABLE ACCESS CLUSTER
row Acceso a una tabla que está en un cluster usando la clave del cluster. Ver ejemplo 2.
TABLE ACCESS FULL row Acceso a una tabla sin usar un índice
TABLE ACCESS HASH row Acceso a un hash cluster usando la clave hash
UNION-ALL row Operación UNION o UNION ALL (ver la
operación SORT UNIQUE)
SORT UNIQUE set
Operación que hace parte de una UNION (que elimina duplicados), MINUS, INTERSECT, entre otras.
![Page 48: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/48.jpg)
VIEW set Puede aparecer en algunas subconsultas complejas y en algunas ocasiones cuando se usan vistas
Muchas otras…
De acuerdo con la versión del SGBD, las operacionespueden cambiar, no estar definidas, algunas remplazan a otras, se definen nuevas, puede haber cambios en sus nombres, etc.
![Page 49: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/49.jpg)
Ejemplo 1 con un INDEX RANGE SCAN: CREATE TABLE producto(codigo NUMBER(3) PRIMARY KEY,nombre VARCHAR(10) NOT NULL,peso_prom NUMBER(3) NOT NULL);
CREATE INDEX i_peso ON producto(peso_prom);
INSERT INTO producto VALUES(1,'Gato',50);INSERT INTO producto VALUES(2,'Rueda',5);
![Page 50: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/50.jpg)
EXPLAIN PLAN FORSELECT *FROM productoWHERE peso_prom < 40;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN FORSELECT /*+ INDEX(p i_peso) */ *FROM producto p WHERE peso_prom < 40;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Hint: ensayar también NO_INDEX
![Page 51: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/51.jpg)
Ejemplo 2 con un cluster. Un cluster de tablas está conformado por tablas que se almacenan juntas ya que tienen en común una o más columnas. Con frecuencia las tablas del cluster se usan
juntas en consultas, (especialmente en joins)
Nota: Este es un index cluster, también existen los hash clusters.
![Page 52: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/52.jpg)
Se crea un cluster llamado mi_cluster cuya columna clave es departamento*:
CREATE CLUSTER mi_cluster (departamento NUMBER(6));
Se debe crear un índice sobre la clave del cluster para poder ejecutar sentencias DML:
CREATE INDEX i_mi_cluster ON CLUSTER mi_cluster;
* Hay opciones adicionales que se pueden especificar en la creación del cluster, por simplicidad no se presentan acá.
![Page 53: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/53.jpg)
Se crean las tablas y se agregan al cluster:
CREATE TABLE dpto(codigo NUMBER(6) PRIMARY KEY, descripcion VARCHAR2(10) NOT NULL) CLUSTER mi_cluster(codigo);
CREATE TABLE emp (cedula NUMBER(8) PRIMARY KEY,nombre VARCHAR2(20) NOT NULL,dep NUMBER(6) NOT NULL
REFERENCES dpto) CLUSTER mi_cluster(dep);
![Page 54: Optimización Francisco Moreno. Introducción al afinamiento (tuning) de SQL El afinamiento de SQL es: un aspecto clave para mejorar el desempeño de las.](https://reader035.fdocumento.com/reader035/viewer/2022062222/5665b48f1a28abb57c923038/html5/thumbnails/54.jpg)
Observar ahora el plan de ejecución de:
EXPLAIN PLAN FORSELECT *FROM dpto, empWHERE codigo = dep;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);