SELECT Statement is Fast, INSERT INTO Using the SELECT Statement is Brutally Slow 3

20 12 2010

December 20, 2010

(Back to the Previous Post in the Series)

The previous article in this series included a test case that demonstrated significantly different performance for a SELECT statement and an INSERT INTO statement that used the same SELECT statement as the data source.  This blog article includes the timing results and the execution plans that were generated from the script in the previous blog article.  At the end of this blog article is an unexpected twist – see if you are able to determine what caused the behavior.

———–

SELECT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.23 seconds when selecting 10,478 rows:

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |  1425 |           |
| 1   |  SORT GROUP BY               |            |   486 |   46K |  1425 |  00:00:12 |
| 2   |   FILTER                     |            |       |       |       |           |
| 3   |    HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1423 |  00:00:12 |
| 4   |     VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 5   |      SORT GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 6   |       HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 7   |        TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 8   |        TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 9   |     NESTED LOOPS             |            |   19K | 1126K |  1055 |  00:00:09 |
| 10  |      VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 11  |       SORT GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 12  |        FILTER                |            |       |       |       |           |
| 13  |         INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 14  |      INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       8303  consistent gets                                                   
          0  physical reads                                                    
      62292  redo size                                                         
     141157  bytes sent via SQL*Net to client                                  
        470  bytes received via SQL*Net from client                            
         12  SQL*Net roundtrips to/from client                                 
          3  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed  

INSERT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in… 30 minutes, 18.01 seconds when inserting 10,478 rows:

-------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT               |         |       |       | 4964K |           |
| 1   |  LOAD TABLE CONVENTIONAL       |         |       |       |       |           |
| 2   |   SORT GROUP BY                |         |   842 |   60K | 4964K |  11:18:45 |
| 3   |    FILTER                      |         |       |       |       |           |
| 4   |     HASH JOIN OUTER            |         |   842 |   60K | 4964K |  11:18:45 |
| 5   |      VIEW                      |         |   842 |   28K | 4964K |  11:18:42 |
| 6   |       FILTER                   |         |       |       |       |           |
| 7   |        SORT GROUP BY           |         |   842 |   53K | 4964K |  11:18:42 |
| 8   |         FILTER                 |         |       |       |       |           |
| 9   |          HASH JOIN             |         |  852M |   54G |  7031 |  00:00:57 |
| 10  |           INDEX FAST FULL SCAN | T1_PK   |  106K | 2755K |    60 |  00:00:01 |
| 11  |           TABLE ACCESS FULL    | T1_LINES| 5628K |  214M |  3388 |  00:00:28 |
| 12  |      VIEW                      |         |   700 |   27K |   368 |  00:00:03 |
| 13  |       SORT GROUP BY            |         |   700 |   40K |   368 |  00:00:03 |
| 14  |        HASH JOIN               |         |  146K | 8452K |   362 |  00:00:03 |
| 15  |         TABLE ACCESS FULL      | T2      |   700 |   18K |     3 |  00:00:01 |
| 16  |         TABLE ACCESS FULL      | T2_LINES|  146K | 4663K |   358 |  00:00:03 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - filter("EL"."RELID"=MAX("RELID"))
8 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
9 - access("E"."CUSTPO"="EL"."CUSTPO")
11 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND "EL"."FCSTQTY">0))
14 - access("CO"."ID"="COL"."CUST_ORDER_ID")
15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
16 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

Statistics
----------------------------------------------------------                     
       1174  recursive calls                                                   
        454  db block gets                                                     
      84217  consistent gets                                                   
     214160  physical reads                                                    
     401488  redo size                                                         
        569  bytes sent via SQL*Net to client                                  
       1520  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
          6  sorts (memory)                                                    
          2  sorts (disk)                                                      
      10478  rows processed                                                     

SELECT_10.2.0.2 (OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.20 seconds when selecting 10,478 rows:

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |  1425 |           |
| 1   |  SORT GROUP BY               |            |   486 |   46K |  1425 |  00:00:12 |
| 2   |   FILTER                     |            |       |       |       |           |
| 3   |    HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1423 |  00:00:12 |
| 4   |     VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 5   |      HASH GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 6   |       HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 7   |        TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 8   |        TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 9   |     NESTED LOOPS             |            |   19K | 1126K |  1055 |  00:00:09 |
| 10  |      VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 11  |       HASH GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 12  |        FILTER                |            |       |       |       |           |
| 13  |         INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 14  |      INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       7457  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     141157  bytes sent via SQL*Net to client                                  
        470  bytes received via SQL*Net from client                            
         12  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

INSERT_10.2.0.2 (OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.23 seconds when inserting 10,478 rows:

---------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name       | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT              |            |       |       |  1425 |           |
| 1   |  LOAD TABLE CONVENTIONAL      |            |       |       |       |           |
| 2   |   SORT GROUP BY               |            |   486 |   46K |  1425 |  00:00:12 |
| 3   |    FILTER                     |            |       |       |       |           |
| 4   |     HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1423 |  00:00:12 |
| 5   |      VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 6   |       SORT GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 7   |        HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 8   |         TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 9   |         TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 10  |      NESTED LOOPS             |            |   19K | 1126K |  1055 |  00:00:09 |
| 11  |       VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 12  |        SORT GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 13  |         FILTER                |            |       |       |       |           |
| 14  |          INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 15  |       INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
---------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - access("CO"."ID"="COL"."CUST_ORDER_ID")
8 - filter("CUSTOMER_PO_REF" IS NOT NULL)
9 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
13 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
15 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
15 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
        186  recursive calls                                                   
        341  db block gets                                                     
       7561  consistent gets                                                   
          0  physical reads                                                    
     397832  redo size                                                         
        569  bytes sent via SQL*Net to client                                  
       1520  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
          4  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

SELECT_11.1.0.7 (OPTIMIZER_FEATURES_ENABLE=’11.1.0.7′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.23 seconds when selecting 10,478 rows:

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |  1392 |           |
| 1   |  SORT GROUP BY               |            |   829 |   79K |  1392 |  00:00:12 |
| 2   |   FILTER                     |            |       |       |       |           |
| 3   |    HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1390 |  00:00:12 |
| 4   |     VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 5   |      HASH GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 6   |       HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 7   |        TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 8   |        TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 9   |     NESTED LOOPS             |            |   19K | 1126K |  1022 |  00:00:09 |
| 10  |      VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 11  |       HASH GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 12  |        FILTER                |            |       |       |       |           |
| 13  |         INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 14  |      INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       7457  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     141157  bytes sent via SQL*Net to client                                  
        470  bytes received via SQL*Net from client                            
         12  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

INSERT_11.1.0.7 (OPTIMIZER_FEATURES_ENABLE=’11.1.0.7′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.25 seconds when inserting 10,478 rows:

---------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name       | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT              |            |       |       |  1392 |           |
| 1   |  LOAD TABLE CONVENTIONAL      |            |       |       |       |           |
| 2   |   SORT GROUP BY               |            |   829 |   79K |  1392 |  00:00:12 |
| 3   |    FILTER                     |            |       |       |       |           |
| 4   |     HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1390 |  00:00:12 |
| 5   |      VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 6   |       SORT GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 7   |        HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 8   |         TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 9   |         TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 10  |      NESTED LOOPS             |            |   19K | 1126K |  1022 |  00:00:09 |
| 11  |       VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 12  |        SORT GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 13  |         FILTER                |            |       |       |       |           |
| 14  |          INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 15  |       INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
---------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - access("CO"."ID"="COL"."CUST_ORDER_ID")
8 - filter("CUSTOMER_PO_REF" IS NOT NULL)
9 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
13 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
15 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
15 - filter("EL"."FCSTQTY">0) 

Statistics
----------------------------------------------------------                     
        186  recursive calls                                                   
        341  db block gets                                                     
       7564  consistent gets                                                   
          0  physical reads                                                    
     397816  redo size                                                         
        569  bytes sent via SQL*Net to client                                  
       1520  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
          4  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

SELECT_11.2.0.1 (OPTIMIZER_FEATURES_ENABLE=’11.2.0.1′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.21 seconds when selecting 10,478 rows:

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |  1392 |           |
| 1   |  SORT GROUP BY               |            |   829 |   79K |  1392 |  00:00:12 |
| 2   |   FILTER                     |            |       |       |       |           |
| 3   |    HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1390 |  00:00:12 |
| 4   |     VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 5   |      HASH GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 6   |       HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 7   |        TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 8   |        TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 9   |     NESTED LOOPS             |            |   19K | 1126K |  1022 |  00:00:09 |
| 10  |      VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 11  |       HASH GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 12  |        FILTER                |            |       |       |       |           |
| 13  |         INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 14  |      INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       7457  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     141157  bytes sent via SQL*Net to client                                  
        470  bytes received via SQL*Net from client                            
         12  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

INSERT_11.2.0.1 (OPTIMIZER_FEATURES_ENABLE=’11.2.0.1′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.26 seconds when inserting 10,478 rows:

---------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name       | Rows  | Bytes | Cost  | Time      |
---------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT              |            |       |       |  1392 |           |
| 1   |  LOAD TABLE CONVENTIONAL      |            |       |       |       |           |
| 2   |   SORT GROUP BY               |            |   829 |   79K |  1392 |  00:00:12 |
| 3   |    FILTER                     |            |       |       |       |           |
| 4   |     HASH JOIN RIGHT OUTER     |            |   19K | 1882K |  1390 |  00:00:12 |
| 5   |      VIEW                     |            |   700 |   27K |   368 |  00:00:03 |
| 6   |       SORT GROUP BY           |            |   700 |   40K |   368 |  00:00:03 |
| 7   |        HASH JOIN              |            |  146K | 8452K |   362 |  00:00:03 |
| 8   |         TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 9   |         TABLE ACCESS FULL     | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 10  |      NESTED LOOPS             |            |   19K | 1126K |  1022 |  00:00:09 |
| 11  |       VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 12  |        SORT GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 13  |         FILTER                |            |       |       |       |           |
| 14  |          INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 15  |       INDEX RANGE SCAN        | T1_LINES_PK|    28 |  1036 |     3 |  00:00:01 |
---------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - access("CO"."ID"="COL"."CUST_ORDER_ID")
8 - filter("CUSTOMER_PO_REF" IS NOT NULL)
9 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
13 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
15 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
15 - filter("EL"."FCSTQTY">0) 

Statistics
----------------------------------------------------------                     
        186  recursive calls                                                   
        341  db block gets                                                     
       7563  consistent gets                                                   
          0  physical reads                                                    
     397876  redo size                                                         
        569  bytes sent via SQL*Net to client                                  
       1520  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
          4  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

——————–

SELECT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=100, OPTIMIZER_INDEX_COST_ADJ=80), completed in 0.21 seconds when selecting 10,478 rows (execution plan is affected by the changes to OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |   436 |           |
| 1   |  SORT GROUP BY               |            |   486 |   46K |   436 |  00:00:04 |
| 2   |   NESTED LOOPS               |            |   994 |   94K |   435 |  00:00:04 |
| 3   |    FILTER                    |            |       |       |       |           |
| 4   |     HASH JOIN OUTER          |            |   700 |   41K |   433 |  00:00:04 |
| 5   |      VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 6   |       SORT GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 7   |        FILTER                |            |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 9   |      VIEW                    |            |   700 |   27K |   368 |  00:00:03 |
| 10  |       SORT GROUP BY          |            |   700 |   40K |   368 |  00:00:03 |
| 11  |        HASH JOIN             |            |  146K | 8452K |   362 |  00:00:03 |
| 12  |         TABLE ACCESS FULL    | T2         |   700 |   18K |     3 |  00:00:01 |
| 13  |         TABLE ACCESS FULL    | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 14  |    INDEX RANGE SCAN          | T1_LINES_PK|     1 |    37 |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200)
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
11 - access("CO"."ID"="COL"."CUST_ORDER_ID")
12 - filter("CUSTOMER_PO_REF" IS NOT NULL)
13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter(("EL"."FCSTQTY">0 AND "EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))) 

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       7457  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     141157  bytes sent via SQL*Net to client                                  
        470  bytes received via SQL*Net from client                            
         12  SQL*Net roundtrips to/from client                                 
          3  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

INSERT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=100, OPTIMIZER_INDEX_COST_ADJ=80), completed in… 15 minutes, 11.32 seconds when inserting 10,478 rows (execution plan is unaffected by the changes to OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ, although the execution completed twice as fast):

-------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT               |         |       |       | 4964K |           |
| 1   |  LOAD TABLE CONVENTIONAL       |         |       |       |       |           |
| 2   |   SORT GROUP BY                |         |   842 |   60K | 4964K |  11:18:45 |
| 3   |    FILTER                      |         |       |       |       |           |
| 4   |     HASH JOIN OUTER            |         |   842 |   60K | 4964K |  11:18:45 |
| 5   |      VIEW                      |         |   842 |   28K | 4964K |  11:18:42 |
| 6   |       FILTER                   |         |       |       |       |           |
| 7   |        SORT GROUP BY           |         |   842 |   53K | 4964K |  11:18:42 |
| 8   |         FILTER                 |         |       |       |       |           |
| 9   |          HASH JOIN             |         |  852M |   54G |  7031 |  00:00:57 |
| 10  |           INDEX FAST FULL SCAN | T1_PK   |  106K | 2755K |    60 |  00:00:01 |
| 11  |           TABLE ACCESS FULL    | T1_LINES| 5628K |  214M |  3388 |  00:00:28 |
| 12  |      VIEW                      |         |   700 |   27K |   368 |  00:00:03 |
| 13  |       SORT GROUP BY            |         |   700 |   40K |   368 |  00:00:03 |
| 14  |        HASH JOIN               |         |  146K | 8452K |   362 |  00:00:03 |
| 15  |         TABLE ACCESS FULL      | T2      |   700 |   18K |     3 |  00:00:01 |
| 16  |         TABLE ACCESS FULL      | T2_LINES|  146K | 4663K |   358 |  00:00:03 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - filter("EL"."RELID"=MAX("RELID"))
8 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
9 - access("E"."CUSTPO"="EL"."CUSTPO")
11 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND "EL"."FCSTQTY">0))
14 - access("CO"."ID"="COL"."CUST_ORDER_ID")
15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
16 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

Statistics
----------------------------------------------------------                     
        618  recursive calls                                                   
        388  db block gets                                                     
      42148  consistent gets                                                   
     107080  physical reads                                                    
     397876  redo size                                                         
        569  bytes sent via SQL*Net to client                                  
       1523  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
          3  sorts (memory)                                                    
          1  sorts (disk)                                                      
      10478  rows processed

SELECT_10.2.0.2 (OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′, OPTIMIZER_INDEX_CACHING=100, OPTIMIZER_INDEX_COST_ADJ=80), completed in 0.20 seconds when selecting 10,478 rows (excution plans are identical for SELECT and INSERT INTO for the OPTIMIZER_FEATURES_ENABLE values 10.2.0.2, 11.1.0.7, and 11.2.0.1):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |   436 |           |
| 1   |  SORT GROUP BY               |            |   486 |   46K |   436 |  00:00:04 |
| 2   |   NESTED LOOPS               |            |   994 |   94K |   435 |  00:00:04 |
| 3   |    FILTER                    |            |       |       |       |           |
| 4   |     HASH JOIN OUTER          |            |   700 |   41K |   433 |  00:00:04 |
| 5   |      VIEW                    |            |   700 |   14K |    65 |  00:00:01 |
| 6   |       HASH GROUP BY          |            |   700 |   18K |    65 |  00:00:01 |
| 7   |        FILTER                |            |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | T1_PK      |  106K | 2755K |    60 |  00:00:01 |
| 9   |      VIEW                    |            |   700 |   27K |   368 |  00:00:03 |
| 10  |       HASH GROUP BY          |            |   700 |   40K |   368 |  00:00:03 |
| 11  |        HASH JOIN             |            |  146K | 8452K |   362 |  00:00:03 |
| 12  |         TABLE ACCESS FULL    | T2         |   700 |   18K |     3 |  00:00:01 |
| 13  |         TABLE ACCESS FULL    | T2_LINES   |  146K | 4663K |   358 |  00:00:03 |
| 14  |    INDEX RANGE SCAN          | T1_LINES_PK|     1 |    37 |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200)
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
11 - access("CO"."ID"="COL"."CUST_ORDER_ID")
12 - filter("CUSTOMER_PO_REF" IS NOT NULL)
13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter(("EL"."FCSTQTY">0 AND "EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)))

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       7457  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     141157  bytes sent via SQL*Net to client                                  
        470  bytes received via SQL*Net from client                            
         12  SQL*Net roundtrips to/from client                                 
          1  sorts (memory)                                                    
          0  sorts (disk)                                                      
      10478  rows processed

————————-

The odd twist… assume that you used either Oracle Database 11.2.0.1 or 11.2.0.2 for the test.  12 hours after your test completed, you executed the following SQL statements to pull the execution plans and non-default optimizer parameters for the test SQL statements from the server’s memory:

SET AUTOTRACE OFF
SET LINESIZE 150
SET PAGESIZE 1000
SET TRIMSPOOL ON
SPOOL MYSQL_PLANS.TXT

SELECT /*+ LEADING(S) USE_NL(S T) */
  T.PLAN_TABLE_OUTPUT
FROM
  (SELECT
    SQL_ID,
    CHILD_NUMBER,
    COUNT(*) OVER (PARTITION BY SQL_ID) C
  FROM
    V$SQL
  WHERE
    SQL_TEXT LIKE '%T2%'
    AND SQL_TEXT LIKE '%T1%'
    AND SQL_TEXT NOT LIKE '%MGMT_NOTIFY%'
  ORDER BY
    SQL_ID,
    CHILD_NUMBER) S,
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'TYPICAL')) T
WHERE
  S.C>=1;

SPOOL OFF

SET LINESIZE 150
SET PAGESIZE 1000
SET TRIMSPOOL ON
SPOOL MYSQL_OPTIMIZER_ENV.TXT

SELECT /*+ LEADING(S) USE_NL(S T) */
  SOE.SQL_ID,
  SOE.CHILD_NUMBER,
  SOE.NAME,
  SOE.VALUE
FROM
  (SELECT
    SQL_ID,
    CHILD_NUMBER,
    COUNT(*) OVER (PARTITION BY SQL_ID) C
  FROM
    V$SQL
  WHERE
    SQL_TEXT LIKE '%T2%'
    AND SQL_TEXT LIKE '%T1%'
    AND SQL_TEXT NOT LIKE '%MGMT_NOTIFY%'
  ORDER BY
    SQL_ID,
    CHILD_NUMBER) S,
  V$SQL_OPTIMIZER_ENV SOE
WHERE
  S.SQL_ID=SOE.SQL_ID
  AND S.CHILD_NUMBER=SOE.CHILD_NUMBER
  AND SOE.ISDEFAULT='NO'
ORDER BY
  SOE.SQL_ID,
  SOE.CHILD_NUMBER,
  SOE.NAME;

SPOOL OFF 

Assume that you saw included in the output something like this following:

SQL_ID  4kkx042dnfuvf, child number 0
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+ _80 */       E.CUSTPO,       MAX(RELID) RELID     FROM
 T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY

Plan hash value: 1947245329

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |   436 (100)|          |
|   1 |  SORT GROUP BY              |             |   829 | 80413 |   436   (7)| 00:00:04 |
|   2 |   NESTED LOOPS              |             |   994 | 96418 |   435   (6)| 00:00:04 |
|*  3 |    FILTER                   |             |       |       |            |          |
|*  4 |     HASH JOIN OUTER         |             |   700 | 42000 |   433   (6)| 00:00:04 |
|   5 |      VIEW                   |             |   700 | 14700 |    65  (10)| 00:00:01 |
|   6 |       HASH GROUP BY         |             |   700 | 18200 |    65  (10)| 00:00:01 |
|*  7 |        FILTER               |             |       |       |            |          |
|   8 |         INDEX FAST FULL SCAN| T1_PK       |   108K|  2754K|    60   (2)| 00:00:01 |
|   9 |      VIEW                   |             |   700 | 27300 |   368   (6)| 00:00:03 |
|  10 |       HASH GROUP BY         |             |   700 | 40600 |   368   (6)| 00:00:03 |
|* 11 |        HASH JOIN            |             |   149K|  8456K|   362   (4)| 00:00:03 |
|* 12 |         TABLE ACCESS FULL   | T2          |   700 | 18200 |     3   (0)| 00:00:01 |
|* 13 |         TABLE ACCESS FULL   | T2_LINES    |   149K|  4665K|   358   (4)| 00:00:03 |
|* 14 |    INDEX RANGE SCAN         | T1_LINES_PK |     1 |    37 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200)
   4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  11 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  12 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND
              "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND
              "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter(("EL"."FCSTQTY">0 AND "EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DA
              TE",SYSDATE@!-365))) 

 

SQL_ID  4kkx042dnfuvf, child number 1
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+ _80 */       E.CUSTPO,       MAX(RELID) RELID     FROM
 T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY

Plan hash value: 1232865634

---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |       |       |       |  5087K(100)|          |
|   1 |  SORT GROUP BY                   |              |   842 | 61466 |       |  5087K  (2)| 11:18:20 |
|*  2 |   FILTER                         |              |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER               |              |   842 | 61466 |       |  5087K  (2)| 11:18:20 |
|   4 |     VIEW                         |              |   842 | 28628 |       |  5083K  (2)| 11:17:48 |
|*  5 |      FILTER                      |              |       |       |       |            |          |
|   6 |       SORT GROUP BY              |              |   842 | 54730 |    77G|  5083K  (2)| 11:17:48 |
|*  7 |        FILTER                    |              |       |       |       |            |          |
|   8 |         NESTED LOOPS             |              |   893M|    54G|       |  7225 (100)| 00:00:58 |
|   9 |          INDEX FAST FULL SCAN    | T1_PK        |   108K|  2754K|       |    59   (2)| 00:00:01 |
|* 10 |          INDEX RANGE SCAN        | T1_LINES_PK  |  8233 |   313K|       |     2  (50)| 00:00:01 |
|  11 |     VIEW                         |              |   700 | 27300 |       |  3939   (1)| 00:00:32 |
|  12 |      SORT GROUP BY               |              |   700 | 40600 |       |  3939   (1)| 00:00:32 |
|* 13 |       TABLE ACCESS BY INDEX ROWID| T2_LINES     |   213 |  6816 |       |     6   (0)| 00:00:01 |
|  14 |        NESTED LOOPS              |              |   149K|  8456K|       |  3933   (1)| 00:00:32 |
|* 15 |         TABLE ACCESS FULL        | T2           |   700 | 18200 |       |     3  (34)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN         | SYS_C0027538 |   321 |       |       |     2  (50)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   5 - filter("EL"."RELID"=MAX("RELID"))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  10 - access("E"."CUSTPO"="EL"."CUSTPO" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND
              "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND
              "EL"."FCSTQTY">0))
  13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  16 - access("CO"."ID"="COL"."CUST_ORDER_ID") 

 

SQL_ID  4kkx042dnfuvf, child number 2
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+ _80 */       E.CUSTPO,       MAX(RELID) RELID     FROM
 T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY

Plan hash value: 4293267978

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |       |  5083K(100)|          |
|   1 |  SORT GROUP BY               |             |   842 | 61466 |       |  5083K  (2)| 11:17:51 |
|*  2 |   FILTER                     |             |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER           |             |   842 | 61466 |       |  5083K  (2)| 11:17:51 |
|   4 |     VIEW                     |             |   842 | 28628 |       |  5083K  (2)| 11:17:48 |
|*  5 |      FILTER                  |             |       |       |       |            |          |
|   6 |       SORT GROUP BY          |             |   842 | 54730 |    77G|  5083K  (2)| 11:17:48 |
|*  7 |        FILTER                |             |       |       |       |            |          |
|   8 |         NESTED LOOPS         |             |   893M|    54G|       |  7226 (100)| 00:00:58 |
|   9 |          INDEX FAST FULL SCAN| T1_PK       |   108K|  2754K|       |    60   (2)| 00:00:01 |
|* 10 |          INDEX RANGE SCAN    | T1_LINES_PK |  8233 |   313K|       |     2  (50)| 00:00:01 |
|  11 |     VIEW                     |             |   700 | 27300 |       |   369   (6)| 00:00:03 |
|  12 |      SORT GROUP BY           |             |   700 | 40600 |       |   369   (6)| 00:00:03 |
|* 13 |       HASH JOIN              |             |   149K|  8456K|       |   362   (4)| 00:00:03 |
|* 14 |        TABLE ACCESS FULL     | T2          |   700 | 18200 |       |     4  (25)| 00:00:01 |
|* 15 |        TABLE ACCESS FULL     | T2_LINES    |   149K|  4665K|       |   358   (4)| 00:00:03 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   5 - filter("EL"."RELID"=MAX("RELID"))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  10 - access("E"."CUSTPO"="EL"."CUSTPO" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND
              "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND
              "EL"."FCSTQTY">0))
  13 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  14 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  15 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365) 

 

SQL_ID  4kkx042dnfuvf, child number 3
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+ _80 */       E.CUSTPO,       MAX(RELID) RELID     FROM
 T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY

Plan hash value: 3038192993

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |   436 (100)|          |
|   1 |  SORT GROUP BY              |             |   486 | 47142 |   436   (7)| 00:00:04 |
|   2 |   NESTED LOOPS              |             |   994 | 96418 |   435   (6)| 00:00:04 |
|*  3 |    FILTER                   |             |       |       |            |          |
|*  4 |     HASH JOIN OUTER         |             |   700 | 42000 |   433   (6)| 00:00:04 |
|   5 |      VIEW                   |             |   700 | 14700 |    65  (10)| 00:00:01 |
|   6 |       SORT GROUP BY         |             |   700 | 18200 |    65  (10)| 00:00:01 |
|*  7 |        FILTER               |             |       |       |            |          |
|   8 |         INDEX FAST FULL SCAN| T1_PK       |   108K|  2754K|    60   (2)| 00:00:01 |
|   9 |      VIEW                   |             |   700 | 27300 |   368   (6)| 00:00:03 |
|  10 |       SORT GROUP BY         |             |   700 | 40600 |   368   (6)| 00:00:03 |
|* 11 |        HASH JOIN            |             |   149K|  8456K|   362   (4)| 00:00:03 |
|* 12 |         TABLE ACCESS FULL   | T2          |   700 | 18200 |     3   (0)| 00:00:01 |
|* 13 |         TABLE ACCESS FULL   | T2_LINES    |   149K|  4665K|   358   (4)| 00:00:03 |
|* 14 |    INDEX RANGE SCAN         | T1_LINES_PK |     1 |    37 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200)
   4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  11 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  12 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND
              "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND
              "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter(("EL"."FCSTQTY">0 AND "EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DA
              TE",SYSDATE@!-365))) 

 

SQL_ID  bqsrv9mngjdfu, child number 0
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+  */       E.CUSTPO,       MAX(RELID) RELID     FROM
T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY   CO

Plan hash value: 1522673873

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |  1392 (100)|          |
|   1 |  SORT GROUP BY              |             |   829 | 80413 |  1392   (3)| 00:00:12 |
|*  2 |   FILTER                    |             |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER    |             | 19874 |  1882K|  1390   (2)| 00:00:12 |
|   4 |     VIEW                    |             |   700 | 27300 |   368   (6)| 00:00:03 |
|   5 |      HASH GROUP BY          |             |   700 | 40600 |   368   (6)| 00:00:03 |
|*  6 |       HASH JOIN             |             |   149K|  8456K|   362   (4)| 00:00:03 |
|*  7 |        TABLE ACCESS FULL    | T2          |   700 | 18200 |     3   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL    | T2_LINES    |   149K|  4665K|   358   (4)| 00:00:03 |
|   9 |     NESTED LOOPS            |             | 19874 |  1125K|  1022   (1)| 00:00:09 |
|  10 |      VIEW                   |             |   700 | 14700 |    65  (10)| 00:00:01 |
|  11 |       HASH GROUP BY         |             |   700 | 18200 |    65  (10)| 00:00:01 |
|* 12 |        FILTER               |             |       |       |            |          |
|  13 |         INDEX FAST FULL SCAN| T1_PK       |   108K|  2754K|    60   (2)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN       | T1_LINES_PK |    28 |  1036 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)
              AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
   7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
   8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND
              "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND
              "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter("EL"."FCSTQTY">0) 

 

SQL_ID  bqsrv9mngjdfu, child number 1
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+  */       E.CUSTPO,       MAX(RELID) RELID     FROM
T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY   CO

Plan hash value: 1232865634

---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |       |       |       |    16M(100)|          |
|   1 |  SORT GROUP BY                   |              |   842 | 61466 |       |    16M  (1)| 35:51:03 |
|*  2 |   FILTER                         |              |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER               |              |   842 | 61466 |       |    16M  (1)| 35:51:03 |
|   4 |     VIEW                         |              |   842 | 28628 |       |    16M  (1)| 35:50:07 |
|*  5 |      FILTER                      |              |       |       |       |            |          |
|   6 |       SORT GROUP BY              |              |   842 | 54730 |    77G|    16M  (1)| 35:50:07 |
|*  7 |        FILTER                    |              |       |       |       |            |          |
|   8 |         NESTED LOOPS             |              |   893M|    54G|       |    11M  (1)| 24:33:17 |
|   9 |          INDEX FAST FULL SCAN    | T1_PK        |   108K|  2754K|       |    59   (2)| 00:00:01 |
|* 10 |          INDEX RANGE SCAN        | T1_LINES_PK  |  8233 |   313K|       |   104   (1)| 00:00:01 |
|  11 |     VIEW                         |              |   700 | 27300 |       |  7023   (1)| 00:00:57 |
|  12 |      SORT GROUP BY               |              |   700 | 40600 |       |  7023   (1)| 00:00:57 |
|* 13 |       TABLE ACCESS BY INDEX ROWID| T2_LINES     |   213 |  6816 |       |    11  (10)| 00:00:01 |
|  14 |        NESTED LOOPS              |              |   149K|  8456K|       |  7016   (1)| 00:00:57 |
|* 15 |         TABLE ACCESS FULL        | T2           |   700 | 18200 |       |     3  (34)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN         | SYS_C0027538 |   321 |       |       |     4  (25)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   5 - filter("EL"."RELID"=MAX("RELID"))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  10 - access("E"."CUSTPO"="EL"."CUSTPO" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND
              "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND
              "EL"."FCSTQTY">0))
  13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  16 - access("CO"."ID"="COL"."CUST_ORDER_ID") 

 

SQL_ID  bqsrv9mngjdfu, child number 2
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+  */       E.CUSTPO,       MAX(RELID) RELID     FROM
T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY   CO

Plan hash value: 3881822961

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |       |  5083K(100)|          |
|   1 |  SORT GROUP BY               |          |   842 | 61466 |       |  5083K  (2)| 11:17:50 |
|*  2 |   FILTER                     |          |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER           |          |   842 | 61466 |       |  5083K  (2)| 11:17:50 |
|   4 |     VIEW                     |          |   842 | 28628 |       |  5083K  (2)| 11:17:47 |
|*  5 |      FILTER                  |          |       |       |       |            |          |
|   6 |       SORT GROUP BY          |          |   842 | 54730 |    77G|  5083K  (2)| 11:17:47 |
|*  7 |        FILTER                |          |       |       |       |            |          |
|*  8 |         HASH JOIN            |          |   893M|    54G|  4032K|  7033   (9)| 00:00:57 |
|   9 |          INDEX FAST FULL SCAN| T1_PK    |   108K|  2754K|       |    60   (2)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL   | T1_LINES |  5763K|   214M|       |  3389  (16)| 00:00:28 |
|  11 |     VIEW                     |          |   700 | 27300 |       |   369   (6)| 00:00:03 |
|  12 |      SORT GROUP BY           |          |   700 | 40600 |       |   369   (6)| 00:00:03 |
|* 13 |       HASH JOIN              |          |   149K|  8456K|       |   362   (4)| 00:00:03 |
|* 14 |        TABLE ACCESS FULL     | T2       |   700 | 18200 |       |     4  (25)| 00:00:01 |
|* 15 |        TABLE ACCESS FULL     | T2_LINES |   149K|  4665K|       |   358   (4)| 00:00:03 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   5 - filter("EL"."RELID"=MAX("RELID"))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
   8 - access("E"."CUSTPO"="EL"."CUSTPO")
  10 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND
              "EL"."FCSTQTY">0))
  13 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  14 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  15 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365) 

 

SQL_ID  bqsrv9mngjdfu, child number 3
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+  */       E.CUSTPO,       MAX(RELID) RELID     FROM
T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY   CO

Plan hash value: 4176222027

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |  1425 (100)|          |
|   1 |  SORT GROUP BY              |             |   486 | 47142 |  1425   (2)| 00:00:12 |
|*  2 |   FILTER                    |             |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER    |             | 19874 |  1882K|  1423   (2)| 00:00:12 |
|   4 |     VIEW                    |             |   700 | 27300 |   368   (6)| 00:00:03 |
|   5 |      SORT GROUP BY          |             |   700 | 40600 |   368   (6)| 00:00:03 |
|*  6 |       HASH JOIN             |             |   149K|  8456K|   362   (4)| 00:00:03 |
|*  7 |        TABLE ACCESS FULL    | T2          |   700 | 18200 |     3   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL    | T2_LINES    |   149K|  4665K|   358   (4)| 00:00:03 |
|   9 |     NESTED LOOPS            |             | 19874 |  1125K|  1055   (1)| 00:00:09 |
|  10 |      VIEW                   |             |   700 | 14700 |    65  (10)| 00:00:01 |
|  11 |       SORT GROUP BY         |             |   700 | 18200 |    65  (10)| 00:00:01 |
|* 12 |        FILTER               |             |       |       |            |          |
|  13 |         INDEX FAST FULL SCAN| T1_PK       |   108K|  2754K|    60   (2)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN       | T1_LINES_PK |    28 |  1036 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)
              AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
   7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
   8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND
              "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND
              "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter("EL"."FCSTQTY">0) 

 

SQL_ID  bqsrv9mngjdfu, child number 4
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+  */       E.CUSTPO,       MAX(RELID) RELID     FROM
T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY   CO

Plan hash value: 1232865634

---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |       |       |       |    16M(100)|          |
|   1 |  SORT GROUP BY                   |              |   842 | 61466 |       |    16M  (1)| 35:51:03 |
|*  2 |   FILTER                         |              |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER               |              |   842 | 61466 |       |    16M  (1)| 35:51:03 |
|   4 |     VIEW                         |              |   842 | 28628 |       |    16M  (1)| 35:50:07 |
|*  5 |      FILTER                      |              |       |       |       |            |          |
|   6 |       SORT GROUP BY              |              |   842 | 54730 |    77G|    16M  (1)| 35:50:07 |
|*  7 |        FILTER                    |              |       |       |       |            |          |
|   8 |         NESTED LOOPS             |              |   893M|    54G|       |    11M  (1)| 24:33:17 |
|   9 |          INDEX FAST FULL SCAN    | T1_PK        |   108K|  2754K|       |    59   (2)| 00:00:01 |
|* 10 |          INDEX RANGE SCAN        | T1_LINES_PK  |  8233 |   313K|       |   104   (1)| 00:00:01 |
|  11 |     VIEW                         |              |   700 | 27300 |       |  7023   (1)| 00:00:57 |
|  12 |      SORT GROUP BY               |              |   700 | 40600 |       |  7023   (1)| 00:00:57 |
|* 13 |       TABLE ACCESS BY INDEX ROWID| T2_LINES     |   213 |  6816 |       |    11  (10)| 00:00:01 |
|  14 |        NESTED LOOPS              |              |   149K|  8456K|       |  7016   (1)| 00:00:57 |
|* 15 |         TABLE ACCESS FULL        | T2           |   700 | 18200 |       |     3  (34)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN         | SYS_C0027538 |   321 |       |       |     4  (25)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   5 - filter("EL"."RELID"=MAX("RELID"))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  10 - access("E"."CUSTPO"="EL"."CUSTPO" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND
              "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
       filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND
              "EL"."FCSTQTY">0))
  13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
  15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  16 - access("CO"."ID"="COL"."CUST_ORDER_ID") 

 SQL_ID  bqsrv9mngjdfu, child number 5
-------------------------------------
/* SQL Analyze(7,1) */ SELECT /*+ LEADING(E) */
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,   E.FCDUEDATE
REQUIRED_DATE,   SUM(E.FCSTQTY) QTY,   1 PLAN_LEVEL FROM   (SELECT /*+
LEADING(E) */     E.CUSTPO,     EL.FCSTQTY,     EL.FCDUEDATE   FROM
(SELECT /*+  */       E.CUSTPO,       MAX(RELID) RELID     FROM
T1 E     GROUP BY       E.CUSTPO) E,     T1_LINES EL   WHERE
E.CUSTPO=EL.CUSTPO     AND E.RELID=EL.RELID     AND EL.FCDUEDATE
BETWEEN SYSDATE-365 AND SYSDATE+1200     AND EL.FCSTQTY>0) E,   (SELECT
/*+  */     CO.CUSTOMER_PO_REF,     MAX(COL.DESIRED_SHIP_DATE)
DESIRED_SHIP_DATE,     MAX(COL.PART_ID) PART_ID   FROM     T2 CO,
T2_LINES COL   WHERE     CO.ID=COL.CUST_ORDER_ID     AND
CUSTOMER_PO_REF IS NOT NULL     AND COL.DESIRED_SHIP_DATE>=SYSDATE-365
 GROUP BY     CO.CUSTOMER_PO_REF) CO WHERE
E.CUSTPO=CO.CUSTOMER_PO_REF(+)   AND
E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY
COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),   E.FCDUEDATE ORDER BY   CO

Plan hash value: 3881822961

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |       |  5083K(100)|          |
|   1 |  SORT GROUP BY               |          |   842 | 61466 |       |  5083K  (2)| 11:17:50 |
|*  2 |   FILTER                     |          |       |       |       |            |          |
|*  3 |    HASH JOIN OUTER           |          |   842 | 61466 |       |  5083K  (2)| 11:17:50 |
|   4 |     VIEW                     |          |   842 | 28628 |       |  5083K  (2)| 11:17:47 |
|*  5 |      FILTER                  |          |       |       |       |            |          |
|   6 |       SORT GROUP BY          |          |   842 | 54730 |    77G|  5083K  (2)| 11:17:47 |
|*  7 |        FILTER                |          |       |       |       |            |          |
|*  8 |         HASH JOIN            |          |   893M|    54G|  4032K|  7033   (9)| 00:00:57 |
|   9 |          INDEX FAST FULL SCAN| T1_PK    |   108K|  2754K|       |    60   (2)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL   | T1_LINES |  5763K|   214M|       |  3389  (16)| 00:00:28 |
|  11 |     VIEW                     |          |   700 | 27300 |       |   369   (6)| 00:00:03 |
|  12 |      SORT GROUP BY           |          |   700 | 40600 |       |   369   (6)| 00:00:03 |
|* 13 |       HASH JOIN              |          |   149K|  8456K|       |   362   (4)| 00:00:03 |
|* 14 |        TABLE ACCESS FULL     | T2       |   700 | 18200 |       |     4  (25)| 00:00:01 |
|* 15 |        TABLE ACCESS FULL     | T2_LINES |   149K|  4665K|       |   358   (4)| 00:00:03 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
   5 - filter("EL"."RELID"=MAX("RELID"))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
   8 - access("E"."CUSTPO"="EL"."CUSTPO")
  10 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND
              "EL"."FCSTQTY">0))
  13 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  14 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  15 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365) 

Assume that the second script produced the following (the blue text is directly related to the SQL statements that were executed by the script):

SQL_ID        CHILD_NUMBER NAME                                     VALUE
------------- ------------ ---------------------------------------- -------------------------
33n2n2z5a330f            0 _pga_max_size                            409600 KB
33n2n2z5a330f            0 optimizer_features_enable                10.1.0.4
-
33n2n2z5a330f            1 _pga_max_size                            409600 KB
33n2n2z5a330f            1 optimizer_features_enable                10.2.0.2
-
33n2n2z5a330f            2 _pga_max_size                            409600 KB
33n2n2z5a330f            2 optimizer_features_enable                11.1.0.7
-
33n2n2z5a330f            3 _pga_max_size                            409600 KB
------------------------------------------------------------------------------
4kkx042dnfuvf            0 _pga_max_size                            409600 KB
4kkx042dnfuvf            0 is_recur_flags                           72
4kkx042dnfuvf            0 optimizer_features_hinted                11.2.0.1
4kkx042dnfuvf            0 optimizer_index_caching                  100
4kkx042dnfuvf            0 optimizer_index_cost_adj                 80
4kkx042dnfuvf            0 optimizer_mode_hinted                    true
-
4kkx042dnfuvf            1 _pga_max_size                            409600 KB
4kkx042dnfuvf            1 is_recur_flags                           72
4kkx042dnfuvf            1 optimizer_dynamic_sampling               0
4kkx042dnfuvf            1 optimizer_features_hinted                9.0.0
4kkx042dnfuvf            1 optimizer_index_caching                  100
4kkx042dnfuvf            1 optimizer_index_cost_adj                 80
4kkx042dnfuvf            1 optimizer_mode_hinted                    true
4kkx042dnfuvf            1 query_rewrite_enabled                    false
4kkx042dnfuvf            1 skip_unusable_indexes                    false
-
4kkx042dnfuvf            2 _pga_max_size                            409600 KB
4kkx042dnfuvf            2 is_recur_flags                           72
4kkx042dnfuvf            2 optimizer_dynamic_sampling               1
4kkx042dnfuvf            2 optimizer_features_hinted                9.2.0.8
4kkx042dnfuvf            2 optimizer_index_caching                  100
4kkx042dnfuvf            2 optimizer_index_cost_adj                 80
4kkx042dnfuvf            2 optimizer_mode_hinted                    true
4kkx042dnfuvf            2 query_rewrite_enabled                    false
4kkx042dnfuvf            2 skip_unusable_indexes                    false
-
4kkx042dnfuvf            3 _pga_max_size                            409600 KB
4kkx042dnfuvf            3 is_recur_flags                           72
4kkx042dnfuvf            3 optimizer_features_hinted                10.1.0.5
4kkx042dnfuvf            3 optimizer_index_caching                  100
4kkx042dnfuvf            3 optimizer_index_cost_adj                 80
4kkx042dnfuvf            3 optimizer_mode_hinted                    true
------------------------------------------------------------------------------
bqsrv9mngjdfu            0 _pga_max_size                            409600 KB
bqsrv9mngjdfu            0 is_recur_flags                           72
bqsrv9mngjdfu            0 optimizer_features_hinted                11.2.0.1
bqsrv9mngjdfu            0 optimizer_mode_hinted                    true
-
bqsrv9mngjdfu            1 _pga_max_size                            409600 KB
bqsrv9mngjdfu            1 is_recur_flags                           72
bqsrv9mngjdfu            1 optimizer_dynamic_sampling               0
bqsrv9mngjdfu            1 optimizer_features_hinted                9.0.0
bqsrv9mngjdfu            1 optimizer_mode_hinted                    true
bqsrv9mngjdfu            1 query_rewrite_enabled                    false
bqsrv9mngjdfu            1 skip_unusable_indexes                    false
-
bqsrv9mngjdfu            2 _pga_max_size                            409600 KB
bqsrv9mngjdfu            2 is_recur_flags                           72
bqsrv9mngjdfu            2 optimizer_dynamic_sampling               1
bqsrv9mngjdfu            2 optimizer_features_hinted                9.2.0.8
bqsrv9mngjdfu            2 optimizer_mode_hinted                    true
bqsrv9mngjdfu            2 query_rewrite_enabled                    false
bqsrv9mngjdfu            2 skip_unusable_indexes                    false
-
bqsrv9mngjdfu            3 _pga_max_size                            409600 KB
bqsrv9mngjdfu            3 is_recur_flags                           72
bqsrv9mngjdfu            3 optimizer_features_hinted                10.1.0.5
bqsrv9mngjdfu            3 optimizer_mode_hinted                    true
-
bqsrv9mngjdfu            4 _pga_max_size                            409600 KB
bqsrv9mngjdfu            4 is_recur_flags                           72
bqsrv9mngjdfu            4 optimizer_dynamic_sampling               0
bqsrv9mngjdfu            4 optimizer_features_hinted                9.0.0
bqsrv9mngjdfu            4 optimizer_mode_hinted                    true
bqsrv9mngjdfu            4 query_rewrite_enabled                    false
bqsrv9mngjdfu            4 skip_unusable_indexes                    false
-
bqsrv9mngjdfu            5 _pga_max_size                            409600 KB
bqsrv9mngjdfu            5 is_recur_flags                           72
bqsrv9mngjdfu            5 optimizer_dynamic_sampling               1
bqsrv9mngjdfu            5 optimizer_features_hinted                9.2.0.8
bqsrv9mngjdfu            5 optimizer_mode_hinted                    true
bqsrv9mngjdfu            5 query_rewrite_enabled                    false
bqsrv9mngjdfu            5 skip_unusable_indexes                    false
------------------------------------------------------------------------------
gfb3g0axbj17z            0 _pga_max_size                            409600 KB
gfb3g0axbj17z            0 optimizer_features_enable                10.1.0.4
gfb3g0axbj17z            0 optimizer_index_caching                  100
gfb3g0axbj17z            0 optimizer_index_cost_adj                 80
-
gfb3g0axbj17z            1 _pga_max_size                            409600 KB
gfb3g0axbj17z            1 optimizer_features_enable                10.2.0.2
gfb3g0axbj17z            1 optimizer_index_caching                  100
gfb3g0axbj17z            1 optimizer_index_cost_adj                 80
-
gfb3g0axbj17z            2 _pga_max_size                            409600 KB
gfb3g0axbj17z            2 optimizer_features_enable                11.1.0.7
gfb3g0axbj17z            2 optimizer_index_caching                  100
gfb3g0axbj17z            2 optimizer_index_cost_adj                 80
-
gfb3g0axbj17z            3 _pga_max_size                            409600 KB
gfb3g0axbj17z            3 optimizer_index_caching                  100
gfb3g0axbj17z            3 optimizer_index_cost_adj                 80  

—————————————————————————————————————————–
—————————————————————————————————————————–

Just to put a slightly different spin on the test results, I repeated the test case on Oracle Database 10.2.0.5 Standard Edition (also Enterprise Edition) with an 8,000M SGA_TARGET and 1,800M PGA_AGGREGATE_TARGET.  The results?

SELECT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.34 seconds when selecting 10,478 rows (same execution plan for OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |  1368 |           |
| 1   |  SORT GROUP BY               |            |   489 |   46K |  1368 |  00:00:11 |
| 2   |   FILTER                     |            |       |       |       |           |
| 3   |    HASH JOIN RIGHT OUTER     |            |   20K | 1906K |  1367 |  00:00:11 |
| 4   |     VIEW                     |            |   700 |   27K |   375 |  00:00:03 |
| 5   |      SORT GROUP BY           |            |   700 |   40K |   375 |  00:00:03 |
| 6   |       HASH JOIN              |            |  146K | 8454K |   368 |  00:00:03 |
| 7   |        TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 8   |        TABLE ACCESS FULL     | T2_LINES   |  146K | 4664K |   364 |  00:00:03 |
| 9   |     NESTED LOOPS             |            |   20K | 1140K |   992 |  00:00:08 |
| 10  |      VIEW                    |            |   671 |   14K |    65 |  00:00:01 |
| 11  |       SORT GROUP BY          |            |   671 |   17K |    65 |  00:00:01 |
| 12  |        FILTER                |            |       |       |       |           |
| 13  |         INDEX FAST FULL SCAN | T1_PK      |  107K | 2773K |    60 |  00:00:01 |
| 14  |      INDEX RANGE SCAN        | T1_LINES_PK|    30 |  1110 |     3 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
       1434  recursive calls                                                   
          0  db block gets                                                     
       7818  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     104015  bytes sent via SQL*Net to client                                  
        549  bytes received via SQL*Net from client                            
          9  SQL*Net roundtrips to/from client                                 
         36  sorts (memory)                                                    
          0  sorts (disk)                                                      
       7604  rows processed

INSERT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in… 0.34 seconds when inserting 10,478 rows (same execution plan for OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT             |            |       |       |  1368 |           |
| 1   |  SORT GROUP BY               |            |   489 |   46K |  1368 |  00:00:11 |
| 2   |   FILTER                     |            |       |       |       |           |
| 3   |    HASH JOIN RIGHT OUTER     |            |   20K | 1906K |  1367 |  00:00:11 |
| 4   |     VIEW                     |            |   700 |   27K |   375 |  00:00:03 |
| 5   |      SORT GROUP BY           |            |   700 |   40K |   375 |  00:00:03 |
| 6   |       HASH JOIN              |            |  146K | 8454K |   368 |  00:00:03 |
| 7   |        TABLE ACCESS FULL     | T2         |   700 |   18K |     3 |  00:00:01 |
| 8   |        TABLE ACCESS FULL     | T2_LINES   |  146K | 4664K |   364 |  00:00:03 |
| 9   |     NESTED LOOPS             |            |   20K | 1140K |   992 |  00:00:08 |
| 10  |      VIEW                    |            |   671 |   14K |    65 |  00:00:01 |
| 11  |       SORT GROUP BY          |            |   671 |   17K |    65 |  00:00:01 |
| 12  |        FILTER                |            |       |       |       |           |
| 13  |         INDEX FAST FULL SCAN | T1_PK      |  107K | 2773K |    60 |  00:00:01 |
| 14  |      INDEX RANGE SCAN        | T1_LINES_PK|    30 |  1110 |     3 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)

Statistics
----------------------------------------------------------                     
       1350  recursive calls                                                   
        468  db block gets                                                     
       7981  consistent gets                                                   
          0  physical reads                                                    
     293716  redo size                                                         
        817  bytes sent via SQL*Net to client                                  
       1709  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
         21  sorts (memory)                                                    
          0  sorts (disk)                                                      
       7604  rows processed

Notice in the above that the execution plan is the same for both the SELECT and INSERT INTO SQL statements, unlike what happened in Oracle Database 11.2.0.1 with the same OPTIMIZER_FEATURES_ENABLE setting.

————

SELECT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=100, OPTIMIZER_INDEX_COST_ADJ=80), completed in 0.34 seconds when selecting 10,478 rows (same execution plan for OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |            |       |       |   442 |           |
| 1   |  SORT GROUP BY               |            |   489 |   46K |   442 |  00:00:04 |
| 2   |   NESTED LOOPS               |            |  1006 |   95K |   441 |  00:00:04 |
| 3   |    FILTER                    |            |       |       |       |           |
| 4   |     HASH JOIN OUTER          |            |   671 |   39K |   440 |  00:00:04 |
| 5   |      VIEW                    |            |   671 |   14K |    65 |  00:00:01 |
| 6   |       SORT GROUP BY          |            |   671 |   17K |    65 |  00:00:01 |
| 7   |        FILTER                |            |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | T1_PK      |  107K | 2773K |    60 |  00:00:01 |
| 9   |      VIEW                    |            |   700 |   27K |   375 |  00:00:03 |
| 10  |       SORT GROUP BY          |            |   700 |   40K |   375 |  00:00:03 |
| 11  |        HASH JOIN             |            |  146K | 8454K |   368 |  00:00:03 |
| 12  |         TABLE ACCESS FULL    | T2         |   700 |   18K |     3 |  00:00:01 |
| 13  |         TABLE ACCESS FULL    | T2_LINES   |  146K | 4664K |   364 |  00:00:03 |
| 14  |    INDEX RANGE SCAN          | T1_LINES_PK|     1 |    37 |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200)
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
11 - access("CO"."ID"="COL"."CUST_ORDER_ID")
12 - filter("CUSTOMER_PO_REF" IS NOT NULL)
13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0))

Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                     
       7508  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
     104015  bytes sent via SQL*Net to client                                  
        549  bytes received via SQL*Net from client                            
          9  SQL*Net roundtrips to/from client                                 
          3  sorts (memory)                                                    
          0  sorts (disk)                                                      
       7604  rows processed

INSERT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in… 0.35 seconds when inserting 10,478 rows (same execution plan for OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′):

--------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT             |            |       |       |   442 |           |
| 1   |  SORT GROUP BY               |            |   489 |   46K |   442 |  00:00:04 |
| 2   |   NESTED LOOPS               |            |  1006 |   95K |   441 |  00:00:04 |
| 3   |    FILTER                    |            |       |       |       |           |
| 4   |     HASH JOIN OUTER          |            |   671 |   39K |   440 |  00:00:04 |
| 5   |      VIEW                    |            |   671 |   14K |    65 |  00:00:01 |
| 6   |       SORT GROUP BY          |            |   671 |   17K |    65 |  00:00:01 |
| 7   |        FILTER                |            |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | T1_PK      |  107K | 2773K |    60 |  00:00:01 |
| 9   |      VIEW                    |            |   700 |   27K |   375 |  00:00:03 |
| 10  |       SORT GROUP BY          |            |   700 |   40K |   375 |  00:00:03 |
| 11  |        HASH JOIN             |            |  146K | 8454K |   368 |  00:00:03 |
| 12  |         TABLE ACCESS FULL    | T2         |   700 |   18K |     3 |  00:00:01 |
| 13  |         TABLE ACCESS FULL    | T2_LINES   |  146K | 4664K |   364 |  00:00:03 |
| 14  |    INDEX RANGE SCAN          | T1_LINES_PK|     1 |    37 |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter(COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200)
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
11 - access("CO"."ID"="COL"."CUST_ORDER_ID")
12 - filter("CUSTOMER_PO_REF" IS NOT NULL)
13 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0))

Statistics
----------------------------------------------------------                     
        183  recursive calls                                                   
        495  db block gets                                                     
       7702  consistent gets                                                   
          2  physical reads                                                    
     294848  redo size                                                         
        830  bytes sent via SQL*Net to client                                  
       1712  bytes received via SQL*Net from client                            
          3  SQL*Net roundtrips to/from client                                 
          4  sorts (memory)                                                    
          0  sorts (disk)                                                      
       7604  rows processed

Again, notice that the execution plan remained the same for the SELECT and INSERT INTO SQL statements.  Is this a bug in 11.2.0.1 that does not exist in 10.2.0.5, or was a bug introduced in 10.2.0.5?  Let’s take a look at a portion of the 10053 trace file from Oracle Database 11.2.0.1, comparing the output for the two settings of OPTIMIZER_FEATURES_ENABLE where a change in the execution plan was identified in 11.2.0.1.  With the setting at 10.2.0.2 we see that the complex view merge was prevented due to a CBQT (cost-based query transformation) directive, while that did not happen with the setting at 10.1.0.4:


11.2.0.1 

OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′

OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′

 

JE:   Considering Join Elimination on query block SEL$4 (#0)

JE:   Considering Join Elimination on query block SEL$4 (#0)

 

*************************

*************************

 

Join Elimination (JE)   

Join Elimination (JE)   

 

*************************

*************************

 

SQL:******* UNPARSED QUERY IS *******

SQL:******* UNPARSED QUERY IS *******

 

SELECT “CO”.”CUSTOMER_PO_REF” “CUSTOMER_PO_REF”,MAX(“COL”.”DESIRED_SHIP_DATE”) “DESIRED_SHIP_DATE”,MAX(“COL”.”PART_ID”) “PART_ID” FROM “TESTUSER”.”T2″ “CO”,”TESTUSER”.”T2_LINES” “COL” WHERE “CO”.”ID”=”COL”.”CUST_ORDER_ID” AND “CO”.”CUSTOMER_PO_REF” IS NOT NULL AND “COL”.”DESIRED_SHIP_DATE”>=SYSDATE@!-365 GROUP BY “CO”.”CUSTOMER_PO_REF”

SELECT “CO”.”CUSTOMER_PO_REF” “CUSTOMER_PO_REF”,MAX(“COL”.”DESIRED_SHIP_DATE”) “DESIRED_SHIP_DATE”,MAX(“COL”.”PART_ID”) “PART_ID” FROM “TESTUSER”.”T2″ “CO”,”TESTUSER”.”T2_LINES” “COL” WHERE “CO”.”ID”=”COL”.”CUST_ORDER_ID” AND “CO”.”CUSTOMER_PO_REF” IS NOT NULL AND “COL”.”DESIRED_SHIP_DATE”>=SYSDATE@!-365 GROUP BY “CO”.”CUSTOMER_PO_REF”

 

SQL:******* UNPARSED QUERY IS *******

SQL:******* UNPARSED QUERY IS *******

 

SELECT “CO”.”CUSTOMER_PO_REF” “CUSTOMER_PO_REF”,MAX(“COL”.”DESIRED_SHIP_DATE”) “DESIRED_SHIP_DATE”,MAX(“COL”.”PART_ID”) “PART_ID” FROM “TESTUSER”.”T2″ “CO”,”TESTUSER”.”T2_LINES” “COL” WHERE “CO”.”ID”=”COL”.”CUST_ORDER_ID” AND “CO”.”CUSTOMER_PO_REF” IS NOT NULL AND “COL”.”DESIRED_SHIP_DATE”>=SYSDATE@!-365 GROUP BY “CO”.”CUSTOMER_PO_REF”

SELECT “CO”.”CUSTOMER_PO_REF” “CUSTOMER_PO_REF”,MAX(“COL”.”DESIRED_SHIP_DATE”) “DESIRED_SHIP_DATE”,MAX(“COL”.”PART_ID”) “PART_ID” FROM “TESTUSER”.”T2″ “CO”,”TESTUSER”.”T2_LINES” “COL” WHERE “CO”.”ID”=”COL”.”CUST_ORDER_ID” AND “CO”.”CUSTOMER_PO_REF” IS NOT NULL AND “COL”.”DESIRED_SHIP_DATE”>=SYSDATE@!-365 GROUP BY “CO”.”CUSTOMER_PO_REF”

 

Query block SEL$4 (#0) unchanged

Query block SEL$4 (#0) unchanged

Different

CVM:   Checking validity of merging in query block SEL$2 (#0)

CVM:     CVM bypassed: view on right side of Outer Join + MuLTiple TABle.

Different

CVM: Considering view merge in query block SEL$2 (#0)

 

Different

CVM:   Checking validity of merging in query block SEL$3 (#0)

 

Different

CVM: Considering view merge in query block SEL$3 (#0)

 

 

CNT:   Considering count(col) to count(*) on query block SEL$3 (#0)

CNT:   Considering count(col) to count(*) on query block SEL$3 (#0)

 

*************************

*************************

 

Count(col) to Count(*) (CNT)

Count(col) to Count(*) (CNT)

 

*************************

*************************

 

CNT:     COUNT() to COUNT(*) not done.

CNT:     COUNT() to COUNT(*) not done.

Different

 

CVM: CBQT Marking query block SEL$3 (#0) as valid for CVM.

Different

CVM:   Merging complex view SEL$3 (#0) into SEL$2 (#0).

CVM:   Not Merging SEL$3 (#0) into SEL$2 (#0) due to CBQT directive.

Different

qbcp:******* UNPARSED QUERY IS *******

 

Different

SELECT /*+ LEADING (“E”) */ “E”.”CUSTPO” “CUSTPO”,”EL”.”FCSTQTY” “FCSTQTY”,”EL”.”FCDUEDATE” “FCDUEDATE” FROM  (SELECT “E”.”CUSTPO” “CUSTPO”,MAX(“E”.”RELID”) “RELID” FROM “TESTUSER”.”T1″ “E” GROUP BY “E”.”CUSTPO”) “E”,”TESTUSER”.”T1_LINES” “EL” WHERE “E”.”CUSTPO”=”EL”.”CUSTPO” AND “E”.”RELID”=”EL”.”RELID” AND “EL”.”FCDUEDATE”>=SYSDATE@!-365 AND “EL”.”FCDUEDATE”<=SYSDATE@!+1200 AND “EL”.”FCSTQTY”>0

 

Different

vqbcp:******* UNPARSED QUERY IS *******

 

Different

SELECT “E”.”CUSTPO” “CUSTPO”,MAX(“E”.”RELID”) “RELID” FROM “TESTUSER”.”T1″ “E” GROUP BY “E”.”CUSTPO”

 

Different

CVM: result SEL$2 (#0)

 

Different

******* UNPARSED QUERY IS *******

 

Different

SELECT /*+ LEADING (“E”) */ “E”.”CUSTPO” “CUSTPO”,”EL”.”FCSTQTY” “FCSTQTY”,”EL”.”FCDUEDATE” “FCDUEDATE” FROM “TESTUSER”.”T1″ “E”,”TESTUSER”.”T1_LINES” “EL” WHERE “E”.”CUSTPO”=”EL”.”CUSTPO” AND “EL”.”FCDUEDATE”>=SYSDATE@!-365 AND “EL”.”FCDUEDATE”<=SYSDATE@!+1200 AND “EL”.”FCSTQTY”>0 GROUP BY “E”.”CUSTPO”,”EL”.ROWID,”EL”.”RELID”,”EL”.”FCDUEDATE”,”EL”.”FCSTQTY” HAVING MAX(“E”.”RELID”)=”EL”.”RELID”

 

 

JE:   Considering Join Elimination on query block SEL$2 (#0)

JE:   Considering Join Elimination on query block SEL$2 (#0)

 

*************************

*************************

 

Join Elimination (JE)   

Join Elimination (JE)   

 

*************************

*************************

 

SQL:******* UNPARSED QUERY IS *******

SQL:******* UNPARSED QUERY IS *******

Different

SELECT /*+ LEADING (“E”) */ “E”.”CUSTPO” “CUSTPO”,”EL”.”FCSTQTY” “FCSTQTY”,”EL”.”FCDUEDATE” “FCDUEDATE” FROM “TESTUSER”.”T1″ “E”,”TESTUSER”.”T1_LINES” “EL” WHERE “E”.”CUSTPO”=”EL”.”CUSTPO” AND “EL”.”FCDUEDATE”>=SYSDATE@!-365 AND “EL”.”FCDUEDATE”<=SYSDATE@!+1200 AND “EL”.”FCSTQTY”>0 GROUP BY “E”.”CUSTPO”,”EL”.ROWID,”EL”.”RELID”,”EL”.”FCDUEDATE”,”EL”.”FCSTQTY” HAVING MAX(“E”.”RELID”)=”EL”.”RELID”

SELECT /*+ LEADING (“E”) */ “E”.”CUSTPO” “CUSTPO”,”EL”.”FCSTQTY” “FCSTQTY”,”EL”.”FCDUEDATE” “FCDUEDATE” FROM  (SELECT “E”.”CUSTPO” “CUSTPO”,MAX(“E”.”RELID”) “RELID” FROM “TESTUSER”.”T1″ “E” GROUP BY “E”.”CUSTPO”) “E”,”TESTUSER”.”T1_LINES” “EL” WHERE “E”.”CUSTPO”=”EL”.”CUSTPO” AND “E”.”RELID”=”EL”.”RELID” AND “EL”.”FCDUEDATE”>=SYSDATE@!-365 AND “EL”.”FCDUEDATE”<=SYSDATE@!+1200 AND “EL”.”FCSTQTY”>0

 ——————-

Let’s take a look at a portion of the 10053 trace file on Oracle Database 10.2.0.5, comparing the output for the two settings of OPTIMIZER_FEATURES_ENABLE where a change in the execution plan was identified.  With the setting at 10.2.0.2 we see that the complex view merge was prevented due to a CBQT (cost-based query transformation) directive, and that also happened with the setting at 10.1.0.4:

10.2.0.5

OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′

OPTIMIZER_FEATURES_ENABLE=’10.2.0.2′

 

**************************

**************************

 

Predicate Move-Around (PM)

Predicate Move-Around (PM)

 

**************************

**************************

 

PM: Considering predicate move-around in INS$1 (#0).

PM: Considering predicate move-around in INS$1 (#0).

 

PM:   Checking validity of predicate move-around in INS$1 (#0).

PM:   Checking validity of predicate move-around in INS$1 (#0).

 

CBQT: Validity checks passed for 2bxr3x54dk7nq.

CBQT: Validity checks passed for 2bxr3x54dk7nq.

 

voptcojrj: logp:0000000012312FF8

voptcojrj: logp:0000000012312FF8

 

voptcojrj:”E”.”CUSTPO”=”CO”.”CUSTOMER_PO_REF”(+)

voptcojrj:”E”.”CUSTPO”=”CO”.”CUSTOMER_PO_REF”(+)

 

rejected

rejected

 

voptcojrj:”E”.”FCDUEDATE”>COALESCE(“CO”.”DESIRED_SHIP_DATE”,SYSDATE@!-365)

voptcojrj:”E”.”FCDUEDATE”>COALESCE(“CO”.”DESIRED_SHIP_DATE”,SYSDATE@!-365)

 

rejected

rejected

Different

 

Query block (0000000012314D80) before join elimination:

Different

 

SQL:******* UNPARSED QUERY IS *******

Different

 

SELECT “CO”.”CUSTOMER_PO_REF” “CUSTOMER_PO_REF”,MAX(“COL”.”DESIRED_SHIP_DATE”) “DESIRED_SHIP_DATE”,MAX(“COL”.”PART_ID”) “PART_ID” FROM “TESTUSER”.”T2″ “CO”,”TESTUSER”.”T2_LINES” “COL” WHERE “CO”.”ID”=”COL”.”CUST_ORDER_ID” AND “CO”.”CUSTOMER_PO_REF” IS NOT NULL AND “COL”.”DESIRED_SHIP_DATE”>=SYSDATE@!-365 GROUP BY “CO”.”CUSTOMER_PO_REF”

Different

 

Query block (0000000012314D80) unchanged

 

CVM: CBQT Marking query block SEL$3 (#0)as valid for CVM.

CVM: CBQT Marking query block SEL$3 (#0)as valid for CVM.

 

CVM:  Not merging SEL$3 (#0) into SEL$2 (#0) due to CBQT directive.

CVM:  Not merging SEL$3 (#0) into SEL$2 (#0) due to CBQT directive.

Different

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Query block (000000001231DE28) before join elimination:

Different

 

SQL:******* UNPARSED QUERY IS *******

Different

 

SELECT /*+ LEADING (“E”) */ “E”.”CUSTPO” “CUSTPO”,”EL”.”FCSTQTY” “FCSTQTY”,”EL”.”FCDUEDATE” “FCDUEDATE” FROM  (SELECT “E”.”CUSTPO” “CUSTPO”,MAX(“E”.”RELID”) “RELID” FROM “TESTUSER”.”T1″ “E” GROUP BY “E”.”CUSTPO”) “E”,”TESTUSER”.”T1_LINES” “EL” WHERE “E”.”CUSTPO”=”EL”.”CUSTPO” AND “E”.”RELID”=”EL”.”RELID” AND “EL”.”FCDUEDATE”>=SYSDATE@!-365 AND “EL”.”FCDUEDATE”<=SYSDATE@!+1200 AND “EL”.”FCSTQTY”>0

Different

 

Query block (000000001231DE28) unchanged

Different

 

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

 

voptcojrj: logp:0000000012312FF8

voptcojrj: logp:0000000012312FF8

 

voptcojrj:”E”.”CUSTPO”=”CO”.”CUSTOMER_PO_REF”(+)

voptcojrj:”E”.”CUSTPO”=”CO”.”CUSTOMER_PO_REF”(+)

 

rejected

rejected

 

voptcojrj:”EL”.”FCDUEDATE”>COALESCE(“CO”.”DESIRED_SHIP_DATE”,SYSDATE@!-365)

voptcojrj:”EL”.”FCDUEDATE”>COALESCE(“CO”.”DESIRED_SHIP_DATE”,SYSDATE@!-365)

 

rejected

rejected

 

voptcojrj:”E”.”CUSTPO”=”EL”.”CUSTPO”

voptcojrj:”E”.”CUSTPO”=”EL”.”CUSTPO”

 

rejected

rejected

 

voptcojrj:”E”.”RELID”=”EL”.”RELID”

voptcojrj:”E”.”RELID”=”EL”.”RELID”

 

rejected

rejected

 

voptcojrj:”EL”.”FCDUEDATE”>=SYSDATE@!-365

voptcojrj:”EL”.”FCDUEDATE”>=SYSDATE@!-365

 

rejected

rejected

 

voptcojrj:”EL”.”FCDUEDATE”<=SYSDATE@!+1200

voptcojrj:”EL”.”FCDUEDATE”<=SYSDATE@!+1200

 

rejected

rejected

 

voptcojrj:”EL”.”FCSTQTY”>0

voptcojrj:”EL”.”FCSTQTY”>0

 

rejected

rejected

Different

 

Query block (000000001231ED68) before join elimination:

Different

 

SQL:******* UNPARSED QUERY IS *******

Different

 

SELECT /*+ LEADING (“E”) LEADING (“E”) */ COALESCE(“CO”.”PART_ID”,’NOT-IN-SYSTEM’) “PART_ID”,”EL”.”FCDUEDATE” “REQUIRED_DATE”,SUM(“EL”.”FCSTQTY”) “QTY”,1 “PLAN_LEVEL” FROM  (SELECT “E”.”CUSTPO” “CUSTPO”,MAX(“E”.”RELID”) “RELID” FROM “TESTUSER”.”T1″ “E” GROUP BY “E”.”CUSTPO”) “E”,”TESTUSER”.”T1_LINES” “EL”, (SELECT “CO”.”CUSTOMER_PO_REF” “CUSTOMER_PO_REF”,MAX(“COL”.”DESIRED_SHIP_DATE”) “DESIRED_SHIP_DATE”,MAX(“COL”.”PART_ID”) “PART_ID” FROM “TESTUSER”.”T2″ “CO”,”TESTUSER”.”T2_LINES” “COL” WHERE “CO”.”ID”=”COL”.”CUST_ORDER_ID” AND “CO”.”CUSTOMER_PO_REF” IS NOT NULL AND “COL”.”DESIRED_SHIP_DATE”>=SYSDATE@!-365 GROUP BY “CO”.”CUSTOMER_PO_REF”) “CO” WHERE “E”.”CUSTPO”=”CO”.”CUSTOMER_PO_REF”(+) AND “EL”.”FCDUEDATE”>COALESCE(“CO”.”DESIRED_SHIP_DATE”,SYSDATE@!-365) AND “E”.”CUSTPO”=”EL”.”CUSTPO” AND “E”.”RELID”=”EL”.”RELID” AND “EL”.”FCDUEDATE”>=SYSDATE@!-365 AND “EL”.”FCDUEDATE”<=SYSDATE@!+1200 AND “EL”.”FCSTQTY”>0 GROUP BY COALESCE(“CO”.”PART_ID”,’NOT-IN-SYSTEM’),”EL”.”FCDUEDATE” ORDER BY COALESCE(“CO”.”PART_ID”,’NOT-IN-SYSTEM’),”EL”.”FCDUEDATE”

Different

 

Query block (000000001231ED68) unchanged

 

Registered qb: SEL$F5BB74E1 0x1231ed68 (VIEW MERGE SEL$1; SEL$2)

Registered qb: SEL$F5BB74E1 0x1231ed68 (VIEW MERGE SEL$1; SEL$2)

 

  signature (): qb_name=SEL$F5BB74E1 nbfros=3 flg=0

  signature (): qb_name=SEL$F5BB74E1 nbfros=3 flg=0

 

    fro(0): flg=1 objn=0 hint_alias=”CO”@”SEL$1″

    fro(0): flg=1 objn=0 hint_alias=”CO”@”SEL$1″

 

    fro(1): flg=1 objn=0 hint_alias=”E”@”SEL$2″

    fro(1): flg=1 objn=0 hint_alias=”E”@”SEL$2″

 

    fro(2): flg=0 objn=47930 hint_alias=”EL”@”SEL$2″

    fro(2): flg=0 objn=47930 hint_alias=”EL”@”SEL$2″

 

Now what?  Is it considered a bug if Oracle Database 11.2.0.1 behaves similar to 10.2.0.2, but not similar to 10.2.0.5, when the OPTIMIZER_FEATURES_ENABLE parameter is set to 10.1.0.4?


Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: