SA-29484

Summary

Work objects taking a long time to open in production
The work objects do eventually open but take 5-6 minutes to show on UI.

Error Messages

From alert logs:

Database operation took more than the threshold of 500 ms: 489,474 ms SQL: SELECT PXHISTORYFORREFERENCE AS "pxHistoryForReference" , PYMEMO AS "pyMemo" , PXTIMECREATED AS "pxTimeCreated", PZINSKEY as "pxInsHandle" from yourTable WHERE pxObjClass LIKE ? AND ( ( PXHISTORYFORREFERENCE = ? ) )

WHERE pxObjClass LIKE 'History-YourClass-ABC-Inception-Work-YourClassName%' AND ( ( PXHISTORYFORREFERENCE = 'YOURCLASS-ABC-INCEPTION-WORK-YOURCLASSNAME AA-12345' ) ); 

Steps to Reproduce

Try to open a Work Object.

Root Cause

A defect or configuration issue in the operating environment.  From the alert logs see that the database query taking a long time.  Oracle histograms are calculated on the first 32 characters of a column.  Since the values in the column are the same for more than the first 32 characters the histogram is actually blocking the index from being used and the result is a full table scan.  In this case both the pxObjClass and pxHistoryForReference column values are running into this issue.
 

Resolution

Make the following change to the operating environment: 

Delete the histogram on the database table columns per this article to improve the performance in query execution.

https://pdn.pega.com/troubleshooting-how-solve-similar-characters-issue-sql-query-oracle-database