Hello Asktom Team,
Hope you could throw some light on this.
We have two Oracle 11g instances where one MERGE statement has radically different execution plans. One finishes in less than a minute and one times out with ORA-01652: unable to extend temp segment by 128. It takes 700TB or tmp space (yes, terabytes).
Data volumes are very close in both the instances. Table structures and indexes are same.
I ran execution plan on both the instances and this is what i got. Could there be any particular reason why an execution plan could go for a tail spin? I not sure it is relevant but i found this from Support.
Bug 17397506 - SORT MERGE JOIN incorrectly chosen over HASH JOIN (Doc ID 17397506.8)
Explain plan with no issues:
===========================
NO MERGE JOIN OR SORT, expecting 602 rows, 700 bytes, 53 seconds estimated time, 0 TMP SPACE
Plan hash value: 4268375684
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 602 | 700K| 3740 (2)| 00:00:53 |
| 1 | MERGE | DIM_T_SW_REL_PLATFORM$ | | | | |
| 2 | VIEW | | | | | |
| 3 | NESTED LOOPS OUTER | | 602 | 723K| 3737 (2)| 00:00:53 |
|* 4 | HASH JOIN | | 602 | 713K| 3736 (2)| 00:00:53 |
|* 5 | HASH JOIN | | 1 | 39 | 1526 (2)| 00:00:22 |
| 6 | INDEX FULL SCAN | TDT_SW_PROD_SUBSCRIBED_CAT_IDX | 1 | 13 | 0 (0)| 00:00:01 |
| 7 | VIEW | CAT_SW_RELEASE | 585K| 14M| 1523 (2)| 00:00:22 |
| 8 | UNION-ALL | | | | | |
|* 9 | HASH JOIN RIGHT ANTI | | 585K| 14M| 1520 (2)| 00:00:22 |
| 10 | INDEX FULL SCAN | PK_TPC_SW_RELEASE | 1 | 13 | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | TCAT_SW_RELEASE | 585K| 7434K| 1517 (2)| 00:00:22 |
| 12 | NESTED LOOPS OUTER | | 1 | 43 | 1 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 30 | 1 (0)| 00:00:01 |
| 14 | INDEX FULL SCAN | PK_TPC_UUID | 1 | 4 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| TPC_SW_RELEASE | 1 | 26 | 0 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_TPC_SW_RELEASE | 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | TCAT_SW_RELEASE | 1 | 13 | 0 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_CAT_SW_RELEASE | 1 | | 0 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | TPC_SW_RELEASE | 1 | 26 | 2 (0)| 00:00:01 |
|* 20 | VIEW | CAT_SW_REL_PLATFORM | 1027K| 1151M| 2205 (2)| 00:00:31 |
| 21 | UNION-ALL | | | | | |
|* 22 | HASH JOIN RIGHT ANTI | | 1027K| 70M| 2202 (2)| 00:00:31 |
| 23 | INDEX FULL SCAN | PK_TPC_SW_REL_PLATFORM | 1 | 13 | 0 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | TCAT_SW_REL_PLATFORM | 1027K| 57M| 2197 (2)| 00:00:31 |
| 25 | NESTED LOOPS OUTER | | 1 | 1238 | 1 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 1179 | 1 (0)| 00:00:01 |
| 27 | INDEX FULL SCAN | PK_TPC_UUID | 1 | 4 | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | TPC_SW_REL_PLATFORM | 1 | 1175 | 0 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_TPC_SW_REL_PLATFORM | 1 | | 0 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | TCAT_SW_REL_PLATFORM | 1 | 59 | 0 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PK_CAT_SW_REL_PLATFORM | 1 | | 0 (0)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | TPC_SW_REL_PLATFORM | 1 | 1175 | 2 (0)| 00:00:01 |
|* 33 | TABLE ACCESS FULL | BDNA_A_PROPERTIES$ | 1 | 84 | 3 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | DIM_T_SW_REL_PLATFORM$ | 1 | 16 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | DIM_T_SW_REL_PLATFORM_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CAT_SW_REL_PLATFORM"."CAT_SW_RELEASE_ID"="CAT_SW_RELEASE"."CAT_SW_RELEASE_ID")
5 - access("TMP_DIM_T_SW_PROD_SUBSCRIBED"."CAT_SW_PRODUCT_ID"="CAT_SW_RELEASE"."CAT_SW_PRODUCT_ID")
9 - access("TPC_TAB"."CAT_SW_RELEASE_ID"="F_TCAT"."CAT_SW_RELEASE_ID")
16 - access("TPC"."CAT_SW_RELEASE_ID"="UUID"."CAT_UUID_ID")
filter(SIGN("TPC"."CAT_SW_RELEASE_ID")<>(-1))
18 - access("TCAT"."CAT_SW_RELEASE_ID"(+)="TPC"."CAT_SW_RELEASE_ID")
19 - filter(SIGN("TPC"."CAT_SW_RELEASE_ID")=(-1))
20 - filter("CAT_SW_REL_PLATFORM"."LAST_MODIFIED_DATE"> (SELECT "VALUE_DATE" FROM "BDNA_A_PROPERTIES$"
"BDNA_A_PROPERTIES$" WHERE "PROPERTY"=U'DIM_T_SW_REL_PLATFORM$.LAST_MODIFIED_DATE'))
22 - access("TPC_TAB"."CAT_SW_REL_PLATFORM_ID"="F_TCAT"."CAT_SW_REL_PLATFORM_ID")
29 - access("TPC"."CAT_SW_REL_PLATFORM_ID"="UUID"."CAT_UUID_ID")
filter(SIGN("TPC"."CAT_SW_REL_PLATFORM_ID")<>(-1))
31 - access("TCAT"."CAT_SW_REL_PLATFORM_ID"(+)="TPC"."CAT_SW_REL_PLATFORM_ID")
32 - filter(SIGN("TPC"."CAT_SW_REL_PLATFORM_ID")=(-1))
33 - filter("PROPERTY"=U'DIM_T_SW_REL_PLATFORM$.LAST_MODIFIED_DATE')
35 - access("T"."KEY_SW_REL_PLATFORM"(+)="CAT_SW_REL_PLATFORM"."CAT_SW_REL_PLATFORM_ID")
Explain plan which takes TMP space:
===================================
MERGE JOIN AND SORT JOIN, epecting 620G rows, 707T bytes, 999:59:59 estimated time, 1540T TMP SPACE
Plan hash value: 1403514838
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 620G| 707T| | 7324M (21)|999:59:59 |
| 1 | MERGE | DIM_T_SW_REL_PLATFORM$ | | | | | |
| 2 | VIEW | | | | | | |
| 3 | MERGE JOIN OUTER | | 620G| 725T| | 7324M (21)|999:59:59 |
| 4 | SORT JOIN | | 620G| 680T| 1540T| 7324M (21)|999:59:59 |
|* 5 | HASH JOIN | | 620G| 680T| 24M| 74M(100)| 32:09:58 |
|* 6 | HASH JOIN | | 594K| 18M| 2968K| 4063 (20)| 00:00:07 |
| 7 | TABLE ACCESS FULL | TMP_DIM_T_SW_PROD_SUBSCRIBED | 168K| 987K| | 95 (35)| 00:00:01 |
| 8 | VIEW | CAT_SW_RELEASE | 590K| 14M| | 2576 (23)| 00:00:05 |
| 9 | UNION-ALL | | | | | | |
|* 10 | HASH JOIN RIGHT ANTI | | 590K| 10M| | 2566 (23)| 00:00:05 |
| 11 | INDEX FULL SCAN | PK_TPC_SW_RELEASE | 78 | 390 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | TCAT_SW_RELEASE | 590K| 7497K| | 2494 (21)| 00:00:04 |
| 13 | NESTED LOOPS OUTER | | 4 | 120 | | 7 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 4 | 68 | | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | TPC_SW_RELEASE | 4 | 44 | | 3 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_TPC_UUID | 1 | 6 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| TCAT_SW_RELEASE | 1 | 13 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_CAT_SW_RELEASE | 1 | | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | TPC_SW_RELEASE | 1 | 11 | | 3 (0)| 00:00:01 |
|* 20 | VIEW | CAT_SW_REL_PLATFORM | 1042K| 1167M| | 3706 (23)| 00:00:06 |
| 21 | UNION-ALL | | | | | | |
|* 22 | HASH JOIN RIGHT ANTI | | 1042K| 71M| | 3701 (23)| 00:00:06 |
| 23 | INDEX FULL SCAN | PK_TPC_SW_REL_PLATFORM | 1 | 13 | | 0 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | TCAT_SW_REL_PLATFORM | 1042K| 58M| | 3576 (20)| 00:00:06 |
| 25 | NESTED LOOPS OUTER | | 1 | 1240 | | 3 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 1181 | | 2 (0)| 00:00:01 |
|* 27 | TABLE ACCESS FULL | TPC_SW_REL_PLATFORM | 1 | 1175 | | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_TPC_UUID | 1 | 6 | | 0 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | TCAT_SW_REL_PLATFORM | 1 | 59 | | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_CAT_SW_REL_PLATFORM | 1 | | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS FULL | TPC_SW_REL_PLATFORM | 1 | 1175 | | 2 (0)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | BDNA_A_PROPERTIES$ | 1 | 84 | | 3 (0)| 00:00:01 |
|* 33 | SORT JOIN | | 1041K| 78M| 208M| 23938 (9)| 00:00:38 |
| 34 | TABLE ACCESS FULL | DIM_T_SW_REL_PLATFORM$ | 1041K| 78M| | 3377 (15)| 00:00:06 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CAT_SW_REL_PLATFORM"."CAT_SW_RELEASE_ID"="CAT_SW_RELEASE"."CAT_SW_RELEASE_ID")
6 - access("TMP_DIM_T_SW_PROD_SUBSCRIBED"."CAT_SW_PRODUCT_ID"="CAT_SW_RELEASE"."CAT_SW_PRODUCT_ID")
10 - access("TPC_TAB"."CAT_SW_RELEASE_ID"="F_TCAT"."CAT_SW_RELEASE_ID")
15 - filter(SIGN("TPC"."CAT_SW_RELEASE_ID")<>(-1))
16 - access("TPC"."CAT_SW_RELEASE_ID"="UUID"."CAT_UUID_ID")
18 - access("TCAT"."CAT_SW_RELEASE_ID"(+)="TPC"."CAT_SW_RELEASE_ID")
19 - filter(SIGN("TPC"."CAT_SW_RELEASE_ID")=(-1))
20 - filter("CAT_SW_REL_PLATFORM"."LAST_MODIFIED_DATE"> (SELECT "VALUE_DATE" FROM "BDNA_A_PROPERTIES$"
"BDNA_A_PROPERTIES$" WHERE "PROPERTY"=U'DIM_T_SW_REL_PLATFORM$.LAST_MODIFIED_DATE'))
22 - access("TPC_TAB"."CAT_SW_REL_PLATFORM_ID"="F_TCAT"."CAT_SW_REL_PLATFORM_ID")
27 - filter(SIGN("TPC"."CAT_SW_REL_PLATFORM_ID")<>(-1))
28 - access("TPC"."CAT_SW_REL_PLATFORM_ID"="UUID"."CAT_UUID_ID")
30 - access("TCAT"."CAT_SW_REL_PLATFORM_ID"(+)="TPC"."CAT_SW_REL_PLATFORM_ID")
31 - filter(SIGN("TPC"."CAT_SW_REL_PLATFORM_ID")=(-1))
32 - filter("PROPERTY"=U'DIM_T_SW_REL_PLATFORM$.LAST_MODIFIED_DATE')
33 - access("T"."KEY_SW_REL_PLATFORM"(+)="CAT_SW_REL_PLATFORM"."CAT_SW_REL_PLATFORM_ID")
filter("T"."KEY_SW_REL_PLATFORM"(+)="CAT_SW_REL_PLATFORM"."CAT_SW_REL_PLATFORM_ID")