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

Document ID : KB000087824
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
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.
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