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

Document ID : KB000105719
Last Modified Date : 09/07/2018
Show Technical Document Details
Issue:
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. 
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.