Clarity: Performance of sub object based grid portlet is slow due to missing index when you have multiple sub object with oracle as database

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

STEPS TO REPRODUCE

1. Login to clarity  and navigate to Administration --> Objects 
2. Create a sub object names BUSINESS under Project Object 
3. Now create six sub objects keeping BUSINESS as Master Object
4. On each sub object create at least 50 attributes 
5. Create at least 1000 instance on the newly created sub object i.e. BUSINESS
6. Create a grid portlet using the first sub object created i.e. BUSINESS
7. Add 10 columns on the newly created grid portlet
8. Filter the portlet and observer the query execution at the database  

Expected Results: The filter on the portlet should fetch data within few seconds for 2k records

Actual Results: The filter takes around 25 seconds to show the data on the grid portlet

Environment

Release : 15.5.1

Component : CA PPM SAAS INSTALLATION AND UPGRADE

Cause

This is a bug DE50773 and its due to missing index 

Resolution

The resolution is to create few additional index to improve the grid portlet performance. Also the below mentioned indexes will be included in the product once the defect is fixed. 

The index on table ODF_OBJECT_INSTANCE_MAPPING is added in the product. 

Additional Information

1. Performed the above mentioned STR and monitoring the database using the Oracle Enterprise Manager 

 

2. Captured the SQL ID

Session  Id.............................................: 10485

Session Status .........................................: EXECUTING

User Name ..............................................: SC52043P

Action .................................................: u_financial_dashboard

sql_id..................................................: 6ktd38paj486s

Plan_hash_value .........................................: 3523434695

Sql start time.............................. ............: 27-aug-2019 11:46:38

Elapsed time ...........................................: 21

Sec_wait time ..........................................: 0

Wait Event .............................................: PGA memory operation

3. Ran the Tuning advisor on the SQL ID and oracle showed an estimate benefit of 70%

 

Recommendation (estimated benefit: 70.36%)

  ------------------------------------------

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index NIKU.IDX$$_1DE40001 on

    NIKU.ODF_CA_U_SUB_PLAN_DETAILS("U_CHARGE_CODE","ODF_PARENT_ID");

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index NIKUP.IDX$$_1DE40002 on

    NIKU.ODF_OBJECT_INSTANCE_MAPPING("PRIMARY_OBJECT_INSTANCE_CODE","SECOND

    ARY_OBJECT_INSTANCE_CODE","PRIMARY_OBJECT_INSTANCE_ID","SECONDARY_OBJECT_IN

    STANCE_ID");

4. Created the above mentioned indexes 

 

5. Executed the portlet and now for 2k records it took 8 seconds and earlier it took 21 seconds