JWPs hang with high load due to a long running DB query

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Symptoms: 

• high load on all JWPs
• (high/medium) count of entries in JWP or JCP table
• long running Select database query (attached)

 

Problem: 

• AWI login not possible (especially with Kerberos or LDAP that depend on the JWP)

Environment

Release : 12.3

Component :

Cause

A 'big' query from in Process Monitoring, with 'Include deactivated Tasks' ticked, is repeated on each JWP, which blocks the login.

The repetition can be cause by:

• User clicking on the Filter button several tiles
• User clicking on the refresh button several times
• Auto refresh

Resolution

Solution:

In version v21 this behavior should disappear: in that version the concept of JWP roles is introduced, this will split which JWP does what, so JWPs that retrieve records in Process Monitoring cannot not block other JWPs with different roles anymore.

Workaround:

1. When an administrator is still logged into AWI, they can use a select statement on the JWP or JCP tables looking at baddr, cpaddr,... to find out which user causes the issue and manually disconnect this user. After disconnection the blocking statements disappear. 

2. Autorefresh can be limited:

• The user can switch off Autorefresh in "AWI Settings" by setting it to Manually for Process Monitoring.
• In the USER object the minimum refresh time can be set to a high values for instance 600, now the user cannot choose a lower value anymore:

User Session Settings

Additional Information

If you are not logged into the system you need to do the following:

• Determine which are the JWPs
• Look in the JWP logs for their Oracle Session IDs: U00003535 DB INFO: 'SESSION-ID                = <SID>'
• Use these SIDs to kill the Sessions in the DB.