on Jun 14, 2016Article Content
| Article Number | 000031009 |
| Applies To | RSA Via Lifecycle and Governance Version: 7.0.0 |
| Issue | When 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' |
| Cause | During 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. |
| Resolution | Statistics 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. |
| Workaround | The 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:
$ sqlplus avuser/password@avdb 5. Restart ACM to avoid any package invalidation issues $ acm stop 6. Rerun the cancelled collections |
| Notes | If 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 3. Restart ACM to avoid any package invalidation issues $ acm stop |