XOG integration is hanging when high volume of CMN_SESSIONS Impersonation query runs

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

XOG integration is hanging after 16.2.3.2 upgrade, high volume of CMN_SESSIONS Impersonation query runs and high number of records in CMN_SESSIONS table (500K). 

• DBA checked and found large volumes (1.4M) of the below query:
• SELECT IMPERSONATED_BY FROM CMN_SESSIONS WHERE IMPERSONATED_BY IS NOT NULL AND USER_ID = :B2 AND LAST_USED_BY_IMPERSONATED = :B1 AND ROWNUM = 1 AND CREATED_DATE = (SELECT MAX(CREATED_DATE) FROM CMN_SESSIONS WHERE USER_ID = :B2 AND IMPERSONATED_BY IS NOT NULL AND LAST_USED_BY_IMPERSONATED = :B1 AND ROWNUM = 1)

The query will run even if the Impersonation feature is disabled

Environment

Clarity 16.2.3, 16.3.0, 16.3.1, 16.3.2

Cause

DE168694

The query to check CMN_SESSIONS for impersonation runs on audited attributes.

Resolution

• Fixed in Clarity 16.3.3
• Fixed in Clarity 16.2.3 Patch 5 (16.2.3.5)

Workaround:

• Keep the number of records in CMN_SESSIONS low by running Clean User Sessions Job