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
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 | | | | |
---------------------------------------------------------------------------------------------------------------
Engineering has resolved this issue in 6.9.1.
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.