Talent Management data model timeouts with large datasets despite enabling SQL pruning in ohcm-23c

HCM Oracle HCM Cloud
, , , , , , , ,
1

We’re experiencing significant timeout issues with our Talent Management BI Publisher reports when querying large datasets (500K+ employee records). The data model queries are taking 8-12 minutes and frequently timing out before completion.

Our current query structure pulls comprehensive talent data including performance ratings, succession plans, and career development history. We’ve noticed the timeout occurs most consistently during the succession planning data extraction phase.

SELECT emp.person_id, emp.assignment_id,
       succ.succession_plan_id, succ.readiness_level,
       perf.rating, perf.review_period
FROM per_all_people_f emp
JOIN succession_plans succ ON emp.person_id = succ.person_id
JOIN performance_reviews perf ON emp.person_id = perf.person_id
WHERE TRUNC(SYSDATE) BETWEEN emp.effective_start_date
      AND emp.effective_end_date

The execution plan shows full table scans on the succession_plans table. We need guidance on SQL pruning techniques and whether parallel execution would help with this volume. Has anyone successfully optimized similar large-scale Talent Management queries?

2

Let me provide a comprehensive solution addressing all three optimization areas you need to focus on.

SQL Pruning Strategy: First, rewrite your query to eliminate the TRUNC function and add proper filtering:

SELECT /*+ PARALLEL(4) */ emp.person_id, emp.assignment_id,
       succ.succession_plan_id, succ.readiness_level,
       perf.rating, perf.review_period
FROM per_all_people_f emp
JOIN succession_plans succ ON emp.person_id = succ.person_id
JOIN performance_reviews perf ON emp.person_id = perf.person_id
WHERE SYSDATE BETWEEN emp.effective_start_date
      AND emp.effective_end_date
  AND succ.active_flag = 'Y'
  AND perf.review_period >= ADD_MONTHS(SYSDATE, -24)

The key changes: removed TRUNC to allow index usage, added active_flag filter on succession plans, and limited performance reviews to last 24 months. This pruning alone should reduce your dataset by 40-50%.

Explain Plan Analysis: Run this before and after optimization:

EXPLAIN PLAN FOR [your query];
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for these red flags in the output:

  • Full table scans on large tables (succession_plans, performance_reviews)
  • Hash joins without proper indexes
  • High cost operations (>10000) in the execution path

Work with your DBA to add these indexes:

  • succession_plans: (person_id, active_flag, succession_plan_id)
  • performance_reviews: (person_id, review_period, rating)
  • per_all_people_f: (person_id, effective_start_date, effective_end_date)

Parallel Execution Configuration: For 500K+ records, parallel execution is essential. In your BI Publisher data model:

  1. Add the PARALLEL hint (shown above) with degree 4-8 depending on your HCM Cloud tier
  2. Configure data model properties: Row Fetch Size = 1000, Scalable Mode = On
  3. Set query timeout to 600 seconds (10 minutes) in Administration > System Settings
  4. Enable data model caching for frequently run reports

Additional Optimizations:

  • Break complex data models into smaller, focused models
  • Use bind variables for date ranges: WHERE review_period >= :P_START_DATE
  • Consider materialized views for succession planning data (refreshed nightly)
  • Implement report bursting to process subsets in parallel

Monitoring Results: After implementing, track these metrics:

  • Query execution time (target: under 3 minutes)
  • Explain Plan cost (should drop by 60-70%)
  • Timeout occurrences (should eliminate completely)
  • System resource usage during report runs

This three-pronged approach - SQL pruning, Explain Plan-driven indexing, and parallel execution - should resolve your timeout issues. Start with the SQL pruning and indexing, then add parallelism. We’ve used this exact methodology to optimize similar Talent Management reports from 12+ minute timeouts to consistent 2-3 minute successful runs.

3

Don’t overlook the importance of SQL pruning in the data model itself. In the BI Publisher data model SQL, add explicit date range filters that leverage bind variables from your report parameters. This ensures you’re not pulling the entire history unnecessarily. Also, if you’re only displaying current assignments, add assignment_status_type filters early in the query.

4

Have you considered using incremental data extraction? For Talent Management reports that don’t need real-time data, we implement a staging approach where the heavy lifting happens overnight in scheduled jobs. The BI Publisher report then queries pre-aggregated staging tables instead of hitting the live transactional tables. This is especially effective for succession planning data which doesn’t change frequently throughout the day.

5

I’ve dealt with similar timeout scenarios in Talent Management reporting. First recommendation - run an Explain Plan on that query to identify the bottlenecks. The full table scan on succession_plans is definitely a red flag.

For datasets over 500K records, you should absolutely consider adding indexes on the join columns (person_id in succession_plans and performance_reviews tables). Also, that date filter on per_all_people_f should be moved earlier in the execution path.

6

The succession planning join is killing your performance. Consider breaking this into separate data models - one for core employee data, another for succession plans. Then use BI Publisher’s data linking feature to combine them at report runtime. This approach has cut our query times by 60-70% for similar volumes.

7

Check your BI Publisher data model settings too. Under Advanced Options, there’s a ‘Row Fetch Size’ parameter that defaults to 10. Increasing this to 500-1000 can dramatically reduce round trips to the database. We went from 12 minute timeouts to 4 minute successful runs just by tuning this setting alongside query optimization.