DISEÑO FISICO Y OPTIMIZACION EN ORACLE

Diseño de Bases de Datos

Administración de BD

Diseño físico y Optimización

Grupo de BD Avanzadas. UC3M. 2005-06



Proceso de Diseño de Bases de Datos Conceptual

Lógico Estándar

Lógico Específico Esquema SQL

Em (id, dep, nombre, fecha) Dep ( ide, desc, pres) . .

o bien PK

PK FK

Físico

. CREATE TABLE Empleados ( Id INTEGER PRIMARY KEY, Dept CHAR(2) REFERENCES Dept ( Id ), Nombre VARCHAR(50) NOT NULL, Fecha_naci DATE NOT NULL . ); CREATE TABLE . . .

. .

Diseño físico = Estructuras lógicas

Estructuras físicas Grupo de BD Avanzadas. UC3M. 2005-06



Diseño Físico: Motivación • Buscam Buscamos os implem implement entaci ación ón “sufici “suficient enteme emente nte””

eficiente, en una plataforma concreta Hard+SO+SGBD+ (aplicación) •

La implementación implícita (probablemente) es – Suficiente: en BD para pruebas funcionales, formación,

demos – Insuficiente: en BD para pruebas de carga, producción




Diseño Físico: Tareas El objetivo de esta etapa es producir una descripción de la implementación de la base de datos en memoria secundaria. Esta descripción incluye las estructuras de almacenamiento y los métodos de acceso que se utilizarán para conseguir un acceso eficiente a los datos. Tareas: • Traducir el esquema lógico global para el SGBD específico. • Diseñar la representación física. • Diseñar los mecanismos de seguridad. • Pruebas de rendimiento. Monitorizar y afinar el sistema. Grupo de BD Avanzadas. UC3M. 2005-06



Diseño Físico: Criterios •

Mejorar el rendimiento – Espacio en memoria y en disco – Tiempo de procesador – Tiempo de disco – Contención – Coste de los procesos auxiliares




Diseño Físico: Criterios • Escalabilidad – Volumen de usuarios y datos

• Disponibilidad / Integridad • Facilidad de administración • Integridad • Pero … – Medios limitados – Criterios contrapuestos – Pérdida de independencia




Pero además del diseño físicos de la BD, en el rendimiento también influyen …

Diseño de los procesos (en C/S) - Separación entre BD y lógica Restricciones separadas, Vistas, Proc. Almacenados, Disparadores - Conexiones, interacción y tráfico Programación - Los optimizadores no son perfectos - Optimización estática - Optimización dinámica. Estadísticas Plataformas, la red Grupo de BD Avanzadas. UC3M. 2005-06



Un buen diseño físico exige conocer bien: Posibilidades del SGBD Posibilidades de los equipos de almacenamiento (ej: RAID) • Interacción entre el SO y • •

• •

•

SGBD Equipos de almacenamiento

Y como los procesos / usuarios utilizan la BD •

Perfil de uso Grupo de BD Avanzadas. UC3M. 2005-06



Diseño Físico: Prototipos • Diseño “preventivo”: Evaluación previa – Volúmenes, frecuencias, caminos, …

•

Pruebas y prototipos – Esqueletos de los procesos críticos – Simulación de datos y usuarios Perfil de carga – Herramientas de análisis de la ejecución Planes, trazados y mediciones Grupo de BD Avanzadas. UC3M. 2005-06



• Tuning ; por diseño / pro-activo / reactivo – Es mejor prevenir que curar Control y tuning permanentes




Optimización y ajuste según Oracle. •

Objetivos – Codigo SQL eficiente – Reservar recursos apropiados y suficientes (CPU, Memoria

, Disco, E/S) – Analizar problemas de espera y contención •

Enfoques – Reactivo: resolver problemas que aparecen en producción – Proactivo: diseñar el sistema teniendo en cuenta el

rendimiento




Almacenamiento de datos

La gestión eficiente del espacio en la base de datos es importante para su rendimiento.




Jerarquía de almacenamiento en Oracle: Estructuras virtuales Tablespace

Datafile1 Datafile2

Segmento1

Segmento2 Datafile3

Ext 2 Ext 3

Bloque 1




Espacio de tablas (Tablespace I) •

Es el espacio lógico de almacenamiento de datos (físicamente en data files)

•

Tipos de tablespaces: –

SYSTEM: encargado de almacenar • el diccionario de datos (tablas con información sobre la propia BD) • códigos PL/SQL fuentes y compilados, etc.

– – –

•

TEMP: almacena datos temporales ROLLBACK: almacena información transaccional DATA: almacena datos de la aplicación

Posibles estados de un tablespace: – –

En línea (on line): a disposición de las aplicaciones y BDs Desconectado (offline): datos no están disponibles, aunque lo esté la BD



Sintaxis


Espacio de tablas (Tablespace II)

CREATE [UNDO] TABLESPACE tablespace [DATAFILE datafile [cláusula_autoextend] [, datafile2 [cláusula_autoextend]]...] [{MINIMUM EXTENT entero [K|M] | BLOCKSIZE entero [K] | {LOGGING | NOLOGGING} | cláusula_gestión_extensiones | DEFAULT cláusula_almacenamiento | {ONLINE | OFFLINE} | {PERMANENT | TEMPORARY} | cláusula_gestión_segmentos }...]




Espacio de tablas (Tablespace III) •

Cláusula_autoextend := AUTOEXTEND {OFF | ON [NEXT integer [K|M]] [MAXSIZE {UNLIMITED | integer [K|M]}]}

•

Cláusula_almacenamiento STORAGE({ INITIAL entero [K|M] | NEXT entero [K|M] | MINEXTENTS entero | MAXEXTENTS { entero | UNLIMITED} | PCTINCREASE entero | FREELISTS integer | FREELIST GROUPS entero | OPTIMAL [ entero [K|M] | NULL ] | BUFFER_POOL {KEEP | RECYCLE | DEFAULT} }...)




Espacio de tablas (Tablespace IV) •

Cláusula_gestión_extensiones EXTENT MANAGEMENT {DICTIONATY | LOCAL [AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]]}

•

Cláusula_gestión_segmento SEGMENT SPACE MANAGEMENT {MANUAL|AUTO}

•

Creación de TABLESPACE temporal CREATE TEMPORARY TABLESPACE ...




Espacio de tablas (Tablespace V) •

Ejemplo CREATE TABLESPACE mitablespace DATAFILE „D:\database\ts_1_a.dbf ' SIZE 20M, „E:\database\ts_1_b.dbf ‟ SIZE 20M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 3M NEXT 4M PCTINCREASE 50 MINEXTENTS 2 );



•


Parámetros de almacenamiento – DBA_TABLESPACES – USER_TABLESPACES: (visualizar con select * from user_tablespaces; )

•

Métodos de determinar el espacio libre y utilizado. Gestionando las extensiones mediante: El diccionario de datos: actualizar (update) la entrada correspondiente en el diccionario de datos cada vez que se asigna o libera una extensión a un TABLESPACE – El propio TABLESPACE (localmente) mantiene un mapa de bits en cada archivo de datos de los bloques o cjtos de bloques liberados o usados del archivo de datos Oracle actualiza automáticamente (update) el mapa de bits –




Uso de Tablespaces •

Es conveniente reservar diferentes tablespaces para objetos que tienen características de almacenamiento y uso diferentes: – Objetos del diccionario de datos – Segmentos de rollback – Segmentos temporales – Tablas – Índices – Objetos grandes

•

(SYSTEM)

Si es posible deberían estar en diferentes discos para permitir el uso en paralelo Grupo de BD Avanzadas. UC3M. 2005-06



Segmentos I Conjunto de extensiones (conjunto de bloques de datos) dedicadas a un objeto de la BD, y almacenado en un fichero. • La cantidad de espacio que utiliza está determinada por sus parámetros de almacenamiento: •

– –

•

al crear el objeto (tabla, índice, cluster, rollback segment) utiliza los parámetros de almacenamiento predeterminados del TABLESPACE en el que se almacena (se permite posterior modificación).

Parámetros de almacenamiento del segmento: – INITIAL: tamaño inicial de la extensión – NEXT: tamaño de la siguiente extensión – PCTINCREASE: factor de incremento geométrico para sucesivas extensiones – MAXEXTENTS: número máximo de extensiones – MINEXTENTS: número mínimo de extensiones




Segmentos II •

Cuando las extensiones de un segmento ya no pueden contener más datos el segmento se amplia con nuevas extensiones. Así sucesivamente hasta que: – no haya más espacio disponible en los datafiles de las tablespaces (no ampliables automáticamente) – hasta MAXEXTENTS por segmento (si definido). – No haya más espacio (cuota) para ese usuario en el TABLESPACE

•

El tamaño de la siguiente extensión Oracle lo calcula con la siguiente fórmula: – NEXT*(1+(PCTINCREASE/100)) – Ejemplo (next:4Mb y pctincrease:50%): • 1ª extensión: = INITIAL • 2ª extensión: = NEXT= 4Mb • 3ª extensión: = 4MB*(1+50/100) = 4Mb + 2Mb = 6Mb • 4ª extensión: = 9Mb NOTA: Cuidado con el tamaño del bloque




Segmentos III •

Tipos de segmentos: TABLE, INDEX, ROLLBACK, TEMPORARY,

•

Al crear un segmento TABLE:

PARTITION, CLUSTER

– Al menos una extensión – Su espacio no se libera hasta que se elimina – Se puede utilizar “alter table” para modificar el storage. Y el comando “move” para cambiarlo de tablespace. – Cláusula PCTFREE para reservar, en el interior de cada bloque de

cada extensión, un porcentaje de espacio para actualizaciones datos • •

Nulos (con valor NULL) Otros valores que impliquen crecimiento del registro

NOTA: Específico de cada aplicación




Segmentos IV •

Ejemplo: CREATE TABLE ejemplo ( cod NUMBER(2), nombre VARCHAR2(14) ) STORAGE (INITIAL 100K NEXT 50K MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 5); SELECT * FROM USER_SEGMENTS;

•

COMENTARIOS – A partir de la 3ª extensión se incrementa un 5% el espacio de la extensión anterior: • 52,5 K  52 si el tamaño de bloque fuera 2K




Extensiones • Conjunto de bloques de datos contiguos de un

TABLESPACE • Cuando se elimina un segmento, sus extensiones se liberan • Agrupación extensiones libres contiguas cuando pctincrease≠0 – SMON (periódicamente o cuando lo necesita)

alter tablespace name coalesce; • Asignación de extensiones libres: – La más adecuada (suficiente tamaño) cercana a los datos




Tamaño de las extensiones Es necesario determinar el tamaño de un objeto – Datos iniciales tengan espacio en el segmento – Espacio disponible para el crecimiento de los

objetos




Extensiones grandes: pros y contras •

Pros – Evitan las extensiones dinámicas – Pequeñas ventajas de rendimiento: búsqueda de

tabla completa – Lecturas simples contra el mapa de extensiones •

Contras – Encontrar espacio libre contiguo – Desperdicio de espacio inicialmente




Bloques I Unidad de acceso a disco para una BD Oracle (unidad mínima de transferencia de información) • Su tamaño se define al crear la BD. • Debe ser múltiplo del tamaño de bloque del S.O. del servidor (entre 2Kb y 32 Kb). • Parámetro de configuración (create table / create index ...) •

[{PCTFREE integer | PCTUSED integer | INITRANS integer | MAXTRANS integer | cláusula_almacemanamiento }...]




Bloques II Bloque Cabecera Bloque

Overhead

Espacio Útil

PCTFREE

61+(INITRANS*23) si es un bloque tabla 61+(INITRANS*46) si es un bloque índice o clúster

Directorio Tablas en Cluster

(4 * nº tablas en cluster) + 4

Directorio Líneas

(2 * nº de líneas en bloque )

Espacio Libre para puesta al día

Depende del parámetro PCTFREE

(zona desbordamiento) desbordamiento)

PCTUSED 3/8/9 + (1) + (10) + [1 + 1(si long>255) + atributo1] + [...]




Gestión del espacio en bloques: Espacio libre distribuido •

PCTFREE: mínimo porcentaje de un bloque de datos que se reserva para actualizaciones de filas – Por defecto 10

•

PCTUSED: mínimo porcentaje de un bloque que debiera estar ocupado para no admitir más inserciones. – Por defecto 40

•

FRELIST: Lista de bloques disponibles para insertar Grupo de BD Avanzadas. UC3M. 2005-06



Tipos de datos básicos en Oracle •

Numéricos: SMAL SMALLI LINT NT(2 (200 byte bytes) s),, INT( INT(20 20 byt bytes es), ), DECIMAL(2/20 bytes), NUMBER(2/21 bytes)

•

Alfanuméricos CHAR(<2000 bytes),

•

VARCHAR2(<4000 bytes)

Fechas y tiempo DATE, TIMESTIME Grupo de BD Avanzadas. UC3M. 2005-06



Almacenamiento de tablas en Oracle Cada fila se almacena en un bloque si tiene espacio suficiente y menos de 256 columnas • De otra forma la información de la fila se encadena a través de varios bloques. • Cada fila se compone: – Cabecera de fila >= 3 bytes – Columna de datos • Longitud de los datos • Datos • Cada fila se identica con ROWID •




Encadenamiento y reorganización Los datos de una fila de una tabla pueden ser demasiado grandes para almacenarlos en un único bloque de datos vacío. Encadenamiento: Oracle almacena los datos de la fila en una cadena de uno o más bloques de datos (inserción ó modificación).




Encadenamiento y reorganización Una sentencia UPDATE incrementa la cantidad de datos en una fila, de modo que la fila no se puede dejar en el bloque de datos Reorganización: Oracle intenta encontrar otro bloque con espacio libre suficiente para mantener la fila entera. Si el bloque está disponible, Oracle mueve la fila entera al nuevo bloque. – Oracle guarda la parte de la fila original de la fila migrada apuntando al nuevo bloque que contiene la fila actual, el ROWID de la fila migrada no cambia. Los índices no son modificados, así apuntan a la localización original de la fila. –




Almacenamiento básico •

Para aumentar el al rendimiento en el acceso a los datos se pueden utilizar los siguientes métodos: – LOBs (Large Binary Objects) – Indices – Tablas organizadas por índices – Clusters y Hash Clusters




Índices: motivación •

Estructuras auxiliares para, básicamente, mejorar el tiempo de búsqueda – WHERE condición • Igualdad (una o varias filas) • Intervalo • Prefijo – Join – Integridad referencial – Unicidad – Orden • ORDER BY, GROUP BY, DISTINCT, ..




Índices: tipos Arbol-B+ Es el más habitual • Mapa de bits • Indices de palabras (full-text indexing) •

– Ficheros invertidos




Índices multinivel: Árboles B+ Hojas encadenadas por orden de la clave

Clave

Índice

...... Punteros a las filas

fila página




Indices en Arbol-B+

Búsqueda de un valor

Búsqueda de un rango Grupo de BD Avanzadas. UC3M. 2005-06



Indices en Arbol-B+ Son flexibles • Degeneración limitada • Tiempo de acceso razonable • No útiles para pocos valores •




Índices multidimensionales: Mapas de Bits AND, OR, contadores • Para pocos valores (región, año, ..) y pocas actualizaciones • DW/OLAP •

1234……n

ipr 21 0111….. 43 1000…..

iar ipe ipr ventas

B6 B6 B6 B7

3 3 1 1

43 21 21 21

375 100 20 100

. .

. .

. .

. .

1 2 3 4 . n

..

iar B6 B7

..

..

1110…. 0001….

..

ipe 1 1100…. 3 0011…. Grupo de BD Avanzadas. . . UC3M. 2005-06



Índices en Oracle CREATE [

] INDEX índice UNIQUE BITMAP ON tabla (columnasOexpresión) CLUSTER nomcluster TABLESPACE tbesp detallealmacenamiento COMPRESS NOSORT REVERSED Los NULL solo se indexan si es un índice mapa de bit Grupo de BD Avanzadas. UC3M. 2005-06



Utilización de índices UNIQUE y no UNIQUE • NULLs • Coste en espacio • Coste en tiempo de actualización • No definir índice si: •

– El SGBD no lo va a

utilizar – La clave es muy volátil o muy larga Grupo de BD Avanzadas. UC3M. 2005-06



Tablas organizadas como índices • Contienen datos que son recuperados más rapidamente que si hubiesen sido almacenados en tablas normales. • Es una tabla normal con un índice en una o más de sus columnas. • Índice y tabla se almacenan juntos: – Tablas que se consultan por la clave primaria

con pocas columnas




Tablas organizadas como índices • Ventajas – Acceso más rápido a las filas de las

tablas ya que

están en el mismo bloque – Acceso secuencial y por rangos por la clave primaria o un sufijo – Ahorro en espacio: la clave se guarda una sola vez •

Desventajas – No usar con filas grandes




Clusters: motivación Grupos de una o más tablas • Las filas se guardan físicamente juntas porque se usan generalmente juntas • Comparten una o más columnas (clave del cluster) •




Clusters: ventajas • En tablas con joins frecuentes o con relación maestro-detalle – Disminuye la E/S de disco – Mejora el tiempo de acceso • La clave sólo se almacena una vez, ahorro de espacio




Clusters: problemas No usar cuando – Valor clave se modifica a menudo – En tablas que se recorren completas a menudo – Si las filas que se agrupan juntas • Varian en número • Superan el tamaño de uno o dos bloques




Hash Clusters en Oracle • Similar a los clusters • A la clave de cada cluster se le aplica una función de dispersión • Bueno para consultas con condiciones de igualdad: – Un solo acceso

•

Desventaja: Ocupa más espacio Grupo de BD Avanzadas. UC3M. 2005-06



Particiones en Oracle •

Partición horizontal física – Repartir una tabla en espacios/ficheros/discos, … – Paralelismo, loks, copias y recuperación, …

• • • •

Partición por: rango, expresión, circular, al azar Particiones de índices Collocation / Alineación Inserciones/supresiones masivas – Rápido – Indices particionados




Carga masiva de datos




Carga masiva de datos: Aplicaciones •

Población de una base de datos desde otra Sistema de ficheros especializado • Base de Datos con •

– diferente esquema o modelo de datos – diferente gestor de bases de datos – replicación, distribución

•

Almacenes de datos:Aplicaciones de soporte a la decisión para funciones de dirección y estrategia Carga periódica de grandes cantidades de datos para el análisis o la consulta de históricos • Carga de datos comprados: datos geográficos •




Carga masiva de datos: Problemas •

Diferente esquema transformación y carga • carga en un esquema provisional y transformación •

•

No existen/comprueban las mismas restricciones – deshabilitar restricciones y estructuras auxiliares

•

Requiere demasiado tiempo – evitar los mecanismos de registro (log) de BBDD – realizar la carga por unidades más grandes: bloques

•

Problemas con la redundancia Grupo de BD Avanzadas. UC3M. 2005-06



Inserción de carga directa en ORACLE (direct path) • •

• • •

INSERT /+ append nologging +/ Es más rápido: – Datos se añaden a la tabla, no se reusa el espacio libre – Escribe directamente en los ficheros de datos, sin pasar por la caché de buffer – No se comprueba la integridad referencial – Se puede deshabilitar la escritura de redo/undo logs Se ejecuta como una transacción atómica Los índices se actualizan tras la carga Tiene limitaciones importantes Grupo de BD Avanzadas. UC3M. 2005-06



Herramientas ETL ETL: Extracción, Transformación y Carga (Loading) • ORACLE – SQL Loader: herramienta básica: carga y transformación – Import/Export: Traslado de datos entre diferentes instancias de BBDD – Oracle Datawarehouse Builder • Embarcadero • PowerData •




SQL Loader Fichero de texto *.ctl • Controla la sesión de carga – Fuente de datos a cargar y tipo de datos – Tablas y columnas destino – Transformaciones de datos – Define el resto de ficheros • Bad : registros que no se ajustan a la descripción • Discard: registros que no se pueden insertar en la BBDD •




Definición de registros en SQL*Loader LOAD DATA INFILE * BADFILE test.bad DISCARDFILE test.dsc INSERT INTO TABLE tablename (column1 position (1:2) char column2 position (3:9) integer external column3 position (10:15) integer external column4 position (16:16) char ) BEGINDATA AY3456789111111Y Grupo de BD Avanzadas. UC3M. 2005-06



Fichero de control de SQL*Loader LOAD DATA : carga de datos, principio del fichero • INTO tablename: tabla donde se cargan datos •

– N tablas = N INTO : cada fila se procesa N veces

•

INSERT/ REPLACE / APPEND INSERT: la tabla debe estar vacía – REPLACE: se borran los datos existentes – APPEND: se añaden los datos a la tabla –

•

INFILE : fichero fuente – INFILE = „C:/midirectorio/mifichero –

INFILE = * : carga desde el fichero de control con BEGINDATA




Tipo y caracter de los registros origen •

Tipos de registro origen –

Flujo: un carácter indica el fin de registro (CR) Juan, 23, Informático • Pedro, 25, Industrial •

– Variable: infile „fichvar.dat‟ “var 1” •

4Juan5Pedro

– Fijo : infile „fichvar.dat‟ “fix 23” Juan , 23, Informático • Pedro, 25, Industrial •

•

Caracter de los registros: Físico/Lógico –

Los registros físicos se pueden combinar en uno o varios registros lógicos: • concatenate N: N es número de registros físicos • reclen N: longitud del registro • continueif last=„&‟: el registro lógico continua mientras el último caracter

del registro físico sea &




Asignación de columnas •

Columna Posición TipoDatos Delimitador – Columna: nombre de la columna en la tabla – Posición • relativa: POSITION (*) char 30 • absoluta:POSITION (1:30) char – Tipo de datos • Integer (n), Smallint, char, (integer|float|decimal) external... – Delimitadores • TERMINATED BY • ENCLOSED BY Grupo de BD Avanzadas. UC3M. 2005-06



Carga directa con SQL Loader • DIRECT=true en el fichero de control • ¿Qué pasa con las r estr icciones? – Se mantienen: NOT N UL L , UNI QUE, PK – Se desactivan: F K, CH ECK , Disparadores

Los índices se construyen en áreas aparte y se actualizan al final • NOT NULL: se comprueba para cada registro • UNIQUE: se omprueba cuando se reconstruye el índice, si existen errores INDEX UNUSABLE • CLAVES PRIMARIAS (PK) = NOT NULL + UNIQUE •

•

CLAV ES AJEN AS (FK) Y CH ECK: no se activan auto.

•

DISPARADORES: Se activan automáticamente pero no se disparan durante la carga directa Grupo de BD Avanzadas. UC3M. 2005-06



Optimización de consultas




Optimización de consultas Objetivo: disminuir el tiempo de ejecución de las consultas que se realizan más frecuentemente sobre una base de datos • ¿Cuál es el camino de acceso a los datos? – Modificar el diseño físico •

Añadir redundancia y modificar la organización : añadir o cambiar índices, dividir relaciones, particionar relaciones • Reorganizar las estructuras para mantener las característcas a pesar de borrados o actualizaciones •




Procesamiento de consultas •

Análisis de la consulta – representación árborea de la estructura de la consulta

•

Reescritura de la consulta – representación en álgebra relacional – transformación a un plan lógico más eficiente:

•

Generación de un plan físico de ejecución – Selección de los algoritmos para la ejecución de cada

operación lógica




Análisis de la consulta •

Generación del árbol de la consulta –

•

Comprueba sintácticas de SQL

Preprocesado: Comprobaciones semánticas – Relaciones: las tablas existen en la BD – Atributos: estan definidos para las tablas – Tipos: los tipos de los atributos usados en

las

condiciones son compatibles




Ejemplo de consulta ACTOR (nombre,direccion,genero,fecha_nacimiento) ACTUA (pelicula, año_estreno, actor) • Obtener los títulos de las peliculas en las que participan actores nacidos antes en1960 SELECT pelicula SELECT pelicula FROM ACTUA, ACTOR FROM ACTUA WHERE actor = nombre AND WHERE actor IN ( fecha_nacimiento LIKE '%1960' SELECT nombre FROM actor WHERE fecha_nacimiento LIKE '%1960') Grupo de BD Avanzadas. UC3M. 2005-06



Ejemplo de análisis de consulta SELECT FROM WHERE ,

pelicula

ACTUA

ACTOR AND

= LIKE actor

%1960 nombre fecha_nacimiento Grupo de BD Avanzadas. UC3M. 2005-06



Plan lógico de la consulta Transformación del árbol a álgebra relacional • Reglas de optimización algebraicas •

– Algunas reglas generales • Conmutatividad / Asociatividad • Bajar las selecciones (reduce el número de tuplas) • Bajar las proyecciones (reduce el tamaño) • Eliminar subconsultas de las condiciones



PI pelicula


Plan lógico de la consulta PI pelicul a

SIGMAnombre = actor AND fecha_nacimiento LIKE %1960

bajar seleccion

SIGMAnombre = actor

X

X ACTOR

ACTOR

ACTUA

SIGMAfecha_nacimiento like %1960

PI pelicula

ACTUA

JOINnombre = actor ACTOR

bajar proyecciones

SIGMAfecha_nacimiento like %1960 ACTUA

convertir producto y selección a una combinación




Plan físico de la consulta Transformar cada operación lógica a un algoritmo de recuperación concreto • Los algoritmos pueden ser de tres tipos: •

– Memoria (si las tablas pueden almacenarse

completamente en memoria) – De una pasada (cuando las tablas no se pueden almacenar en memoria pero si el resultado) – De dos o más pasadas (cuando ni las tablas ni los resultados se pueden mantener en memoria) Grupo de BD Avanzadas. UC3M. 2005-06



Algoritmos físicos •

Según el tipo de la estructura (auxiliar) utilizada – Recorrido total (scan) – Basada en índices (index): ej. join natural

– Basada en una tabla de dispersión ( hash)

(sort): algunas operaciones se simplifican si los resultados están ordenados: ej. agrupación – ¿Es posible realizar en paralelo? – Basado en ordenación




Tipos de optimización •

Optimización basada en reglas – Heurísticas generales basadas en la experiencia del

administrador o de los diseñadores •

Optimización badada en costes – Estimacion de los costes de realizar una operación

fisica. Depende: Tamaño de la estructura: estadísticas • Tamaño de la memoria: depende de los procesos que se ejecuten simultáneamente •




Procesamiento de consultas en ORACLE Almacenamiento de consultas y su plan de ejecución en memoria • Antes de analizar una consulta comprueba que está en memoria: •

– Comprueba que una consulta es la misma

carácter por

carácter – ¿Qué ocurre con las variables? películas con actores nacidos en 1960 • películas con actores nacidos en 1977 •




Optimización de consultas en ORACLE: Herramientas •

Optimización basada en reglas: – SQL*Expert: Sistema experto para el diseño físico y la

optimización •

Optimización basada en costes – SQL-Analyze (EXPLAIN PLAN) • Planes de ejecución lógicos/físicos y depuración – Recolección de estadísticas: • Paquete SQL_STAT : Es necesario contar con estadísticas actualizadas Grupo de BD Avanzadas. UC3M. 2005-06



EXPLAIN PLAN EXPLAIN PLAN FOR SELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103 AND e.job_id = j.job_id AND e.department_id = d.department_id; ----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)| | 1 | NESTED LOOPS | | 3 | 189 | 10 (10)| | 2 | NESTED LOOPS | | 3 | 141 | 7 (15)| |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)| | 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)| |* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)| |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | | ----------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("E"."EMPLOYEE_ID"<103) 5 - access("E"."JOB_ID"="J"."JOB_ID") 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")




EXPLAIN PLAN 1) Recorrido serial de toda la tabla employees 2) Recorrido del índice primario de la tabla jobs 3) Aceeso mediante ROWID a las filas obtenidas en 2) 4) Bucle anidado (por bloques) de los resultados 2) y 3) 5) Recorrido del índice primario de la tabla departament 6) Aceeso mediante ROWID a las filas obtenidas en 5) 7) Bucle anidado (por bloques) de los resultados 4) y 6) 8) Sentencia SELECT que devuelve los resultados al usuario




Pistas de ejecución (Hints) en ORACLE El administrador puede tener una mejor percepción del problema: puede dar pistas para mejorar el plan de ejecución física • SELECT /*+ */ ..... •

– +INDEX – +ALL_ROWS

+NO_INDEX +FIRST_ROWS

– +USE_MERGE(merge_sort) – +USE_NL (nested loop)




Tuning: Ajuste de rendimiento Ajuste de la Base de Datos para un rendimiento óptimo • Requiere una perspectiva global del Sistema de Información: •

– Procesos de la empresa – Software: SO y SGBD • Uso de memoria y uso del almacenamiento • Transacciones y procedimientos de recuperación • Arquitectura de comunicaciones

– Hardware Grupo de BD Avanzadas. UC3M. 2005-06



Proceso de ajuste de rendimiento •

Proceso preventivo: diseño detallado de la BBDD – Planificación de los volumenes de datos – Frecuencias de consulta y actualización – Objetivos de recuperación y de seguridad – Planificación de políticas de mantenimiento

Realización de prototipos y simulaciones • Implementación definitiva y mantenimiento • Proceso correctivo: detección de problemas y búsqueda de soluciones •




Mantenimiento: Evolución de la BBDD •

Las condiciones de la Base de Datos varían a lo largo del tiempo – Del perfil de uso: aplicaciones nuevas , nuevos canales

(web,móvil), etc. – De los volúmenes: la información almacenada en la BBDD crece. Mantener o gestionar los archivos temporales – De la tecnología – Momentos singulares: ej. año nuevo o final de mes. Pueden requerir cambios puntuales en la base de datos.




Principios de Shasha (ajuste correctivo) 1. Estudio global, acción local 2. Fragmentar para resolver atascos 3. Lo que cuesta es arrancar 4. Dar al servidor lo que es suyo 5. Llegar a compromisos entre costes




Ajuste de rendimiento: Proceso • Se suele analizar:

1. Sentencias SQL: uso de los índices, lectura avanzada 2. Memoria Caché 3. Entrada/Salida: Discos, colas, paralelismo 4. Contención: Cerrojos y Registros (logs) • Se suele intervenir 1. Aplicación 2. Diseño físico 3. Párametros de la BBDD: memoria asignada, procesos de escucha, frecuencia de log, etc... 4. Hardware: solucionar problemas, comprar Grupo de BD Avanzadas. UC3M. 2005-06

DISEÑO FISICO Y OPTIMIZACION EN ORACLE

Recommend Documents