Performance: Index missing on odf_ca_task table for both MSSQL and ORACLE

Document ID : KB000026450
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Due to the missing index on the task table, performance will be significantly slower when copying a project from template which has associated tasks. The larger the task list, the slower the performance.

Steps to Reproduce:

  1. Setup CA PPM Action Trace for a user.
  2. Login to the application as the trace user.
  3. Create a project from template using a large dataset which has at least 70+ tasks linked to the template.
  4. Observe in the SQL trace log that the following statements will take on average 2200ms to complete each time they are called. Depending on the number of tasks assigned to the template being used, this code could be called literally 100's of times. For customers with large datasets this causes a huge performance hit. (odf-pmd.task.select.map, odf-pmd.task.select-ca.map, odf-pmd.task.update.odf_ca_task)

Expected Result: Project from template to complete within a reasonable amount of time.
Actual Result: Project from template takes 6+ min to complete when using 70+ tasks on template.

Solution:

This performance issue was caused by a small cache size (earlier only 8) to create only 8 ID values in the cache. Each time the cache was read, it would call the procedure again. The cache size has now been increased to 1000 and this reduced the performance because the procedure is not called so frequently.

This issue is documented as CLRT-21289 and is resolved with CLRT-57412 : Sequence cache size is too small, results in Oracle ALTER SEQUENCE and CMN_ID_SP high CPU load during Time Slicing with large numbers of inserts.

Resolved in Clarity 12.0.6 Generic Patch. Reference TEC522707
Resolved in Clarity 12.1.0 Generic Patch. Reference TEC542313
Resolved in Clarity 12.1.1
Resolved in Clarity 13.0