In CA PPM, the IDEA create page was hanging for 3+ minutes after the upgrade

book

Article ID: 105719

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

After upgrading CA PPM to 15.4, the IDEA create page was taking more than 3 minutes to save. This page used to take 2-3 seconds to save before the upgrade. 
The performance issue is happening when the page is not populated with all the required fields. 

Environment

Release: 15.4 
Component: ODPRM

Resolution

After performing an action trace on this page, the below query was taking 46 - 50 seconds and it was executed 6 times with a single save. 

select * from (select row_number() over ( order by last_name  asc) row_num, count(*) over () num_rows, q.* from ( SELECT
  MAIN.USER_ID USER_ID
  , MAIN.LAST_NAME LAST_NAME
  , MAIN.FIRST_NAME FIRST_NAME
  , MAIN.FULL_NAME FULL_NAME
  , MAIN.UNIQUE_NAME UNIQUE_NAME
  , MAIN.UNIQUE_CODE UNIQUE_CODE
  , MAIN.LAST_UPDATED_DATE LAST_UPDATED_DATE
  , MAIN.IS_ACTIVE IS_ACTIVE
  , MAIN.USER_NAME USER_NAME
  , MAIN.PERSON_TYPE_ID PERSON_TYPE_ID
  , MAIN.PERSON_TYPE PERSON_TYPE
  , MAIN.USER_STATUS_ID USER_STATUS_ID
  , MAIN.USER_STATUS USER_STATUS
  , MAIN.LANGUAGE_ID LANGUAGE_ID
  , MAIN.LANGUAGE_CODE LANGUAGE_CODE
FROM (
  SELECT 
    R.USER_ID USER_ID
    , R.LAST_NAME LAST_NAME
    , R.FIRST_NAME FIRST_NAME
    , R.FULL_NAME FULL_NAME
    , R.UNIQUE_NAME UNIQUE_NAME
    , R.UNIQUE_NAME UNIQUE_CODE
    , R.LAST_UPDATED_DATE LAST_UPDATED_DATE
    , R.IS_ACTIVE IS_ACTIVE
    , U.USER_NAME USER_NAME
    , R.PERSON_TYPE PERSON_TYPE_ID
    , L.NAME PERSON_TYPE
    , U.USER_STATUS_ID USER_STATUS_ID
    , S.NAME USER_STATUS
    , LANG.ID LANGUAGE_ID
    , LANG.LANGUAGE_CODE LANGUAGE_CODE
  FROM 
    SRM_RESOURCES R
    INNER JOIN CMN_SEC_USERS U
      ON U.ID=R.USER_ID
    INNER JOIN CMN_LOOKUPS_V L
      ON R.person_type = L.id
      AND L.lookup_type='SRM_RESOURCE_TYPE'
      AND L.language_code = 'en' /* force to english due to DWH performance loads all languages */
    INNER JOIN CMN_LOOKUPS_V S
      ON U.user_status_id = s.id
      AND S.language_code = L.language_code
      AND S.lookup_type='SEC_USER_STATUS'
    INNER JOIN CMN_LANGUAGES LANG
      ON LANG.LANGUAGE_CODE = L.LANGUAGE_CODE
  WHERE 
    case when R.user_id is null then 0 else R.user_id end != -99
     
  ) MAIN
WHERE 
  1=1 
  AND 1=? and 1=1 and 2 = 2 ) q) q where q.row_num < 50001 order by q.row_num
setInt(): 1, 1

With the help of DBA, it was found that he execution plan of this query was not correct. DBA did correct the execution plan of the query and since then the page was responding in a couple of seconds. 

Additional Information

If this issue occurs against a PPM SAAS environment, please log an issue with Broadcom Support for this to be reviewed against the database.

Provide:

  1. Your request for this Knowledge Article to be run:
    Performance issue on Idea Save page after the upgrade. The response time was 3+ minutes as opposed to 2-3 seconds before the upgrade.
    https://ca-broadcomcsm.wolkenservicedesk.com/wolken/esd/knowledgebase_list?articleId=105719

  2. The environment name and URL.

  3. Your Authorisation to run this procedure.

  4. Any time windows or constraints, if needed.

  5. Advise roughly when the upgrade was done.

  6. Advise the version that the upgrade moved to and from.

Note that the Support engineer may need to confirm that it is this problem, and not a similar one, that has been encountered before processing this request.