Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ravi.

Asked: July 30, 2016 - 7:00 pm UTC

Last updated: August 03, 2016 - 7:56 am UTC

Version: 11g (11.2.03)

Viewed 1000+ times

You Asked

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")

and Chris said...

There's some big differences in row estimates between the plans:

- The first hash join. In the first plan (step 4) Oracle estimates it returns just 602 rows. In the second it is 620,000,000,000! This seems to be primarily due to the first objected scanned.

In the first plan, it's looking at TDT_SW_PROD_SUBSCRIBED_CAT_IDX and expecting just one row. In the second it's TMP_DIM_T_SW_PROD_SUBSCRIBED for 168,000 rows!

I'm guessing that the fast plan is the one with nested loop outer. If this is the case, then choosing a hash join vs sort merge is besides the point. They're both the wrong join method!

So look into why these estimates. Are they wrong? If so, why?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ravi B, August 01, 2016 - 6:40 pm UTC

TMP_DIM_T_SW_PROD_SUBSCRIBED table has same number of rows in the both the systems. Not sure why it is not doing the INDEX FULL SCAN on TDT_SW_PROD_SUBSCRIBED_CAT_IDX as i the "fast" plan. We did gather_table_stats on all the tables but did not help.
Chris Saxon
August 02, 2016 - 7:57 am UTC

Are the plans still the same after gathering stats?

What do the execution plans show?

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Ravi B, August 02, 2016 - 8:03 am UTC

The plans I posted are after gathering statistics.
Chris Saxon
August 02, 2016 - 11:11 am UTC

They are explain plans, not execution plans. We need to see the actual rows processed!

Post the plans showing both the E-rows and A-row in the same plan.

Ravi B, August 02, 2016 - 6:58 pm UTC

I could not get execution plans because I could never execute the SQL. It hangs forever and timesout with TMP space error.

Please let me know if there is any other way to get execution plan other than

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
Chris Saxon
August 03, 2016 - 7:56 am UTC

Are you licensed for the Diagnostics and Tuning packs? Then you can use the SQL Monitor to get progress "so far":

https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1

I thought the first statement completed quickly though? Surely you can get the plan for this?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.