AWI slow when looking up job statistics
Article ID: 185178
CA Automic Workload Automation - Automation Engine
CA Automic One Automation
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"/>...
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
Release : 12.1
Component : ACTION BUILDER
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