Difference in Execution Plan - Same Environment, Same SQL

From: Ross Lafferty <ross.lafferty_at_gmail.com>
Date: Wed, 1 May 2013 16:15:46 -0400
Message-Id: <A09570BB-3FF5-4BE5-8732-C72086322114_at_gmail.com>

Running into interesting behavior with execution plans in 11.2.0.3 (running on Exadata x3 platform). The below query was executed at roughly the same time, against the same environment, originating from different clients (different machines all together). The first one listed below, using hash joins, returns results in ~1 minute. The second one listed below, using nested loops, doesn’t return, even after hours. No alter sessions have been performed. OPTIMIZER_MODE for the environment is ALL_ROWS (which should gravitate towards hash joins).  

Query:

SELECT DEL.AE_LINE_ID,
       DEL.MD_CHANGE_NUMBER,
       DEL.MD_SOURCE_SYSTEM,
       A.DETAIL_KEY
  FROM DEL_CST_AE_LINES_REF DEL, MFG_PERIODICACCTLINES_DET A
WHERE     DEL.ODS_CHANGE_FLAG = 'Y'
       AND DEL.MD_SOURCE_SYSTEM = 1.00000000000000
       AND DEL.AE_LINE_ID = A.MD_LOOKUP_VALUE
       AND DEL.MD_SOURCE_SYSTEM = A.MD_SOURCE_SYSTEM;
 
Table Sizes:
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
MFG_PERIODICACCTLINES_DET         8991569
DEL_CST_AE_LINES_REF               155693
 

Statistics on both tables have been gathered within 1 hour of the execution, and have not changed (as no rows appear in dba_tab_modifications) since statistics were last gathered.  

Result coming in under 1 minute:  

---

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | | | | 58143 (100)| |

|*  1 |  HASH JOIN                      |                              |   155K|  5171K|  4272K| 58143   (1)| 00:13:35 |
|*  2 |   TABLE ACCESS STORAGE FULL     | DEL_CST_AE_LINES_REF         |   155K|  2432K|       |   192   (2)| 00:00:03 |


|   3 |   VIEW                          | index$_join$_002             |  8991K|   154M|       | 46742   (1)| 00:10:55 |

|*  4 |    HASH JOIN                    |                              |       |       |       |            |          |


|   5 |     INDEX STORAGE FAST FULL SCAN| MFG_PERIODICACCTLINES_DET_PK |  8991K|   154M|       | 12313   (1)| 00:02:53 |

|*  6 |     INDEX STORAGE FAST FULL SCAN| MFG_PERIODICACCTLI_IX2       |  8991K|   154M|       | 17640   (1)| 00:04:07 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - access("DEL"."AE_LINE_ID"=TO_NUMBER("A"."MD_LOOKUP_VALUE") AND
              "DEL"."MD_SOURCE_SYSTEM"="A"."MD_SOURCE_SYSTEM")
   2 - storage(("DEL"."ODS_CHANGE_FLAG"=:SYS_B_0 AND "DEL"."MD_SOURCE_SYSTEM"=:SYS_B_1))
       filter(("DEL"."ODS_CHANGE_FLAG"=:SYS_B_0 AND "DEL"."MD_SOURCE_SYSTEM"=:SYS_B_1))

   4 - access(ROWID=ROWID)
   6 - storage("A"."MD_SOURCE_SYSTEM"=:SYS_B_1)

       filter("A"."MD_SOURCE_SYSTEM"=:SYS_B_1)  

Will not Return (killed after 2 hours):

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | | | 3669 (100)| |
| 1 | NESTED LOOPS | | 1 | 33 | 3669 (1)| 00:00:52 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEL_CST_AE_HEADERS_REF | 1 | 16 | 2 (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | DEL_CST_AE_HEADERS_REF_IX2  |     1 |       |     1   (0)| 00:00:01 |


|   4 |   TABLE ACCESS BY INDEX ROWID| MFG_PERIODICACCTHEADERS_DET |     1 |    17 |  3667   (1)| 00:00:52 |

|*  5 |    INDEX SKIP SCAN           | MFG_PERIODICACCTHEAD_IX2    |     1 |       |  3666   (1)| 00:00:52 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   3 - access("DEL"."ODS_CHANGE_FLAG"=:SYS_B_0 AND "DEL"."MD_SOURCE_SYSTEM"=:SYS_B_1)
   5 - access("A"."MD_SOURCE_SYSTEM"=:SYS_B_1)
       filter(("A"."MD_SOURCE_SYSTEM"=:SYS_B_1 AND
              "DEL"."AE_HEADER_ID"=TO_NUMBER("A"."MD_LOOKUP_VALUE")))
 

My question is, why the varying execution path for the same query, same environment, being run at the same time - and any way to correct it? Thanks in advance!

-- 
B. Ross Lafferty
==============
E: ross.lafferty_at_gmail.com
==============


-- 
B. Ross Lafferty
==============
M: 412-608-7505
E: ross.lafferty_at_gmail.com
==============




--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 01 2013 - 22:15:46 CEST