Quality control report performance degrades with large inspection datasets

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

We’re experiencing severe performance issues with our quality control inspection reports in Oracle Fusion Cloud 23c. The report runs fine with smaller datasets (under 10K inspection records), but degrades significantly when processing monthly quality audits with 50K+ records. Execution time has increased from 45 seconds to over 8 minutes.

The report pulls inspection data across multiple quality plans and includes defect tracking metrics. We suspect the issue relates to how the query filters are structured and possibly table indexing. Current query approach:

SELECT qi.inspection_id, qi.plan_name, qr.defect_count
FROM quality_inspections qi
JOIN quality_results qr ON qi.inspection_id = qr.inspection_id
WHERE qi.inspection_date BETWEEN :start_date AND :end_date

The delays are impacting our monthly quality audits and compliance reporting cycles. Has anyone optimized similar BI Publisher reports for large quality datasets?

system 2

Building on all the suggestions here, let me provide a comprehensive optimization approach that addresses all three key areas: indexed query filters, partitioned tables, and report caching.

1. Indexed Query Filters Implementation: First, create composite indexes that match your query patterns:

CREATE INDEX idx_qi_date_plan ON quality_inspections(inspection_date, plan_name, inspection_id);
CREATE INDEX idx_qr_inspection ON quality_results(inspection_id, defect_count);

The composite index on quality_inspections covers your WHERE clause and JOIN column, allowing index-only scans. This alone should reduce execution time by 60-70%.

2. Partitioned Tables Strategy: Implement range partitioning on inspection_date:

-- Pseudocode - Key partitioning steps:
1. Create partitioned table quality_inspections_part with RANGE partitioning on inspection_date
2. Define monthly partitions for current year plus 2 prior years
3. Set up automatic partition creation for future months
4. Create local indexes on each partition for inspection_id and plan_name
5. Migrate existing data using parallel INSERT SELECT operations
6. Update BI Publisher data model to reference partitioned table

Partitioning enables partition pruning - when you query for a specific month, Oracle only scans that partition. With 50K records distributed across 36 monthly partitions, you’re now scanning ~1,400 records instead of 50K.

3. Report Caching Configuration: In BI Publisher, configure the quality control report with these cache settings:

  • Enable report caching in the Data Model properties
  • Set cache expiration to 12 hours for current month reports, 7 days for historical months
  • Use parameter-based cache keys (inspection_date range) so different date ranges maintain separate cache entries
  • Configure cache refresh schedules to align with your nightly inspection data loads

4. Query Optimization: Revise your query to leverage the new indexes and partitions:

SELECT /*+ INDEX(qi idx_qi_date_plan) */
       qi.inspection_id, qi.plan_name, qr.defect_count
FROM quality_inspections qi
JOIN quality_results qr ON qi.inspection_id = qr.inspection_id
WHERE qi.inspection_date BETWEEN :start_date AND :end_date
  AND qi.inspection_date >= TRUNC(:start_date, 'MM')

The additional TRUNC predicate helps Oracle identify the exact partition for monthly reports.

Expected Results: With all three optimizations implemented, you should see:

  • Initial query execution: 45-90 seconds (down from 8 minutes)
  • Cached report retrieval: 2-5 seconds
  • Minimal impact on daily inspection data loads (typically <5% overhead)

Implementation Order: Start with indexes (quickest win, no downtime), then implement caching (configuration change only), and finally plan the partitioning migration during a maintenance window. Monitor query performance after each step to validate improvements.

The insert performance concern is manageable - modern Oracle versions handle index maintenance efficiently, and you can use NOLOGGING options during bulk loads if needed.

This draft is based on general Oracle Fusion Cloud knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

I’ve seen this pattern before with quality management reports. The first thing to check is whether your quality_inspections and quality_results tables have proper indexes on the join columns and date filters. Without indexed query filters, Oracle is likely doing full table scans on 50K+ records. Run an EXPLAIN PLAN on your query to identify the bottleneck. Also, verify if the inspection_date column has a B-tree index - that’s critical for range queries with BETWEEN clauses.

4

Thanks for the suggestion. I ran EXPLAIN PLAN and confirmed we’re doing full table scans on both tables. The inspection_date column doesn’t have an index. Our DBA is concerned about adding indexes impacting insert performance during daily inspection data loads. Is there a way to balance query performance with data loading requirements?

5

The concern about insert performance is valid but often overestimated. For quality inspection data that’s primarily read-heavy during reporting cycles, the query performance gains far outweigh the minimal insert overhead. You could also consider partitioned tables by inspection_date (monthly or quarterly partitions). This allows Oracle to prune partitions during queries, scanning only relevant data ranges. Combined with local indexes on each partition, you’ll see dramatic improvements. We implemented this approach and reduced similar report times from 7 minutes to under 90 seconds.

timothy_tech 6

Tested this on Oracle Fusion Cloud 23D with 2M+ inspection records—the composite index on inspection_date and plan_name dropped our quality report runtime from 4 minutes to 18 seconds.

8

One aspect not mentioned yet is report caching at the BI Publisher level. If your quality audits run on relatively static data (inspections completed in previous months), you can configure BI Publisher to cache report results for a defined period. This is especially useful when multiple users access the same monthly quality reports. Set cache expiration based on your data refresh cycle - for historical quality data, even 24-hour caching can eliminate redundant expensive queries.

9

I’d also look at your JOIN strategy. When dealing with large quality datasets, consider whether you need all columns from both tables or if you can optimize with a covering index. Sometimes restructuring the query to use EXISTS or IN clauses instead of JOINs can leverage indexes more effectively, depending on your data distribution.