Measure and improve PL/SQL processing

Development and modifications

26R1 reference · Documentation-based walkthrough · Sources reviewed 2026-09-28 · Not executed in IFS or Oracle. Match the database, build home, and tools to your installed release. Example names are fictional.

Improve a processing routine while preserving its results, permissions, and transaction behavior. Start with measurements and a bounded fetch exercise rather than replacing every cursor with bulk processing.

Before you start

Use an authorized development database and a representative input set. Record Oracle version, row count, elapsed time, logical reads where available, and result checks in the review worksheet. The Oracle references describe language behavior; their documentation version is not a statement of IFS database certification.

1. Establish correctness and a baseline

Choose small, typical, and large input sizes, including zero rows.
Capture returned values, business effects, and the number of processed rows.
Run repeatable measurements with the same inputs and permissions. Record warm/cold-cache conditions rather than reporting one favorable timing.
Identify the dominant cost: SQL execution, repeated API lookups, SQL/PLSQL switches, serialization, network calls, or locking.
Change one cause at a time and rerun the same checks.

2. Remove repeated work without changing meaning

If several supported getters fetch attributes of the same object, inspect whether the API exposes an appropriate record-returning Get method. Use its actual return type and access contract; there is no universal Get signature for every entity.

Move a value outside a loop only when it remains invariant for the entire loop. A company-specific rate cannot be cached once for rows from several companies. Include identity, site, effective date, and other relevant keys in any cache design; avoid sharing security-sensitive lookup results across callers.

For nested SQL lookups, consider a single correctly keyed join. Compare row counts and values: a one-to-many join may duplicate results that the original routine did not duplicate.

3. Run a bounded fetch exercise

Download bulk-fetch.sql. It reads numbers 1 through 100 from DUAL, fetches batches of at most 64, and asserts the final count and sum.

Open it in an approved Oracle SQL client and enable DBMS_OUTPUT.
Run as a script. Expected final output: Rows=100; total=5050.
Change the fetch limit to 25, then 1. Results must remain identical.
Change the source to zero rows and adjust the expected assertions deliberately; confirm that no batch is processed.
Restore the original fixture before recording the result.

The essential loop is:

LOOP
   FETCH numbers_ BULK COLLECT INTO batch_ LIMIT 64;
   EXIT WHEN batch_.COUNT = 0;
   FOR i IN 1 .. batch_.COUNT LOOP
      count_ := count_ + 1;
      total_ := total_ + batch_(i);
   END LOOP;
END LOOP;

Process the final nonempty partial batch. Exiting on cursor %NOTFOUND immediately after the fetch can skip that batch. The complete download closes the cursor on success and on error, then propagates an unexpected exception.

This is a correctness exercise, not proof that a limit of 64 is optimal. Benchmark batch sizes against memory and latency. An unbounded collection can consume excessive session memory, and cursor FOR loops may already benefit from compiler optimizations.

4. Choose the right write mechanism

For one relational transformation on data you own, evaluate set-based SQL.
When per-row business processing is required, preserve the supported IFS API calls and transaction contract.
FORALL batches a DML statement. It does not turn arbitrary PL/SQL business-API calls into a bulk operation.
If partial failures are allowed, define how each input maps to success/error and how retry avoids duplicate effects. SAVE EXCEPTIONS is not a complete business recovery design.

Do not replace IFS business APIs with direct table writes solely because the latter benchmark faster.

5. Review parameter and serialization costs

NOCOPY is a compiler hint for OUT/IN OUT parameters, not guaranteed copy elimination. Aliasing and exception behavior matter: after failure, the caller’s variable may already have changed. Catching an exception does not automatically restore its original value. Decide whether to validate before mutation, use a deliberate working copy, or document invalid output on failure. Test both successful and failed calls.

LOB parameters are often locators; do not assume passing a CLOB copies its entire content. Measure the actual routine.

Where you own both sides of an internal interface, a supported typed record may avoid repeated attribute-string packing. Preserve validation/defaulting in the intended call chain. Do not expose private %ROWTYPE contracts to external consumers or call an internal New___ from unrelated code to bypass the supported attribute-string API.

Troubleshooting

Symptom	Investigation
Last records missing	Exit condition before processing the final partial batch
Memory spikes	Unbounded collection or oversized batch
Faster result but wrong totals	Join multiplication or an incorrectly cached lookup
Caller state changed after error	NOCOPY/aliasing and the mutation order
Business validation disappears	Direct DML or private method substituted for the supported interface

Exercise and solution

Exercise: with 100 rows and a limit of 64, how many nonempty batches should be processed?

Show the solution

Two: 64 rows and 36 rows. A subsequent empty fetch terminates the loop. The final count must be 100 and the sum 5050. The row count is the correctness check; elapsed time is a separate measurement.

Cleanup and delivery evidence

The read-only fixture creates no objects. Retain baseline/changed measurements and regression results. Deploy an optimization only after equivalent business behavior and acceptable resource use have been demonstrated in the target environment.