How to Double Oracle Data Load Speed with Bulk Inserts and Parallelism

Concept

Many developers process large data sets by looping over small transactions, believing that frequent commits are faster or that undo space is insufficient. In reality, a single SQL statement that leverages the database's bulk processing capabilities is usually faster, and excessive commits can cause snapshot errors (ORA‑1555).

Case Analysis

A bank's data‑analysis system loaded massive tables using a procedural approach that opened a cursor, fetched rows in batches, and committed inside the loop. The PL/SQL procedure CREATE OR REPLACE PROCEDURE P_PERM_TEST01 ... performed a UNION ALL between two source tables and inserted the result batch‑by‑batch, committing after each bulk fetch.

This method suffered from long execution time (≈5 minutes for 4 million rows) and risked ORA‑1555 errors due to prolonged cursor lifetimes.

Optimization Steps

Step 1 – Single‑statement bulk insert : Replace the loop with a single INSERT‑SELECT that processes the entire data set in one statement. The new procedure

CREATE OR REPLACE PROCEDURE P_PERM_TEST02 ... INSERT INTO TAB_PERM_TEST02 SELECT ... FROM A_BOCS_INVM1 ... UNION ALL SELECT ... FROM A_BOCS_INVM2 ... COMMIT;

reduced execution time to about 2 minutes and doubled the insertion rate.

Step 2 – Increase UNDO and clear caches : Adjust UNDO tablespace size, flush the shared pool and buffer cache with ALTER SYSTEM FLUSH SHARED_POOL; and ALTER SYSTEM FLUSH BUFFER_CACHE; before running the procedure.

Step 3 – Enable parallelism and APPEND hint : Create CREATE OR REPLACE PROCEDURE P_PERM_TEST03 ... that sets the session to force parallel query and DML with degree 4, truncates the target table, and uses INSERT /*+ APPEND NOLOGGING */ to bypass redo logging. This version completed the same load in 24 seconds, achieving a six‑fold speed increase (≈1.66 million rows per second).

Execution plans and statistics (shown in the included screenshots) confirmed that the optimizer performed full table scans on the source tables and used appropriate indexes for the UNION ALL, with no plan anomalies.

Results in Production

After applying the parallel, APPEND‑optimized procedure to the production environment handling roughly 1.2 billion rows, the total load time was about 40 minutes, averaging 500 k rows per second—an order‑of‑magnitude improvement over the original loop‑based approach.

Optimization Summary

Fully exploit SQL bulk processing capabilities and avoid procedural row‑by‑row loops; minimize commit frequency.

When processing extremely large volumes, leverage the database's parallel execution features (e.g., FORCE PARALLEL QUERY/DML) and the APPEND hint for direct‑path inserts.