ETL Oracle: de 4 horas a 25 minutos con staging tables, MERGE y DML paralelo

---
title: "La ventana que se cerraba: cómo reescribimos un ETL Oracle de 4 horas a 24 minutos"
seoTitle: "ETL Oracle lento: de 4 horas a 24 minutos con bulk y parallel DML"
description: "Un batch nocturno que se pasaba de la ventana. AWR, row-by-row, COMMIT frecuente y cero paralelismo. Cómo lo resolvimos con staging, MERGE y parallel DML."
tags: ["oracle-19c", "etl", "performance-tuning", "parallel-dml", "data-warehouse"]
---

La ventana que se cerraba #

El DBA del cliente nos mandó un mensaje lacónico: “El batch de anoche terminó a las 7:12. Los informes de las 7:00 estaban vacíos.”

No era la primera vez. Desde hacía unas semanas el proceso de carga nocturno iba retrasándose — primero tres horas y media, luego casi cuatro, luego más. La ventana batch estaba fijada entre las 23:00 y las 6:30, y el sistema había empezado a pasarse regularmente. Al día siguiente nos sentamos con el DBA del cliente delante de los logs y empezamos a ver qué estaba pasando de verdad.

El contexto: un Data Warehouse Oracle 19c, un proceso ETL legacy escrito en PL/SQL, 15 millones de filas que cargar cada noche desde fuentes operacionales. El volumen no había cambiado de forma significativa respecto al año anterior — había crecido un 12%, nada dramático. La lentitud no era un problema de escala: era un problema de cómo estaba escrito el código.

Lo que contaban los logs #

El primer instrumento que usamos fue AWR [1]. Un informe AWR sobre la ventana nocturna mostraba enseguida dónde iba el tiempo: el top SQL por elapsed time era un bloque PL/SQL con un cursor que iteraba fila a fila sobre 15 millones de registros.

-- patrón original (simplificado) — el problema era exactamente este
FOR rec IN (SELECT * FROM stg_source_data WHERE process_flag = 'N') LOOP
    -- lookup sobre tabla de referencia sin índice
    SELECT dim_id INTO v_dim_id
    FROM dim_customer
    WHERE ext_code = rec.customer_code;

    INSERT INTO fact_sales (
        dim_customer_id, sale_date, amount, product_id
    ) VALUES (
        v_dim_id, rec.sale_date, rec.amount, rec.product_id
    );

    v_count := v_count + 1;
    IF MOD(v_count, 100) = 0 THEN
        COMMIT;
    END IF;
END LOOP;

Tres líneas de código, tres causas de lentitud. Vamos por orden.

Las cuatro causas de un ETL que no llega #

1. INSERT fila a fila (row-by-row = slow-by-slow)

Es una de las frases más citadas en los cursos de Oracle, pero sigue apareciendo en producción. Cada INSERT individual genera un round-trip hacia el buffer cache, actualiza los segmentos de undo y escribe en el redo log. Multiplicado por 15 millones de filas, el coste de contexto por cada operación individual acaba dominando sobre el coste del dato en sí.

La comparación que hicimos internamente: un INSERT ... SELECT bulk sobre 15 millones de filas tarda una fracción del tiempo respecto a 15 millones de INSERT individuales, con los mismos datos. No es una cuestión de IO — es una cuestión de overhead por operación.

2. Lookup sin índice sobre dim_customer

La tabla dim_customer tenía unos 2,8 millones de filas. La columna ext_code — la usada para el join con la fuente — no tenía ningún índice. Cada lookup era un full table scan de 2,8 millones de filas, repetido 15 millones de veces.

AWR mostraba dim_customer como la tabla con el mayor número de logical reads en toda la ventana nocturna. No era casualidad.

3. COMMIT cada 100 filas

El COMMIT frecuente se introduce a menudo con buenas intenciones: “si algo falla, no lo perdemos todo”. En realidad, en Oracle, cada COMMIT tiene un coste nada despreciable: flush del redo log buffer, actualización de los SCN, sincronización con los procesos de background. Hacerlo 150.000 veces por noche (15M / 100) añade un overhead medible y, sobre todo, impide que la base de datos optimice las operaciones en batch.

4. Ningún paralelismo

El proceso era completamente serial: un único proceso PL/SQL, un cursor, un loop. Oracle 19c en ese servidor tenía 16 cores disponibles, pero la carga usaba solo uno durante toda la noche.

La reescritura: staging, MERGE, bulk y parallel #

La estrategia que adoptamos con el equipo se articulaba en cuatro movimientos, en el orden en que los implementamos.

Staging table con bulk load #

El primer paso fue separar la carga de la transformación. Los datos de la fuente se cargan primero en una staging table con un INSERT /*+ APPEND */ ... SELECT — una única operación bulk que bypasea el buffer cache y escribe directamente en los datafiles (direct path insert) [2].

-- carga staging: direct path insert, nologging
INSERT /*+ APPEND PARALLEL(stg_sales_load, 8) */ INTO stg_sales_load
    NOLOGGING
SELECT
    s.customer_code,
    s.sale_date,
    s.amount,
    s.product_id
FROM source_sales_ext s  -- external table o db link
WHERE s.load_date = TRUNC(SYSDATE);

COMMIT;  -- un solo commit tras el bulk

El hint APPEND activa el direct path insert. NOLOGGING reduce la escritura en el redo log (aceptable para una staging table que se recrea cada noche). PARALLEL distribuye el trabajo en 8 procesos paralelos.

Índice sobre dim_customer.ext_code #

Simple, pero necesario. Antes de proceder con cualquier transformación:

CREATE INDEX idx_dim_customer_ext_code
    ON dim_customer (ext_code)
    PARALLEL 4
    NOLOGGING;

Tras la creación, los lookups sobre 2,8 millones de filas pasaron a ser index range scan sobre una columna con alta selectividad. El coste por lookup se desplomó.

MERGE en lugar de INSERT + UPDATE separados #

El proceso original tenía también una lógica de “upsert” implícita: si la fila ya existía en fact_sales (por reprocesados parciales), había que actualizarla; si no, insertarla. El código original gestionaba esto con un SELECT COUNT(*) antes de cada INSERT, añadiendo un round-trip adicional por fila.

La reescritura usa MERGE [3]:

MERGE /*+ PARALLEL(f, 8) */ INTO fact_sales f
USING (
    SELECT
        dc.dim_id AS dim_customer_id,
        stg.sale_date,
        stg.amount,
        stg.product_id
    FROM stg_sales_load stg
    JOIN dim_customer dc ON dc.ext_code = stg.customer_code
) src
ON (
    f.dim_customer_id = src.dim_customer_id
    AND f.sale_date    = src.sale_date
    AND f.product_id   = src.product_id
)
WHEN MATCHED THEN
    UPDATE SET f.amount = src.amount
WHEN NOT MATCHED THEN
    INSERT (dim_customer_id, sale_date, amount, product_id)
    VALUES (src.dim_customer_id, src.sale_date, src.amount, src.product_id);

COMMIT;  -- un solo commit para todo el MERGE

Una sola operación, un solo commit, el join con dim_customer ejecutado una única vez sobre el dataset completo en lugar de 15 millones de veces.

Parallel DML habilitado a nivel de sesión #

Para que el paralelismo funcione en el MERGE, hay que habilitarlo explícitamente [4]:

ALTER SESSION ENABLE PARALLEL DML;

Sin esta instrucción, los hints PARALLEL sobre operaciones DML se ignoran en silencio — un detalle que también nos hizo perder tiempo la primera vez que probamos la reescritura y no veíamos mejoras significativas.

Los números, antes y después #

Ejecutamos tres runs de prueba en un entorno de staging con un dataset real anonimizado (misma cardinalidad, misma distribución de valores).

El redo generado bajó también gracias al NOLOGGING sobre la staging table — que hay que usar con criterio: una staging table NOLOGGING no es recuperable desde un backup incremental tomado durante la carga. En nuestro caso era aceptable porque la staging se recrea desde cero cada noche a partir de la fuente.

El proceso de carga ahora termina a las 00:24. La ventana batch vuelve a tener margen de sobra.

Lo que vale la pena llevarse #

Unas semanas después de la puesta en producción, el DBA del cliente nos mandó otro mensaje — esta vez menos lacónico: “Funciona. Gracias.”

Lo que aprendimos (o mejor dicho, confirmamos) en este proyecto no es nuevo, pero merece escribirse de forma explícita porque sigue apareciendo una y otra vez:

El row-by-row es el asesino silencioso de los ETL legacy. No es obvio hasta que se mira AWR o un trace 10046. El código parece razonable — un loop, un insert, un commit. El problema es que “razonable” no significa “eficiente” cuando se escala a millones de filas.

El COMMIT frecuente no protege: ralentiza. Si el proceso debe poder reanudarse en caso de error, la estrategia correcta es la staging table con un flag de estado — no el commit cada N filas sobre la tabla de destino.

El paralelismo en Oracle requiere configuración explícita. ALTER SESSION ENABLE PARALLEL DML no es opcional si se quieren operaciones DML paralelas. Y los grados de paralelismo hay que calibrarlos sobre el servidor real, no elegirlos al azar.

El MERGE está infrautilizado. Muchos ETL legacy gestionan el upsert con SELECT + INSERT/UPDATE separados. El MERGE hace lo mismo en una sola operación, con un único acceso a la tabla de destino.

El patrón — staging table → transformación con joins indexados → MERGE bulk con parallel DML → commit único — es reutilizable en cualquier ETL Oracle con características similares. No es una solución mágica: requiere entender el perfil del dato (cardinalidad, distribución, frecuencia de actualización) y probar los grados de paralelismo sobre el hardware real. Pero como punto de partida para una reescritura, funciona.

Fuentes oficiales #

1. Oracle Database — Automatic Workload Repository (AWR)
2. Oracle Database — Direct Path INSERT
3. Oracle Database SQL Language Reference 19c — MERGE
4. Oracle Database — Parallel DML

Glosario candidato #

• AWR (Oracle Automatic Workload Repository) — Repositorio de snapshots periódicos de métricas de workload Oracle. Base para los informes AWR y para ADDM. Fundamental para diagnosticar cuellos de botella en ventanas temporales específicas como una noche de batch.

• Direct Path Insert — Modalidad de INSERT en Oracle (activada por el hint APPEND) que bypasea el buffer cache y escribe directamente en los datafiles. Reduce drásticamente el coste de carga bulk, pero requiere atención a la estrategia de backup y recovery.

• MERGE (SQL) — Instrucción SQL que combina INSERT y UPDATE en una única operación atómica (upsert). Realiza un único acceso a la tabla de destino, eliminando el patrón SELECT + INSERT/UPDATE separados típico de los ETL legacy.

• Parallel DML (Oracle) — Ejecución paralela de operaciones DML (INSERT, UPDATE, DELETE, MERGE) sobre múltiples procesos Oracle. Requiere ALTER SESSION ENABLE PARALLEL DML y hints explícitos. Sin la habilitación a nivel de sesión, los hints se ignoran en silencio.

• Staging table — Tabla temporal usada como área de aterrizaje de los datos en bruto antes de la transformación y la carga en el destino final. Permite separar las fases del ETL, gestionar la reanudabilidad y aplicar transformaciones en bulk en lugar de fila a fila.