Performance - List of executions takes an extremely long time to load

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

When search for executions within the Automic Web Interface, the results are slow and upon review, the logs show an extremely long getstatistics call search for executions:

20210429/222136.306 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'SLUC' time: '29:402.839.858'
20210429/222136.306 - U00029131 UCUDB - List of bind parameters:
20210429/222136.306 - U00029132 UCUDB: 'AH_Client          = 100'
20210429/222136.306 - U00029132 UCUDB: 'AH_OH_Idnr         = 2632008'
20210429/222136.306 - U00003525 UCUDB: ===> 'select AH.*, coalesce(EH.EH_Status, AH.AH_Status) as EH_Status from AH left join EH on EH.EH_AH_Idnr = AH.AH_Idnr where AH_Client = ? and AH_OH_Idnr = ? order by AH_TimeStamp1 desc'
20210429/222136.431 - U00003434 Server routine  'UCDS_R/getstatistics' required '0' minutes and '29,547' seconds for processing.

This may occur on some jobs, or across the entire system.

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Cause

Unoptimized SQL query

Resolution

This query has been optimized with the release of 12.3.7.  To fully resolve this issue, the utilities, initialdata, AWI, and automation engine components should be upgraded to 12.3.7 or higher

Workaround:

To mitigate this issue, a DBA would need to review the execution plan and see if any improvements can be made within the environment. To do this, please first set the 11th digit of SERVER_OPTIONS in UC_SYSTEM_SETTING to a numeric value (the default is 3). That will give the detailed output above:

Note: This is an example and the bind parameter values will be different for your system

20210429/222136.306 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'SLUC' time: '29:402.839.858'
20210429/222136.306 - U00029131 UCUDB - List of bind parameters:
20210429/222136.306 - U00029132 UCUDB: 'AH_Client          = 100'
20210429/222136.306 - U00029132 UCUDB: 'AH_OH_Idnr         = 2632008'
20210429/222136.306 - U00003525 UCUDB: ===> 'select AH.*, coalesce(EH.EH_Status, AH.AH_Status) as EH_Status from AH left join EH on EH.EH_AH_Idnr = AH.AH_Idnr where AH_Client = ? and AH_OH_Idnr = ? order by AH_TimeStamp1 desc'

The two bind parameters can then be put into the direct SQL statement:

select AH.*, coalesce(EH.EH_Status, AH.AH_Status) as EH_Status from AH left join EH on EH.EH_AH_Idnr = AH.AH_Idnr where AH_Client = 100 and AH_OH_Idnr = 2632008 order by AH_TimeStamp1 desc

Provide that to your DBA to run direct against the DB and review the execution plan. This should confirm that it is optimized within the system and if any hints can be applied.

The other major workaround would be to remove number of records for the job(s) experiencing the issue by removing records from the DB via ILM or Automic Utilities