Tema IV : Administración de Bases de...

1

1

4.1- Diseño Físico4.1.1- Introducción4.1.2- Almacenamiento4.1.3- Tablespaces4.1.4- Segmentos4.1.5- Extensiones4.1.6- Bloques4.1.7- Almacenamiento de tablas en Oracle4.1.8- Índices4.1.9- Clusters4.1.10- Particiones4.1.11- Ajuste de rendimiento

Tema IVTema IV: Administración de Bases : Administración de Bases de Datosde Datos

© LABDA Tema IV: Administración de Bases de Datos 2

Proceso de Diseño de Bases de Datos

Lógico Estándar

FísicoConceptual Lógico Específico

4.1.14.1.1-- IntroducciónIntroducción

.

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 . . .

.

.

Em (id, dep, nombre, fecha)

Dep ( ide, desc, pres)..o bien

Esquema SQL

PKFK

PK +

Diseño físico = Estructuras lógicas Estructuras físicas

2


4.1.14.1.1-- IntroducciónIntroducciónDiseño Físico: Motivación

w Buscamos implementación “suficientemente” eficiente, en una plataforma concretan Hardware+SO+SGBD+ (aplicación)

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

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


4.1.14.1.1-- IntroducciónIntroducciónDiseño Físico: Tareas

w Objetivo de esta etapa:n 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.

w Tareas:n Traducir el esquema lógico global para el SGBD específico. n Diseñar la representación física. n Diseñar los mecanismos de seguridad. n Pruebas de rendimiento. Monitorizar y afinar el sistema.

3


4.1.14.1.1-- IntroducciónIntroducciónDiseño Físico: Criterios

1. Mejorar el rendimienton Espacio en memoria y en discon Tiempo de procesadorn Tiempo de discon Contenciónn Coste de los procesos auxiliares

2. Escalabilidadn Volumen de usuarios y datos



3. Disponibilidad / Integridad4. Facilidad de administración5. Integridad

w Pero …n Medios limitadosn Criterios contrapuestosn Pérdida de independencia

Diseño Físico: Criterios

4



Pero además del diseño físico de la BD, en el rendimiento también influyen …wDiseño de los procesos (en C/S)

n Separación entre BD y lógical Restricciones separadas, Vistas, Proc. Almacenados, Disparadores

n Conexiones, interacción y tráfico

wProgramaciónn Los optimizadores no son perfectosn Optimización estática

n Optimización dinámica. Estadísticas

wPlataformas, la red



Un buen diseño físico exige conocerbien:w Posibilidades del SGBDw Posibilidades de los equipos de almacenamiento

(Ej.: RAID)w Interacción entre el SO y

n SGBDn Equipos de almacenamiento

w Y cómo los procesos / usuarios utilizan la BDn Perfil de uso

5



w Diseño “preventivo”: Evaluación previan Volúmenes, frecuencias, caminos, …

w Pruebas y prototiposn Esqueletos de los procesos críticosn Simulación de datos y usuarios

l Perfil de carga

n Herramientas de análisis de la ejecuciónl Planes, trazados y mediciones

Diseño Físico: Prototipos



w Objetivosn Código SQL eficiente n Reservar recursos apropiados y suficientes (CPU,

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

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

rendimiento.

Optimización y ajuste según Oracle

6


4.1.14.1.1-- IntroducciónIntroducciónArquitectura Oracle – Componentes principales

Instancia

SGAConj.Compartido

Caché biblioteca

Caché diccionario de datos

Caché de buffers de base de datos

Buffer de Redo Log

Large PoolJava Pool

PMON SMON DBWR LGWR CKPT Otros

Archivos de datos

(user01.dbf)

Archivos de control

(ctrl01.ctl)

Archivos de Redo Log

(log1a.rdo)

Proceso de usuario

Proceso de servidor

Base de Datos

PGA

Arch.parámetros

Arch.contraseña


w Instancia: w SGA (Área Global del Sistema): Memoria compartida para almacenar las

informaciones de control y los datos de la instancia.• Contiene Conjunto compartido, Caché de buffers de Base de Datos, Buffer Redo

Log, Large Pool y Java Pool.

w PGA (Área Global de Programas o Proceso): Memoria reservada para cada proceso de usuario que se conecte a una base de datos. Se asigna cuando se crea un proceso y se libera cuando se termina un proceso.


Proceso de usuario

Proceso de servidor

Conexión establecida Sesión creadaOracle Server

PGA

7


w Basede datos:n Estructuras lógicas: jerarquía formada por tablespaces, segmentos,

extensiones y bloquesn Estructuras físicas: archivos de datos que forman los tablespace.

Tablespaces

Segmento2 Segmento1

Archivo de datos1

Extensión

BloquesCabeceraDirectorio tabla

Espacio Libre

Datos

Directorio fila

Archivo de datos3

Archivo de datos2




w Instanciasn Conjunto de procesos y

estructuras en memoria (SGA)

n Proporciona mecanismos de acceso y control de la BD

n Los procesos (y parte del almacenamiento principal) son compartidos por todos los usuarios.

n Sus parámetros están en el fichero init.ora, que se lee al arrancarla.

w Bases de datosn Conjunto de datos

almacenado y accesible según una estructura lógica de tablas

n Se divide en Tablespaces (uno o más)

n Un tablespace consta de ficheros (uno o más)

n Un fichero sólo pertenece a un tablespace, y puede contener varios objetos

8


4.1.24.1.2-- AlmacenamientoAlmacenamiento

n El espacio lógicol visión desde los programas usuariosl atributos, filas, tablas, …

n El espacio físicol visión desde el SOl ficheros del SO y extensiones

El espacio: Nivel lógico y nivel físico



w Visión desde el SGBDn visión simple del espacio físico, para facilitar el

direccionamiento, la independencia, ..n espacios (tablespaces,..)n campos, registros, páginasn particionesn clustersn índices

Un tercer nivel: El espacio virtual de almacenamiento

9


4.1.24.1.2-- AlmacenamientoAlmacenamientoCorrespondencia entre los tres niveles

ficheros

tablas

particiones

datoslargíndice

datos+ índicedatos

Clusterde tablas


Jerarquía de almacenamiento en Oracle: Estructuras virtuales


Tablespace

Datafile2

Datafile3

Datafile1

Segmento1 Segmento2

Ext 2Ext 3

Bloque 1

10


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

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

n TEMP: almacena datos temporalesn ROLLBACK: almacena información transaccionaln DATA: almacena datos de la aplicación

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

4.1.34.1.3-- TablespacesTablespaces



Sintaxis:

11



Sintaxis (permanent tablespace):



Sintaxis (temporal tablespace):

12



Sintaxis (undo tablespace):


Sintaxis:• Bigfile o smallfile para grandes o pequeños tablespaces

del orden de teras, el defecto es smallfile y es el que se utilizaba hasta ahora (como máximo pude tener 32 Gb)

• Datafile para datos de la aplicación. • Tempfile para datos temporales.

• En general se debe de tener al menos un tablespace por esquema y un temporal general, salvo que la aplicación haga una utilización masiva del temporal, entonces se crea uno para el esquema oportuno


13


Ejemplo:Creamos el tablespace llamado prueba en la que todos sus objetos cuando son creados se guardan en redolog. Se guarda en donde se indica el datafile, con un tamaño de 5 Mb, creciendo de mega en mega hasta 10 Mb como máximo y lo gestiona Oracle automáticamente

CREATE SMALLFILE TABLESPACE “PRUEBA" LOGGING DATAFILE 'C:\Path\PRUEBA.ora' SIZE 5M AUTOEXTENDON NEXT 1M MAXSIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO



Ejemplo:Borra el tablespace, si no tiene objetos no hace falta “including contents” y borra el fichero del sistema operativo (“and datafiles”). Además borra todas las restricciones de clave ajena

SQL> DROP TABLESPACE prueba INCLUDING CONTENTS and datafiles CASCADE CONSTRAINTS


14


w La forma de gestionar las extensiones de los tablespaces pude ser con la técnica del diccionario, única forma conocida antes de Oracle 8i.

w Con el manejo del diccionario Oracle modifica una serie de tablas en el diccionario de datos en cuanto una extensión es asignada o liberada. Pero también almacena la información en los segmentos de rollback, actualmente en el “undo”. Como las tablas del diccionario y los segmentos de rollback son parte de la instancia de base de datos, el espacio que ellos ocupan está sujeto a las mismas operaciones que otros esquemas de otros tablespaces

w El manejo local utiliza bitmaps en el propio tablespace. Es el recomendado por Oracle

w Un manejo ineficiente de las extensiones hace que se produzcan demoras en la creación y asignación de las extensiones



Ejemplo:Creamos el tablespace temporal llamado TEMPPRUEBA. Se guarda en donde se indica el tempfile, con un tamaño de 2 Mb, creciendo en 640 Kb. el fichero hasta 32 Mb como máximo. Oracle gestiona automáticamente las extensiones de 1024 en 1024 Kb.

CREATE SMALLFILE TEMPORARY TABLESPACE "TEMPPRUEBA" TEMPFILE 'C:\path\TEMPPRUEBA1.ora' SIZE 2M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 32767M EXTENT MANAGEMENT

LOCAL UNIFORMSIZE 1024K


15


Ejemplo:Borramos el tablespace

SQL> DROP TABLESPACE TEMPPRUEBA INCLUDING CONTENTS and datafiles CASCADE CONSTRAINTS

w Los espacios temporales son usados para segmentos temporales, los cuales son creados, manejados y borrados por la Base de Datos.

w Estos segmentos temporales son comúnmente generados por instrucciones como order by, group by o create index



w El tablespace undo se usa para los segmentos de undo, son parecidos a los segmentos de “rollback”, recomendado por Oracle su no utilización.

w Aunque puede haber mas de un tablespace de undo por instancia, sólo uno está activo. Los segmentos de undo crecen o disminuyen de acuerdo a las necesidades de las transacciones.

w Se utiliza para:n Rollback de las transacciones.n Lectura consistente.n Operaciones de recuperación de la base de datos.n Funcionalidad de Flashback.


16


w Parámetros de almacenamienton DBA_TABLESPACESn USER_TABLESPACES: (visualizar con select * from user_tablespaces; )

w Métodos de determinar el espacio libre y utilizado: Gestionando las extensiones mediante:n 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

n El propio TABLESPACE (localmente) mantiene un mapa de bits en cada archivo de datos de los bloques o conjuntos de bloques liberados o usados del archivo de datos

n Oracle actualiza automáticamente (update) el mapa de bits



4.1.44.1.4-- SegmentosSegmentos

w Conjunto de extensiones (conjunto de bloques de datos) dedicadas a un objeto de la BD, y almacenado en un fichero

w La cantidad de espacio que utiliza está determinada por sus parámetros de almacenamiento:n al crear el objeto (tabla, índice, cluster, Undo segment)n utiliza los parámetros de almacenamiento predeterminados del TABLESPACE

en el que se almacena (se permite posterior modificación)

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

17


w Oracle asigna a cada tabla una o más extensiones para formar el segmento de datos de una tabla

w Tipos de segmentos:n Segmentos de datos: conjunto de extensiones asignadas a

una tabla. l No puede haber extensiones de otras tablas en un segmento

dedicado a una tabla.n Segmentos de índicesn Segmentos de undo: son uno o varios segmentos con

información para deshacer transaccionesn Segmentos temporales



w Cuando las extensiones de un segmento ya no pueden contener más datos el segmento se amplía con nuevas extensiones. Así sucesivamente hasta que:n no haya más espacio disponible en los datafiles de las tablespaces (no ampliables

automáticamente) n hasta MAXEXTENTS por segmento (si definido).n no haya más espacio (cuota) para ese usuario en el TABLESPACE

w El tamaño de la siguiente extensión Oracle lo calcula con la siguiente fórmula:n NEXT*(1+(PCTINCREASE/100))n Ejemplo (next:4Mb y pctincrease:50%):

l 1ª extensión: = INITIAL

l 2ª extensión: = NEXT= 4Mbl 3ª extensión: = 4MB*(1+50/100) = 4Mb + 2Mb = 6Mb

l 4ª extensión: = 9Mb

n NOTA: Cuidado con el tamaño del bloque


18


w Tipos de segmentos: TABLE, INDEX, ROLLBACK, TEMPORARY, PARTITION, CLUSTER

w Al crear un segmento TABLE:n Al menos una extensiónn Su espacio no se libera hasta que se eliminan Se puede utilizar “alter table” para modificar el storage. Y el comando

“move” para cambiarlo de tablespace.n Cláusula PCTFREE para reservar, en el interior de cada bloque de

cada extensión, un porcentaje de espacio para actualizaciones datosl Nulos (con valor NULL)l Otros valores que impliquen crecimiento del registrol NOTA: Específico de cada aplicación



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;

w COMENTARIOSn A partir de la 3ª extensión se incrementa un 5% el espacio de la extensión anterior:

l 52,5 K à 52 si el tamaño de bloque fuera 2K


19


w Al crear un segmento INDEX:n Al menos una extensiónn Su espacio no se libera hasta que se elimina. Pero estos se pueden

eliminar automáticamente al eliminar las TABLAS o los CLUSTERS a los que indexan.

n Se aconseja guardar los índices en diferente TABLESPACE que las tablas, para eliminar contiendas.

n Se puede utilizar la opción “REBUILD” del comando “alter index” para modificar la configuración del storage y tablespace de un índice.



w Al crear un segmento ROLLBACK:n Sus extensiones mantendrán copias temporales de bloques de datos que

cambiaron durante alguna transacción.n Al menos DOS extensiones.n Todas las extensiones del mismo tamaño.n Se podrá reducir el tamaño del segmento dinámica o manualmente

hasta un tamaño específico (cláusula optimal)

Ejemplo:CREATE ROLLBACK segment R1TABLESPACE user40STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 2 MAXEXTENTS 249 OPTIMAL 20M)


20


w Al crear un segmento TEMPORARY:n Almacenan datos temporales durante las operaciones (p.e.: consultas de

gran tamaño, creación de índices, operaciones de unión, etc.)n Cada usuario tiene su tablespace temporal (create user). Habitualmente

se crea un tablespace temporal para cada BD y todos sus usuarios.Select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = ‘DEFAULT_TEMP_TABLESPACE’

n Se pueden crear nuevos tablespaces temporales:Create temporary tablespaceAlter tablespace AAAA temporary; alter tablespace AAAA permanent;

n Ver el estado del tablespaceSelect CONTENT from DBA_TABLESPACES where TABLESPACE_NAME=“AAAA”



w Otras operaciones:n Desasignar espacio de los segmentos

l Reducción tamaño de datafilesl Reducción tamaño tablas, clusters, índices (segmentos, etc.)l Reconstruir índices.


21


w Conjunto de bloques de datos contiguos de un TABLESPACEw Cuando se elimina un segmento, sus extensiones se liberanw Agrupación extensiones libres contiguas cuando

pctincrease?0n SMON (periódicamente o cuando lo necesita)n alter tablespace name coalesce;

w Asignación de extensiones libres:n La más adecuada (suficiente tamaño) cercana a los datos

4.1.54.1.5-- ExtensionesExtensiones


w Unidad de acceso a disco para una BD Oracle (unidad mínima de transferencia de información)

w Su tamaño se define al crear la BD. w Debe ser múltiplo del tamaño de bloque del S.O. del servidor

(entre 2Kb y 32 Kb).w DB_BLOCK_SIZE: parámetro determina tamaño del bloquew Parámetros de configuración (create table / create index ...)

n [{PCTFREE integer| PCTUSED integer| INITRANS integer| MAXTRANS integer| cláusula_almacemanamiento

}...]

4.1.64.1.6-- BloquesBloques

22


Bloque

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

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

Cabecera Bloque

Directorio Tablas en Cluster

Directorio Líneas

Espacio Libre para puesta al día

(zona desbordamiento)PCTFREE

PCTUSED

(4 * nº tablas en cluster) + 4

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

Depende del parámetro PCTFREE

Espacio Útil

Overhead

las líneas

4.1.64.1.6-- BloquesBloques

PCTFREE: mínimo porcentaje de un bloque que se reserva para actualizaciones de filas. Por defecto 10% del tamaño útil PCTUSED: mínimo porcentaje de un bloque que debiera estar ocupado para no admitir más inserciones. Por defecto 40%FREELIST: Lista de bloques disponibles para insertar


CAMPO TIPO LONG. MAX

TAMAÑO EFECTIVO

PROB. NO NULO

Codigo NUMBER 4 4 100

Nombre VARCHAR2 10 10 90

Direccion VARCHAR2 60 30 10

Nº estimado TUPLAS: 3000INITRANS = 1MAXTRANS = 20Tamaño Bloque = 4 KbPCTFREE=10PCTUSED=85

Tamaño Cabecera: 61+23*1 = 84 bytes

Tamaño Útil = Tamaño bloque – Tamaño Cabecera = 4096-84 = 4014

EspacioPCTFREE = T.Útil*PCTFREE = 4014*10%=401 bytes

EspacioPCTUSED = T.Útil*PCTUSED = 4014*85%= 3412 bytes

EspacioFilaTabla = 3 (Cabecera) + 1(atributo) + 1(atributo largo) + LogMediaFila = 3+3+0+18 = 24LongMediaFila = SUMA(longMediaColumn) = 2 * 1(DirectorioLíneas para 1 línea) + probNoNulo(Atrib1)*TamañoEfectivo(Atrib1) + ... = 2 + 100%*4 + 90%*10 + 10%30 = 2 + 4 + 9 + 3 = 18

Nº Filas por Bloque = TRUNC(ExpacioPCTUSED/EspacioFilaTabla) = TRUNC(3412/24) = 142

Nº Bloques = [Nº Estimado Tuplas / Nº Filas por Bloque] = 3000/142 = 22 bloques

TamañoTabla = Nº Bloques*T.ÚtilBloque = 22*4014 = 88308 bytes = 86,24 Kb

Ejemplo: Dimensionamiento de la TABLA MUSEO4.1.64.1.6-- BloquesBloques

23


w Determinación del nº de bloques necesarios utilizados para todas las filas de una tabla.n Consultado la columna ROWID

l Selecciona el número de fila y número de bloque utilizado para esa fila

l Los últimos tres caracteres de la cabecera del bloque indica el número de fila dentro del bloque (16-18)

SELECT COUNT(DISTINCT SUBSTR(ROWID,1,16)) FROM museo;

Ejemplo: Dimensionamiento de la TABLA MUSEO4.1.64.1.6-- BloquesBloques


4.1.74.1.7-- Almacenamiento de tablas en Almacenamiento de tablas en OracleOracle

w Cada fila se almacena en un bloque si tiene espacio suficiente y menos de 256 columnas

w De otra forma la información de la fila se encadena a través de varios bloques.

w Cada fila se compone: n Cabecera de fila >= 3 bytes n Columna de datos

l Longitud de los datosl Datos

w Cada fila se identifica con ROWID

24



w Los datos de una fila de una tabla pueden ser demasiado grandes para almacenarlos en un único bloque de datos vacío.

w Encadenamiento:n 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



w Reorganización:n Si 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 entonces debe reorganizar:l Oracle intenta encontrar otro bloque con espacio libre suficiente

para mantener la fila enteral Si el bloque está disponible, Oracle mueve la fila entera al nuevo

bloquel 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

Encadenamiento y reorganización

25



wPara aumentar el al rendimiento en el acceso a los datos se pueden utilizar los siguientes métodos: n Índices n Tablas organizadas por índicesn Clusters (Agrupamientos) y Hash Clusters

(Agrupamiento mediante dispersión)

Almacenamiento básico


4.1.84.1.8-- ÍndicesÍndices

w Estructuras auxiliares para mejorar el tiempo de búsquedan WHERE condición

l Igualdad (una o varias filas)l Intervalol Prefijo

w Reducir el tiempo de las operaciones de combinación de tablas w Mejorar las consultas que requieren agrupación u ordenación

n ORDER BY, GROUP BY, DISTINCT, ..

w Facilitar la implementación de restriccionesn Integridad referencialn Unicidad

Índices: Motivación

26



w Árbol-B+n Es el más habitual

w Mapa de bitsw Hashingw Índices de palabras (full-text indexing)

n Ficheros invertidos

w Etc.

Índices: Tipos



......

Clave

Punteros a las filas

fila

página

Hojas encadenadas por orden de la clave

Índice

Índices multinivel: Árboles B ++

27



Búsqueda de un valor

Búsqueda de un rango

Índices en Árbol-B ++



w Son flexibleswDegeneración limitadawTiempo de acceso razonablewNo útiles para pocos valores


28



w Los SGBD no suelen hacer fusión n para ahorrarse trabajo... posiblemente inútil

n pero liberan las páginas vacías

w Compresión de índices

n Nudos intermedios: prefijo discriminador

n Diferencial / Jerárquica

n Coste procesador

n Contención

w Filas no ordenadas físicamenten Puede ser más rápido un barrido que usar el índicen No confundir estructura con forma de acceso




w AND, OR, contadoresw Para pocos valores (región, año, ..) y pocas

actualizacionesw DW/OLAP 1234……n

ipr 21 0111…..43 1000…... . . .

iar B6 1110….B7 0001….. .. .

ipe 1 1100….3 0011….. .. .

B6 3 43 375 1B6 3 21 100 2B6 1 21 20 3B7 1 21 100 4. . . . .. . . . n

iar ipe iprventas

Índices multidimensionales: Mapas de Bits

29



w No util para Orden, Comparaciones > <

w Pero útil para Unicidad. Existencia (IR), Join

w Algoritmo: Usuario, Internon Declaración espacio asignado (degeneración)n No posible si el num.filas es imprevisible

w Factor de carga pequeño

w Uno o dos accesos menos que con árbol B+

Índices Hashing



w CREATE [ ] INDEX índice

w ON

w TABLESPACE tablespace detalle almacenamientow COMPRESSw NOSORTw REVERSED

w Los NULL sólo se indexan si es un índice mapa de bit

UNIQUEBITMAP

tabla (columnasOexpresión)CLUSTER nomcluster

Índices en Oracle

30



w UNIQUE y no UNIQUEw NULLsw Coste en espaciow Coste en tiempo de actualización w No definir índice si:

n El SGBD no lo va a utilizarn La clave es muy volátil o muy larga

Utilización de índices



w Contienen datos que son recuperados más rapidamente que si hubiesen sido almacenados en tablas normales.w Es una tabla normal con un índice en una o

más de sus columnas.w Índice y tabla se almacenan juntos:

n Tablas que se consultan por la clave primaria con pocas columnas

Tablas organizadas como índices

31



w Ventajas ☺n Acceso más rápido a las filas de las tablas ya que

están en el mismo bloque n Acceso secuencial y por rangos por la clave

primaria o un sufijon Ahorro en espacio: la clave se guarda una sola vez

w Desventajas Ln No usar con filas grandes

Tablas organizadas como índices


4.1.94.1.9-- ClustersClusters

w Grupos de una o más tablas w Las filas se guardan

físicamente juntas porque se usan generalmente juntasw Comparten una o

más columnas (clave del cluster)

Clusters: Motivación

32



w En tablas con joins frecuentes o con relación maestro-detallen Disminuye la E/S de disco n Mejora el tiempo de acceso

w La clave sólo se almacena una vez, ahorro de espacio

Clusters: Ventajas ☺



w No usar cuandon Valor clave se modifica a menudon En tablas que se recorren completas a menudo n Si las filas que se agrupan juntas

l Varian en número l Superan el tamaño de uno o dos bloques

Clusters: Problemas L

33



w Similar a los clusters w A la clave de cada cluster se le aplica una

función de dispersión w ☺ Bueno para consultas con condiciones de

igualdad:n Un solo acceso

w LDesventaja: Ocupa más espacio

Hash Clusters en Oracle


4.1.104.1.10-- ParticionesParticiones

w El particionamiento permite descomponer tablas e índices en trozos más pequeños y manejables llamados particiones que son almacenados en segmentos separados

w Partición horizontal físican Repartir una tabla en espacios/ficheros/discos, …n Paralelismo, bloqueos (locks), copias y recuperación, …

w Partición por: rango, expresión, circular, al azarw Particiones de índicesw Collocation / Alineaciónw Inserciones/supresiones masivas

n Rápidon Índices particionados

34


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

n Procesos de la empresa n Software: SO y SGBD

l Uso de memoria y uso del almacenamiento l Transacciones y procedimientos de recuperación l Arquitectura de comunicaciones

n Hardware

w Cuando hay problemas o se prevén, o periódicamente ....w Mediciones/estadísticasw Elegir puntos a mejorarw Aplicar mejoras una a una e ir comprobando su efecto

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoTuning: Motivación


w Problema: el usuario considera que el tiempo de respuesta del sistema es alto

w Proceso iterativo1. Monitorización del sistema 2. Diagnóstico 3. Búsqueda de la solución

l (Re)diseño apropiado de la aplicaciónl Optimización del código SQL l Ajuste de la memoria l Ajuste de la E/Sl Ajuste de los problemas de contención u otros problemas

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoProceso

35


1.- Estudio global, acción locala) Identificación del problemaw Panorama global

b) Intervención minimalista

2.- Fragmentar para resolver atascosa) Detectar el cuello de botellab) Romperlo w Mejora del componente causantew ej.: índices

w Repartir la cargaw entre componentesw en el tiempo

Principios de Shasha 4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento


Principios de Shasha 4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

3.- Lo que cuesta es arrancar n Minimizar los “arranques”

l Tiempo de disco: 512b = ½ 64Kbl Tiempo de mensaje: 1b ~ 1Kbl Conexiones con SGBD: pocas y reaprovecharlasl ...

4.- Dar al servidor lo que es suyon ¿Dónde poner cada tarea?

5.- Llegar a compromisos entre costesn accesos a disco vs. procesadorn espacio vs. tiempon OLTP vs. OLAPn ...

36


w Afinación de los procesos usuarios: Uso “eficiente” de las órdenes SQLwOptimizadorw Pruebas con “explicadores” (EXPLAIN) de planwRetoquesw etc.

w Sólo los relevantes (periódicamente) y los problemáticosw Conexiones y tráficow Restricciones declaradasw Disparadores y procedimientos

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoObjetivo


w RegeneraciónwEspaciowContigüidadwEstadísticas ANALYZE, RUN STATISTICS,...

n Contención (esperas) / Bloqueos

w Afinación de la BDw Básico: los índicesw Distribución entre discos y sistemas


37


w Afinación de la plataforman Hardware

n Memoria

n Discos: Número de ejes, Caché en controlador, Niveles de RAIDn SSDn CPU

n SO, SGBD (parámetros)

w Tendencia a la automatizaciónn Wizards/Advisors que aconsejan:

n índices, particiones, vistas materializadasn Investigación en auto-tuning y auto-administración BD



w Para la prevención, identificación y resolución de problemas de rendimiento

w Tienden a la automatización de la ABDw Herramientas que explotan...

n Información sobre las operaciones y tráfico entren SGBD, SO , Red

n Estadísticas mantenidas por el SGBD, sobre..n estado de la BD (datos y objetos físicos)

n num.filas, espacio, histogramas de valores de las columnas

n grado de degeneración (datos e índice)n uso de la BD y los buffers, bloqueos, índices, ..

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoHerramientas

38


n Análisis de:

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoHerramientas

Contención (esperas), bloqueos, colas ..

Alertas (scripts para avisar de “anomalías”)

Fragmentación y espacio perdido Planes de ejecución SQL: n “explicadores” y trazadoresn detalle de los pasos de las operaciones y sus tiempos

Encadenamientos y desplazamientos Comunicación C/SUso del buffer-pool /caché Consumo de recursos por las

transaccionesUso del LOG Instantáneas y su comparación

Uso de I/O (discos..), RAM, CPU Tendencias

Uso de los índices


1. Diccionario de Datos (DBA_)2. Vistas dinámicas de Rendimiento (V$_)3. Herramientas de monitorización del sistema:

n Windows: Task Manager, etc...n Unix/Linux: top,df...

4. Oracle Enterprise Manager (OEM)5. StatsPack (AWR/ASH en 10g), Index

Tuning Wizard, Quest, Embarcadero...

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoHerramientas de monitorización

39


4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

w Almacena metadatos acerca den Usuariosn Objetos: Tablas, índices, paquetes, etc...n Roles y Privilegiosn Almacenamiento (ficheros y tablespaces) n Auditoria e información operacional (backup, logs...)

w Información estática sobre la BDw Sólo lectura (excepto SYS)w Almacenado en System Tablespace

Diccionario de datos


w El acceso al diccionario de datos se hace a través de vistas que se identifican con los prefijos:n USER: objetos que posee el usuarion ALL: objetos sobre los que se tiene permisosn DBA: todos los objetos de la base de datos

w Diferentes tipos de vistas:n Vistas generales (DBMS y DB)n Vistas para monitorización de usuarios (DBA_USERS…)n Vistas de auditoría (DBA_AUDIT_OBJECT)n Vistas para monitorizar transaccionesn Vistas para almacenamiento n Vistas sobre tipos de objetos: tablas, índices

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoVistas del diccionario de datos

40



w Dict (dictionary): vista con los nombres de todas las tablas del diccionario de datos y sus descripciones.

w GLOBAL_NAME : nombre global de la BDw PRODUCT_COMPONENT_VERSION: versión de los componentes

principales w DBA_CATALOG: tablas, vistas, sinónimos y secuencias en la BDw DBA_REGISTRY: componentes instalados de Oraclew NLS_DATABASE_PARAMETERS,

NLS_INSTANCE_PARAMETERS, NLS_SESSION_PARAMETERS: parámetros de localización.

w DBA_SOURCE: código fuente de los objetos w DBA_JOBS: monitoriza la definición de procesos en background (jobs)w DBA_OBJECT: objetos de la BD como tablas, índices, paquetes,

procedimientos, funciones, dimensiones, vistas materializadas, planes de recursos, tipos, secuencias, sinónimos, disparadores, vistas y particiones

Vistas generales


w DBA_EXTENTS: información sobre las extensiones que utilizan los objetos w DBA_FREE_SPACE: espacio libre en la BDw DBA_SEGMENTS: información detallada

sobre los segmentos: tipo, n bloques...w DBA_DATA_FILES/DBA_TEMP_FILESw DBA_TABLESPACES

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoVistas relacionadas con el almacenamiento

41


w DBA_CONSTRAINTS: restricciones de integridad y tablas sobre la que se aplican w DBA_CONS_COLUMNS: columnas afectadas por

las restriccionesw DBA_INDEXES: índices y sus característicasw DBA_IND_COLUMNS: columnas que se han

indizadow INDEX_STATS: resultados del comando analyze,

analiza el rendimiento y características de los índices.

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoVistas relacionadas con los índices y restricciones


w Se actualizan con la información que la instancia genera durante su ejecución.w Su información no está disponible si la

instancia no está levantada.w Sólo tenemos acceso para lectura.w Comienzan con el prefijo V$.

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoVistas dinámicas de rendimiento

42


w Vistas actualesn Información actual sobre el estado del sistema n Ej: V$SESSION: sesiones abiertas en el sistema

w Vistas acumuladasn Cuenta el número de veces que un suceso ha ocurrido desde el inicio

de la sesión n Se debe hacer la diferencia entre el inicio y el fin n Ej: V$FILESTAT: Operaciones de I/O

w Vistas de información n Información actual pero no tan dinámica: Estadísticas n V$SQL_PLAN: Plan de ejecución para cursores ejecutados

recientemente

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoVistas dinámicas


w Estadísticas de Rendimiento

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoOracle Enterprise Manager

43


w Opciones de Administración



w Opciones de Mantenimiento


44


w Acceder a los datos en memoria principal es mucho más rápido que acceder a los datos en discow Oracle almacena datos en memoria principal: SGA y

PGAn Se puede consultar el tamaño de la SGA en V$SGA

w El tamaño correcto de la memoria depende :n Tipo de aplicación n Número de usuarios n Tamaño de las transacciones

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoAjuste de la memoria de la instancia


w Afecta directamente al rendimiento de la BD:n Caché de biblioteca: código PL/SQL y sentencias SQL

recientesn Caché de diccionario: Diccionario de datos

w Se modifica conjuntamente el tamaño del conjunto:n alter system

w Los efectos de un mal dimensionamiento son:n Fragmentación n Mayor consumo de CPUn Mayor intercambio entre memoria y disco

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoSGA: Ajuste de Conjunto Compartido (Shared Pool)

45


w Contiene versiones analizadas y ejecutables del código SQL y PL/SQL w Interpretación de SQL

n Análisis (Parsing) n Optimización n Ejecuciónn Recuperación

w Análisis y optimización son operaciones costosas: n Si la sentencia esta en caché solo es necesario un análisis

superficial.

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoSGA: Caché de Biblioteca


w Vista dinámicasn V$LIBRARYCACHE: estadísticas de acierto n V$LIBRARY_CACHE_MEMORY: espacio libre y

objetos en cachén V$SHARED_POOL_ADVICE: informacón para el ajuste

w Algunos consejos para mejorar la eficiencian Uso de variables n Compartir cursores: Cursor_sharing=similar

n Código compartido

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoEficiencia de la Caché de Biblioteca

46


w Gestión eficiente del espacio en disco afecta al rendimiento de las aplicaciones n Asignación dinámica de espacio n Número de accesos a discos n Fragmentación n Encadenamiento de registros

Diseño físico y Gestión del espacio4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento


w Los objetos de la BD pueden crecer y necesitar más espacio físico

w Cada estructura se guarda en un Tablespace, en su propio segmento

w Cuando crece se asignan nuevas extensiones (cto. de bloques)

Tablespace

Segmento

Extensión

Bloque

Jerarquía de almacenamiento4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

47


Gestión del espacio4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

w En Oracle10g hay dos alternativas:n Tablespaces gestionados por el diccionario (DMT)

n Para la asignación de espacio se consulta el diccionarion Los cambios se deben reflejar en los segmentos de rollback, etc

n Tablespaces gestionados localmente (LMT)l La información de control se guarda en cabeceras en los ficheros de

datos.l Mejora la eficiencia sobre todo en tablespaces locales l Permiten la gestión automátical Opción por defecto

n Para convertir de DMT a LMT está el paquete DBMS_SPACE_ADMIN


w Es conveniente reservar diferentes tablespaces para objetos que tienen características de almacenamiento y uso diferentes: n Objetos del diccionario de datos (SYSTEM)

n Segmentos de rollback, actualmente undon Segmentos temporales n Tablasn Índices n Objetos grandes

w Si es posible deberían estar en diferentes discos para permitir el uso en paralelo

Uso de Tablespaces4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

48


w Es necesario determinar el tamaño de un objeto n Datos iniciales tengan espacio en el segmento n Espacio disponible para el crecimiento de los objetos

w Para cada objeto podemos determinar:n Tamaño inicial de la extensiónn Tamaño de la siguiente extensión

l Uniformel Variable l Automático

n Determinar la forma de manejo de la extensión l Manual/Automático

Tamaño de las extensiones4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento


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

tabla completan Lecturas simples contra el mapa de extensiones

w Contras Ln Encontrar espacio libre contiguon Desperdicio de espacio inicialmente

Extensiones grandes: pros y contras4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

49


w Generalmente para BDs transaccionales n Ventajas: ☺

l Acceso aleatorio eficientel Bueno para filas pequeñas l Reducen la contención de bloques

n Desventajas Ll Excesiva sobrecarga de información de controll Pocas filas por bloque

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoTamaño del bloque: pequeño


w Generalmente para Almacenes de Datos n Ventajas ☺

l Menor sobrecargal Acceso secuencial l Filas grandesl Lectura secuencial de índices

n Desventajas Ll Incrementan la contención en los bloques hoja de los

índicesl Desaprovecha espacio en buffer caché

4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimientoTamaño del bloque: grande

50


w Al crear objetos de la BD en tablespaces gestionados localmente con su propio segmento:n La opción AUTOALLOCATE especifica que las

nuevas extensiones se crean dinámicamente. La BD decide el tamaño de la extensión

n La opción AUTO gestiona el espacio libre dentro de los bloques

Gestión automática del espacio4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento



w Espacio libre distribuido:n PCTFREE: mínimo porcentaje de un bloque de

datos que se reserva para actualizaciones de filas. Por defecto 10

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

n FREELIST: Lista de bloques disponibles para insertar

Estimación del espacio en bloques

51


w Encadenamiento: Filas que no caben en un único bloque w Migración: Tras una operación de actualización la

fila no tiene espacio en el bloquen La fila se mueve a otro bloquen La parte de la fila original se mantiene y apunta a la

nueva localización para evitar la modificación de los índices

Problemas en bloques4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento


w Borrados frecuentes provocan tablas ”con huecos”n En las operaciones de lectura se leen todos los

bloques hasta una marca máximaw Índices sobre tablas volátiles

n Los bloques vacíos van a FREELISTn Es necesario mantener los bloques aunque sólo

tengan una entrada

Problemas en bloques4.1.114.1.11-- Ajuste de rendimientoAjuste de rendimiento

52


w Es posible obtener datos sobre el estado de las tablas e índices para monitorizar y solucionar los problemas de almacenamienton Comando ANALYZE n Paquete DBMS_SPACE

Estadísticas de almacenamiento


BibliografíaBibliografía

w Database systems : a practical approach to design, implementation and management. Thomas M. Connolly. Addison -Wesley. 2005.

w Sistemas de bases de datos : un enfoque práctico para diseño, implementación y gestión. Thomas M. Connolly. Addison -Wesley. 2005.

w Desarrollo de Bases de Datos: casos prácticos desde el análisis a la implementación. Dolores Cuadra, Elena Castro, Ana M. Iglesias, Paloma Martínez, Fco. Javier Calle, César de Pablo, Harith Al-Jumaily y Lourdes Moreno. Editorial Ra-Ma. 2007.

w Database System Implementation. Héctor García-Molina, jeffrey D. Ullman and Jeniffer Widom. Prentice Hall. 2000.

w Physical database design : the database professional's guide to exploiting indexes, views, storage, and more. Sam Lightstone. Morgan Kaufmann/Elsevier. 2007.

w Database administration : the complete guide to practices and procedures. Craig Mullins. Addison-Wesley. 2002.

w Oracle 10g : administración y análisis de bases de datos. César Pérez. Editorial Ra-Ma. 2005.w Database management systems. Raghu Ramakrishnan. McGraw Hill. 2003.

w Fundamentos de Diseño de Bases de Datos. Abrahan Silberschatz and Henry Korth and S. Sudarshan. McGraw Hill. 2007.


Tema IV : Administración de Bases de...

Documents

Transcript of Tema IV : Administración de Bases de...