Multiutility
All Case Studies
9 min

Oracle Optimization for Sage X3: A Case Study in the Energy & Gas Sector

-99,8%
Latency
Account inquiries down from 15s to a steady 25ms
-99,7%
Matching time
Accounting operations down from 10s to 25ms per run
+9h
CPU savings
Processing time recovered in a single morning of testing
Context

The customer operates in the utilities and energy trading market, a sector marked by regulatory volatility and massive data volumes (big data). Its core system is the Sage X3 ERP on an Oracle Standard Edition database. The infrastructure must handle continuous flows of metering data (meter readings) and consumption profiles, ensuring accuracy in mass billing cycles and real-time visibility into sales margins.

Problems

The database suffered from chronic execution plan instability, with unpredictable response times that created bottlenecks in financial processes.

  • Sage X3 engine constraints: the architecture generated SQL queries automatically at runtime, and the predefined joins ruled out direct changes to the source code.
  • Oracle Standard Edition limitations: without Enterprise Edition features, virtual indexes and plan stability tools were not available to prevent sudden execution plan changes.
  • Skewed data: the uneven distribution of energy data made the optimizer short-sighted, leading to poor data access choices based on unrepresentative statistical averages.
  • I/O contention: the ERP's transactional activity overlapped with bulk extractions to the data warehouse.

From 15 seconds to 25 milliseconds


 

In this scenario, the goal was to achieve predictable performance, eliminate computational overhead (parsing), and protect the business-critical month-end closing processes.

The ongoing maintenance protocol. To preserve the results, we introduced proactive plan monitoring to catch regressions in the absence of automatic plan locking tools, periodic review of the SQL patches whenever Sage X3 is updated, and rigorous maintenance of the extended statistics (avoiding unnecessary histograms) to keep the optimizer effective over time.

Solution

Miriade took a multidimensional approach, working exclusively on database objects and session parameters:

  • Guidance through SQL patches: we identified the critical SQL IDs and injected external hints to force the use of specific indexes, bypassing the optimizer's unstable decisions without touching the ERP code.
  • Extended statistics: we mapped the actual correlations between columns so the optimizer could correctly estimate the rows returned and avoid suboptimal execution plans.
  • Memory and cursor tuning: we optimized cursor management to minimize parsing of frequent statements, so the database could reuse the most efficient access paths immediately.
  • Physical optimization: we increased the size of the redo logs and redistributed them across disks to remove bottlenecks during bulk writes.
  • Workload governance: we moved the analytical extractions (DWH/data lake) to nighttime windows to decouple them from transactional operations.

The results

The work eliminated more than 32,000 seconds of unnecessary processing in a few hours. The database no longer has to guess the fastest route: it follows deterministic paths. Stability now holds even during peak loads, and the infrastructure can support growing business volumes with no additional license costs.

Technologies
  • Oracle Database Standard Edition: instance optimization and session parameter management.
  • Sage X3 Runtime: management of the automatically generated SQL queries.
  • Oracle SQL Patch: a tool for injecting hints and stabilizing execution plans.
  • Oracle Redo Log: optimization of the physical write layer.
  • Oracle Extended Statistics: analysis of column correlations for optimizer accuracy.
  • SQL ID & Parsing Tuning: techniques for reducing computational overhead.