MUX Project and Idea Grid performance with Group OBS Rights

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

In MUX the user Group OBS permissions are adding a large overhead on MUX Grids (Project Grid), with even more performance impact with their custom OBS secure field added

STEPS TO REPRODUCE:

1. Connect to Clarity with user with Resource Global rights and no Group OBS rights 
2. Go to Project Grid in MUX
3. Open a presaved view that contains two Investment object custom Secure fields: a Lookup String and a custom OBS unit field. Also open a basic view just with Project ID and Project Name fields
4. Note they take 2-4 sec to load
5. Now log in with user with Group OBS permissions only and no Global permissions or Global Project Edit permissions

Expected Results: The views to be returned within reasonable time

Actual Results: The views with secure OBS fields take up to 74-78 sec. The basic view takes up to 35 sec.

Environment

Clarity 16.2.x, 16.3.x with Oracle

Cause

Reviewed as DE166703

In Oracle the security views CMN_SEC_CHK_USER_V0 and CMN_SEC_CHK_USER_INST_V0 have a hint on the first part of the union queries that cause an ineffective Oracle plan choice on some Oracle environments only

Resolution

Ask the DBA to perform the below, on a lower environment first, and test the performance before moving to Production.

1. Connect to the Clarity database with a query analyzer tool.
2. Open the security views:
   • CMN_SEC_CHK_USER_V0
   • CMN_SEC_CHK_USER_INST_V0
3. You will notice a hint in the view code with a little '+' sign
4. Delete the plus sign and save and compile the views
5. Restart Clarity services

• Removing the hint affects how Oracle optimizer is running the query.
• This should only be done on environments that have this specific issue and confirm on a lower environment that the performance is improved

Alternate Workaround: Grant permissions : Project - Approve – All to the user (if applicable)