Capacity planning data model with complex joins causing slow query timeouts

ERP Oracle Fusion Cloud
, , , , , , , ,
1

We’re experiencing severe timeout issues with our capacity planning reports in BI Publisher on OFC 22d. The data model joins multiple capacity tables (resource availability, demand forecasts, utilization metrics) and consistently times out after 5 minutes.

The query uses SELECT * across 6 tables with LEFT JOINs, pulling historical data for 18 months. When we try to generate monthly capacity reports, users get “report unavailable” errors. We’ve noticed the query doesn’t leverage any pruning configuration, and aggregate metrics are recalculated on every report run.

Has anyone dealt with similar performance issues in capacity planning data models? Specifically looking at SQL optimization approaches and caching strategies that work well with BI Publisher’s architecture.

2

One thing not mentioned yet - BI Publisher’s built-in query caching for aggregate metrics. In 22d, you can configure cache refresh schedules for capacity calculations that don’t change frequently. Historical utilization data is perfect for this. Set up cache groups for monthly aggregates and only run live queries for current-period data.

3

Let me provide a comprehensive solution based on what worked for our capacity planning optimization:

SQL Pruning Configuration: In your data model properties, add SQL pruning filters that automatically limit date ranges. Configure partition pruning to target specific monthly partitions rather than scanning the entire 18-month dataset. This is critical for capacity tables with time-series data.

Selective Column Specification: Replace SELECT * with explicit column lists. Analyze your report layout and identify exactly which capacity metrics you’re displaying. We reduced our column count from 47 to 12 and saw immediate improvement. For example, instead of pulling all resource attributes, specify only resource_id, capacity_hours, utilization_pct.

Query Caching for Aggregates: Implement BI Publisher’s query result caching specifically for aggregate capacity metrics. Configure cache groups for:

  • Historical monthly utilization (refresh weekly)
  • Resource availability summaries (refresh daily)
  • Demand forecast aggregates (refresh on-demand)

Set cache expiration based on data volatility - historical months never change, so cache them indefinitely.

Explain Plan Analysis and Join Optimization: Run Explain Plan and focus on these optimizations:

  1. Convert LEFT JOINs to INNER JOINs where referential integrity exists
  2. Reorder joins to process smallest result sets first
  3. Add indexes on join columns (resource_id, period_date)
  4. Use subqueries for complex aggregations rather than inline calculations

Example optimization:

-- Instead of calculated join field
SELECT cp.resource_id, um.utilization_pct
FROM capacity_plan cp
LEFT JOIN utilization_metrics um
  ON TRUNC(cp.period_date) = um.metric_date

-- Use indexed direct join
SELECT cp.resource_id, um.utilization_pct
FROM capacity_plan cp
INNER JOIN utilization_metrics um
  ON cp.period_date = um.metric_date
  AND cp.resource_id = um.resource_id
WHERE cp.period_date >= ADD_MONTHS(SYSDATE, -3)

Additional Recommendations:

  • Enable parallel query execution in data model advanced settings
  • Implement incremental refresh: cache prior months, query current month only
  • Consider creating a denormalized capacity summary table for frequently accessed aggregates
  • Set appropriate fetch size (we use 500 rows) to balance memory and network efficiency

After implementing these changes, our capacity planning reports went from timing out to completing in under 60 seconds, even with 24-month historical analysis.

4

Check your data model connection pool settings too. With complex joins, you might be hitting connection timeout limits before query timeout. Increase the connection timeout parameter and review your JDBC settings in the BI Publisher configuration.