AWA: JCP/JWP shows long running calls against IndexBuilder/TIMER
Article ID: 193164
Updated On:
Products
Issue/Introduction
After upgrading to 12.2 or 12.3, the JCP shows a large number of time critical calls similar to:
20200610/014816.357 - 41 U00003525 UCUDB: ===> 'SELECT OH_Idnr, OH_Client, OH_OType, COALESCE(OH_ModDate, OH_CrDate) AS OH_ModDate , OFS_ModDate FROM OH left join OFS ON OH_Idnr = ofs_oh_idnr_o WHERE OH_DELETEFLAG = 0 AND (OH_ModDate > ? OR OH_CrDate > ? OR OFS_ModDate > ?)'
20200610/014817.456 - 41 U00003524 UCUDB: ===> Time critical DB call! OPC: 'SLCT' time: '1072ms'
20200610/014817.456 - 41 U00003525 UCUDB: ===> 'SELECT OH_Client, COUNT(OH_Idnr) from OH GROUP BY OH_Client'
20200610/014817.478 - 41 U00003434 Server routine 'IndexBuilder/TIMER' required '0' minutes and '7' seconds for processing.
Or in later versions:
20210214/040823.981 - 30 U00003525 UCUDB: ===> 'SELECT OH_Idnr, OH_Client, OH_OType, COALESCE(OH_ModDate, OH_CrDate) AS OH_ModDate , OFS_ModDate, OH_LastDate FROM OH left join OFS ON OH_Idnr = ofs_oh_idnr_o WHERE OH_DELETEFLAG = 0 AND (OH_ModDate > ? OR OH_CrDate > ? OR OFS_ModDate > ? OR OH_LastDate > ?)'
Version 21.0, these show up in the JWP logs, specifically the one covering role IDX
Environment
Release : 12.3
Component : AUTOMATION ENGINE
Cause
These are due to potential performance issues within the database the Automation Engine is located.
Resolution
The indexbuilder is running two statements as part of generating the lucene index for advanced search:
1.) SELECT OH_Idnr, OH_Client, OH_OType, COALESCE(OH_ModDate, OH_CrDate) AS OH_ModDate , OFS_ModDate FROM OH left join OFS ON OH_Idnr = ofs_oh_idnr_o WHERE OH_DELETEFLAG = 0 AND (OH_ModDate > ? OR OH_CrDate > ? OR OFS_ModDate > ?)
and:
2.) SELECT OH_Client, COUNT(OH_Idnr) from OH GROUP BY OH_Client
There are a few items that could narrow down the time loss:
For query 1, there are two major tables it is joining: folders (OFS) and objects (OH). Check the amount listed for both and, if possible, minimize the number if there are non-essential objects and folders present. Also confirm that the execution plan is utilizing indexes for all searches.
For query 2, compare the time and results of running the above versus:
SELECT OH_Client, COUNT(OH_Idnr) from OH GROUP BY OH_Client where OH_DELETEFLAG = 0
If the second query shows a great increase in performance and/or much lower numbers of results, this means that the system has a large number of deleted objects that have not been removed with our utilities. Remove and rerun the above.
As a workaround, you can increase the value of INDEXSEARCH_INTERVAL in UC_SYSTEM_SETTINGS within client 0; however, this will increase the period in which new objects are not indexed for the advanced search.
Also a index rebuild of the indexes of OH can improve the time this query takes to complete.
A restart of the JCPs in 12.x is required. In 21.0, a restart of the JWPs is required.
Additional Information
If there are 1 million+ active objects in OH, it is likely that the query will take just over a second to complete, in that case consider the message as health indicator. If the message reports a number between 1000 and 3000 milliseconds there is no problem. If the average values is higher than 3000 look at the actions above.
Feedback
