Intermittent slow performance starting jobs

Products

• CA Workload Automation AE

Issue/Introduction

Intermittent issue:

It took almost took 30seconds to trigger the first job in the box

edh2-z4-AO-autosys-stat-box-MoSu                                 01/27/2022 23:10:04  01/27/2022 23:10:56  SU    27248307/1 0     

 edh2-z4-AO-autosys-stat-fa                                      01/27/2022 23:10:33  01/27/2022 23:10:37  SU    27248307/1 0     

 edh2-z4-AO-autosys-stat-fa-sendmail                             01/27/2022 23:10:51  01/27/2022 23:10:51  SU    27248307/1 0     

 edh2-z4-AO-autosys-stat-fa-scp2dev                              01/27/2022 23:10:55  01/27/2022 23:10:56  SU    27248307/1 0     

Environment

Release : 11.3.6

Component : CA Workload Automation AE (AutoSys)

Resolution

When the scheduler was run in debug mode (ISDBGACTIV=LIGHT,HEAVY,AFM,COMM,DBQUERY,GBE) it was seen that the database queries related to the UJO_COMM_* tables were slow.

Example:
[01/28/2022 11:53:42.2174] 17709 1586551616 L:128 start_rem_cyb.cpp 2807 DBQ CybSendAfm  ret[1] elapsed [12279351]

The DBA reviewed the Oracle AWR report and noticed the poor performance of the queries. 

After further investigation it was discovered the EXPLAIN PLAN for the SQL ID for the poor queries changed a few days prior, when the poor performance started. 

The DBA reverted the change to resolve the issue.