Article Number
000032881
Applies To
RSA Product Set: RSA Via Lifecycle & Governance (RSA Via L&G)
RSA Version/Condition: 6.9.1 P07
Platform: WebSphere
Issue
The Change Verification process after the ADC is taking many hours to be completed. However, the run Elapsed time for all activities inside the "Change Verification" run id is 00:00
The below SQL query has highest elapsed time in the Oracle AWR (Automatic Workload Repository) Report.
INSERT ALL INTO T_AV_DATAPROCESSINGEVENTS (ID, WATCH_ID, TYPE, RUN_ID, DATA_ID) VALUES (DATA_PROCESSING_EVENT_SEQUENCE.NEXTVAL, DPW_ID, DPW_TYPE, :B5 , GID) SELECT GRP.DPW_ID, GRP.DPW_TYPE, GID FROM ( SELECT DPW.ID AS DPW_ID, DPW.TYPE AS DPW_TYPE, DPW.DATA_1, GRP_IN.NAME, GRP_IN.DC_ID FROM T_AV_DATAPROCEVENTWATCHES DPW JOIN T_GROUPS GRP_IN ON DPW.TARGET_ID = GRP_IN.ID WHERE GRP_IN.DC_ID = :B1 AND DPW.STATUS = 'O' AND DPW.TYPE = 'GUA' AND DPW.IS_EXPLICIT = 'Y' ) GRP JOIN ( SELECT G.ID AS GID, G.NAME, G.DC_ID, GM_INNER.MEMBER_ID FROM ( ( SELECT GROUP_ID, MEMBER_ID FROM T_GROUP_MEMBERSHIPS WHERE RUN_ID = :B2 AND DC_ID = :B1 AND MEMBER_TYPE = :B4 AND MEMBER_DERIVED_FROM_TYPE=:B3 ) GM_INNER JOIN ( SELECT ID, NAME, DC_ID FROM T_GROUPS WHERE RUN_ID = :B2 AND DC_ID = :B1 ) G ON GM_INNER.GROUP_ID = G.ID ) ) GM ON GRP.NAME = GM.NAME AND GRP.DC_ID = GM.DC_ID AND GRP.DATA_1 = GM.MEMBER_ID
Cause
Currently the code does the statistics gathering of T_GROUPS table conditionally, which is causing the erratic time in the Verification process.
Below SQL query is used to determine the last statistics gathering date for tables - T_GROUPS,T_GROUP_MEMBERSHIPS and T_AV_DATAPROCEVENTWATCHES
select table_name, last_analyzed, num_rows from user_tables where table_name in ('T_GROUPS','T_GROUP_MEMBERSHIPS','T_AV_DATAPROCEVENTWATCHES');Resolution
To resolve the issue, upgrade to RSA Via L&G 6.9.1 P13.
Notes
The code changes to the fix involves changes in Database_Statistics.pkb to always gather statistics on the T_GROUPS table.
