Let me provide a comprehensive solution addressing all the key optimization areas:
1. BOM Recursion Depth Optimization:
Implement recursive CTEs for deep hierarchy processing. Replace iterative explosion logic with:
WITH RECURSIVE bom_tree AS (
SELECT item_id, component_id, 1 as level
FROM MBOM_STRUCTURE WHERE parent_item_id = :root_item
UNION ALL
SELECT b.item_id, b.component_id, bt.level + 1
FROM MBOM_STRUCTURE b JOIN bom_tree bt ON b.parent_item_id = bt.component_id
WHERE bt.level < 10
)
SELECT * FROM bom_tree;
This handles recursion in a single query execution rather than multiple round trips.
2. Batch Parallel Processing Configuration:
Update your batch settings:
batch.timeout=43200
batch.parallel.threads=10
batch.chunk.size=500
bom.explosion.parallel.enabled=true
bom.max.recursion.depth=10
Increase parallel threads to 10 for your volume. Enable parallel BOM explosion processing and set explicit recursion limits to prevent runaway queries.
3. Database Index Strategy:
Implement the composite indexes James suggested, plus add covering indexes:
CREATE INDEX idx_mbom_parent_level ON MBOM_STRUCTURE(parent_item_id, level, component_id);
CREATE INDEX idx_mbom_effective ON MBOM_STRUCTURE(effective_date, item_id) WHERE active_flag='Y';
CREATE INDEX idx_mbom_covering ON MBOM_STRUCTURE(parent_item_id, component_id, quantity, effective_date) INCLUDE (level, item_id);
The covering index eliminates table lookups for common BOM queries.
4. Incremental Caching vs Full Recalculation:
Enable incremental caching with change detection:
bom.explosion.cache.enabled=true
bom.cache.strategy=incremental
bom.cache.ttl=86400
bom.change.tracking.enabled=true
This processes only modified BOMs. Maintain a change log that tracks BOM updates and triggers selective re-explosion. For unchanged BOMs, serve from cache. This typically reduces processing volume by 70-80% in stable production environments.
Implement cache warming for high-usage BOMs during off-peak hours. Use a two-tier strategy: hot cache (memory) for top 20% frequently accessed BOMs, warm cache (database) for remaining 80%.
5. Query Execution Plan Analysis:
Regularly analyze execution plans and monitor:
- Table scan occurrences on MBOM tables
- Join operation costs for multi-level explosions
- Index usage statistics
- Query compilation times
Set up automated monitoring:
bom.query.monitoring.enabled=true
bom.slow.query.threshold=5000
bom.execution.plan.logging=true
Additional Recommendations:
- Partition MBOM_STRUCTURE table by effective_date for faster date-range queries
- Archive inactive BOMs older than 2 years to separate table
- Increase batch JVM heap to 8GB minimum
- Consider splitting BOMs with 8+ levels into separate high-priority batch job
- Implement batch job monitoring with alerts for queries exceeding 5-second threshold
Expected Results:
With these changes, you should see:
- Batch completion time: 8+ hours → 2-3 hours
- Individual BOM explosion: 15-30s → 1-3s
- Overall processing volume: Reduced by 70% through incremental approach
- Database load: Significantly lower due to efficient indexing and caching
Implement these changes in stages: indexes first, then caching, then parallel processing tuning. Monitor performance after each stage.