ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
AWI slow when looking up job statistics
book
Article ID: 185178
calendar_today
Updated On:
Products
CA Automic Workload Automation - Automation Engine
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