Performance Optimization of Oracle SCM/WMS Workflows Using Set-Oriented Data Access: An Industry Case Study

Enterprise Oracle SCM/WMS applications frequently experience performance degradation due to inefficient UI-driven data access patterns. In many production environments, row-wise database queries executed inside iterative UI logic result in excessive database round trips, poor scalability, and prolonged client escalations. This paper presents an industry case study conducted at Intellinum Private Limited, focusing on performance optimization of a Pick Confirmation workflow in Oracle WMS. The legacy implementation relied on executing aggregation queries per pick line, leading to severe response delays. The proposed solution replaces this approach with a set-oriented bulk aggregation strategy combined with in-memory key-based lookup. Experimental results demonstrate a significant improvement, reducing execution time from 2.55 minutes to 6 seconds for 185 pick lines, while preserving functional correctness. The study confirms that architectural restructuring of data access patterns is an effective approach for resolving performance bottlenecks in enterprise ERP systems.