Datamart Rollup - Time Facts and Time Summary performance - large dataset

Products

• Clarity PPM SaaS

Issue/Introduction

The table NBI_PM_PROJECT_TIME_SUMMARY has over 100M records and is taking a long time to process, if it finishes at all

This causes higher usage of UNDO tablespace as it's an update statement that is consuming more time.  

Populating the NBI_PM_PROJECT_TIME_SUMMARY table is taking time and inserting several million records.

It appears there are some queries that can be optimized.

 

 

Below are the time-consuming update statements which run as part of this job, 

============================================================

Query 1:

update NBI_RESOURCE_TIME_SUMMARY p
set (external_resources, internal_resources) =
(select external_resources, internal_resources
from NBI_RESOURCE_TIME_SUMMARY c
where calendar_time_key = :"SYS_B_0"
and (c.obs1_unit_id = p.obs1_unit_id or c.obs1_unit_id is null)
and (c.obs2_unit_id = p.obs2_unit_id or c.obs2_unit_id is null)
and (c.obs3_unit_id = p.obs3_unit_id or c.obs3_unit_id is null)
and (c.obs4_unit_id = p.obs4_unit_id or c.obs4_unit_id is null)
and (c.obs5_unit_id = p.obs5_unit_id or c.obs5_unit_id is null)
and rownum = :"SYS_B_1")
where p.calendar_time_key = :"SYS_B_2"

============================================================

The following update statement is running multiple times. It looks like that one per calendar key. One update takes more than 20 minutes.

Query 2:

update nbi_pm_project_time_summary p
set (approved_projects, not_approved_projects, budget_hours, baseline_hours,
etc_hours, allocated_hours, cost_base_labor, cost_base_equip,
cost_base_expense, cost_base_matl, cost_base_total,
cost_etc_labor, cost_etc_equip, cost_etc_expense, cost_etc_matl,
cost_etc_total, open_tasks, closed_tasks,
open_issues, closed_issues, open_risks, closed_risks,
number_of_resources, planned_value, earned_value) =
(select approved_projects, not_approved_projects, budget_hours, baseline_hours,
etc_hours, allocated_hours, cost_base_labor, cost_base_equip,
cost_base_expense, cost_base_matl, cost_base_total,
cost_etc_labor, cost_etc_equip, cost_etc_expense, cost_etc_matl,
cost_etc_total, open_tasks, closed_tasks,
open_issues, closed_issues, open_risks, closed_risks,
number_of_resources,  planned_value, earned_value
from nbi_pm_project_time_summary c
where calendar_time_key = :"SYS_B_0"
and (c.obs1_unit_id = p.obs1_unit_id or c.obs1_unit_id is null)
and (c.obs2_unit_id = p.obs2_unit_id or c.obs2_unit_id is null)
and (c.obs3_unit_id = p.obs3_unit_id or c.obs3_unit_id is null)
and (c.obs4_unit_id = p.obs4_unit_id or c.obs4_unit_id is null)
and (c.obs5_unit_id = p.obs5_unit_id or c.obs5_unit_id is null)
and rownum = :"SYS_B_1")
where p.calendar_time_key = :"SYS_B_2"

 

 

 

 

Environment

Release : 16.0.1

Component : Clarity Financial Management

Cause

Reported as DE65407 to review the queries for optimization.

 

Resolution

Workaround: Reduce the number of periods in the related time slices.

This is reported as DE65407 and is resolved in 16.1.0.

 

Additional Information

Improve Datamart Rollup Time Facts & Time summary job performance

 

Datamart Extraction and Datamart Rollup are taking long time