AWA: JCP shows long running calls against IndexBuilder/TIMER
Article ID: 193164
Updated On:
Products
Issue/Introduction
After upgrading to 12.2+, 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 > ?)'
Cause
These are due to potential performance issues within the database the Automation Engine is located.
Environment
Release : 12.3
Component : AUTOMATION ENGINE
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.
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
