Resource Instance Check Rights Query is slow on large datasets where instance rights are used. During the rights check view cmn_sec_chk_user_v0 is being called and execution is seen at around 2-3 second which ideally should be in milliseconds
Steps to Reproduce:
Pre-Requisite: Clarity implementation where instance rights are provided on Resource Booking Rights
Expected Results: The navigation to workspaces should be relative faster with 2-5 seconds
Actual Results: The navigation to workspaces is faster for admin and slower for non admin
select NVL((select 1
from dual
where exists (select pv.object_instance_id
from cmn_sec_chk_user_v0 pv
where pv.object_id=664
and pv.user_id=XXXXX
and pv.permission_code='ResourceHardBook')), 0) has_hard from dual
select NVL((select 1
from dual
where exists (select pv.object_instance_id
from cmn_sec_chk_user_v0 pv
where pv.object_id=664
and pv.user_id=XXXX
and pv.permission_code='ResourceSoftBook')), 0)
SELECT case when (select 1
from dual
where 1 = 0)=1 then 1 else (case when exists (SELECT USER_ID
FROM CMN_SEC_CHK_USER_V0
WHERE USER_ID = XX
AND PERMISSION_CODE = 'ResourceHardBook'
AND OBJECT_ID=664) then 1 else 0 end) end as has_hard, case when (select 1
from dual
where 1 = 0)=1 then 1 else (case when exists (SELECT USER_ID
FROM CMN_SEC_CHK_USER_V0
WHERE USER_ID = XX
AND PERMISSION_CODE = 'ResourceSoftBook'
AND OBJECT_ID=664) then 1 else 0 end) end as has_soft
FROM DUAL
Clarity Release: 16.2.1, 16.2.2, 16.2.3
DE153707
DE153707 is fixed in 16.3.0 (Targeted to release November 2024) and also backported to 16.2.3 patch 1