Mastering High-Volume Sales Order Processing: An Architectural Guide
Processing approximately 300,000–400,000 Sales Order line records per hour presents a significant challenge that exceeds the capabilities of standard, one-at-a-time integration patterns. To handle this load—translating to roughly 55–83 records per second—architects must implement a robust pipeline that ensures data integrity and graceful recovery from failures. The core philosophy found in the sources is that Oracle Integration (OIC) should serve as the orchestration layer, while the Oracle Autonomous Transaction Processing (ATP) database performs the heavy lifting.
This post walks through a proven five-layer architecture that handles this volume reliably, recovers gracefully from failures, and gives operations teams the visibility and control they need — all without OCI Streaming
- Layer 1 ATP Staging Table
- Layer 2 Batch Controller — DBMS_SCHEDULER + APEX
- Layer 3 Parallel OIC Instances
- Layer 4 FBDI Bulk Load into Oracle Fusion
- Layer 5 Monitoring, Error Handling & Retry
The ATP Staging Buffer
The foundation of this high-volume architecture is the ATP staging table, which acts as a "shock absorber" between the source e-commerce system and OIC. By allowing the source system to write records freely to ATP, the architecture decouples the two systems; if OIC is down for maintenance, records simply accumulate in staging without data loss. This staging table tracks critical information, including record status (Pending, Batched, Processing, Completed), batch IDs, and error messages.
High-Frequency Batching with DBMS_SCHEDULER
