UT_6_-_Procesamiento_y_Optimizacion_de_consultas_version_2021

Vista previa del material en texto

Procesamiento y optimización de consultas
1
UT 1: Introducción a los sistemas de datos
UT 2: Diseño conceptual y lógico de las bases de datos
UT 3: Esquema de la base de datos. 
UT 4: Diseño físico y organizaciones de archivos. 
UT 5: Lenguajes de consulta y actualización de datos. 
UT 6: Procesamiento y optimización de consultas. 
UT 7: Procesamiento de transacciones. 
UT 8: Protección de datos.
UT 9: Data warehouse
UT 10: Bases de datos distribuidas
Programa de la materia
Usted está 
aquí
2
Esquema
1. Procesamiento de consultas
2. El optimizador del DBMS
6. Los Ajustes del Rendimiento
4. Expresiones equivalentes
5. Operaciones Primitivas
3. Plan de ejecución
7. Recursos de hardware
8. DBMS y el esquema
9. Las transacciones
A. 
¿Cómo funciona el procesamiento de una consulta en un DBMS?
B. 
¿Cómo mejorar el desempeño de las consultas?
3
Procesamiento de consultas
Consulta
Resultado de la Consulta
SELECT *
FROM Empleado INNER JOIN Sucursal on Empleado.Cod_Sucursal = Sucursal.Cod_Sucursal
WHERE Empleado.Puesto = ‘gerente’ AND Sucursal.Ciudad = ‘Londres’
Ejecución
Datos
	legajo	nombre	Apellido	Cargo	Cod_suc	barrio	ciudad	
	10001	Jorge	Matow	Gerente	10	xxx	Londres	xxx
	000234	Mariana	Perez	Gerente	15	xxx	Londres	xxx
	05443	Peter	Watson	Gerente	16	xxx	Londres	xxx
	10076	David	Larson	Gerente	18	xxx	Londres	xxx
	20067	Susan	Dow	Gerente	40	xxx	Londres	xxx
4
Optimizador
Procesamiento de consultas
Consulta
Analizador y traductor
Expresión del álgebra relacional
Plan de Ejecución
Ejecución
Resultado de la Consulta
Datos
Codificación
5
Procesamiento de consultas
Consulta
Analizador y traductor
Expresión del álgebra relacional
SELECT *
FROM Empleado INNER JOIN Sucursal on Empleado.Cod_Sucursal = Sucursal.Cod_Sucursal
WHERE Empleado.Puesto = ‘gerente’ AND Sucursal.Ciudad = ‘Londres’
σ(puesto=‘gerente’)λ(ciudad=‘Londres’) 
Empleado ⋈ Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal Sucursal
 Empleado Sucursal
Análisis sintaxis en instrucciones.
2. Traducción al algebra relacional extendido (árbol invertido) 
3. Valida nombres objetos en catalogo y operaciones válidas.
4. Análisis semántico (ej: predicados contradictorios: Sucursal.Ciudad = ‘Londres’ AND Sucursal.Ciudad <> ‘Londres’)
5. Resolver vistas y simplificación
6
Optimizador
Optimizador
Expresiones equivalentes
2. Primitivas de cada operación
3. Materialización o encauzamiento
Expresión del álgebra relacional
Plan de Ejecución
Estadísticas
E/S disco
CPU

Conectividad
1. σ(puesto=‘gerente’)λ(ciudad=‘Londres’)λ(Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal) (Sucursal x Empleado)
2. σ(puesto=‘gerente’)λ(ciudad=‘Londres’) (Empleado ⋈ Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal Sucursal)
3. (σpuesto=‘gerente’ Empleado) ⋈ Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal (σciudad=‘Londres’ Sucursal)
Plan de Ejecución
Plan de Ejecución
Plan de Ejecución
Plan de Ejecución
Planes
posibles
Operación 1
Tabla
Tabla
Tabla
Operación 2
 (σpuesto=‘gerente’ Empleado) 
Búsqueda lineal
Búsqueda binaria
Con índice cluster
Algoritmo
< Costo
7
El Optimizador
SELECT REV.Codigo_Guia
FROM reserva_visita REV INNER JOIN reserva_visita_grado RVG ON REV.Codigo_Reserva = RVG.Codigo_Reserva
WHERE RVG.GRADO=3
GROUP BY REV.Codigo_Guia
SELECT REV.Codigo_Guia
FROM reserva_visita REV 
WHERE REV.Codigo_Reserva IN( 
	 SELECT RVG.Codigo_Reserva 
	 FROM reserva_visita_grado RVG
 	WHERE RVG.GRADO=3)
GROUP BY REV.Codigo_Guia
SELECT REV.Codigo_Guia
FROM reserva_visita REV, reserva_visita_grado RVG 
WHERE RVG.GRADO=3 AND REV.Codigo_Reserva = RVG.Codigo_Reserva
GROUP BY REV.Codigo_Guia
Consulta 1
Consulta 2
Consulta 3
Condición de Unión de las Tablas
Optimizador
= Plan de ejecución
Estadísticas
Tiempos de optimización versus 
ejecución
Niveles de Optimización (solo algunos motores).
8
El Optimizador y las estadísticas
Optimizador
Estadísticas
Tablas:
Número de filas, factor de bloqueo, bytes por fila, , numero de páginas.
. 
Índices: 
Número de filas, cantidad de niveles, factor de bloqueo, numero de paginas. 
Actualización
 Atributos: histogramas (muestreo o full)
Manual
Automática
	Puesto	filas
	Director	6
	Gerente	50
	Jefes	200
	Analistas de riesgo	100
	Cajeros	500
	…	…
Tabla Empleado
Promedio para el atributo puesto = 100
1000 empleados (filas)
10 Puestos distintos
9
Crear tabla: CREATE TABLE Person.Contact( FirstName nvarchar(60), LastName nvarchar(60), Phone nvarchar(15), Title nvarchar(15) ) 
Cargar la tabla con filas. 
INSERT INTO Person.Contact VALUES(N'James',N'Smith',N'425-555-1234',N'Mr') 
INSERT INTO Person.Contact VALUES(N'James',N'Andersen',N'425-555-1111',N'Mr') 
INSERT INTO Person.Contact VALUES(N'James',N'Andersen',N'425-555-3333',N'Mr') 
INSERT INTO Person.Contact VALUES(N'Christine',N'Williams',N'425-555-0000',N'Dr') 
INSERT INTO Person.Contact VALUES(N'Susan',N'Zhang',N'425-555-2222',N'Ms') 
DBCC SHOW_STATISTICS (N'Person.Contact', LastName) 
	statistics_name	statistics_keys
	_WA_Sys_00000002_1B29035F	LastName
	Name	Updated	Rows	Rows Sampled	Density	Average key length	String Index
	_WA_Sys_00000002_1B29035F	Mar 25 2015 11:21AM	5	5	0	13.6	YES
	ll Density	Average Length	Columns
	0.25	13.6	LastName
	RANGE_HI_KEY	RANGE_ROWS	EQ_ROWS	DISTINCT_RANGE_ROWS
	Andersen	0	2	0
	Smith	0	1	0
	Williams	0	1	0
	Zhang	0	1	0
Ejemplo de estadísticas
RANGE_HI_KEY
A key value showing the upper boundary of a histogram step.
RANGE_ROWS
Specifies how many rows are inside the range (they are smaller than this RANGE_HI_KEY, but bigger than the previous smaller RANGE_HI_KEY).
EQ_ROWS
Specifies how many rows are exactly equal to RANGE_HI_KEY.
DISTINCT_RANGE_ROWS
Specifies how many distinct key values are inside this range (not including the previous key before RANGE_HI_KEY and RANGE_HI_KEY itself);
10
1. Lectura de Tablas: 1000 + 50 accesos a disco
Se crea una table de 1000 * 50 tuplas
Lectura de las 1000 * 50 filas para comprobar si cumplen con el predicado de la seleccion
(1000 + 50) + 2 * (1000 * 50) = 101050 accesos a disco 
Evaluación de expresiones equivalentes
Optimizador
Estadísticas
SELECT *
FROM Empleado INNER JOIN Sucursal on Empleado.Cod_Sucursal = Sucursal.Cod_Sucursal
WHERE Empleado.Puesto = ‘gerente’ AND Sucursal.Ciudad = ‘Londres’
Sucursal (50 filas) 5 sucursales en Londres
Empleado (1000 filas) 50 empleados gerentes
1. σ(puesto=‘gerente’)λ(ciudad=‘Londres’)λ(Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal) (Sucursal x Empleado)
Histogramas
2. Lectura de tablas: 1000 + 50 accesos a disco
Combinacion de las tablas: 1000 filas (un empleado solo puede trabajar en una sucursal)
Lectura de las 1000 filas para comprobar si cumplen con el predicado de la seleccion
(1000 + 50) + 2 * (1000) = 3050 accesos a disco 
2. σ(puesto=‘gerente’)λ(ciudad=‘Londres’) (Empleado ⋈ Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal Sucursal)
3. Lectura de los 1000 Empleados para determinar si se trata de un gerente: 1000 accesos a disco
Produce una table de 50 filas (un empleado solo puede trabajar en una sucursal)
Lectura de las 50 Sucursales para determinar si se trata de Londres: 50 accesos a disco
Produce una tabla de 5 filas
Combinacion de las tablas: 50 + 5 accesos a disco
1000 + 2 * 50 + 5 + (50 + 5) = 1160 accesos a disco 
3. (σpuesto=‘gerente’ Empleado) ⋈ Sucursal.Nro_Sucursal=Empleado.Nro_Sucursal (σciudad=‘Londres’ Sucursal)
11
Primitivas: Selección
	legajo	nombre	Apellido	Cargo	Cod_suc	barrio	ciudad	
	10001	Jorge	Matow	Gerente	10	xxx	Londres	xxx
	000235	Mariana	Suarez	Cajero	6	xxx	Bs As	xxx
	10089	Roger 	Karpentie	Cajero	8	xxx	Miami	xxx
	4557	Roberto 	Noriega	Analista de Riesgo	3	Xxx	New Yorrk	xxx
	20067	Susan	Dow	Gerente	40	Xxx	Miami	xxx
	45990	Pedro 	Garcia	Cajero	5	Xxx	Bogotá	xx
	3000	Virginia	Larson	Analista de riesgo	10	Xxx	Londres	Xxx
	9908	Christine 	Roberts	Cajero	3	Xxx	New Yorrk	xx
	05443	Peter	Watson	Gerente	16	Xxxx	Londres	Xxx
	10076	David	Larson	Gerente	18	xxx	Londres	XxxPara clave primaria =
Para <> clave primaria = 
b/2
Búsqueda lineal
b
factor de bloqueo = 20 (Bloques de 8 kb y filas de 400 bytes)
filas = 1.000-> 1000/20 = total de bloques 50 (b)
Para clave primaria =
Para <> clave primaria = 
Búsqueda binaria
Fb
Log2 b + n
Log2 b
Q filas cumplen 
seleccion
N + n
Fb
Para clave primaria =
Para <> clave primaria = 
N
Índice primario
Niveles 
del indice
Ej atributo = PK: Legajo = 3000
Ej atributo <> PK: Puesto = ´Gerente´ 
N + n
Para clave primaria =
Para <> clave primaria = 
N + 1
Índice secundario
Tabla EMPLEADO (1.000 filas)
12
Reunión: Bucle Anidado
	legajo	nombre	Apellido	Cargo	Cod_suc
	10001	Jorge	Matow	Gerente	10
	000235	Mariana	Suarez	Cajero	6
	10089	Roger 	Karpentie	Cajero	8
	4557	Roberto 	Noriega	Analista de Riesgo	3
	20067	Susan	Dow	Gerente	40
	45990	Pedro 	Garcia	Cajero	5
	3000	Virginia	Larson	Analista de riesgo	10
	9908	Christine 	Roberts	Cajero	3
	05443	Peter	Watson	Gerente	16
	10076	David	Larson	Gerente	18
	Cod_suc	Nombre sucursal	Domicilio	m2	Pais
	1	Miami-Center			
	2	Houston			
	3	Bs As-Centro			
	4	Bs.As-Almagro			
	5	Bogota			
	6	New York-Center			
	7	New York- China town			
	…				
	80	Pekin			
1000 filas - b: 50
Tabla EMPLEADO
Tabla SUCURSAL
80 filas - Fb. 40 (filas de 200 bytes) 
- b: 2
 
Bucle Anidado
Cuando atributo de busqueda PK
De TI -> frenar la busqueda 
cuando la encuentra
nE * bI + bE
Bucle Anidado por Bloque
bE * bI + bE
nfTE=1
nfTI=1
Hacer mientras nfTE<=filasTE
 ir a fila nfTE 
 Hacer mientras nfTI<=filasTI
 ir a fila nfTI
 Si cod_sucTE=cod_sucTI
 agregar en Tabla reunion
 Fin si
 nfTI=nfTI+1
 Fin 
 nfTE=nfTE+1
Fin 
Bucle anidado Indexado
bE + nE * c 
Costo selección
ej N
13
Nested Loop Join
In nested loop joins, the decision of which is the outer table and which is the inner table is very importаnt becаuse the outer table is scanned only once, and the inner table is аccessed once for every quаlifying row in the outer table. The optimizer decide which table will plаy which role in the join. 
Some of the fаctors tаken into аccount by the optimizer when mаking this decision include:
Tаble size
Buffer pool size
Predicаtes
Ordering requirements
Presence of indexes
Reunión por Mezcla
	legajo	nombre	Apellido	Cargo	Cod_suc
	4557	Roberto 	Noriega	Analista de Riesgo	3
	9908	Christine 	Roberts	Cajero	3
	45990	Pedro 	Garcia	Cajero	5
	235	Mariana	Suarez	Cajero	6
	10089	Roger 	Karpentie	Cajero	8
	10001	Jorge	Matow	Gerente	10
	3000	Virginia	Larson	Analista de riesgo	10
	5443	Peter	Watson	Gerente	16
	10076	David	Larson	Gerente	18
	20067	Susan	Dow	Gerente	40
	Cod_suc	Nombre sucursal	Domicilio	m2	Pais
	1	Miami-Center			
	2	Houston			
	3	Bs As-Centro			
	4	Bs.As-Almagro			
	5	Bogota			
	6	New York-Center			
	7	New York- China town			
	…				
	80	Pekin			
Tabla empleado (ORDENADA POR COD_SUC)
Tabla sucursaL
 (ORDENADA POR COD_SUC)
Reunión por Mezcla
bE + bI
3 (bE + bI)
Reunión por Asociacion
14
Becаuse the tables are generаlly scanned only once, the decision on which is the outer and which is the inner table is somewhаt less importаnt thаn with а nested loop join; however, becаuse of the possibility of duplicаte vаlues, the optimizer will аttempt to choose the table with fewer duplicаte vаlues аs the outer table.
Optimizador
Procesamiento de consultas
Consulta
Analizador y traductor
Expresión del álgebra relacional
Plan de Ejecución
Ejecución
Resultado de la Consulta
Datos
Consultas y resultados en Memoria – Bind de Variables
15
Ajustes del Rendimiento
Desarrolladores de aplicaciones
Hardware
Ajustes en el DBMS
Hardware
Base de datos
almacenada
Diccionario de datos (Meta-datos)
SGBD (DBMS)
Software a
Software b
Software n
…
DDL
DML
Servicios que permiten gestionar:
Transacciones, concurrencia, accesos, restricciones, archivos, memoria intermadia, optimizar consultas, etc.
Sistema Operativo
Usuarios
Diseño lógico o físico
del Esquema 
DBA
Escritura de transacciones
16
Ajustes del Hardware
Procesador
Memoria principal
Tipo, cantidad 
y asignaciones
Discos:
Tipos de discos
Configuración RAID
Arquitecturas
17
Se pueden agregar vistas de monitoreo del hardware de la base de datos. Disco, procesadores, memoria.
Ajustes del DBA con el DBMS
Reorganización de archivos de paginas de datos
Regeneración de índices.
Actualización de estadísticas
Tamaño de memoria intermedia: asignaciones de cache para datos, para procedimientos almacenados, etc.
Tamaño de bloques y de extensiones
Asignación a discos de los archivos (datos, temporales, logs)
Tareas periódicas:
18
Diseño
	Codigo	Nombre	Apellido	CUIT
				
				
Descomponer tablas mediante relaciones 1 a 1.
	Codigo	Nacimiento	Lugar nacimiento
	Lugar de entrega	….
					
					
4 atributos de uso frecuente
25 atributos de uso poco frecuente
Elección de tipos de datos
Smallint (2 bytes) 
vs 
bigint (8 bytes) 
vs 
Numeric (hasta 17 bytes)
Varchar 
vs 
Char
Vs
nVarchar o nChar
Aplicar redundancia. 
	Codigo cliente	Id mov	importe
	1005	1	$100.000
	1005	2	$-20.000
	1005	3	$-15.000
	…	…	 $35.000
	Codigo	Saldo cliente
	1005	$53.000
19
Poner ejemplos de cada uno:
 tabla uno a uno. 
diferencia entre una tabla con uno u otro tipo de datos en la cantidad de MB de la misma, 
Saldo del cliente
Partición tabla VENTAS por año de venta.
Diseño
Indices cluster e índices segundarios. 
Elección de atributos a indizar.
actualización vs consultas
Uso de vistas materializadas. Datos calculados.
Manejo de Particiones.
Índices
Tabla de Ventas
Particionada por el año de la venta
>=2021
=2020
> 2015 AND < 2019
= 2019
<= 2015
20
Poner ejemplos de cada uno:
 tabla uno a uno. 
diferencia entre una tabla con uno u otro tipo de datos en la cantidad de MB de la misma, 
Saldo del cliente
Partición tabla VENTAS por año de venta.
Desarrollo de transacciones
Escritura de las consultas
Traer la menor cantidad de datos posible 
 (Evitar el *, solo los datos que se usan, etc.)
2. Tratar de evitar las iteraciones: Consultas anidadas complejas: reemplazar por Inner Join.
3. Uso de stored procedures.
4. Uso de cursores limitado.
5. Trabajo con abstracción de datos desde la aplicación
6. Usar hints en casos muy específicos (orden del join, uso de indices, etc.)
 
SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...])
SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index))
Ayudar a que el optimizador use su potencial
21
Ejemplos de uso
Herramientas de Optimización
Trace: monitorear sentencias más costosas o más habituales y datos de las mismas. Puede automatizar recomendaciones (índices, etc.)
TextData: Texto plano. Por ejemplo la SQL ejecutada.
Duration: Duración en mili segundos para SPID: Identificador para la sesión de base de datos que provoca el evento.
DatabaseName: Base de datos sobre la que ocurre el evento.
TimeStart: La fecha/hora para ubicar el evento.
Ej: SQL Server Profiler
(MSSQLSever)
Estrategias
Proactiva: 
Para asegurar performance de opciones criticas
Reactiva:
Cuando hay problemas con tiempo de respuesta. 
22
Resumiendo
1. Procesamiento de consultas
2. El optimizador del DBMS
6. Los Ajustes del Rendimiento
4. Expresiones equivalentes
5. Operaciones Primitivas
3. Plan de ejecución
7. Recursos de hardware
8. DBMS y el esquema
9. Las transacciones
A. 
¿Cómo funciona el procesamiento de una consulta en un DBMS?
B. 
¿Cómo mejorar el desempeño de las consultas?
23
Plan de Ejecución
SELECT ESC.Nombre_Escuela, ESC.Domicilio_Escuela, TEL.Telefono_Escuela
FROM escuela ESC INNER JOIN telefono_escuela TEL ON ESC.Codigo_Escuela = TEL.Codigo_Escuela
Estructura de árbol (se lee de derecha a izquierda)
SQL Server
24
Este plan debiera ponerlo adelante. Quizas uno un poco mas complejo. Tratar de que entiendan algo, y luego empezar la clase. Luego volver a ponerlo aquí.
Plan de Ejecución - SQL SERVER
25
Plande Ejecución - ORACLE: SQL Monitoring
26
Plan de Ejecución - DB2 (Visual Explain)
27
image2.png
image4.png
image7.png
image1.jpeg
image3.png
image5.png
image6.png
image8.png
image4.jpeg
image5.jpeg
image6.jpeg
image9.png
image10.png
image11.png
image12.png
image13.png
image14.png
image20.png
image15.png
image23.png
image16.png
image17.png
image18.png
image19.png