Speeding Up a Slow PL/SQL Routine with BULK COLLECT and FORALL

#database #sql #performance #tutorial

The single most common performance problem I have run into in Oracle PL/SQL is not a missing index or a bad join. It is a loop that processes one row at a time. The code reads fine, it passes review, and then it runs for forty minutes on a table that has grown past what anyone tested against. This tutorial walks through why that happens and how BULK COLLECT and FORALL fix it, usually by an order of magnitude, without changing what the routine actually does.

The problem: the context switch

When a PL/SQL block runs a SQL statement, control passes from the PL/SQL engine to the SQL engine and back. That handoff is called a context switch, and on its own it is cheap. The trouble is doing it inside a loop, once per row. A cursor FOR loop that inserts each fetched row looks harmless:

BEGIN
  FOR rec IN (SELECT id, amount FROM staging_payments) LOOP
    INSERT INTO payments (id, amount, loaded_at)
    VALUES (rec.id, rec.amount, SYSDATE);
  END LOOP;
  COMMIT;
END;

For a few hundred rows this is fine. For a few million, you are paying for millions of context switches, and that overhead — not the actual insert work — becomes the bottleneck.

Step 1: fetch in batches with BULK COLLECT

Instead of fetching one row per round trip, BULK COLLECT pulls many rows into a collection in a single switch. The key detail people miss is the LIMIT clause: without it, you load the entire result set into memory at once, which can blow up PGA on a large table. Batching in chunks keeps memory bounded.

DECLARE
  CURSOR c IS SELECT id, amount FROM staging_payments;

  TYPE t_ids IS TABLE OF staging_payments.id%TYPE;
  TYPE t_amts IS TABLE OF staging_payments.amount%TYPE;

  v_ids  t_ids;
  v_amts t_amts;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids, v_amts LIMIT 5000;
    EXIT WHEN v_ids.COUNT = 0;
    -- v_ids / v_amts now hold up to 5000 rows fetched in one context switch
  END LOOP;
  CLOSE c;
END;

Note the %TYPE anchors on the collection element types — if the column definition changes, the code follows it instead of breaking. And place the EXIT WHEN v_ids.COUNT = 0 right after the fetch so the last, partial batch is still processed before you leave the loop.

Step 2: write in batches with FORALL

FORALL is the write-side counterpart. It sends an entire collection to the SQL engine in one statement instead of looping in PL/SQL. Putting the two together:

DECLARE
  CURSOR c IS SELECT id, amount FROM staging_payments;

  TYPE t_ids IS TABLE OF staging_payments.id%TYPE;
  TYPE t_amts IS TABLE OF staging_payments.amount%TYPE;

  v_ids  t_ids;
  v_amts t_amts;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids, v_amts LIMIT 5000;
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1 .. v_ids.COUNT
      INSERT INTO payments (id, amount, loaded_at)
      VALUES (v_ids(i), v_amts(i), SYSDATE);

    COMMIT;  -- commit per batch, not per row
  END LOOP;
  CLOSE c;
END;

The routine does exactly the same thing as the original — read staging rows, insert them into payments — but instead of millions of context switches it now makes a handful per batch. On real workloads this is routinely the difference between minutes and seconds.

Step 3: don't let one bad row kill the batch

A FORALL insert stops on the first error by default, which means one malformed row can abort a 5000-row batch. SAVE EXCEPTIONS lets the batch finish and collects the failures so you can log them instead of losing the whole run:

    BEGIN
      FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
        INSERT INTO payments (id, amount, loaded_at)
        VALUES (v_ids(i), v_amts(i), SYSDATE);
    EXCEPTION
      WHEN OTHERS THEN
        FOR e IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(
            'row ' || SQL%BULK_EXCEPTIONS(e).ERROR_INDEX ||
            ' failed: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(e).ERROR_CODE));
        END LOOP;
    END;

Now a handful of bad rows get reported by index and reason, and the millions of good rows still load.

When not to reach for this

The honest caveat: if the whole thing can be expressed as a single set-based statement — INSERT INTO payments SELECT ... FROM staging_payments — do that instead. Pure SQL avoids the PL/SQL engine entirely and is almost always fastest. BULK COLLECT and FORALL earn their keep when you genuinely need procedural logic in the middle — per-row transformation, calling a function, conditional routing — that can't be pushed down into one SQL statement. In that case, batching is what turns an overnight job back into a coffee-break one.

DEV Community