Optimizing Bulk Queries with BULK COLLECT, LIMIT, and Exception Handling
📤 Compartilhe este artigo com o link curto:
The Danger of Excessive PGA Memory Consumption
As we have seen previously, the BULK COLLECT command is a powerful tool to eliminate the context switching bottleneck between PL/SQL and SQL engines, loading entire collections of data at once. However, collecting tens or hundreds of millions of records in a single operation without proper controls can quickly exhaust the PGA (Program Global Area) memory allocated to your session on the database server, resulting in severe out‑of‑memory errors or systemic slowdown.
To mitigate this risk in large‑scale batch processing routines, Oracle provides the LIMIT clause combined with iterative cursor consumption.
Controlling Memory Consumption with the LIMIT Clause
The LIMIT clause restricts the maximum number of rows that the BULK COLLECT command loads into the collection at each loop iteration. This way, processing occurs in controlled blocks (for example, batches of 5,000 or 10,000 records at a time), ensuring stability and continuous release of memory resources.
Practical example of controlled batch processing:
DECLARE CURSOR c_transacoes IS SELECT transaction_id, account_id, amount FROM transactions WHERE status = 'PENDING'; TYPE t_trans_list IS TABLE OF c_transacoes%ROWTYPE; l_lote_transacoes t_trans_list; -- Setting the ideal batch size c_tamanho_lote CONSTANT PLS_INTEGER := 5000; BEGIN OPEN c_transacoes; LOOP -- Loads only 5,000 records at a time FETCH c_transacoes BULK COLLECT INTO l_lote_transacoes LIMIT c_tamanho_lote; EXIT WHEN l_lote_transacoes.COUNT = 0; -- Process the current batch in memory FOR i IN 1..l_lote_transacoes.COUNT LOOP -- Business logic applied to each record in the batch NULL; -- Replace with actual rule END LOOP; -- Optional: commit partial batch if architecture allows COMMIT; END LOOP; CLOSE c_transacoes; END;
Partial Error Handling in Batch with SAVE EXCEPTIONS
Another major challenge when processing large data volumes via FORALL is ensuring that an isolated failure in a single record (such as violation of a unique key or not‑null constraint) does not bring down the entire batch execution.
Using the SAVE EXCEPTIONS clause, the PL/SQL engine continues executing the remaining operations in the array and stores the occurred errors in the SQL%BULK_EXCEPTIONS exception stack for later auditing.
Best Practices for Production Batch Routines
- Set balanced batch sizes: Very small batches (e.g., 100 rows) generate many round trips to the database; gigantic batches (e.g., 500,000 rows) exhaust PGA memory. The ideal range usually lies between 1,000 and 10,000 records per batch.
- Monitor table locks: In massive updates via FORALL, pay attention to row locks generated to avoid concurrency contention with other transactional applications during business hours.
Conclusion
Mastering the combined use of BULK COLLECT with LIMIT and failure handling via SAVE EXCEPTIONS enables the developer to write robust, secure, and highly performant PL/SQL routines for mission‑critical enterprise environments.