A financial services firm's overnight settlement processing was creeping closer to market-open every quarter. PL/SQL orchestration rework and partition-aware parallelism restored headroom in the batch window without changing the settlement logic's outcome.
The client, a mid-market financial services firm, ran its nightly trade settlement processing as a large, sequential PL/SQL batch against a growing Oracle schema. Each quarter's volume growth pushed the run further toward market-open, with progressively less slack for retries or manual intervention.
The settlement batch had originally been designed as a mostly sequential process. As transaction volumes grew year over year, the batch's runtime grew with it — with no parallelism built into the original design to absorb that growth.
A number of the older stored procedures relied on join patterns that had gone unnoticed at lower data volumes but produced unnecessary cartesian-style row explosions once the underlying tables grew significantly larger.
A batch that spilled past its window risked delaying settlement confirmations into market-open — a compliance-sensitive outcome the firm needed eliminated entirely, not just made less likely.
The batch was decomposed into its logical settlement legs before any code was touched, so parallelisation decisions were based on actual data dependencies rather than the batch's existing script order.
AWR snapshots across several full batch runs identified which settlement legs consumed the most elapsed time and which were CPU-bound versus I/O-bound — informing where parallelism would actually help.
Settlement legs were mapped for true data dependencies versus historical execution order, surfacing legs that could safely run concurrently without affecting settlement outcomes.
Over 340 stored procedures were reviewed; join logic responsible for cartesian-style row explosions was corrected, and row-by-row cursors were replaced with bulk, set-based operations where safe to do so.
Key transaction tables were range-partitioned by settlement date, enabling partition pruning that dramatically cut the data scanned per run and allowed independent legs to operate on isolated partitions in parallel.
The rebuilt batch ran in shadow alongside the legacy process across multiple full cycles, with settlement outputs reconciled row-for-row before the new orchestration was promoted to be the system of record.
"The batch had been creeping toward market-open for two years and everyone assumed the fix was more hardware. It wasn't — it was the join logic and the fact that nothing ran in parallel that didn't have to be sequential." — OptiSol Oracle Engineering Lead
Let's Talk
Tell us what you are running today and where you need expert support.
"*" indicates required fields
Global Presence
Five global locations. One connected engineering team.
North America
Chennai · Coimbatore
Client services
Regional operations
Client services
