Corregir la fragmentación y reducir el High Water Mark (HWM) con SHRINK SPACE
En la administración de bases de datos Oracle, un escenario común de degradación de rendimiento ocurre cuando una tabla que solía albergar millones de registros sufre un proceso masivo de depuración (DELETE). A pesar de que los datos ya no residen en el disco, las consultas tipo Full Table Scan (FTS) sobre dicha tabla continúan demorando el mismo tiempo o consumiendo una cantidad idéntica de operaciones de E/S (I/O).
¿Por qué sucede esto? La respuesta se encuentra en el High Water Mark (HWM) o la "marca de agua" del segmento. En este artículo analizaremos conceptualmente este comportamiento, construiremos un laboratorio en la versión 19c para simular la fragmentación y demostraremos cómo diagnosticar y compactar el espacio desperdiciado utilizando herramientas nativas.
¿Qué es el High Water Mark?
El HWM es un puntero dentro de la estructura de almacenamiento de Oracle que define el límite hasta donde se han escrito bloques de datos en un segmento.
Cuando se realizan operaciones de
INSERT, el HWM sube de manera dinámica para asignar nuevos bloques.Cuando se realiza una operación de
DELETE, los registros se borran lógicamente y los bloques quedan vacíos, pero el HWM no se reduce.
Dado que un Full Table Scan lee obligatoriamente todos los bloques por debajo del HWM (asumiendo que contienen datos válidos), Oracle terminará leyendo bloques vacíos de manera innecesaria, degradando el rendimiento de los queries y desperdiciando memoria en el Buffer Cache.
Simulación de Fragmentación
-- Creamos una tabla basada en los objetos del diccionario para simular volumenCREATE TABLE t_hwm_test AS SELECT rownum as id, a.* FROM all_objects a, (SELECT 1 FROM dual CONNECT BY level <= 10);-- Contamos la cantidad de registros creados en la tabla
SELECT COUNT(*) FROM t_hwm_test;-- Verificamos el tamaño inicial del segmento y bloques asignadosSELECT blocks, bytes/1024/1024 AS MB FROM user_segments WHERE segment_name = 'T_HWM_TEST';
Procederemos a eliminar el 90% de los registros de la tabla para inducir una fragmentación severa del segmento.
-- Eliminamos la gran mayoría de los registros
DELETE FROM t_hwm_test WHERE MOD(id, 10) != 0;
COMMIT;
-- Ejecutamos estadísticas para actualizar el diccionario de datos
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_HWM_TEST');
Si consultamos las vistas
USER_TABLES (datos lógicos) y USER_SEGMENTS (datos físicos), notaremos la discrepancia:SELECT t.table_name, t.num_rows, t.blocks AS blocks_with_data, s.blocks AS total_allocated_blocks FROM user_tables t JOIN user_segments s ON t.table_name = s.segment_name WHERE t.table_name = 'T_HWM_TEST';
Para no depender exclusivamente de la ejecución de estadísticas de las tablas, podemos utilizar el paquete nativo DBMS_SPACE.UNUSED_SPACE para obtener el estado del HWM en tiempo real.
Ejecutaremos el siguiente bloque de código en nuestra sesión:
DECLARE
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER;
v_last_used_extent_block_id NUMBER;
v_last_used_block NUMBER;
BEGIN
DBMS_SPACE.UNUSED_SPACE(
segment_owner => USER,
segment_name => 'T_HWM_TEST',
segment_type => 'TABLE',
total_blocks => v_total_blocks,
total_bytes => v_total_bytes,
unused_blocks => v_unused_blocks,
unused_bytes => v_unused_bytes,
last_used_extent_file_id => v_last_used_extent_file_id,
last_used_extent_block_id => v_last_used_extent_block_id,
last_used_block => v_last_used_block
);
DBMS_OUTPUT.PUT_LINE('Bloques Totales Asignados: ' || v_total_blocks);
DBMS_OUTPUT.PUT_LINE('Bloques sobre el HWM (Sin usar): ' || v_unused_blocks);
DBMS_OUTPUT.PUT_LINE('Bloques por debajo del HWM (HWM Actual): ' || (v_total_blocks - v_unused_blocks));
END;
/
Compactación del Segmento con SHRINK SPACE
A partir de la introducción de los tablespaces con administración automática de espacio de segmentos (ASSM), Oracle provee una instrucción en línea (
ONLINE) extremadamente eficiente para reorganizar las filas, liberar el espacio desperdiciado y reajustar el HWM hacia abajo: el comando SHRINK.El proceso consta de dos fases lógicas ejecutadas internamente por el motor:
Fase de movimiento de filas (Row Movement): Desplaza las filas de la parte superior del segmento hacia los huecos libres en la parte inferior.
Fase de ajuste del HWM: Bloquea brevemente la tabla para bajar el puntero del HWM y liberar el espacio sobrante al tablespace.
ROWID, es obligatorio activar la propiedad ROW MOVEMENT en la tabla:ALTER TABLE t_hwm_test ENABLE ROW MOVEMENT;
Procedemos a ejecutar el shrink. Este comando liberará el espacio de vuelta al tablespace de forma inmediata.
ALTER TABLE t_hwm_test SHRINK SPACE;
ALTER TABLE t_hwm_test SHRINK SPACE COMPACT; para realizar solo la fase 1 sin bloquear el HWM, y posteriormente ejecutar el comando completo en una ventana de mantenimiento corta.EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_HWM_TEST');
-- Verificamos el total de bloques de la tabla nuevamente
El total_allocated_blocks se redujo drásticamente de 20480 que tenia inicialmente a 2048, alineándose al volumen real actual de la tabla.Consideraciones
El comando SHRINK SPACE es la herramienta ideal para combatir la fragmentación en bases de datos modernas, sin embargo, como DBAs debemos considerar las siguientes pautas antes de su aplicación en producción:
Índices: A diferencia de un
ALTER TABLE MOVE, el comandoSHRINK SPACEmantiene los índices de la tabla en estadoVALID, ya que actualiza de manera interna las modificaciones de losROWID. No es necesario un rebuild posterior.Restricciones: No se puede aplicar
SHRINKsobre tablas con índices basados en funciones, vistas materiales con logs de refresco o tablas que contengan columnas de tipoLONG.Alternativa Tradicional: Si el tablespace no cuenta con ASSM (lo cual es raro en versiones 19c/23ai), la alternativa obligada sigue siendo el clásico
ALTER TABLE MOVEseguido de unALTER INDEX REBUILD.
