Based on all the symptoms you’ve described, here’s a comprehensive solution to restore your MRP performance:
ROOT CAUSE ANALYSIS:
The MRP slowdown after upgrading from Oracle 12c to 19c stems from three primary factors:
-
Execution Plan Changes: Oracle 19c’s cost-based optimizer evaluates queries differently than 12c, especially for complex multi-table joins common in MRP processing. Your observation about hash joins replacing nested loops confirms this - the optimizer is choosing plans that look efficient on paper but perform poorly with JDE’s data distribution patterns.
-
Statistics Staleness: While you’ve gathered statistics post-upgrade, the optimizer still lacks historical context about data skew and access patterns in your supply planning tables.
-
Optimizer Feature Incompatibilities: Oracle 19c’s adaptive features and automatic tuning mechanisms can actually degrade performance for batch-intensive ERP operations that have predictable patterns.
IMMEDIATE FIXES:
1. Lock Down Execution Plans:
Since your MRP queries are now using suboptimal hash joins, force better execution plans using hints or SQL plan baselines. For the key queries hitting F3111 and F3112:
-- Example: Force nested loop join
SELECT /*+ USE_NL(f3111 f4101) */
item_data FROM F3111 f3111, F4101 f4101
WHERE f3111.IMITM = f4101.IMITM;
However, modifying JDE’s delivered MRP programs isn’t practical. Instead, create SQL plan baselines for problematic queries.
2. Optimizer Parameter Tuning:
Adjust these initialization parameters to align with your pre-upgrade behavior:
-- Set optimizer to use 12.2 feature set temporarily
ALTER SYSTEM SET optimizer_features_enable='12.2.0.1' SCOPE=BOTH;
-- Disable adaptive features that hurt batch performance
ALTER SYSTEM SET optimizer_adaptive_features=FALSE SCOPE=BOTH;
ALTER SYSTEM SET optimizer_adaptive_statistics=FALSE SCOPE=BOTH;
-- Adjust join order for better nested loop performance
ALTER SYSTEM SET optimizer_index_cost_adj=25 SCOPE=BOTH;
The optimizer_features_enable setting makes 19c behave more like 12c while you tune for native 19c performance. This should immediately improve your MRP runtime.
3. Enhanced Statistics Collection:
Gather extended statistics on correlated columns in supply planning tables:
-- Gather extended stats on commonly joined columns
EXEC DBMS_STATS.GATHER_TABLE_STATS('JDE920','F3111',
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('JDE920','F4101',
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
Focus on F4101 (Item Master), F3111 (Item Ledger), F3112 (Lot Master), F4801 (Work Orders), and F3460 (Purchase Orders) - these drive MRP calculations.
ADDRESSING THE FAILOVER MITIGATION WINDOW:
Your 6+ hour MRP window is unacceptable. Beyond database tuning:
-
Parallel MRP Processing: If your MRP run processes multiple planning groups or facilities, configure parallel execution. JDE 9.2.2 supports multi-threaded MRP generation - split your 45,000 items across 3-4 parallel MRP jobs by planning group or business unit.
-
Incremental MRP: Instead of full regeneration nightly, implement net change MRP for items with recent activity. Reserve full regeneration for weekly runs. This can reduce daily processing by 60-70%.
LONG-TERM OPTIMIZATION:
Once immediate performance is restored:
-
Baseline Capture: Use SQL Plan Management to capture good execution plans during a fast MRP run. These baselines ensure consistent performance going forward.
-
Progressive Migration: Gradually re-enable Oracle 19c features:
- Start with optimizer_features_enable=‘19.1.0.0’
- Monitor MRP performance for 2 weeks
- Re-enable adaptive_features selectively
- Tune any queries that regress
-
Monitoring Setup: Implement AWR-based monitoring of MRP job performance. Track execution plans, wait events, and resource consumption to catch degradation early.
EXPECTED RESULTS:
After implementing the optimizer parameter changes and enhanced statistics:
- Immediate improvement: 4.5 hours → 2.5-3 hours (within 24 hours)
- With parallel processing: 2.5 hours → 1.5-2 hours (within 1 week)
- After full optimization: Back to 2-hour baseline or better
The key is the optimizer_features_enable setting - this will restore your 12c query behavior immediately while you work on native 19c optimization. Your failover mitigation window will drop back into acceptable ranges within a few days.