AWI slow when looking up job statistics

Products

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

Issue/Introduction

When looking up job statistics the response is very slow.

In the WP log the following type of query (UCDS_R/getstatistic) that takes minutes:

20200227/033911.774 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'SLUC' time: '895:683.286.999'
20200227/033911.774 - U00029131 UCUDB - List of bind parameters:
20200227/033911.774 - U00029132 UCUDB: 'AH_Client          = '
20200227/033911.774 - U00029132 UCUDB: 'AH_OH_Idnr         = 1632036'
20200227/033911.774 - U00003525 UCUDB: ===> 'Select * from AH where AH_Client = ? and AH_OH_Idnr = ? order by AH_TimeStamp1 desc'
20200227/033911.990 - U00003434 Server routine  'UCDS_R/getstatistics' required '14' minutes and '55,914' seconds for processing.
20200227/033911.990 -          <uc-env clientVersion="12.1.1+build.9812" request="oh_87" session="0000000117419220" useridnr="0001683140"><request idnr="1632036" name="getstatistics" src="oh"/>...

Cause

Since the Automic DB schema has the following index, this is often an issue with your Oracle Execution Plan:

NK_AH_OH_Idnr -) AH_Client, AH_Idnr, AH_OH_Idnr

Environment

Release : 12.1

Component : ACTION BUILDER

Resolution

To resolve the issue ask your DBA:
1. To check that the index NK_AH_OH_Idnr -) AH_Client, AH_Idnr, AH_OH_Idnr indeed exists

2. To provide the execution plan of the query select * from AH where AH_Client = ? and AH_OH_Idnr = ? order by AH_TimeStamp1 desc

3. To use the Oracle CBO to optimize the plan and possibly block paths that are unfavorable or slow

If these action do not lead to solution, open a case with Broadcom support