martes, 11 de octubre de 2011

Recuperar espacio inutilizado de los segmentos SHRINK SPACE

En una base de datos transaccional nos encontraremos con tablas que son afectadas con transacciones insert, update y delete. Las transacciones que nos puede dejar bloques sub-utilizados (Fragmentado) son el delete y update.

Para recuperar este espacio antes de la version 10G de Oracle las técnicas utilizadas eran:

  • Exportar la tabla, truncar la tabla incluido el storage y finalmente importar los datos.
  • Con la ayuda de DML y DDL: CREATE TABLE MI_TABLA_TMP AS SELECT * FROM MI_TABLA, DROP TABLE MI_TABLA y finalmente RENAME TABLE MI_TABLA_TMP TO MI_TABLA
Ya desde la version 10G existe la utilidad SHRINK que nos ayuda mucho con la tarea de recuperar espacio de bloques fragmentados.

 Para explicar como nos va ayudar la utilidad SHRINK primero veamos una pequeña explicación de la estructura de almacenamiento y bloques de datos.

Definición de la Estructura de Almacenamiento
Primero entendamos como esta diseñada la estructura logica de alacenamiento de Oracle.
Estructura Lógica y Física de almacenamiento

Vemos que un tablespace es un conjunto de segmentos, un segmento es un conjunto de extensiones y una extension es un conjunto de bloques( la minima expresión lógica de almecenamiento).

Bloque de Datos
Nuestro estudio se centrará en el comportamiento de los bloques de datos, por lo que debemos entender su estructura, para ello a continuación presentaré de manera breve su estructura:
Estructura del Bloque de Datos Oracle

 Son cinco partes fundamentales que conforman un bloque de datos:
  1. Cabecera: En esta sección del bloque se almacena la información genera y de control del bloque como: Tipo de segmento (de tabla, indice, lob, rollback, cluster, etc) e información de la ubicación del bloque, número de sincronización.
  2. Directorio de Tabla: Contiene información de la tabla relaciona a la fila almacenada al bloque.
  3. Directorio de Fila: Contiene información de las filas almacendas en el bloque (dirección para cada fila de la tabla almacenada en el bloque).
  4. Espacio Libre: Esta sección esta destinada para los nuevos datos o actualizaciones de datos que requieran otorgar (Siempre y cuando exista disponivilidad de espacio en el bloque). Para el caso de una actualización esta puede incrementar o descrementar el espacio disponible del bloque.
  5. Datos: Esta es la sección en donde se almacena la los datos de las tablas o indices. Los datos de un registro puede encontrarse la necesidad de que ocupen mas de un bloque, esto puede suceder por dos situciones que son: Encadenamiento y Migracion de Filas.

Ahora si con la breve explicación de as esctructuras fisicas y lógicas que Oracle maneja ahora entendamos que es lo que sucede cuando se crea una tabla. Cuando se crea una tabla, a esta se le indica que en que tablespace se almacenará y de manera automatica se asigna a un segmento llamado segmento de tabla y para los indices es igual con un numero inicial de extensiones.

A medida que se insertan datos y se va agotando el espacio asignado a la tabla en el segmento, automaticamente se va asignando al segmentos mas extensiones para dar mas espacio disponible para los datos nuevos.

En la siguiente vista vemos los segmentos y extents asignado a las tabla o indice:
  • select * from dba_segments where segment_name='nombre tabla o indice';
  • select * from dba_extents where segment_name='nombre tabla o indice';
Donde segment_name es el nombre de la tabla o indice.

Comportamiento de una tabla que subre insert y delete
En el siguiente ejercicio veremos como se comporta el almacenamiento asignado a una tabla realizando varias transacciones insert y delete.

 drop table mi_tabla purge;
----------------------------------------------------------
--Creamos la tabla
----------------------------------------------------------create table mi_tabla
(
    numero number,
    es_par varchar2(1)
)
PCTFREE 0
tablespace ts_datos;


--Realicemos un analisis del espacio utilizado, para esto
--usaremos el procedimiento P$ANALIZA_ESPACIO que dará un
--resumen del almacenamiento
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );

----------------------------------------------------------
ASIGNADO
Bloques: 8
KBytes: 64.00
USADO
Bloques: 3
KBytes: 24.00
LIBRE
Bloques: 5
KBytess: 40.00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 0
Registros KBytes: .00
ANALISIS
% SUB UTILIZADO: 100.00

Vemos que % SUB UTILIZADO nos da 100, pero no hay problema, ya que recien creamos tabla.

----------------------------------------------------------
--Ahora realicemos inserción de datos
----------------------------------------------------------
insert into mi_tabla
select rownum, mod(rownum,2)
from dual
connect by level<=1000000;

commit;
Un millon de registros insertados

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 1000000
Registros KBytes: 9,765.63
ANALISIS
% SUB UTILIZADO: 20.53

Ahora vemos que el % SUB UTILIZADO es de 20.53, pero no es problema ya que es un valor aceptable considerando que existe entre un 10 y 15 % de espacio del bloque que es usado para controles internos de oracle. Vemos que la cantidad de registros es de un millon y el tamaño en KB de los registro es de 9,765.63

----------------------------------------------------------
--Ahora realicemos borrado de datos
----------------------------------------------------------
delete mi_tabla where es_par=0;
commit;
Quinientos mil registros borrados

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 60.26

Nos damos cuenta que lo único que vario es la cantidad de registros y el el % SUB UTILIZADO de 60.26 (Alta fragamentación)

Ahora si ponemos en práctica el uso del SHRINK, pero tenemos dos formas de utilizarlo:

SHRINK SPACE COMPACT

--------------------------------------------------------------
--Previo a esto habilitamos el movimiento de filas en la tabla
--y realizamos el shrink space compact
--------------------------------------------------------------
alter table mi_tabla enable row movement;
alter table mi_tabla shrink space compact;

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 1536
KBytes: 12,288.00
USADO
Bloques: 1536
KBytes: 12,288.00
LIBRE
Bloques: 0
KBytess: .00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 60.26
SHRINK SPACE

--------------------------------------------------------------
--Previo a esto habilitamos el movimiento de filas en la tabla
--y realizamos el shrink space
--------------------------------------------------------------
alter table mi_tabla enable row movement;
alter table mi_tabla shrink space;

--Realicemos un analisis del espacio utilizado
call P$ANALIZA_ESPACIO ( 'SISOWN', 'MI_TABLA', 'TABLE' );
----------------------------------------------------------
ASIGNADO
Bloques: 768
KBytes: 6,144.00
USADO
Bloques: 764
KBytes: 6,112.00
LIBRE
Bloques: 4
KBytess: 32.00
INFORMACION REGISTROS ALMACENADOS
Total Registros: 500000
Registros KBytes: 4,882.81
ANALISIS
% SUB UTILIZADO: 20.11

  • SHRINK SPACE COMPACT Se compactan los bloques, dejando lleno los que estan a medio uso y dejando libre los demas bloques pero siguen asignados al segmento tabla o indice.

  • SHRINK SPACE Se compactan los bloques, dejando lleno los que estan a medio uso y dejando libre los demas bloques y se libera la asignación al segmento de las extensiones libres.
Al realizar el SHRINK SPACE notamos que los valores de ASIGNADO, USADO y % SUB UTILIZADO disminuyeron, esto ya que se compactaron y liberaron del segmento los bloques libres.

Otras opciones del SHRINK
ALTER INDEX MI_INDICE SHRINK SPACE Tambien podeis aplicar el SHRINK en los indices.

ALTER TABLE MI_TABLA SHIRINK SPACE CASCADE. Realizas el SHRINK a todos los indices relacionados a las tablas.

Restricciones:
No puedes usarlo en tablas clusterizadas o que contengan columnas LONG.
No funciona en indices basados en función o indices bimap.
No puedes usar esta clausula para tabla comprimidas.

Consideraciones.
Tener cuidado al realizar el ALTER TABLE ENABLE ROW MOVEMENT ya que esto afecta a los triggers asociados a las tablas que hacen referencia a :OLD ya que las filas estan en movimiento.

10 comentarios:

  1. Te felicito , una explicacion muy clara y entendible.

    ResponderEliminar
  2. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  3. ¿Podrías explicar un poco más lo que quieres decir con:

    "Consideraciones.
    Tener cuidado al realizar el ALTER TABLE ENABLE ROW MOVEMENT ya que esto afecta a los triggers asociados a las tablas que hacen referencia a :OLD ya que las filas estan en movimiento."?

    ResponderEliminar
    Respuestas
    1. Saludos, en version 10 al ejecutar el alter te va a generar descompilación de los objetos asociados a la tabla.
      Hay que tener cuidado en no ejecutar en un momento de alta transaccionalidad debido debido al que el comando shrink ocupara recursos(La carga dependera de varios factores como, tamaño de la tabla, tamaño de la memoria libre y procesadores).

      Si ejecutas un shrink en el momento en que se estan realizando DML (insert, update, delete) a tablas que tienen trigger podría causar errores en referencias OLD o NEW debido a que se realizan movimiento de filas que haran cambiar el ROWID de los datos referenciados en OLD o NEW

      Eliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. Muchas gracias,

    Los dos graficos que pusiste, son los que deberian estar en la documentacion de oracle. Era lo que buscaba, gracias.

    ResponderEliminar
  6. tienes algun script, para sacar la fragmentacion de las tablas e indices?

    ResponderEliminar
  7. Hola, es necesario realizar luego del SHRINK, un rebuild o coalesce de los indices relacionados con una tabla?.
    Muchas gracias y te felicito por tus notas.

    ResponderEliminar
  8. Una duda, urgente tengo una tabla con un campo BLOB, puedo hacerlo?
    Otra si no es en cascade lo hace sobbretodos los indices incluido el de BLOB? y los trigrers=

    ResponderEliminar