RSA Via Lifecycle and Governance Aveksa Data Collector (ADC) becomes stuck for hours on "Inserting account mappings into staging table"
2 years ago
Originally Published: 2016-07-25
Article Number
000064531
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.
 

Related Articles

Trending Articles

Don't see what you're looking for?