JWPs at 100 % utilization when using SLO objects

Products

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

Issue/Introduction

On a system where SLO objects are defined, the JWPs are at 100 % showing the following Time Critical DB cal in their logs:

20240328/103443.804 - 44 U00003525 UCUDB: ===> 'SELECT AH_Idnr FROM AH WHERE AH_OH_Idnr = ? AND AH_Status = 0'
20240328/103443.804 - 44 U00003434 Server routine 'SlmComponent/TRG_SLM' required '0' minutes and '8' seconds for processing.
20240328/103452.450 - 44 U00003524 UCUDB: ===> Time critical DB call! OPC: 'SCLO' time: '8630ms'
20240328/103452.450 - 44 U00029131 UCUDB - List of bind parameters:
20240328/103452.450 - 44 U00009907 Memory view '(BINDPAR: 1)' (Address='n/a', Length='000004')
20240328/103452.450 - 44 00000000 000F953C >...<<
20240328/103452.450 - 44 >1021244<

Cause

This is a defect caused by the inefficient use of an index

Resolution

Workaround:

Ask your DBA to add a temporary index called NK_AH_SLO_temporary on AH for columns on AH_OH_IDNR, AH_STATUS, AH_IDNR

Note: While creating the temporary Index please make sure that the columns are in the exact order as described above

Solution:

Update to a fix version listed below or a newer version if available.

Fix version:

Component(s): Automation Engine

Automation.Engine 24.0.0 - Available

Automation.Engine 21.0.10 - Available

Additional Information

Defect ID: AE-33455

Defect Short Description: Improved SQL when adding a SLO violation

Defect Public Description: A problem has been solved, where the SQL query when adding a SLO violation was improved.