PL/SQL Optimization of Stored Procedures in Oracle

A nightly batch recalculates insurance premiums for 800,000 policies — it takes 6 hours. The nightly window is 4 hours. DBMS_PROFILER revealed: row-by-row processing, COMMIT inside a loop, no BULK operations.

BULK COLLECT and FORALL¶

BULK COLLECT loads data into a collection all at once. FORALL performs DML in a single roundtrip. LIMIT 10000 for reasonable memory consumption. COMMIT after each batch instead of after each row.

SQL Instead of PL/SQL¶

A function rewritten as a SQL CASE expression. The entire batch replaced by a single UPDATE statement. 800,000 context switches eliminated. Result: from 6 hours to 8 minutes.

Parallel DML¶

The PARALLEL hint cut the batch down to 3 minutes. Caution — this requires an exclusive lock on the table.

Lessons Learned¶

Row-by-row processing is the main performance killer in PL/SQL. Prefer BULK operations or pure SQL. Profile — don’t guess at the bottleneck.