AWA: JCP shows long running calls against IndexBuilder/TIMER

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation CA Automic Operations Manager CA Automic Oracle

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.

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.