CMN_SEC_GROUPS needs an index on is_hidden column

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

CMN_SEC_GROUPS table is slow performing, there is a lot of calls to this table that are hitting the database and raising its CPU

STEPS TO REPRODUCE:

1. On a large environment, upgrade to 16.1.3 and review database activity
2. Note a very large amount of calls to query:

select sg.ID as group_id from cmn_sec_groups sg
          left join cmn_sec_user_groups sug on sug.group_id = sg.id
          where( sug.user_id = :1  OR sg.is_hidden = 1 ) AND sg.is_active = 1

3. This query is consuming CPU and database resources

Expected Results: the query to run smoonthly

Actual Results: The query is running slower than expected, consuming database resources

 

Environment

Release : 16.1.3

Resolution

This is DE78019, fixed in 16.2.0

Workaround: Create an index on cmn_sec_groups.is_hidden field manually by DBA, using the custom naming convention

(Alternatively create an index on both cmn_sec_groups.is_hidden and cmn_sec_groups.is_active)

Remember to drop this index prior to an upgrade to a higher version