Great progress! Let me provide a comprehensive optimization strategy addressing all focus areas:
Report Query Optimization:
Your queries need tuning to work efficiently with ofc-23b’s query engine:
-
Analyze current query structure:
- Run EXPLAIN PLAN on your slowest maintenance reports
- Look for operations with high cost: HASH JOIN, SORT, TABLE ACCESS FULL
- Identify which tables are causing full scans
-
Optimize date filtering (critical in ofc-23b):
- Always include indexed date filters:
WHERE wo.WORK_ORDER_DATE >= :START_DATE
AND wo.WORK_ORDER_DATE <= :END_DATE
- Use TRUNC() function carefully - it prevents index usage
- Instead of: WHERE TRUNC(WORK_ORDER_DATE) = TRUNC(SYSDATE)
- Use: WHERE WORK_ORDER_DATE >= TRUNC(SYSDATE) AND WORK_ORDER_DATE < TRUNC(SYSDATE) + 1
- Reduce data volume with smart filtering:
- Add work order status filters: STATUS_CODE IN (‘OPEN’, ‘IN_PROGRESS’)
- Filter by asset organization early in the query
- Use EXISTS instead of IN for subqueries:
WHERE EXISTS (
SELECT 1 FROM ASSET_ASSIGNMENTS aa
WHERE aa.WORK_ORDER_ID = wo.WORK_ORDER_ID
)
-
Optimize joins:
- Join order matters - put most restrictive filters first
- Use INNER JOIN explicitly instead of comma-separated tables
- Avoid joining large history tables without date filters
-
Limit result sets appropriately:
- Add TOP N or ROWNUM filters for dashboard reports
- Use pagination for user-facing reports
- Consider summary reports instead of detail for historical data
Performance Optimization Configuration:
System-level settings need adjustment for ofc-23b:
- Adjust OTBI fetch size for maintenance reports:
- Navigate to BI Publisher Administration
- Set Report Fetch Size to 5000 (down from 10000 default)
- For specific reports, add to report properties:
<property name="oracle.bi.fetch.size" value="5000"/>
-
Enable result caching for frequently run reports:
- Go to Catalog > Report Properties
- Enable cache with 4-hour refresh for static reports
- Use real-time mode only for critical operational reports
-
Optimize subject area aggregation:
- Review ‘Asset Management - Maintenance Real Time’ subject area
- Check if aggregate tables are being used
- Verify materialized view refresh schedule (recommend: every 2 hours for maintenance data)
-
Memory allocation for report server:
- If reports still timeout, increase BI Server memory allocation
- Check current setting in EM Console > Capacity Management
- Increase heap size if utilization exceeds 80%
-
Parallel query execution:
- For large maintenance history reports, enable parallel execution:
/*+ PARALLEL(wo, 4) */
SELECT ...
- Use judiciously - consumes more resources
Database Indexes Review:
Critical for maintaining performance after upgrade:
- Verify existing indexes on key maintenance tables:
SELECT table_name, index_name, column_name, column_position
FROM all_ind_columns
WHERE table_owner = 'EAM'
AND table_name IN ('EAM_WORK_ORDERS', 'EAM_WORK_ORDER_OPERATIONS', 'EAM_ASSET_ACTIVITIES')
ORDER BY table_name, index_name, column_position;
-
Based on your full table scans, likely missing indexes on:
- WORK_ORDER_DATE (if not indexed)
- Composite index on (STATUS_CODE, WORK_ORDER_DATE)
- ASSET_ID for join performance
- ORGANIZATION_ID for multi-org filtering
-
Create custom indexes if needed (work with DBA):
CREATE INDEX EAM_WO_STATUS_DATE_IDX
ON EAM_WORK_ORDERS(STATUS_CODE, WORK_ORDER_DATE, ORGANIZATION_ID);
- Rebuild fragmented indexes:
- After upgrade, some indexes may be fragmented
- Identify candidates:
SELECT index_name, blevel, leaf_blocks, distinct_keys
FROM user_indexes
WHERE table_name LIKE 'EAM%'
AND blevel > 3;
- Rebuild if blevel > 3: ALTER INDEX index_name REBUILD ONLINE;
- Update statistics (you’ve started this):
- Complete the statistics gathering:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'EAM',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 4
);
- Schedule weekly auto-gather job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'EAM_STATS_GATHER',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''EAM''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=2',
enabled => TRUE
);
END;
Additional ofc-23b Specific Optimizations:
-
Review new partitioning options:
- ofc-23b supports partition pruning for date-range queries
- Verify maintenance tables are partitioned by month/quarter
- Ensure queries include partition key in WHERE clause
-
Leverage new analytical functions:
- ofc-23b has improved window functions performance
- Use RANK() and ROW_NUMBER() instead of correlated subqueries
-
Check for new subject area features:
- ofc-23b may have introduced pre-aggregated maintenance metrics
- Review ‘Asset Management - Maintenance Metrics’ subject area
- Use these for summary reports instead of detail queries
Monitoring and Validation:
-
Establish performance baselines:
- Document current execution times for key reports
- Set up alerts for reports exceeding thresholds
-
Create a performance monitoring report:
- Track query execution times from BI_QUERY_LOG
- Identify reports that need further optimization
-
Regular maintenance schedule:
- Weekly: Review slow query log
- Monthly: Regather statistics on high-churn tables
- Quarterly: Review and rebuild indexes
Implementing these optimizations systematically should get your maintenance reports back to 2-3 minute performance or better, even with the ofc-23b changes. Focus first on the query optimization and index validation, as these typically yield the biggest improvements.
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.