ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Improve Datamart Rollup Time Facts & Time summary job performance

book

Article ID: 127768

calendar_today

Updated On:

Products

Clarity PPM SaaS 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.