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

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

Don't see what you're looking for?

Related Articles

Trending Articles