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

[#1]

Article Number

000033613

Applies To

RSA Product Set: RSA Via Lifecycle and Governance (RSA Via L&G)
RSA Version/Condition: 6.8.1, 6.9.0
 

Issue

ADC 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

Cause

Oracle 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     |       |       |            |          |
---------------------------------------------------------------------------------------------------------------

Resolution

Engineering has resolved this issue in 6.9.1.

Workaround

As 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.