000031009 - RSA Via L & G: Active Directory ADC first run in 7.0 take much longer than expected

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

Article Content

Article Number000031009
Applies ToRSA Via Lifecycle and Governance 
Version:  7.0.0 
IssueWhen running an active directory account data collector for the first time under 7.0.0, the collector takes much longer than expected to run. 
Checking OEM (Oracle Enterprise Manager) for the top query at the time the collector is running, you will find the following query consuming resources: 
MERGE INTO T_AV_USER_ACCOUNT_MAPPINGS MAP USING (SELECT ACCOUNT_ID, USER_ID FROM GTT_DC_ACCOUNT_MAP WHERE OP_TYPE = :B1 ) GTT ON (MAP.ADC_ID = :B2 AND MAP.ACCOUNT_ID = GTT.ACCOUNT_ID AND MAP.USER_ID = GTT.USER_ID) WHEN MATCHED THEN UPDATE SET MAP.STATE='VA', DELETED_BY = NULL WHERE DELETED_BY IS NOT NULL AND STATE='IC'
CauseDuring the nightly scheduled statistics run, statistics are gathered on certain tables. Recently, statistics were changed so they are also gathered on those tables that happen to be empty. When the merge query mentioned above is run, there are some entries in the table that the current collection has placed in there.  The statistics on that table still shows the row number as 0. The zero row count is causing the Oracle optimizer to choose a wrong execution plan, causing the query to run for a long time.   
 
ResolutionStatistics existing on these empty tables were removed before the collection so that Oracle will dynamically gather the statistics on them at execution time to determine the best path for query execution.
A future release (patch) of RSA VIA Lifecycle in Governance will be scheduled to include a permanent fix for this issue.
Until that fix is released, please use the workaround provided in this article. 
WorkaroundThe following steps can be followed to address this issue in 7.0.0 Patch level x (until this is resolved).
If the collection gets stuck on the query mentioned in the Issue Description, then below are the ways to cancel and rerun that collection: 
  1. Cancel any pending data runs. 
  2. Cancel the collection run that is stuck.
  3. Copy the 3 packages that are attached to this article to a temporary directory on your server (for example /tmp)
  4. Recompile the 3 packages using the following: 
$ sqlplus avuser/password@avdb
SQL> @/tmp/ADC_Data_Collector.pkb
     <any errors will be displayed if there are issues>
SQL> @/tmp/Statistic_Control.pkb
     <any errors will be displayed if there are issues>
SQL> @/tmp/UNFC_Processor.pkb
     <any errors will be displayed if there are issues>

     5. Restart ACM to avoid any package invalidation issues 
$ acm stop
$ acm start

     6. Rerun the cancelled collections
NotesIf there is an issue with the new packages, there is a way to back these out:
1. Find the original ADC_Data_Collector.pkb, Statistics_Control.pkb, UNFC_Processor.pkb files in /home/oracle/database/packages/...
    Make a note of the directory that they are in.
2. Run these packages in Oracle as you did for the new ones above, running the copies found in the directory noted above:
 
$ sqlplus avuser/password@avdb
SQL> @/home/oracle/database/packages/.../ADC_Data_Collector.pkb
     <any errors will be displayed if there are issues>
SQL> @/home/oracle/database/packages/.../Statistic_Control.pkb
     <any errors will be displayed if there are issues>
SQL> @/home/oracle/database/packages/...UNFC_Processor.pkb
     <any errors will be displayed if there are issues>

3. Restart ACM to avoid any package invalidation issues 
$ acm stop
$ acm start

Outcomes