We are seeing performance issue with INV_BROWSE_INVESTMENT lookup on all the portlets wherever it is used.
Below portlets are using this lookup:
- opt_change_attributes
- cop.finTransactionReview
- cop.invTransactionReview
- cop.invTimesheetReview
- 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