We literally get stuck in the below merge sql in our ETL code chain.
That merge statement comes back instantly with zero merge count when we re-play the sql outside of the stored proc with sql developer.
And similar run with compared data also comes back instantly and at some point the perf just degrades and now it is literally hangs.
SQL Monitoring Report
SQL Text
------------------------------
MERGE INTO tmp_repair_status_note e USING ( SELECT
rsn.repair_note_id AS repair_note_id,
rsn.repair_status_id AS repair_status_id,
rn.repair_id,
rn.type_cd,
rn.created_dt,
rs.status_cd,
rs.status_dt,
'N' new_status,
'N' new_note
FROM
repair_status_note rsn,
repair_note rn,
repair_status rs,
apl_repair_publish arp
WHERE
rsn.repair_note_id=rn.repair_note_id
AND rsn.repair_status_id=rs.repair_sta
TUS_ID AND ARP.REPAIR_ID = RS.REPAIR_ID AND ARP.BATCH_ID = :B3 AND ARP.ID >= :B2 AND ARP.ID <= :B1
) H ON ( E.REPAIR_ID = H.REPAIR_ID AND E.STATUS_CD = H.STATUS_CD AND CAST(E.CREATED_DT AS DATE) = CAST(H.CREATED_DT AS DATE) AND E.TYPE_CD = H.TYPE_CD )
WHEN MATCHED THEN UPDATE SET E.REPAIR_STATUS_ID = H.REPAIR_STATUS_ID, E.REPAIR_NOTE_ID = H.REPAIR_NOTE_ID, E.NEW_STATUS = H.NEW_STATUS, E.NEW_NOTE = h.new_note
log errors into repair_status_note_errlog(TO_CHAR(:b4))reject limit unlimited
Error: ORA-28
------------------------------
ORA-00028: your session has been killed
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
Session : GCRM_MIG_USER (8080:59224)
SQL ID : 1nv5xg2n2z06r
SQL Execution ID : 17090972
Execution Started : 07/03/2019 01:12:03
First Refresh Time : 07/03/2019 01:12:13
Last Refresh Time : 07/03/2019 01:21:17
Duration : 554s
Module/Action : SQL Developer/-
Service : SYS$USERS
Program : SQL Developer
PLSQL Entry Ids (Object/Subprogram) : 4752687,1
PLSQL Current Ids (Object/Subprogram) : 4752690,26
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B3 | 1 | NUMBER | -99926 |
| :B2 | 2 | NUMBER | 1 |
| :B1 | 3 | NUMBER | 998 |
========================================================================================================================
Global Stats
=========================================
| Elapsed | Cpu | Other | Buffer |
| Time(s) | Time(s) | Waits(s) | Gets |
=========================================
| 553 | 326 | 227 | 6M |
=========================================
SQL Plan Monitoring Details (Plan Hash Value=4290347794)
==================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
==================================================================================================================================================================================
| 0 | MERGE STATEMENT | | | | | | 1 | | | | |
| 1 | MERGE | TMP_REPAIR_STATUS_NOTE | | | | | 1 | | | | |
| 2 | VIEW | | | | | | 1 | | | | |
| 3 | FILTER | | | | | | 1 | | | | |
| 4 | NESTED LOOPS | | 1 | 128M | | | 1 | | | | |
| 5 | NESTED LOOPS | | 25765 | 128M | 545 | +10 | 1 | 0 | | 1.28 | Cpu (7) |
| 6 | NESTED LOOPS | | 1M | 125M | 545 | +10 | 1 | 217M | | 1.47 | Cpu (8) |
| 7 | MERGE JOIN CARTESIAN | | 31M | 265K | 549 | +6 | 1 | 27M | | 0.18 | Cpu (1) |
| 8 | TABLE ACCESS BY INDEX ROWID | APL_REPAIR_PUBLISH_TB | 606 | 218 | 57 | +10 | 1 | 542 | | | |
| 9 | INDEX RANGE SCAN | APL_PUB_UK1 | 606 | 5 | 545 | +10 | 1 | 542 | | | |
| 10 | BUFFER SORT | | 51385 | 265K | 545 | +10 | 542 | 27M | 17M | 2.56 | Cpu (14) |
| 11 | TABLE ACCESS FULL | TMP_REPAIR_STATUS_NOTE | 51385 | 437 | 1 | +10 | 1 | 49745 | | | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | REPAIR_STATUS | 1 | 4 | 550 | +5 | 27M | 217M | | 38.46 | Cpu (210) |
| 13 | INDEX RANGE SCAN | RST_STSCD_RPR_IDX | 1 | 3 | 553 | +2 | 27M | 217M | | 15.20 | Cpu (83) |
| 14 | INDEX RANGE SCAN | RSN_PK_IDX | 1 | 3 | 488 | +66 | 217M | 0 | | 40.66 | Cpu (222) |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | REPAIR_NOTE | 1 | 3 | | | | | | | |
| 16 | INDEX UNIQUE SCAN | RNO_PK_IDX | 1 | 2 | | | | | | | |
==================================================================================================================================================================================