Optimización
Francisco Moreno
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
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
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
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”
¿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.)
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
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)
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
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”
• 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
Herramientas en Oracle para afinar sentencias
SQL (aspectos esenciales)
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 )
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.
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…
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
Definir nueva estrategia
EjecuciónPlan actual
Comparación de resultados
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.
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.
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.
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
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);
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);
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.
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
ID OBJECT_
NAME
OBJECT_
INSTANCE
OBJECT_TYPE
0
1 EMP 1 TABLE
ID PARENT_ID OPTIMIZER
0 ALL_ROWS
1 0
ID POSITION OTHER OTHER_TAG
0 5
1 1
ID COST CARDINALITY BYTES
0 5 3 99
1 5 3 99
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:
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
ID OPERATION OPTIONS OBJECT_OWNER
0 SELECT STATEMENT
1 HASH JOIN
2 TABLE ACCESS
FULL FJMORENO
3 TABLE ACCESS
FULL FJMORENO
ID OBJECT_NAME
OBJECT_
INSTANCE
OBJECT_TYPE
0
1
2 DEP 1 TABLE
3 EMP 2 TABLE
ID PARENT_ID OPTIMIZER
0 ALL_ROWS
1 0
2 1
3 1
ID POSITION OTHER OTHER_TAG
0 8
1 1
2 1
3 2
ID COST CARDINALITY BYTES
0 8 3 174
1 8 3 174
2 2 3 75
3 5 3 99
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
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
QUERY_PLAN-----------------------------SELECT STATEMENT HASH JOIN TABLE ACCESS FULL DEP TABLE ACCESS FULL EMP
El resultado será algo similar a esto:
EXPLAIN PLANEXPLAIN PLAN
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
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
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
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
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.
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.
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.
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.
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
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.
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.
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);
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
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.
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á.
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);
Observar ahora el plan de ejecución de:
EXPLAIN PLAN FORSELECT *FROM dpto, empWHERE codigo = dep;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);