Attempting to display User Activities causes latency of Graphical Clients (ECC or AWI)

book

Article ID: 84551

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Attempting to open the "Service Catalog" in Enterprise Control Center (ECC)  ( "My Catalog" in the Automic Web Interface (AWI)) causes significant latency and makes all graphical clients (ECC, AWI and UserInterface) unresponsive.

When checking the logs many time critical DB calls against table XAO can be seen:
 
20170222/171728.984 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'SUCB' time: '683:659.045.999'
20170222/171728.984 - U00003525 UCUDB: ===> 'Select XAO_Idnr, XAO_Client, XAO_OType, XAO_OH_Idnr, XAO_USR_Idnr, XAO_Name, XAO_Timestamp1, XAO_Type, XAO_MsgNr, XAO_MsgInsert, USR_FirstName, USR_LastName from XAO,USR where XAO_USR_Idnr = USR_OH_Idnr and XAO_Client = ? and XAO_Timestamp1 <= ? and XAO_Type in(?) and XAO_OType in(?) order by XAO_Timestamp1 desc, XAO_Idnr desc'
20170222/170246.193 - U00003434 Server routine 'UCDS_R/getchangelog' required '13' minutes and '2,249' seconds for processing.
20170222/170246.193 - <uc-env clientVersion="11.2.4+low.build.8171" request="_32" session="0000000050790790" useridnr="0007307082"><request name="getchangelog" src="">...

This query is run by the Automation Engine to provide the view "User activities" in the Service Catalog tab.

Cause

Cause type:
Defect
Root Cause: Retrieving changelog entries (XAO table) causes high latency

Environment

OS Version: N/A

Resolution

As a workaround, the following can be done:

drop index UK_XAO_GetChLog;

If using ILM:
 -- create index NK_XAO_GetChLog on XAO (XAO_Client,XAO_USR_Idnr,XAO_Type,XAO_OType,XAO_TimeStamp1,XAO_Idnr) local online;

If not using ILM:
  -- create index NK_XAO_GetChLog on XAO (XAO_Client,XAO_USR_Idnr,XAO_Type,XAO_OType,XAO_TimeStamp1,XAO_Idnr) tablespace UC4_INDEX online;
 
Long term fix is also planned for future release.