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?
Recent Comments