Improve Job(Datamart Rollup) Performance

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Datamart rollup Time facts & Time Summary job runs for long hours for 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 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 services.

5. Validate execution times of the job.