000033613 - RSA Via Lifecycle and Governance Aveksa Data Collector (ADC) becomes stuck for hours on "Inserting account mappings into staging table"

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

Article Content

Article Number000033613
Applies ToRSA Product Set: RSA Via Lifecycle and Governance (RSA Via L&G)
RSA Version/Condition: 6.8.1, 6.9.0
 
IssueADC takes a very long time to complete the "Inserting account mappings into staging table" step. There are no errors associated with the collection run in the logs. When reviewing OEM and/or an AWR for the associated timeframe we see the below long running SQL statement:
 
INSERT ALL INTO GTT_USER_ACCOUNT_MAPPINGS (ID, ACCOUNT_ID, USER_ID, ACCOUNT_NAME, COMMENTS, CAS1, CAS2, CAS3, CAS4, CAS5, CAS6, CAS7, CAS8, CAS9, CAS10, CAI1, CAI2, CAI3, CAI4, CAI5, CAD1, CAD2, CAD3, CAD4, CAD5) VALUES(ID, ACCOUNT_ID, USER_ID, ACCOUNT_NAME, COMMENTS, CAS1, CAS2, CAS3, CAS4, CAS5, CAS6, CAS7, CAS8, CAS9, CAS10, CAI1, CAI2, CAI3, CAI4, CAI5, CAD1, CAD2, CAD3, CAD4, CAD5) SELECT /*+ INDEX (RSS NIDX_ACC_MAPS_RSS_RUNID_ADCID) */ RSS.ID, RSS.ACCOUNT_ID, USER_MAPPINGS.REF_OBJECT_ID AS USER_ID, RSS.ACCOUNT_NAME, RSS.COMMENTS, RSS.CAS1, RSS.CAS2, RSS.CAS3, RSS.CAS4, RSS.CAS5, RSS.CAS6, RSS.CAS7, RSS.CAS8, RSS.CAS9, RSS.CAS10, RSS.CAI1, RSS.CAI2, RSS.CAI3, RSS.CAI4, RSS.CAI5, RSS.CAD1, RSS.CAD2, RSS.CAD3, RSS.CAD4, RSS.CAD5 FROM T_RAW_ACCOUNT_MAPPING RSS JOIN GTT_RESOLVED_OBJECT_REFS USER_MAPPINGS ON RSS.ID = USER_MAPPINGS.OBJECT_ID WHERE RSS.RUN_ID = :B2 AND RSS.ADC_ID = :B1 AND RSS.ACCOUNT_ID > 0
CauseOracle optimization chose a process that includes multiple nested loops and runs very slowly. The SQL in the Explain Plan will look like what is shown below:
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                               |       |       | 56019 (100)|          |
|   1 |  MULTI-TABLE INSERT           |                               |       |       |            |          |
|   2 |   NESTED LOOPS                |                               |       |       |            |          |
|   3 |    NESTED LOOPS               |                               | 93185 |   480M| 56019   (1)| 00:11:13 |
|   4 |     TABLE ACCESS FULL         | GTT_RESOLVED_OBJECT_REFS      | 93185 |  2366K|    88   (2)| 00:00:02 |
|   5 |     INDEX RANGE SCAN          | NIDX_ACC_MAPS_RSS_RUNID_ADCID |     1 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T_RAW_ACCOUNT_MAPPING         |     1 |  5386 |     1   (0)| 00:00:01 |
|   7 |   INTO                        | GTT_USER_ACCOUNT_MAPPINGS     |       |       |            |          |
---------------------------------------------------------------------------------------------------------------
ResolutionEngineering has resolved this issue in 6.9.1.
WorkaroundAs a workaround, support can modify the SQL hint used for optimization of the query. Please contact support for more details as this needs to be done by support.
 

Attachments

    Outcomes