Slow UI Performance due to 'UCDS_R/getexecutorlist' server routine

book

Article ID: 87824

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine AUTOMIC WORKLOAD AUTOMATION

Issue/Introduction

Error Message :
U00003525 UCUDB: ===> 'SELECT HOST.*, HACL.*, ACTEX.*, OH_Name, OH_LastDate, OH_HostAttrTypeDst ,(SELECT COUNT(*) FROM HACL WHERE HACL_OH_IDNR=OH_IDNR) AS DIVDB_INT4 ,COALESCE(ACTEX_AliveFlag, 'I') AS DIVDB_STRING FROM HOST, HACL, OH LEFT OUTER JOIN...

It takes over three minutes to refresh the System Overview's agent list in the Java UserInterface.

Investigation
  1. Launch the Java UI client
  2. Click on the System Overview
  3. Click on Agents
  4. Refresh (F5)
  5. Check the PWP log
The PWP log shows a long running server routine, UCDS_R/getexecutorlist:

U00003525 UCUDB: ===> 'SELECT HOST.*, HACL.*, ACTEX.*, OH_Name, OH_LastDate, OH_HostAttrTypeDst ,(SELECT COUNT(*) FROM HACL WHERE HACL_OH_IDNR=OH_IDNR) AS DIVDB_INT4 ,COALESCE(ACTEX_AliveFlag, 'I') AS DIVDB_STRING FROM HOST, HACL, OH LEFT OUTER JOIN ACTEX ON OH_Idnr = ACTEX_OH_Idnr WHERE OH_OType = 'HOST' AND OH_Idnr = HOST_OH_Idnr AND OH_Idnr = HACL_OH_Idnr AND OH_DeleteFlag = 0 AND OH_Idnr >= 100000 AND OH_IsTemplate = 0 AND HACL_Client = ? ORDER BY DIVDB_STRING , OH_Name' 

U00003434 Server routine 'UCDS_R/getexecutorlist' required '4' minutes and '48,625' seconds for processing.

Results
Actual:  Takes over three minutes to refresh the agent list.
Expected:  The agent list gets refreshed instantaneously.


 

Cause

Cause type:
Configuration
Root Cause: The DBA must check/trace/profile why the SQL took too long to execute on the database.

Environment

Release: AUTWAB99000-11.0-Automic Workload Automation-Base Edition
Component:

Resolution

1.  Use MARS CONNECTION instead of server side in the ODBC connect string settings of the ucsrv.ini.
2.  Add following index to the DB: 
create NONCLUSTERED INDEX ACTEX_AgentList on ACTEX (ACTEX_Oh_Idnr);

Note:
Automic will not change the DB schema because this table is gone in v12.1. 


Fix Status: No Fix

Fix Version(s):
N/A

Additional Information

Workaround :
N/A