Performance improvements related to T_AV_MODEL_EXPLODEDUSERENTS table in RSA Identity Governance & Lifecycle

[#1]

Article Number

000039493

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.1, 7.2.0
 

Issue

Customers may notice the following issues. 

1. Collectors slow in step "Indirect Relationship processing"

   
   There may be longer run times for the following (or similar) queries:

   3bm571yrusp03	BEGIN Role_Management_Pkg.Explode_LocalRoles(:1 ); END;

2. Longer Data Purging times in "Step 12/12: Purging Abandoned Role Data" where the following symptoms may be observed.
   The "Database Logs for Run" for the Data Purging show the following lines 

   Starting purging Abandoned Role Data
   Purged 10000 Abandoned ModelXUE records.
   Purged 10000 Abandoned ModelXUE records....

   Long run times for the following query.

   03htv7u0zssbj	DELETE FROM T_AV_MODEL_EXPLODEDUSERENTS M WHERE ((ROLE_TYPE = 'P' AND NOT EXISTS (SELECT 1 FROM T_AV_ROLEVERSIONS RV WHERE RV.ROLEVERSION_ID = M.ROLE_ID)) OR (ROLE_TYPE = 'C' AND NOT EXISTS (SELECT 1 FROM T_AV_ROLES R WHERE R.DELETION_DATE IS NULL AND R.ID = M.ROLE_ID))) AND ROWNUM <= :B1

    

Cause

This is a known issue in the following versions.
 

• RSA Identity Governance & Lifecycle 7.1.1 
• RSA Identity Governance & Lifecycle 7.2.0 GA 

Resolution

These issues are resolved in the following versions.   
 

• RSA Identity Governance & Lifecycle 7.2.0 P03
• RSA Identity Governance & Lifecycle 7.2.1 

These versions include code changes, new indexes, and changes to hints that increase performance with queries against the T_AV_MODEL_EXPLODEDUSERENTS (model XUE) table.
 

This reduces execution time in the many aspects of the product that is related to Role explosion and Role management including but not limited to the following.
 

• Improvements in the Data Purging in step "Purging Abandoned Role Data" 
• Improvements in Role explosion in Role_Management_Pkg.Explode_LocalRole

Workaround

Customer should be encouraged to upgrade to a version where these issues are resolved.

Temporary and marginal improvement may sometimes be obtained by rebuilding the existing indexes against the T_AV_MODEL_EXPLODEDUSERENTS table.  Contact RSA Customer Support and reference this KB article for information about how to rebuild the existing indexes. 

Notes

The new data archiving step “Purging Abandoned Role Data” was introduced in 7.1.1 P07.