000032881 - Change Verification task running after Account Data Collection (ADC) taking many hours to be completed in RSA Via Lifecycle & Governance

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000032881
Applies ToRSA 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

CauseCurrently 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');
ResolutionTo resolve the issue, upgrade to RSA Via L&G 6.9.1 P13.
NotesThe code changes to the fix involves changes in Database_Statistics.pkb to always gather statistics on the T_GROUPS table.

Attachments

    Outcomes