INV_BROWSE_INVESTMENT lookup performance issue

Products

• Clarity PPM On Premise

Issue/Introduction

We are seeing performance issue with INV_BROWSE_INVESTMENT lookup on all the portlets wherever it is used. 

Below portlets are using this lookup:

1. opt_change_attributes
2. cop.finTransactionReview
3. cop.invTransactionReview
4. cop.invTimesheetReview
5. cop.prjTimesheetReview

Slow running query found:

select * from (select row_number() over ( order by name  asc) row_num, count(*) over () num_rows, q.* from ( SELECT INV.ID ID
                  ,INV.CODE CODE
                  ,INV.NAME NAME            
                  ,CA.ODF_OBJECT_CODE OBJECT_CODE
                  ,CLS.NAME TYPE_NAME
         ,CA.LAST_UPDATED_DATE LAST_UPDATED_DATE
         ,CLS.LANGUAGE_CODE LANGUAGE_CODE
         ,LANG.ID LANGUAGE_ID
        FROM INV_INVESTMENTS INV LEFT OUTER JOIN INV_PROJECTS PRJ ON INV.ID = PRJ.PRID, ODF_CA_INV CA, ODF_CLASS_V CLS, CMN_LANGUAGES LANG
                WHERE
                 INV.IS_CUSTOM=:"SYS_B_00"
                 AND				 
                 (INV.ID in (select object_instance_id from odfsec_inv_v2 where user_id =  :v0 ) OR EXISTS (
					select  permission_value
                    from    cmn_sec_assgnd_obj_perm_v
                    where   user_id =  :v1 
                    and     permission_code = :"SYS_B_01"
                    and     object_instance_id = inv.id
                    and     object_code = :"SYS_B_02"
                    and     object_type = :"SYS_B_03"
                    and     component_code = :"SYS_B_04")
					)
                   AND NVL(PRJ.IS_TEMPLATE,:"SYS_B_05")=:"SYS_B_06"
                   AND (INV.PURGE_FLAG=:"SYS_B_07" OR INV.PURGE_FLAG IS NULL)
                   AND CA.ID = INV.ID
                   AND CLS.CODE = CA.ODF_OBJECT_CODE
				           AND CLS.LANGUAGE_CODE =  :v2 
		  AND CLS.LANGUAGE_CODE = LANG.LANGUAGE_CODE
                   AND
                :"SYS_B_08"= :v3  and :"SYS_B_09"=:"SYS_B_10" and :"SYS_B_11" = :"SYS_B_12"  AND INV.is_active=:"SYS_B_13" ) q) q where q.row_num between  :v4  and  :v5  order by q.row_num

Environment

Release : 15.6

Component : CA PPM INTEGRATIONS & INSTALLATIONS

Resolution

On Premise customers: 

1. Have the DBA check for slow running queries such as the one referenced above
2. If any are found, rebuilding the execution plan for any slow running queries can help resolve the performance issue.

Clarity PPM SaaS Customers: Contact Broadcom Support for assistance

Note: Generating a PPM Action trace while reproducing the issue can help in determining if there are slow running queries causing the issue

See steps to enable action trace at: 9353

 

Additional Information

See KB 141061 - How to search for known Clarity PPM Issues using Self Service