MQOWP count growing rapidly

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The number of entries in MQOWP keeps growing after a restart of the Automation Engine and reaches above 1 million or more.

A rebuild of the indexes or reorganization of the MQ tables does not lead to improvement.


Procedure to determine if you suffer from this issue:

1. In the WP logs:
- Find the WP with role O (OWP)

Search all WP logs for the most recent message:
U00003343 Server '<system>#WP<xxx>' processes roles 'O'.
 

- Analyse the OWP log for the message:

U00003524 UCUDB: ===> Time critical DB call!       OPC: 'READ' time: '1:490.537.205'

If the duration (seconds:milliseconds.xxx.yyy) is (slowly) increasing you suffer from this issue.
 

2. In the database execution plan:
- Use the function EXPLAIN PLAN

Investigate the execution plan of the the following statement:
SELECT * FROM (SELECT * FROM MQ1OWP WHERE MQOWP_SchedTime<=:A0001 ORDER BY MQOWP_Priority, MQOWP_SchedTime, MQOWP_PK) WHERE rownum <= ?

- Determine what the Bytes, CPU, I/O and Cost columns indicate

If you notably confirm that the first step is not INDEX FULL SCAN but TABLE ACCESS FULL you likely suffer from this issue

Environment

This is more like likely to happen in environments with thousands of agents

Cause

During the shutdown of the Automation Engine, the Agents keep writing log messages in their log files. If the shutdown takes long, and thus there are a lot of messages to be repatriate from the Agent log to the AE DB, it is possible that the MQOWP table and associated WP with role 'O' do not manage to catch up.

Resolution

The problem is likely on the database side.

First, rebuild the indexes of the MQ1OWP or MQ2OWP, depending on which one is active. (Or just in case rebuild indexes for both.)

Since all used fields are indexed, this should speed up the query significantly.

 

If the OWP does not go down (fast enough), ask you DBA to investigate and optimize the so-called 'execution plan' of the following statement:

SELECT * FROM (SELECT * FROM MQ1OWP WHERE MQOWP_SchedTime<=:A0001 ORDER BY MQOWP_Priority, MQOWP_SchedTime, MQOWP_PK) WHERE rownum <= ?

Once the statement is optimized the issue should go away quickly.