Slowness in Backlog Job status changes

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

After upgrading to Applications Manager(AM) 9.6 or 9.6.1, there are intermittent or consistent slowness Job status changes in the Backlog. 

This includes, but not limited to, changes from Queued to Running, Finished to dropping into History, and time between the finishing of one component to the starting of another component in a Process Flow.

A review of the RmiServer logs during the time of issue show random slowness in the return times for the procedure aw_submit_chains. Example in bold below:

13:59:32.218 Timer-5: .DBAccess: getProcedureData():  sID-12649272 aw5.submit_chains elapsed ms 20
14:04:08.149 Timer-5: .DBAccess: getProcedureData():  sID-12649301 aw5.submit_chains elapsed ms 248149
14:06:05.879 Timer-5: .DBAccess: getProcedureData():  sID-12649301 aw5.submit_chains elapsed ms 117729
14:09:03.752 Timer-5: .DBAccess: getProcedureData():  sID-12649301 aw5.submit_chains elapsed ms 177873
14:09:50.658 Timer-5: .DBAccess: getProcedureData():  sID-12649300 aw5.submit_chains elapsed ms 46906
14:09:50.705 Timer-5: .DBAccess: getProcedureData():  sID-12649300 aw5.submit_chains elapsed ms 46

Environment

Applications Manager version 9.6, 9.6.1

Cause

For the first time, Applications Manager 9.6 and above moves from using Oracle dbms_pipes (in memory) to Oracle Advanced Queues (physical tables). While Oracle AQ is more robust and persistent than dbms_pipe, improvements to AM code is required to make the best use of the change from dbms_pipe to AQ. 

Resolution

Solution:

A fix will be included in Applications Manager version 9.6.2 and/or 9.7

Workaround for 9.6 and 9.6.1:

Fixes can be applied by running two sql scripts that are attached to this article, am_aq_aq_fix.sql and aw_operation_body_aq_fix.sql.

1. Download attached aq_fix.zip
2. Extract am_aq_aq_fix.sql and aw_operation_body_aq_fix.sql from zip and move to the master's $AW_HOME/sql directory
3. Stop Applications Manager processes
4. From the $AW_HOME/sql directory, run a sqlplus command and connect to the AM database as the AM Oracle User
5. Run the commands from the sqlplus command line and exit:
   
   @am_aq_aq_fix.sql
   @aw_operation_body_aq_fix.sql
   @aw_post_checks.sql  (this already exist in sql directory and needs to run after the above to recreate AQ)
6. Restart Applications Manager processes

To revert changes:

1. From the same sql directory, follow the same steps replacing the commands with the below commands:
   
   @am_aq.sql
   @aw_operation_body.sql
   @aw_post_checks.sql  (this already exist in sql directory and needs to run after the above to recreate AQ)