RSA Identity Governance and Lifecycle Role Entitlement Data Collector (EDC) slow collection time in Indirect Relationship Processing after applying 7.0.0 P02
4 years ago
Originally Published: 2016-05-27
Article Number
000050427
Applies To
RSA Product Set: RSA Identity Governance and Lifecycle 
RSA Version/Condition: 7.0.0 P02
 
Issue
RSA Identity Governance and Lifecycle Role Entitlement data collectors complete, but take a long to run (over 15 minutes).

In the User Interface, select Admin > Monitoring > Data Runs and choose a run ID.  Under Task Details, note the long processing times when the description notes Indirect Relationship Processing, as in the image below:
 
screen
Cause
Due to enhancements in the way RSA Identity Governance and Lifecycle 7.0.0 calculates metrics for role memberships, the processing can take longer than in previous versions.   The code in version 7.0.0 P02 was not optimized for these new changes. 
Resolution
An optimized version of the Role_Management_Pkg.pkb is included in RSA Identity Governance and Lifecycle in the following versions:
  • RSA Identity Governance and Lifecycle 7.0.0 P04
  • RSA Identity Governance and Lifecycle 7.0.1 
  • RSA Identity Governance and Lifecycle 7.0.2
Notes
In systems that are affected by this issue an AWR (Automatic Workload Repository) report will show an increased time spent in the processing of the following SQL query.
  
MERGE INTO T_AV_ROLE_MISSINGENTS T USING (WITH SHOULDHAVEENTS (ROLE_ID, MEMBER_ID, MEMBER_TYPE, ENT_ID, ENT_TYPE) AS ( SELECT D.ROLE_ID, M.MEMBER_ID, M.MEMBER_TYPE, D.ENT_ID, D.ENT_TYPE FROM T_AV_ROLEDEFINITIONS D JOIN T_AV_ROLEMEMBERSHIPS M ON M.ROLE_ID = D.ROLE_ID WHERE 'C' = :B1 AND D.ROLE_ID IN (SELECT ID FROM GTT_EXP_ROLES) AND D.IS_REQUIRED = 'TRUE' AND D.DELETION_DATE IS NULL AND M.DELETION_DATE IS NULL UNION ALL SELECT D.ROLEVERSION_ID, M.MEMBER_ID, M.MEMBER_TYPE, D.ENT_ID, D.ENT_TYPE FROM T_AV_ROLEVERDEFINITIONS D JOIN T_AV_ROLEVERMEMBERSHIPS M ON M.ROLEVERSION_ID = D.ROLEVERSION_ID WHERE 'P' = :B1 AND D.ROLEVERSION_ID IN (SELECT ID FROM GTT_EXP_ROLES) AND D.IS_REQUIRED = 'TRUE' ), MISSINGENTS AS ( SELECT ROLE_ID, MEMBER_ID, MEMBER_TYPE, ENT_ID, ENT_TYPE FROM SHOULDHAVEENTS WHERE (ENT_ID, MEMBER_ID, ENT_TYPE, MEMBER_TYPE) IN (SELECT ENT_ID, MEMBER_ID, ENT_TYPE, MEMBER_TYPE FROM SHOULDHAVEENTS MINUS SELECT ENTITLEMENT_ID, ENTITLED_ID, ENTITLEMENT_TYPE, ENTITLED_TYPE FROM T_AV_EXPLODEDUSERENTITLEMENTS WHERE DELETION_DATE IS NULL AND (ENTITLEMENT_ID, ENTITLED_ID) IN (SELECT ENT_ID, MEMBER_ID FROM SHOULDHAVEENTS) MINUS SELECT GROUP_ID, MEMBER_ID, 'group', MEMBER_TYPE FROM T_GROUP_MEMBERSHIPS WHERE DELETION_DATE IS NULL AND (GROUP_ID, MEMBER_ID) IN (SELECT ENT_ID, MEMBER_ID FROM SHOULDHAVEENTS WHERE ENT_TYPE = 'group')) ) SELECT E.ID, M.ROLE_ID, M.MEMBER_ID, M.MEMBER_TYPE, M.ENT_ID, M.ENT_TYPE FROM MISSINGENTS M FULL OUTER JOIN (SELECT ID, ROLE_TYPEID, MEMBER_ID, MEMBER_TYPE, ENT_ID, ENT_TYPE FROM T_AV_ROLE_MISSINGENTS WHERE ROLE_TYPEID IN (SELECT ROLE_TYPEID FROM GTT_EXP_ROLES)) E ON E.ROLE_TYPEID = :B1 ||M.ROLE_ID AND E.MEMBER_ID = M.MEMBER_ID AND E.MEMBER_TYPE = M.MEMBER_TYPE AND E.ENT_ID = M.ENT_ID AND E.ENT_TYPE = M.ENT_TYPE WHERE M.ROLE_ID IS NULL OR E.ID IS NULL ) DELTA ON (T.ID = DELTA.ID) WHEN MATCHED THEN UPDATE SET ROLE_ID = T.ROLE_ID DELETE WHERE T.ID IS NOT NULL WHEN NOT MATCHED THEN INSERT (ID, ROLE_TYPEI D, ROLE_ID, MEMBER_ID, MEMBER_TYPE, ENT_ID, ENT_TYPE) VALUES (GETSEQUENCEVALUE('av_rolemissingent_seq.nextval'), :B1 ||DELTA.ROLE_ID, DELTA.ROLE_ID, DELTA.MEMBER_ID, DELTA.MEMBER_TYPE, DELTA.ENT_ID, DELTA.ENT_TYPE)

Related Articles

Trending Articles

Don't see what you're looking for?