Oracle Database Optimization for sedApta at Pedon S.p.A.

The database in question supports a decision-making platform that connects demand, supply, and production data to synchronize planning and execution, providing real-time visibility and integrated processes across the entire supply chain. The operating environment had technical constraints that called for an analytical approach:

Application Constraints and Dynamic Queries

Modern decision-making platforms for the supply chain, such as the sedApta suite, use complex architectures that generate dynamic SQL queries to give users maximum flexibility. Because they are packaged software, their source code cannot (and must not) be altered without breaking the vendor's standards. Our approach succeeded precisely because, by working with surgical precision at the structural level of the database (indexes and statistics), we got around this built-in constraint and improved performance without requiring any change to the application.

Standard Edition Limitations

Because this was an Oracle Standard Edition instance, advanced tools available in Enterprise Edition could not be used. A prime example is the absence of virtual indexes: "non-physical" indexes that let you test the effectiveness of an optimization without actually creating them, so database operations are not affected during testing. Every change therefore required extremely precise empirical validation.
 

During the critical phase, the system showed latency spikes that degraded overall performance. Analysis with monitoring tools revealed that two specific wait events predominated:

• PGA_memory_operation — This event occurs when a session actively requests the allocation or release of memory chunks in the Process Global Area (PGA). On this instance, the wait indicated that queries were running intensive operations that required continuous resizing of process memory, saturating available resources.
• resmgr: cpu quantum — This event indicates intervention by the Database Resource Manager (DBRM). The session is put on hold because it has used up its CPU time allocation (quantum) defined by the resource plan. It signals that the workload has exceeded the configured thresholds, causing the database to "throttle" certain processes to preserve overall stability.

The optimization work was built around three key objectives, essential to ensuring business continuity:

• Restoring performance to bring query response times back to the optimal levels seen before the crisis, eliminating the delays end users experienced in warehouse operations.
• Optimizing resource consumption by reducing computational load (CPU) and memory usage, minimizing throttling by the Resource Manager and preventing server saturation.
• Scalability and stability. Here, this meant configuring the database to handle the natural growth in data volume generated by the application suite, so that table growth would not cause execution times to degrade exponentially.

Respecting the architectural constraints of the sedApta suite and the need to leave the syntax of the application's dynamic queries untouched (so as not to break the vendor's standards), we focused on precise, database-side optimization of data access paths.

• Execution Plan Analysis — By studying query execution plans, we identified numerous underperforming queries that, for example, read every block of a table, causing a high number of logical and physical reads.
• Strategic Index Implementation — We introduced targeted indexes. This lets the database access the required records directly, drastically reducing execution plan cost and the I/O required.
• Manual Statistics Management — Oracle's Cost-Based Optimizer (CBO) depends on the quality of statistics. After the indexes were created, statistics were manually recalculated on the affected tables to give the CBO an up-to-date view of data cardinality and distribution, allowing it to choose the fastest execution path.

The results

The work delivered immediate, measurable benefits, visible in the infrastructure monitoring logs, and avoided the need for costly hardware upgrades:

• 99% reduction in wait times (Queries 1, 2, and 3) — The queries identified as critical, shown in the charts below, recorded a drastic drop in Top Waits. Operations that at peak accumulated up to 5 hours of aggregate wait time, causing operational disruptions, were once again running in milliseconds.
• 70% reduction in overall system load (comparative analysis) — Comparing current metrics with historical data, total daily database load fell from nearly 35 hours of wait time to a normal, stable baseline of about 8–10 hours.
• Bottlenecks resolved — Critical waits linked to memory saturation (PGA_memory_operation) and CPU saturation (resmgr: cpu quantum) dropped to zero, clearly and permanently.

• Oracle Database (Standard Edition): the relational database engine at the center of the logical and performance optimization work.
• sedApta Suite: the decision-making platform for integrated supply chain management (demand, supply, and production) supported by the database.
• Oracle Cost-Based Optimizer (CBO): the core Oracle component we acted on, manually recalculating cardinality and distribution statistics to steer it toward the fastest execution paths.
• Monitoring and Log Analysis Tools: used to track database wait events, extract latency charts, and isolate the dynamic queries responsible for operational disruptions.
• Database Resource Manager (DBRM): Oracle's resource manager, monitored to keep CPU time quotas from being exceeded and putting critical sessions on hold.

Final recommendations

In day-to-day operations, IT departments are increasingly absorbed in meeting business demands, managing the evolution of complex applications, and optimizing logistics processes. In this scenario, in-depth database maintenance and attention to technical details such as execution plan management or wait event analysis risk being relegated to a secondary role. Yet proper management of these elements is the fundamental factor that determines the performance, stability, and longevity of any business-critical software system. In fact, infrastructure performance is what tips the balance: in the eyes of users and the business, a slow system is inevitably seen as a failed project, while a responsive database makes the entire software investment a success.

Neglecting optimization or ignoring signs of query degradation can trigger a chain of latent problems. Over time, these surface as systemic slowdowns and bottlenecks, forcing urgent corrective action to keep warehouse movements from coming to a halt. Moreover, when the real root cause cannot be identified, teams often wrongly conclude that the server needs more hardware. This approach not only inflates costs unnecessarily, but also masks the underlying logical inefficiency without fixing it at the root.

A short-sighted approach that does not continuously monitor the interaction between application workloads and the Oracle infrastructure can have serious consequences. The buildup of critical waits and forced intervention by the Resource Manager can cause sudden outages of the entire system, halting logistics operations and causing immediate financial and reputational damage.

Database optimization is therefore not just a technical "tuning" exercise, but a strategic decision with a direct impact on operational efficiency and costs. Careful design, including proactive index monitoring and regular statistics recalculation, is essential to make the best use of resources in both on-premises and cloud infrastructures, keeping the company's technology core high-performing and reliable over the long term.