Work objects taking a long time to open in production
Work objects taking a long time to open in productionThe work objects do eventually open but take 5-6 minutes to show on UI.
Error MessagesFrom 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 ReproduceTry to open a Work Object.
Root CauseA 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.
ResolutionMake 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.
Published October 22, 2016 - Updated October 28, 2016