Support Article

Work objects taking a long time to open in production

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

 

Published October 22, 2016 - Updated October 28, 2016

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.