Improve Datamart Rollup Time Facts & Time summary job performance

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS - Application
• Clarity PPM On Premise

Issue/Introduction

Datamart rollup Time facts & Time Summary job runs for long hours for customer's with large number of resources and time periods. Since it runs for a longer period of time and most of the time is spent on update NBI_RESOURCE_TIME_SUMMARY table, UNDO tablespace is exhausted causing Database issues.

Cause

Index which is selected to perform the update is not optimal causing performance drop.

Environment

15.4,15.4.1,15.5,15.5.1

Resolution

1. Stop Clarity PPM services.
2. Drop the index NBI_RESOURCE_TIME_SUMMARY_B2.
3. Re-Create index NBI_RESOURCE_TIME_SUMMARY_B2 using query
CREATE INDEX "NBI_RESOURCE_TIME_SUMMARY_B2" ON "NBI_RESOURCE_TIME_SUMMARY" ("OBS1_UNIT_ID","CALENDAR_TIME_KEY").
4. Start Clarity PPM Services.
5. Validate execution times of the job.