Segment Space Management y High Watermark en Oracle 10g

Cualquiera que haya trabajado con tablas que tienen gran cantidad de ‘INSERTs’ y ‘DELETEs’ -la típica base de datos ‘OLTP’- habrá podido observar que el High Watermark (HWM) es algo que hay que tener muy en cuenta. Cuando el acceso a una tabla se hace por medio de un ‘FULL SCAN’ Oracle lee todos los bloques que están por debajo de dicho ‘HWM’. El típico caso de una tabla de la cual se han se han borrado una gran cantidad de filas es el ejemplo más claro. No importa cuantas filas puede tener en el momento en el que hacemos la consulta, si en el pasado llegó a ser muy grande (y por tanto su HWM es muy alto) en el caso de un ‘FULL SCAN’ Oracle leerá todos y cada uno de los bloques que alguna vez fueron ocupados por la tabla.

Cualquier DBA sabe que esto se puede corregir de una de las dos siguientes maneras:

·Reconstruyendo la tabla ‘desde cero’. (Con una tabla auxuliar, TRUNCATE, etc…)
·Haciendo un ALTER TABLE … MOVE

Pero Oracle 10g incorpora una nueva funcionalidad que alivia la tarea de compactar los datos y bajar el HWM mediante las cláusulas ENABLE ROW MOVEMENT, SHRINK SPACE COMPACT y SHRINK SPACE.

Veamos como:

Primero crearemos una tabla que ocupe varios ‘extents’. Luego borraremos un gran número de filas y veremos cómo la ocupación del segmento de la tabla no varía.

carlos@bd01.xxxxxxxx> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlos@bd01.xxxxxxxx> CREATE TABLE PRUEBA09 AS
  2  SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
  3    FROM ALL_OBJECTS
  4  /

Tabla creada.

carlos@bd01.xxxxxxxx> SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
------------ ------------ --------------- --------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -------
PRUEBA09     TABLE        TBS1               458752         56          7          65536                       1  2147483645                                         DEFAULT

carlos@bd01.xxxxxxxx> SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID      BYTES     BLOCKS
------------ ------------ --------------- --------- ---------- ----------
PRUEBA09     TABLE        TBS1                    0      65536          8
PRUEBA09     TABLE        TBS1                    1      65536          8
PRUEBA09     TABLE        TBS1                    2      65536          8
PRUEBA09     TABLE        TBS1                    3      65536          8
PRUEBA09     TABLE        TBS1                    4      65536          8
PRUEBA09     TABLE        TBS1                    5      65536          8
PRUEBA09     TABLE        TBS1                    6      65536          8

7 filas seleccionadas.

Como vemos, la tabla ocupa 56 bloques (‘blocks’) en siete ‘extents’. Esto fija su HWM.

El ‘tablespace’ es un LMT gestionado por el sistema:


carlos@bd01.xxxxxxxx> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TBS1'
  2  /

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG
--------------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
TBS1                  8192          65536                       1  2147483645                65536 ONLINE    PERMANENT LOGGING   NO  LOCAL         SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO

Vamos a seleccionar una fila, que nos servirá luego para verificar cierto aspecto del proceso (Hay que fijarse en el ROWID de la fila).


carlos@bd01.xxxxxxxx> SELECT ROWID, OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
  2    FROM PRUEBA09
  3   WHERE OBJECT_ID = 5000
  4  /

ROWID               OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
------------------ ---------- ------------------------------ -------------------
AAAKJPAAEAAAAg5AB3       5000 USER_BASE_TABLE_MVIEWS         SYNONYM

Ahora procedemos a borrar una gran cantidad de las filas:

carlos@bd01.xxxxxxxx> DELETE FROM PRUEBA09
  2   WHERE MOD(OBJECT_ID,10) != 0
  3  /

9138 filas suprimidas.

carlos@bd01.xxxxxxxx> COMMIT
  2  /

Confirmación terminada.

carlos@bd01.xxxxxxxx>

Verificamos que el segmento de la tabla sigue ocupando lo mismo que antes del borrado:


carlos@bd01.xxxxxxxx> SELECT * FROM USER_EXTENTS
  2 WHERE SEGMENT_NAME='PRUEBA09'
  3 /

SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
------------ ------------------ ------------------------------ ---------- ---------- ----------
PRUEBA09     TABLE              TBS1                                    0      65536          8
PRUEBA09     TABLE              TBS1                                    1      65536          8
PRUEBA09     TABLE              TBS1                                    2      65536          8
PRUEBA09     TABLE              TBS1                                    3      65536          8
PRUEBA09     TABLE              TBS1                                    4      65536          8
PRUEBA09     TABLE              TBS1                                    5      65536          8
PRUEBA09     TABLE              TBS1                                    6      65536          8

7 filas seleccionadas.

Ahora utilizamos la nueva técnica de Oracle 10g:

Primero hay que habilitar el ‘row movement’ para la tabla:


carlos@bd01.xxxxxxxx> ALTER TABLE PRUEBA09 ENABLE ROW MOVEMENT
  2  /

Tabla modificada.

Luego, como primera fase, efectuamos el ‘shrink space compact’. Esto compactará los bloques: las filas se agruparán en los bloques más ‘bajos’ llenándolos completamente y dejando vacíos los más ‘altos’, pero NO BAJARÁ el HWM:


carlos@bd01.xxxxxxxx> ALTER TABLE PRUEBA09 SHRINK SPACE COMPACT
  2  /

Tabla modificada.

carlos@bd01.xxxxxxxx> SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
------------ ------------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -------
PRUEBA09     TABLE        TBS1                458752         56          7          65536                       1  2147483645                                         DEFAULT

carlos@bd01.xxxxxxxx> SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID      BYTES     BLOCKS
------------ ------------ --------------- ---------- ---------- ----------
PRUEBA09     TABLE        TBS1                     0      65536          8
PRUEBA09     TABLE        TBS1                     1      65536          8
PRUEBA09     TABLE        TBS1                     2      65536          8
PRUEBA09     TABLE        TBS1                     3      65536          8
PRUEBA09     TABLE        TBS1                     4      65536          8
PRUEBA09     TABLE        TBS1                     5      65536          8
PRUEBA09     TABLE        TBS1                     6      65536          8

7 filas seleccionadas.

Lo que ha ocurrido es que los bloques ocupados se han compactado, dejando los bloques libres en los ‘extents’ más ‘altos’ justo por debajo del HWM. Se ve así que el segmento de la tabla no ha variado ‘cuantitativamente’, aunque sí ‘cualitativamente’.

Ahora viene el asunto del HWM. Esto lo hacemos con:

carlos@bd01.xxxxxxxx> ALTER TABLE PRUEBA09 SHRINK SPACE
  2  /

Tabla modificada.

La verdad es que aquí Oracle podría haber elegido otra sintaxix un poco más clara, ya que ‘SHRINK SPACE’ y ‘SHRINK SPACE COMPACT’ pueden dar pie a confusión.
En cualquier caso, ahora vemos los resultados:

carlos@bd01.xxxxxxxx> SELECT * FROM USER_SEGMENTS
  2 WHERE SEGMENT_NAME='PRUEBA09'
  3 /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
------------ ------------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -------
PRUEBA09     TABLE        TBS1                 65536          8          1          65536                       1  2147483645                                         DEFAULT

carlos@bd01.xxxxxxxx>  SELECT * FROM USER_EXTENTS
  2 WHERE SEGMENT_NAME='PRUEBA09'
  3 /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID      BYTES     BLOCKS
------------ ------------ --------------- ---------- ---------- ----------
PRUEBA09     TABLE        TBS1                     0      65536          8

Ahora sí tenemos los bloques ‘compactados’ y además el HWM ha bajado. (Sólo hay un ‘extent’, donde antes había siete). Los ‘FULL SCANs’ serán ahora más efectivos para esta tabla.

Por último veamos la ‘SELECT’ sobre el objeto 5000:

carlos@bd01.xxxxxxxx> SELECT ROWID, OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
  2    FROM PRUEBA09
  3   WHERE OBJECT_ID = 5000
  4  /

ROWID               OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
------------------ ---------- ------------------------------ -------------------
AAAKJPAAEAAAAgmAC2       5000 USER_BASE_TABLE_MVIEWS         SYNONYM

carlos@bd01.xxxxxxxx>

Vemos que su ROWID ha cambiado (lo cual es lógico, dada la naturaleza del ROWID). Esto no deja de ser otra razón más en contra de utilizar el ROWID como identificador único e inmutable de una fila.

Nota: Esta funcionalidad sólo está disponible en segmentos que estén ubicados en ‘tablespaces’ con ‘ASSM’ (automatic segment storage management – mantenimiento automático de almacenamiento de segmento).

Saludos.

Carlos.

8 respuestas a Segment Space Management y High Watermark en Oracle 10g

  1. Juan Perez dice:

    Genial, me ha parecido un paso gigante, ahora lo pongo en practica.

  2. roque dice:

    muy bueno gracias por compartir tus conocimientos

  3. renpostit dice:

    magistral explicacion

  4. Carlos J. Gil Bellosta dice:

    Hola… Muy bueno tu blog…

    Una pregunta: ¿sabes cómo se hace esto mismo con Teradata?

  5. Jesús dice:

    excelente, gracias me es de mucha ayuda

  6. Jorems dice:

    Oiga amigo, que bien…. que destreza, felicitaciones y muchas gracias, es de una ayuda invaluable tus articulos

  7. eusebio dice:

    Hola Carlos, he comprobado esto pero creo que para que funcione el automatic segment storage management debe estar en Manual, por lo menos así me ha pasado con campos BLOB. Es cierto esto?

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: