CMN_SESSIONS table needs an index on USER_ID

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The session select check runs a bit slower when there is a lot of records in CMN_SESSIONS table.

STEPS TO REPRODUCE:

1. With DBA check the runtime for the below statement on the database:

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)

Expected Results: Query to be run multiple times and to perform very fast

Actual Results: DBA checked and found large volumes of the query. Due to lack of index on user_id it was not performing as well as it could have been

Environment

Clarity 16.2.2, 16.2.3, 16.3.0, 16.3.1, 16.3.2

Cause

DE168819

Resolution

Fixed in Clarity 16.3.2