Improve Datamart Rollup Time Facts & Time summary job performance
Article ID: 127768
CLARITY PPM FOR ITGCLARITY PPM FEDERALClarity PPM SaaSClarity PPM On Premise
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.
Index which is selected to perform the update is not optimal causing performance drop.
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.