Top 7 PL/SQL Performance Mistakes with BULK COLLECT, FORALL and Pipelined Functions

Introduction: Why PL/SQL Performance Mistakes Hurt at Scale

When I first started tuning server-side PL/SQL, I underestimated how much damage tiny inefficiencies could do once code hit production volumes. A single BULK COLLECT into memory, a careless FORALL, or an overused pipelined function might look harmless in a unit test. But under real load—millions of rows, many concurrent sessions—these PL/SQL performance mistakes can quickly become your biggest bottlenecks.

The problem is that these features are specifically designed for high throughput. If they’re misused—fetching too much into memory, committing too often inside loops, or forcing unnecessary context switches between SQL and PL/SQL—the overhead multiplies with every batch. In my experience, one poorly written bulk operation can consume more CPU than the rest of the application combined.

In the following sections, I’ll break down seven common pitfalls with BULK COLLECT, FORALL and pipelined functions, and show the practical patterns I now use to keep PL/SQL fast and predictable at scale.

1. Using BULK COLLECT Without LIMIT and Blowing Up PGA

One of the most painful PL/SQL performance mistakes I see is a straight BULK COLLECT that tries to load an entire table into memory in a single shot. It’s fast in development with a few thousand rows, but when the table reaches tens of millions of rows, the session’s PGA explodes, Oracle starts spilling to TEMP, and response times crawl.

The core issue is that an unbounded BULK COLLECT gives the optimizer permission to fetch everything into RAM at once. On busy systems, I’ve seen this take down whole nodes when multiple sessions run the same bad code concurrently.

Why unbounded BULK COLLECT is dangerous

Here’s a typical anti-pattern I still run into:

DECLARE
  TYPE t_tab IS TABLE OF my_table%ROWTYPE;
  l_data t_tab;
BEGIN
  SELECT * BULK COLLECT INTO l_data
  FROM   my_table;  -- No WHERE clause, no LIMIT

  -- Process l_data here
END;
/

This looks clean, but it has three big risks in production:

• PGA bloat: All rows live in memory at once, often measured in gigabytes.
• TEMP spills: When PGA can’t cope, you get disk I/O, undoing any benefit of bulk operations.
• Unpredictable behavior: A job that ran fine last year can suddenly fail as data grows.

Safer pattern: BULK COLLECT with LIMIT

What’s worked well for me is a simple pattern: always process in manageable batches using LIMIT. This keeps memory usage flat no matter how big the table grows.

DECLARE
  CURSOR c_data IS
    SELECT *
    FROM   my_table;

  TYPE t_tab IS TABLE OF my_table%ROWTYPE;
  l_data   t_tab;
  c_limit  PLS_INTEGER := 5000; -- tune based on your environment
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data BULK COLLECT INTO l_data LIMIT c_limit;
    EXIT WHEN l_data.COUNT = 0;

    -- Process current batch
    FOR i IN 1 .. l_data.COUNT LOOP
      -- do something with l_data(i)
      NULL;
    END LOOP;
  END LOOP;
  CLOSE c_data;
END;
/

In my experience, this approach gives you three benefits: predictable PGA usage, smoother performance under concurrent load, and code that scales as data grows without surprises. I usually start with a LIMIT between 1,000 and 10,000 and adjust based on PGA headroom and elapsed time.

If you also combine this pattern with FORALL for DML, you can keep both memory and redo under control while still enjoying the speed of bulk operations. The key is to think in batches, not in “load everything” terms, especially when you don’t know how big “everything” will be in two years.

For a deeper explanation of Oracle PGA management and how BULK COLLECT interacts with it, you can read more at Problem with BULK COLLECT with million rows – Ask TOM.

2. Misusing FORALL for Simple Single-SQL Operations

One of the subtler PL/SQL performance mistakes I see is developers reaching for FORALL even when a single set-based SQL statement would do the whole job. When I first got into bulk processing, I was guilty of this myself—assuming that “more FORALL” automatically meant “more performance”. It doesn’t.

FORALL shines when you truly need many independent row operations. But if the logic boils down to a single INSERT or UPDATE with a clean WHERE or VALUES clause, you’re usually better off staying in pure SQL and letting the optimizer do its job.

Example: FORALL vs pure set-based SQL

Here’s an anti-pattern I’ve had to refactor in audits:

DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  l_ids t_ids := t_ids(1,2,3,4,5);
BEGIN
  FORALL i IN l_ids.FIRST .. l_ids.LAST
    UPDATE orders
       SET status = 'CLOSED'
     WHERE order_id = l_ids(i);
END;
/

This loops in PL/SQL, causing extra context switches and code complexity. The same work is cleaner and often faster as a single SQL statement:

UPDATE orders
   SET status = 'CLOSED'
 WHERE order_id IN (1,2,3,4,5);

In my experience, a good rule of thumb is: if you can express the requirement as one set-based SQL statement without losing clarity, do that first. Reserve FORALL for cases where each row truly needs different values or when you’re bridging from PL/SQL collections to DML in a way SQL alone can’t easily express. For more nuanced guidelines on when to use bulk binds versus pure SQL, see More Coding Best Practices – PL/SQL tips – posting rows quickly from an array & more.

3. Ignoring SAVE EXCEPTIONS and Losing Insight into Failures

One of the more painful PL/SQL performance mistakes I’ve had to clean up is a bulk FORALL that fails on a single bad row and aborts the entire batch. Without SAVE EXCEPTIONS, you get one error, one failure, and zero insight into which individual rows caused trouble. Teams then rerun the same job again and again, often adding logging or manual checks that slow things down even further.

What happens when you skip SAVE EXCEPTIONS

Here’s the typical pattern I see in real code:

DECLARE
  TYPE t_orders IS TABLE OF orders%ROWTYPE;
  l_orders t_orders;
BEGIN
  -- l_orders is populated earlier

  FORALL i IN l_orders.FIRST .. l_orders.LAST
    INSERT INTO orders VALUES l_orders(i);  -- Any bad row kills the whole batch

  COMMIT;
END;
/

If a single row violates a constraint, Oracle raises an exception and the entire bulk insert stops. You don’t know which rows were applied, which failed, or why. In my experience, this leads to slow, cautious reruns, extra queries to figure out partial progress, and a lot of frustration in production support.

Safer pattern: SAVE EXCEPTIONS with row-level diagnostics

What works much better for me is wrapping FORALL with SAVE EXCEPTIONS and then iterating over SQL%BULK_EXCEPTIONS to capture row-level details:

DECLARE
  TYPE t_orders IS TABLE OF orders%ROWTYPE;
  l_orders t_orders;

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  -- l_orders is populated earlier

  FORALL i IN l_orders.FIRST .. l_orders.LAST SAVE EXCEPTIONS
    INSERT INTO orders VALUES l_orders(i);

  COMMIT;
EXCEPTION
  WHEN e_bulk_errors THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.put_line(
        'Index=' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
        ' Code='  || SQL%BULK_EXCEPTIONS(j).ERROR_CODE
      );
    END LOOP;
END;
/

With this approach, most valid rows succeed in one fast pass, and only the problematic ones are isolated for review or separate processing. In my experience, this not only keeps bulk operations efficient, it also turns post-failure analysis from a multi-hour detective job into a quick, focused check.

4. Treating Pipelined Functions Like Generic APIs Instead of Streaming

When I review database-heavy systems, I often find pipelined table functions used as “generic APIs” that hide complex logic, rather than as lightweight streaming operators. That’s one of the most underestimated PL/SQL performance mistakes: turning a streaming mechanism into a batch processor with extra overhead on every row.

The anti-pattern: bulk-load then pipe

A common smell is seeing a pipelined function that first does a big BULK COLLECT into memory and then loops to PIPE ROW each element. At that point, you’ve lost the core benefit of pipelining—early row delivery and low memory footprint.

CREATE OR REPLACE FUNCTION f_bad_pipe
  RETURN t_rowset PIPELINED
IS
  l_data t_rowset;
BEGIN
  -- Load everything at once
  SELECT * BULK COLLECT INTO l_data
  FROM   big_source_table;

  -- Then stream from memory
  FOR i IN 1 .. l_data.COUNT LOOP
    PIPE ROW (l_data(i));
  END LOOP;

  RETURN;
END f_bad_pipe;
/

In production, I’ve seen this lead to high PGA usage, long response times, and poor parallel query behavior. The SQL engine can’t start consuming rows until the whole BULK COLLECT finishes, so you lose the natural streaming and back-pressure benefits.

Better approach: true streaming with minimal per-row work

What works far better in my experience is to fetch from the source in modest batches and pipe rows as soon as they’re available, keeping per-row logic lean:

CREATE OR REPLACE FUNCTION f_good_pipe
  RETURN t_rowset PIPELINED
IS
  CURSOR c_src IS
    SELECT * FROM big_source_table;

  TYPE t_tab IS TABLE OF big_source_table%ROWTYPE;
  l_batch t_tab;
BEGIN
  OPEN c_src;
  LOOP
    FETCH c_src BULK COLLECT INTO l_batch LIMIT 1000;
    EXIT WHEN l_batch.COUNT = 0;

    FOR i IN 1 .. l_batch.COUNT LOOP
      -- keep this lightweight
      PIPE ROW (l_batch(i));
    END LOOP;
  END LOOP;
  CLOSE c_src;

  RETURN;
END f_good_pipe;
/

Here, memory stays bounded, the SQL engine can start returning rows quickly, and you still benefit from reduced context switches through batched fetches. In my own projects, I also avoid heavy validations, logging, or cross-service calls inside the pipelined loop—anything expensive per row is a red flag. For more detailed guidance on designing efficient pipelined functions and when to prefer views or plain SQL instead, see Pipelined Table Functions | connect – Oracle Blogs.

5. Overusing CURSOR FOR Loops Instead of Bulk Processing

In code reviews, one of the most common PL/SQL performance mistakes I run into is ETL-style procedures built entirely around row-by-row CURSOR FOR loops. It looks simple and readable, but under load it becomes painfully slow and chatty: every row triggers separate context switches and DML calls. When I first migrated some of my own legacy jobs to bulk processing, the runtime dropped from hours to minutes.

Row-by-row anti-pattern vs bulk approach

Here’s an example of the pattern I still see in many production schemas:

DECLARE
  CURSOR c_src IS
    SELECT * FROM staging_orders;
BEGIN
  FOR r IN c_src LOOP
    INSERT INTO orders (
      order_id, customer_id, amount
    ) VALUES (
      r.order_id, r.customer_id, r.amount
    );
  END LOOP;

  COMMIT;
END;
/

This fires one INSERT per row. With hundreds of thousands of rows, that’s a huge amount of context switching and redo. What’s worked consistently better for me is switching to BULK COLLECT plus FORALL in controlled batches:

DECLARE
  CURSOR c_src IS
    SELECT order_id, customer_id, amount
    FROM   staging_orders;

  TYPE t_orders IS TABLE OF c_src%ROWTYPE;
  l_orders t_orders;
  c_limit  PLS_INTEGER := 5000;
BEGIN
  OPEN c_src;
  LOOP
    FETCH c_src BULK COLLECT INTO l_orders LIMIT c_limit;
    EXIT WHEN l_orders.COUNT = 0;

    FORALL i IN 1 .. l_orders.COUNT
      INSERT INTO orders (
        order_id, customer_id, amount
      ) VALUES (
        l_orders(i).order_id,
        l_orders(i).customer_id,
        l_orders(i).amount
      );
  END LOOP;
  CLOSE c_src;

  COMMIT; -- or controlled commits per batch when appropriate
END;
/

In my experience, even a straightforward refactor like this can yield dramatic speedups for nightly ETL and batch jobs. The key is to treat row-by-row cursor loops as a last resort for truly exceptional logic, not as the default for large-scale DML.

6. Forgetting to Measure PL/SQL Performance with Realistic Data

Many of the worst PL/SQL performance mistakes I’ve seen weren’t about syntax at all—they came from developers (including me, early on) testing only on a tiny dev schema. Code that “flies” on 10,000 rows can completely fall apart at 100 million, especially when BULK COLLECT, FORALL, and pipelined functions are involved.

Why synthetic, tiny tests lie

On small data sets, almost anything looks fast: no significant PGA pressure, no contention, minimal I/O, and very simple execution plans. I’ve watched teams confidently deploy code that ran in seconds on dev, only to discover full table scans, hash joins, and massive TEMP usage in production because the plans changed with real data volume and statistics.

In my own practice, I now insist on three things before calling a PL/SQL change “tuned”:

• Volume-representative test data – at least the same order of magnitude as production, ideally with similar data skew and key distributions.
• Execution plan checks – verify that the underlying SQL used by BULK COLLECT, FORALL, and pipelined functions has sensible plans, using tools like EXPLAIN PLAN or DBMS_XPLAN.DISPLAY_CURSOR.
• Basic instrumentation – log batch sizes, elapsed times, and row counts so you can see how performance scales as data grows.

A simple approach I’ve used is to wrap critical sections with timing calls and row counters:

DECLARE
  l_start  NUMBER := DBMS_UTILITY.get_time;
  l_count  PLS_INTEGER := 0;
BEGIN
  -- do bulk work here, incrementing l_count

  DBMS_OUTPUT.put_line('Rows processed: ' || l_count);
  DBMS_OUTPUT.put_line('Elapsed (s): ' ||
    (DBMS_UTILITY.get_time - l_start) / 100);
END;
/

It’s not fancy APM, but in my experience, running this against a realistic clone of production data quickly reveals whether a “fast” procedure will really stay fast once it leaves the safety of dev. For more structured techniques, including how to combine AWR, ASH, and DBMS_XPLAN when tuning PL/SQL at scale, see How can I track the execution of PL/SQL and SQL? – Ask TOM.

7. Mixing Business Logic and Logging Inside Tight Bulk Loops

After years of tuning bulk PL/SQL, one pattern I’ve learned to watch for immediately is “fat” bulk loops: beautifully written BULK COLLECT and FORALL blocks that are then stuffed with validations, lookups, and verbose logging. It’s one of the easiest PL/SQL performance mistakes to make—you get the bulk structure right, then quietly erase its benefits with heavy per-row work.

How heavy logic and logging kill your bulk gains

The whole point of BULK COLLECT and FORALL is to reduce context switches and round trips. But if each iteration performs extra SQL, complex business rules, or frequent DBMS_OUTPUT.put_line or logging calls, the loop effectively behaves like row-by-row processing.

Here’s an example of the kind of code I’ve had to refactor in real projects:

DECLARE
  TYPE t_orders IS TABLE OF staging_orders%ROWTYPE;
  l_orders t_orders;
BEGIN
  SELECT * BULK COLLECT INTO l_orders
  FROM   staging_orders;

  FORALL i IN 1 .. l_orders.COUNT
    INSERT INTO orders (
      order_id, customer_id, amount
    ) VALUES (
      l_orders(i).order_id,
      l_orders(i).customer_id,
      l_orders(i).amount
    );

  -- "helpful" but dangerous per-row logging & extra logic
  FOR i IN 1 .. l_orders.COUNT LOOP
    DBMS_OUTPUT.put_line('Inserted order ' || l_orders(i).order_id);

    -- extra lookup per row (anti-pattern)
    UPDATE stats
       SET order_count = order_count + 1
     WHERE customer_id = l_orders(i).customer_id;
  END LOOP;
END;
/

In one of my batch jobs, a pattern like this turned what should have been a few minutes of work into an hour-long run. The FORALL insert was fast; it was the logging and per-row updates that dragged everything down.

What’s worked much better for me is:

• Doing as much business rule evaluation as possible in set-based SQL before the bulk step.
• Aggregating logging: store key events in a small in-memory collection and flush once per batch instead of per row.
• Using summary tables or batch-level counters instead of per-row statistic updates.

The mindset shift is simple: treat the tight bulk loop as a performance-critical core and keep it as lean as you can. Move everything non-essential—especially logging—outside or around it, not inside.

Conclusion: Fixing Common PL/SQL Performance Mistakes Before Production

Most PL/SQL performance mistakes I’ve had to fix in production weren’t exotic—they were basic misuse of BULK COLLECT, FORALL and pipelined functions: unbounded collections, missing SAVE EXCEPTIONS, treating pipelined functions like generic APIs, cursor FOR loops everywhere, tiny test data, and heavy logic or logging inside tight loops.

To keep new server-side logic healthy, I now run through a quick checklist before sign-off:

• Are BULK COLLECTs bounded with sensible LIMIT values?
• Do FORALL statements use SAVE EXCEPTIONS where partial success matters?
• Are pipelined functions truly streaming, with minimal per-row work?
• Have row-by-row cursor FOR loops been replaced with BULK COLLECT + FORALL where feasible?
• Has the code been tested and instrumented on realistic data volumes?
• Is business logic, logging, and ancillary work kept outside the hottest bulk loops?

In my experience, consistently applying this short list during development and code review prevents most nasty surprises when PL/SQL finally hits production load.