Download Laboratorio Bases de Datos

UNIVERSIDAD DE ANTIOQUIA
Departamento de Ingeniería de Sistemas
Laboratorio de Bases de Datos
PRACTICA 4
Semestre 2006-2
OBJETIVO: Interpretar cómo trabaja el optimizador de Oracle y utilizar herramientas que permitan el
diagnóstico y análisis del rendimiento en sentencias SQL.
EVALUACIÓN: 20%
FECHA DE ENTREGA: 20 de Febrero
Tema: Optimización de consultas SQL.
INTRODUCCIÓN
El optimizador de consultas del Sistema de Gestión de Bases de Datos (S.G.B.D.), tiene como tarea encontrar
una estrategia adecuada para ejecutar las consultas escritas por los usuarios. La estrategia más adecuada es
aquella que minimice tanto el número de páginas leídas desde el disco como el volumen de datos a manipular.
La optimización se realiza en dos pasos:
1. Optimización algebraica: A partir de la consulta SQL ejecutada por el usuario el optimizador encuentra
una consulta equivalente pero que implique la menor manipulación posible de datos.
2. Optimización por costos. Una vez realizada la transformación algebraica se procede a buscar en disco
la información requerida. El optimizador selecciona entonces la estrategia de acceso a disco que le
signifique leer el menor número de páginas posibles.
Los Sistemas de Gestión de Bases de Datos modernos ofrecen herramientas que permiten monitorear las
labores del optimizador y en algunos casos intervenir en ellas. Los objetivos del monitoreo son entre otros:
 Verificar que el diseño físico escogido sea el adecuado para el rendimiento del sistema; por ejemplo,
que los índices creados sean usados efectivamente en la resolución de consultas. Si esto no ocurre,
una alternativa para mejorar tiempos de respuesta puede ser la de variar el diseño físico inicial.
 Si los tiempos de respuesta no son adecuados puede pensarse en re-escribir la consulta y en algunos
casos inducir las estrategias que el optimizado selecciona.
OPTIMIZACIÓN POR COSTOS
Sea el siguiente caso:
Los campos de empleado son: cédula, nombre y teléfono
Los campos de departamento son: código, nombre y presupuesto
Las reglas de negocio son:
Un empleado trabaja en un solo departamento y en un departamento trabajan varios
empleados, por lo tanto en un modelo relacional clásico se tendría:
DROP TABLE dpto;
CREATE TABLE dpto(
Laboratorio Bases de Datos – Práctica 4
Página 1 de 3
codigo NUMBER(6) PRIMARY KEY,
nombre VARCHAR2(10) NOT NULL,
presupuesto NUMBER(4) NOT NULL
);
DROP TABLE emp;
CREATE TABLE emp (
cedula NUMBER(8) PRIMARY KEY,
nombre VARCHAR2(20) NOT NULL,
tel NUMBER(8),
dep NUMBER(6) NOT NULL REFERENCES dpto
);
En todos los casos la consulta a resolver será la equivalente a:
SELECT e.cedula, e.nombre, d.codigo, d.nombre
FROM emp e, dpto d
WHERE e.dep = d.codigo;
Estructuras de almacenamiento:
1. Index Organized Table (IOT)
2. Cluster
3. Hash Cluster
a) Usando la función hash del sistema
b) Usando una función hash creada por el usuario
Se debe realizar en todos los casos (siempre y cuando aplique):
con muchos y pocos datos (ver código ejemplo de inserción abajo)
- Los 3 tipos de join (nested, hash y merge)
- Resultados de las tasas obtenidas (gráficas)
- Instrucciones de creación y explicación de cada estructura de almacenamiento
Se debe trabajar con los siguientes datos:
Pocos datos: 5000 empleados y 50 departamentos
Muchos datos: 100.000 empleados y 1000 departamentos
Para insertar los datos podrían usar algo como: (acá se muestra el caso de la inserción de pocos datos)
BEGIN
FOR i IN 1..50 LOOP
INSERT INTO dpto VALUES(i, 'Dep'||i, MOD(ABS(DBMS_RANDOM.RANDOM),1000));
END LOOP;
END;
/
Laboratorio Bases de Datos – Práctica 4
Página 2 de 3
Luego para poblar la tabla empleado se puede hacer algo como:
BEGIN
FOR i IN 1..5000 LOOP
INSERT INTO emp VALUES(i, 'Ana'||i, MOD(ABS(DBMS_RANDOM.RANDOM),10000),
MOD(ABS(DBMS_RANDOM.RANDOM),50) + 1);
END LOOP;
END;
/
BIBLIOGRAFÍA
Referencias documentación oracle 10g
1. b10752 - Performance tunning guide.pdf
Part IV - Performance Tuning
Capitulo 1 - Performance Tuning Overview
Part IV - Optimizing SQL Statements
Capitulo 12 - SQL Tuning Overview
Capitulo 14 - The Query Optimizer (Understanding Access Paths for the Query Optimizer,
Understanding Joins)
Capitulo 17 - Optimizer Hints
Capitulo 17 - Using EXPLAIN PLAN
Referencias generales
1. http://ingenieria.udea.edu.co/~ggonzal/laboratoriobd/optimizacion
2. Curso teórico
http://siona.udea.edu.co/~jfduitam/DB-Course/
3. Documento optimizador de Oracle http://siona.udea.edu.co/~jfduitam/DBdoc/Laboratorio/Optimizador ORACLE.doc
4. El Manual de conceptos de Oracle: capítulo 20. http://siona.udea.edu.co/~jfduitam/DBdoc/manuales-oracle/concepts.pdf
5. Guy Harrison. ORACLE SQL high performance tuning. Prentice Hall. 1997. pp. 491.
6. Manuales de Oracle: http://siona.udea.edu.co/~jfduitam/DB-doc/manuales-oracle/
Referencias en la Web
1. Using EXPLAIN PLAN
http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch13_exp.htm
2. How to Run EXPLAIN PLAN Command
http://www.dbaoncall.net/references/ht_run_explain_plan.html
3. Interpreting Explain Plan
http://www.akadia.com/services/ora_interpreting_explain_plan.html
4. Oracle's explain plan
http://www.adp-gmbh.ch/ora/explainplan.html
5. FAQ - Preguntas frecuentes sobre Oracle
http://www.lawebdejm.com/prog/oracle/oracle_faq.xml
Laboratorio Bases de Datos – Práctica 4
Página 3 de 3