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
Para demostrar este impacto, crearemos una tabla de pruebas, la poblaremos con un volumen considerable de datos, realizaremos una depuración masiva y mediremos el comportamiento de los bloques.
CREATE 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;
SELECT 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.
DELETE FROM t_hwm_test WHERE MOD(id, 10) != 0;
COMMIT;
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.
Para poder reubicar físicamente los registros y alterar sus 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;
Nota: Si se tratara de una tabla crítica en un entorno productivo de alta concurrencia, podemos ejecutar 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.
Volvemos a recopilar estadísticas y evaluamos nuevamente el estado físico de la tabla en el diccionario de datos.
-- Ejecutamos nuevamente estadísticas sobre la tabla
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_HWM_TEST');
-- Verificamos el total de bloques de la tabla nuevamente
SELECT t.table_name, t.num_rows, 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';
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 comando SHRINK SPACE mantiene los índices de la tabla en estado VALID, ya que actualiza de manera interna las modificaciones de los ROWID. No es necesario un rebuild posterior.
Restricciones: No se puede aplicar SHRINK sobre tablas con índices basados en funciones, vistas materiales con logs de refresco o tablas que contengan columnas de tipo LONG.
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 MOVE seguido de un ALTER INDEX REBUILD.
Comprender el comportamiento del High Water Mark en Oracle 19c es fundamental para evitar diagnósticos erróneos ante caídas de rendimiento repentinas tras procesos de purga masiva. Como DBAs, nuestro rol no solo consiste en liberar espacio en disco, sino en garantizar la eficiencia de las operaciones de E/S.
Saludos.
