Performance degradation related to access requests in RSA Identity Governance & Lifecycle 7.x

[#1]

Article Number

000037894

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.0.2 P10+, 7.1.0 P03+, 7.1.1 GA

 

Issue

After upgrading to 7.0.2 P10+, 7.1.0 P03+ or 7.1.1, certain activities related to change requests have significant performance degradation. Symptoms include:

• Poor performance when cancelling change requests from workpoint nodes.
• Poor load time when navigating back on a request form after requesting items that create pending accounts.
• Indirect relationship processing taking days to complete after making changes to a Multi-App Entitlement Data Collector.

An Automatic Workload Repository (AWR) report during these time periods may show top executing SQL queries similar to the following:

• BEGIN Access_Request_Pkg.Delete_ChangeRequest(:1 ); END;
• DELETE FROM T_AV_EXPLODEDUSERENTITLEMENTS WHERE CRI_ID = :B1
• DELETE FROM T_AV_EXPLODEDUSERENTITLEMENTS WHERE COLLECTED_STATE_CODE = :B1 AND CRI_ID IN (SELECT ID FROM T_AV_CHANGE_REQUEST_DETAILS WHERE CHANGE_REQUESTS_ID IN (SELECT ID FROM GTT_IDS))
• MERGE /*+ Leading (r) Index (xue IXN_XUE_CE_EXPLODED_PATH) */ INTO T_AV_EXPLODEDUSERENTITLEMENTS XUE USING (SELECT CE_EXPLODED_PATH FROM GTT_CE_AFFECTED_PARENTS) R ON ( XUE.CE_EXPLODED_PATH LIKE ' ' || R.CE_EXPLODED_PATH || '%' ) WHEN MATCHED THEN UPDATE SET XUE.DELETION_DATE = SYSDATE WHERE XUE.DELETION_DATE IS NULL AND XUE.ENTITLED_TYPE = :B1

 

Cause

This problem is due to a missing index on column CRI_ID in table T_AV_EXPLODEDUSERENTITLEMENTS. The missing index forces a full table scan when deleting from this table and this is what causes the poor performance. CRI_ID is a new column added to the table starting in versions 7.0.2 P10, 7.1.0 and 7.1.1. Starting in 7.0.2 P10, 7.1.0 P03 and 7.1.1, there are changes to the scope of the deletes on that table requiring an index on column CRI_ID for optimal performance.

 

Resolution

To verify if you have this issue, first verify if you have the new column (CRI_ID). If so, then verify whether or not there is an index on the column.

1. Login to SQL as AVUSER and check for column CRI_ID:

select owner, table_name, column_name 
from all_tab_columns 
where table_name='T_AV_EXPLODEDUSERENTITLEMENTS' and column_name='CRI_ID'

If this query returns no results, then you do not have this issue.

2. If this query returns a row, then check for an index on the column:

select * 
from all_ind_columns 
where index_name='IXN_XUE_CRI_ID';

If this column returns a row, then you do not have this issue.

3. If this column returns no results and you are on 7.0.2 P10+, 7.1.0 P03+ or 7.1.1, then you have confirmed you have this issue. 

This issue is resolved in the following RSA Identity Governance & Lifecycle patches:

• RSA Identity Governance & Lifecycle 7.0.2 P13
• RSA Identity Governance & Lifecycle 7.1.0 P07
• RSA Identity Governance & Lifecycle 7.1.1 P01

The fix is to add the index 'IXN_XUE_CRI_ID' to the CRI_ID column.

 

Workaround

There is a script available to build the index. Please contact RSA Identity Governance & Lifecycle support for the script.