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

15 12 2010

December 15, 2010 

(Forward to the Next Post in the Series)

I am impressed with the responses that were received from the most recent set of blog articles.  I recently encountered an interesting problem with an Oracle database that had the OPTIMIZER_FEATURES_ENABLE parameter set to 10.1.0.4 and the OPTIMIZER_MODE set to ALL_ROWS.  A somewhat complex SQL statement was written like the following, using LEADING hints to help control the join order (to work around a potential problem related to join order when inline views are used):

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
      EDI830 E
    GROUP BY
      E.CUSTPO) E,
    EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE; 

The above query executes very quickly (a second or less), and the execution plan for the above query looks like this:

Execution Plan
----------------------------------------------------------
Plan hash value: 3426513411

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   205 | 17425 |   157  (21)| 00:00:01 |
|   1 |  SORT GROUP BY              |                 |   205 | 17425 |   157  (21)| 00:00:01 |
|*  2 |   FILTER                    |                 |       |       |            |          |
|*  3 |    HASH JOIN OUTER          |                 |   384 | 32640 |   156  (20)| 00:00:01 |
|   4 |     NESTED LOOPS            |                 |   194 |  9506 |    47  (35)| 00:00:01 |
|   5 |      VIEW                   |                 |  1993 | 35874 |    46  (35)| 00:00:01 |
|   6 |       SORT GROUP BY         |                 |  1993 | 39860 |    46  (35)| 00:00:01 |
|*  7 |        FILTER               |                 |       |       |            |          |
|   8 |         INDEX FAST FULL SCAN| EDI830_PK       |   107K|  2092K|    33  (10)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN       | EDI830_LINES_PK |     1 |    31 |     1   (0)| 00:00:01 |
|  10 |     VIEW                    |                 |  6254 |   219K|   108  (13)| 00:00:01 |
|  11 |      SORT GROUP BY          |                 |  6254 |   250K|   108  (13)| 00:00:01 |
|* 12 |       HASH JOIN             |                 |  8213 |   328K|   106  (12)| 00:00:01 |
|* 13 |        TABLE ACCESS FULL    | CUSTOMER_ORDER  |  7554 |   125K|    14   (8)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL    | CUST_ORDER_LINE |  8582 |   201K|    92  (12)| 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"(+))
   7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
   9 - 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)
  12 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  13 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  14 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365) 

Now, assume that we have a very simple table that currently contains no rows (the table was TRUNCATEd) and no indexes, and we would like to insert the rows returned by the above SQL statement into the table:

INSERT INTO
  T1
SELECT /*+ LEADING(E) */
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,
...
ORDER BY
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE; 

The INSERT INTO SQL statement required 20 to 30 minutes to complete.  Using SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’TYPICAL’)); the following execution plan is displayed:

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |                 |       |       |       |   279K(100)|          |
|   1 |  SORT GROUP BY               |                 |   205 | 13735 |       |   279K  (9)| 00:22:11 |
|*  2 |   FILTER                     |                 |       |       |       |            |          |
|   3 |    MERGE JOIN OUTER          |                 | 81363 |  5323K|       |   279K  (9)| 00:22:11 |
|   4 |     VIEW                     |                 | 70658 |  2139K|       |   279K  (9)| 00:22:10 |
|*  5 |      FILTER                  |                 |       |       |       |            |          |
|   6 |       SORT GROUP BY          |                 | 70658 |  3519K|  7169M|   279K  (9)| 00:22:10 |
|*  7 |        FILTER                |                 |       |       |       |            |          |
|*  8 |         HASH JOIN            |                 |   109M|  5343M|  3352K|   1154 (30)| 00:00:06 |
|   9 |          INDEX FAST FULL SCAN| EDI830_PK       |   107K|  2092K|       |     33 (10)| 00:00:01 |
|* 10 |          TABLE ACCESS FULL   | EDI830_LINES    |   242K|  7334K|       |    941 (35)| 00:00:05 |
|* 11 |     SORT JOIN                |                 |  6254 |   219K|       |    110 (15)| 00:00:01 |
|  12 |      VIEW                    |                 |  6254 |   219K|       |    108 (13)| 00:00:01 |
|  13 |       SORT GROUP BY          |                 |  6254 |   250K|       |    108 (13)| 00:00:01 |
|* 14 |        HASH JOIN             |                 |  8213 |   328K|       |    106 (12)| 00:00:01 |
|* 15 |         TABLE ACCESS FULL    | CUSTOMER_ORDER  |  7554 |   125K|       |     14  (8)| 00:00:01 |
|* 16 |         TABLE ACCESS FULL    | CUST_ORDER_LINE |  8582 |   201K|       |     92 (12)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
   2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
   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"."FCSTQTY">0 AND
       "EL"."FCDUEDATE"<=SYSDATE@!+1200))
  11 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
       filter("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
  14 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  16 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365) 

What happened, how would you investigate the problem, and how would you correct the problem?  Think about that for a couple of minutes before scrolling down.

Assume that I was able to obtain an execution plan for the INSERT INTO SQL statement that looked like this:

-------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name           | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT              |                |       |       |   157 |           |
| 1   |  SORT GROUP BY                |                |   194 |   13K |   157 |  00:00:01 |
| 2   |   FILTER                      |                |       |       |       |           |
| 3   |    HASH JOIN OUTER            |                |   223 |   15K |   156 |  00:00:01 |
| 4   |     VIEW                      |                |   194 |  6014 |    47 |  00:00:01 |
| 5   |      NESTED LOOPS             |                |   194 |  9118 |    47 |  00:00:01 |
| 6   |       VIEW                    |                |  1993 |   31K |    46 |  00:00:01 |
| 7   |        SORT GROUP BY          |                |  1993 |   39K |    46 |  00:00:01 |
| 8   |         FILTER                |                |       |       |       |           |
| 9   |          INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 10  |       INDEX RANGE SCAN        | EDI830_LINES_PK|     1 |    31 |     1 |  00:00:01 |
| 11  |     VIEW                      |                |  6254 |  220K |   108 |  00:00:01 |
| 12  |      SORT GROUP BY            |                |  6254 |  250K |   108 |  00:00:01 |
| 13  |       HASH JOIN               |                |  8213 |  329K |   106 |  00:00:01 |
| 14  |        TABLE ACCESS FULL      | CUSTOMER_ORDER |  7554 |  125K |    14 |  00:00:01 |
| 15  |        TABLE ACCESS FULL      | CUST_ORDER_LINE|  8582 |  201K |    92 |  00:00:01 |
-------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
8 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
10 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
10 - filter("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) 

Using the above execution plan, the INSERT INTO SQL statement completed in about a second (or less).  What did I do to fix the problem?

———

Here is what the sections of the query are intended to accomplish:

On a daily basis the header rows for custom order forecasts are inserted into the EDI830 table, and each order may be revised multiple times (possibly once a day or once a week), each time bumping the RELID value up by 1 or 2 (RELID is a zero padded number stored in a VARCHAR2 column).  This part of the query retrieves the most recent release number for each of the customer POs so that we are able to return the line item detail for the most recent customer order forecast release:

    (SELECT
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      EDI830 E
    GROUP BY
      E.CUSTPO) E 

Join the above inline view to the line item detail, with restrictions so that we do not look at releases from more than a year ago.  The LEADING hint is used to make certain that the optimizer first returns the rows from the EDI830 table before accessing the EDI830_LINES table (there is a chance that the optimizer will access the EDI830_LINES table first before returning the rows from the inline view):

  (SELECT /*+ LEADING(E) */
    E.CUSTPO,
    EL.FCSTQTY,
    EL.FCDUEDATE
  FROM
    (SELECT
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      EDI830 E
    GROUP BY
      E.CUSTPO) E,
    EDI830_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 

The goal of the following subquery is to return the ship date of the last order line that made it into the order entry system for each of the customer POs.  This subquery will allow us to determine which of the rows in the most recent EDI release are beyond the last firmed customer order line in the system (typically, only the firmed EDI release lines are brought into the CUST_ORDER_LINE table):

  (SELECT
    CO.CUSTOMER_PO_REF,
    MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE,
    MAX(COL.PART_ID) PART_ID
  FROM
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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

The outer-most portion of the SQL statement joins the two inline views together, using a LEADING hint to make certain that the EDI release rows are retrieved before accessing the CUSTOMER_ORDER and CUST_ORDER_LINE tables (even with the outer join, it is possible that those tables might be accessed first):

SELECT /*+ LEADING(E) */
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID,
  E.FCDUEDATE REQUIRED_DATE,
  SUM(E.FCSTQTY) QTY,
  1 PLAN_LEVEL
FROM
...
  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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;  

What happened?  What process would you use to investigate the performance problem?  How would you resolve this performance problem.

If you tell me to buy a bigger server, I will simply tell you to read a different book.  :-)


Actions

Information

28 responses

15 12 2010
Flado

/*+ no_merge */ in all inline views? Just a first thought…

15 12 2010
Charles Hooper

That is actually a very good guess (note to myself: a NO_MERGE hint works, while a NO_UNNEST hint does not work – I need to re-read the hints documentation). This is what the execution plan looks like with your hint:

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |                 |   194 | 12998 |   157  (21)| 00:00:01 |
|   1 |  SORT GROUP BY               |                 |   194 | 12998 |   157  (21)| 00:00:01 |
|*  2 |   FILTER                     |                 |       |       |            |          |
|*  3 |    HASH JOIN OUTER           |                 |   227 | 15209 |   156  (20)| 00:00:01 |
|   4 |     VIEW                     |                 |   194 |  6014 |    47  (35)| 00:00:01 |
|   5 |      NESTED LOOPS            |                 |   194 |  9118 |    47  (35)| 00:00:01 |
|   6 |       VIEW                   |                 |  1993 | 31888 |    46  (35)| 00:00:01 |
|   7 |        SORT GROUP BY         |                 |  1993 | 39860 |    46  (35)| 00:00:01 |
|*  8 |         FILTER               |                 |       |       |            |          |
|   9 |          INDEX FAST FULL SCAN| EDI830_PK       |   107K|  2092K|    33  (10)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN       | EDI830_LINES_PK |     1 |    31 |     1   (0)| 00:00:01 |
|  11 |     VIEW                     |                 |  6255 |   219K|   108  (13)| 00:00:01 |
|  12 |      SORT GROUP BY           |                 |  6255 |   250K|   108  (13)| 00:00:01 |
|* 13 |       HASH JOIN              |                 |  8203 |   328K|   106  (12)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL     | CUSTOMER_ORDER  |  7556 |   125K|    14   (8)| 00:00:01 |
|* 15 |        TABLE ACCESS FULL     | CUST_ORDER_LINE |  8571 |   200K|    92  (12)| 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"(+))
   8 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
  10 - 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)
  13 - access("CO"."ID"="COL"."CUST_ORDER_ID")
  14 - filter("CUSTOMER_PO_REF" IS NOT NULL)
  15 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

The trivial difference is this line in the Predicate Information section:

    8 - filter(SYSDATE@!-365<=SYSDATE@!+1200)

Would the use of a hint be your first step in troubleshooting this problem, or is there a logical progression that you would try first before attempting to use a hint? (logical progression of other steps first is probably the right answer).

The bonus question: why is the filter predicate not present for ID 8 in my execution plan, but it is present in your hinted execution plan?

15 12 2010
Charles Hooper

Actually, that filter is applied in both cases – but where did it come from should have been the bonus question.

15 12 2010
Flado

But you do have the filter for ID 8 in your good plan! True, the star in front of ID 8 is missing, but so are all other stars…

Well, in this case, the thinking went like this:
a) he has described in detail how the optimal execution should proceed, in stages defined by in-line views
b) he is a (very smart) human, which cannot be said about the CBO, so he probably knows better
c) there are just two VIEW steps in the bad plan, ergo some of the views got merged by the CBO trying to be smarter than him
d) I can’t be bothered to track down which was the critical view, so just suggest sprinkling no_merge hints and go home.

Sorry, my goal was not to tune your statement, just to guess what you did :-)

By the way, I’m not sure your LEADING hint is safe, as you have two things aliased E. Have you checked the query block section of the dbms_xplan output?

Cheers!
Flado

15 12 2010
Flado

> where did it come from should have been the bonus question

Here:
EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200

SYSDATE is an unknown non-deterministic function.

15 12 2010
Charles Hooper

I probably would have stated that it is a result of transitive closure, but I believe that you are stating essentially the same thing.

Someone might be interested in knowing what I did to investigate the problem. First, I enabled a 10053 trace, made a small modification to the query (just so that it would be hard parsed, adding a comment will work), and then re-executed the query. I searched though the 10053 trace file to find the output that showed the final transformation of the SQL statement. The SELECT portion of the transformed INSERT INTO SQL statement looked like this in the trace file (white space and carriage returns added to improve readability):

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" "CUSTPO",
    "EL"."FCSTQTY" "FCSTQTY",
    "EL"."FCDUEDATE" "FCDUEDATE"
  FROM
    "TESTUSER"."EDI830" "E",
    "TESTUSER"."EDI830_LINES" "EL"
  WHERE
    SYSDATE@!-365<=SYSDATE@!+1200
    AND "E"."CUSTPO"="EL"."CUSTPO"
    AND "EL"."FCDUEDATE">=SYSDATE@!-365
    AND "EL"."FCSTQTY">0
    AND "EL"."FCDUEDATE"<=SYSDATE@!+1200
  GROUP BY
    "E"."CUSTPO",
    "EL".ROWID,
    "EL"."RELID",
    "EL"."FCDUEDATE",
    "EL"."FCSTQTY"
  HAVING
    "EL"."RELID"=MAX("E"."RELID")) "E",
  (SELECT
    "CO"."CUSTOMER_PO_REF" "CUSTOMER_PO_REF",
    MAX("COL"."DESIRED_SHIP_DATE") "DESIRED_SHIP_DATE",
    MAX("COL"."PART_ID") "PART_ID"
  FROM
    "TESTUSER2"."CUSTOMER_ORDER" "CO",
    "TESTUSER2"."CUST_ORDER_LINE" "COL"
  WHERE
    "CO"."CUSTOMER_PO_REF" IS NOT NULL
    AND "CO"."ID"="COL"."CUST_ORDER_ID"
    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
  COALESCE("CO"."PART_ID",'NOT-IN-SYSTEM'),
  "E"."FCDUEDATE"

When I saw that the optimizer transformed the inline view that queried the EDI830_LINES table, I knew that I had a problem on my hands. I believe that an INSERT INTO statement is always processed as if the OPTIMIZER_MODE is set to ALL_ROWS (assuming that the OPTIMIZER_MODE is not set to CHOOSE), so that might explain an execution plan for the SQL statement change if the default OPTIMIZER_MODE is FIRST_ROWS (or FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, or FIRST_ROWS_1000). I have not yet determined why the INSERT INTO version of the SQL statement was transformed, while the plain SELECT SQL statement was not.

15 12 2010
Flado

I actually don’t think it is transitive closure. Have you got the relevant section in the 10053 trace? I think it is more of a guard condition, essentially SYSDATE being treated as two bind variables whose value may change between executions, i.e., more like a “conditional plan” (like a plan that can result from “:x is null or colX=:x”)

15 12 2010
Charles Hooper
FPD:   Following transitive predicates are generated in SEL$335DD26A (#0) :
         SYSDATE@!-365<=SYSDATE@!+1200
15 12 2010
Flado

Cool! Thanks! It seems some re-thinking on my part is in order ;-) And some tests with constants, binds, and custom functions instead of sysdate… Let’s hope I’ll have time for it tomorrow.
Good night!

15 12 2010
Flado

The first_rows/all_rows switcheroo was my very first hunch as well (after reading the title of your post), but you did say it was all_rows in both cases, so I ruled it out.
10053 would have been my last troubleshooting step, I must admit. Sadly, I have no access to the production OS usually (I can get a trace e-mailed to me, but I’d need several e-mails and probably a manager sign-off, so I try to do without)

15 12 2010
Gary

My approach would have been different. We have a good plan for the SELECT query, but one which isn’t chosen for the INSERT.

I’d use DBMS_XPLAN.DISPLAY_CURSOR with OUTLINE to get the full set of hints that would form an outline for the fast SELECT, then stick them in the INSERT

http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/

15 12 2010
Charles Hooper

Gary,

Thank you for sharing the idea of using the OUTLINE information. When I was troubleshooting the SQL statement, I recalled a couple of Jonathan’s articles that mentioned “full hinting”. I thought “OK, sure I will fully hint that INSERT statement so that it performs like the SELECT statement.” – that is part of the reason why I generated a 10053 trace. I looked at the OUTLINE information in the trace file and I started to wonder if I had made a mistake. The OUTLINE for the SELECT (from the 10053 trace file):

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.1.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 80)
      OPT_PARAM('optimizer_index_caching' 100)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "E"@"SEL$2")
      INDEX(@"SEL$F5BB74E1" "EL"@"SEL$2" ("EDI830_LINES"."CUSTPO" "EDI830_LINES"."RELID" "EDI830_LINES"."FCDUEDATE" EDI830_LINES"."FCSTQTY"))
      NO_ACCESS(@"SEL$F5BB74E1" "CO"@"SEL$1")
      LEADING(@"SEL$F5BB74E1" "E"@"SEL$2" "EL"@"SEL$2" "CO"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "EL"@"SEL$2")
      USE_HASH(@"SEL$F5BB74E1" "CO"@"SEL$1")
      INDEX_FFS(@"SEL$3" "E"@"SEL$3" ("EDI830"."CUSTPO" "EDI830"."RELID"))
      FULL(@"SEL$4" "CO"@"SEL$4")
      FULL(@"SEL$4" "COL"@"SEL$4")
      LEADING(@"SEL$4" "CO"@"SEL$4" "COL"@"SEL$4")
      USE_HASH(@"SEL$4" "COL"@"SEL$4")
    END_OUTLINE_DATA
  */

The outline for the slow INSERT:

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.1.0.4')
      OPT_PARAM('optimizer_index_cost_adj' 80)
      OPT_PARAM('optimizer_index_caching' 100)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$335DD26A")
      MERGE(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      FULL(@"INS$1" "MRP_FORECAST_PART_DATE_QTY"@"INS$1")
      NO_ACCESS(@"SEL$1" "E"@"SEL$1")
      NO_ACCESS(@"SEL$1" "CO"@"SEL$1")
      LEADING(@"SEL$1" "E"@"SEL$1" "CO"@"SEL$1")
      USE_MERGE(@"SEL$1" "CO"@"SEL$1")
      INDEX_FFS(@"SEL$335DD26A" "E"@"SEL$3" ("EDI830"."CUSTPO" "EDI830"."RELID"))
      FULL(@"SEL$335DD26A" "EL"@"SEL$2")
      LEADING(@"SEL$335DD26A" "E"@"SEL$3" "EL"@"SEL$2")
      USE_HASH(@"SEL$335DD26A" "EL"@"SEL$2")
      FULL(@"SEL$4" "CO"@"SEL$4")
      FULL(@"SEL$4" "COL"@"SEL$4")
      LEADING(@"SEL$4" "CO"@"SEL$4" "COL"@"SEL$4")
      USE_HASH(@"SEL$4" "COL"@"SEL$4")
    END_OUTLINE_DATA
  */

I was a little concerned that the query blocks might be named differently, so I did not even attempt to use the hints from the OUTLINE. However, now that you mention it, I probably could have named the different query blocks with identical names for both the SELECT and the INSERT queries (I did not think of doing that when troubleshooting). See this page for more information:

http://jonathanlewis.wordpress.com/2007/06/25/qb_name/

—-

It is interesting to see the different approaches that people take to troubleshoot problems like this. I will try to build a test case that simulates the problem so that the execution plan change may be investigated in different environments (that will be in part 2 of this blog article series).

15 12 2010
joel garry

Just in case anyone hasn’t noticed, Kerry Osborne’s blog has had some interesting observations about profiles and hints recently, with some interesting scripts.

15 12 2010
Charles Hooper

Thanks Joel. I think that this is the article:

http://kerryosborne.oracle-guy.com/2010/12/interaction-between-baselines-and-profiles/

I intended to try out the test script on his site but have not had a chance yet.

16 12 2010
Narendra

Charles,

Interesting problem.
What happened, how would you investigate the problem, and how would you correct the problem?
I am not sure at this stage how to investigate this. Could it be non-default values of OPTIMIZER_INDEX_* values that is affecting the plan cost calculations? What is the estimated cost of the slow plan ? At first, I would probably see the impact of resetting the OPTIMIZER_INDEX_* parameter values to their default. Or is it a case that changing values of those parameters is not an option?
To me, it looks like the ability (or lack of) of CBO to calculate the cost of access paths, especially considering that it is a 10.1.0.4 version.
What did I do to fix the problem?
If not hints, did you use ROWNUM in one of the subqueries to prevent it from merging?

16 12 2010
Narendra

Just had a look at those plans again and noticed that while the plan for the slow query shows large TEMP usage, the good plan does not show any TEMP usage. Is the WORKAREA_SIZE_POLICY set to AUTO or MANUAL? By any chance, did you fiddle with those values to solve the issue?

16 12 2010
Charles Hooper

Good questions. WORKAREA_SIZE_POLICY is set to AUTO, and PGA_AGGREGATE_TARGET is set to 2000M. Once I build a test case that simulates the problem, I can try different parameter settings to see if I am able to identify the cause of the execution plan change.

16 12 2010
Charles Hooper

Narendra,

Very good observations based on what I posted – Gary’s suggestion to look at the OUTLINE information provided a couple of potential clues. When I build the test case I will try a couple of variations to see why an entirely different execution plan was used for the SELECT and the INSERT – it very well could be caused by the items that you mentioned.

There is a certain risk in changing parameters in a production database. Despite the value of OPTIMIZER_FEATURES_ENABLE, this is a 10.2.0.x database. Shortly after migrating the data to a 10.2.0.2 database in April 2006 I started receiving compaints that some of the ERP application features were displaying errors, causing a couple accounting reports to fail to execute. Fortunately, I had recently read Jonathan’s “Cost-Based Oracle Fundamentals” book – so I had a clue where to start the investigation. After finding the problematic SQL statement that was triggering the error, I enabled a 10053 trace and simultaneously enabled a 10046 trace. This is a copy of a portion of the trace file from a couple of months later, when I finally filed a report on Metalink about the problem, after finding that the problem was still present in 10.2.0.2 patch 9:

Number of join permutations tried: 3
*********************************
    SORT resource      Sort statistics
      Sort width:        1198 Area size:     1048576 Max Area size:   209715200
      Degree:               1
      Blocks to Sort:       0 Row size:          101 Total Rows:              1
      Initial runs:         2 Merge passes:        1 IO Cost / pass:          2
      Total IO sort cost: 2      Total CPU sort cost: 1
      Total Temp space used: 1
*** 2006-11-28 15:53:27.666
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
Current SQL statement for this session:
select
  CASH_RECEIPT.CUSTOMER_ID,
  CASH_RECEIPT.CHECK_ID,
  CUSTOMER.NAME,
  CURRENCY.NAME,
  CASH_RECEIPT.AMOUNT,
  CASH_RECEIPT.POSTING_DATE
from
  CASH_RECEIPT,
  CUSTOMER,
  CURRENCY
where
  CASH_RECEIPT.CUSTOMER_ID = CUSTOMER.ID
  And CUSTOMER.CURRENCY_ID = CURRENCY.ID(+)
  and ( CASH_RECEIPT.CUSTOMER_ID like :"SYS_B_0" )
order by
  :"SYS_B_1"

As you can probably tell by the bind variables in the above, the CURSOR_SHARING parameter was NOT set to EXACT. Through experimentation, the following made the error disappear:

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLED=10.1.0.4;

Setting OPTIMIZER_FEATURES_ENABLED to either 10.1.0.5 or 10.2.0.1 resulted in the error re-appearing. With the help of Jonathan’s book I was able to narrow the problem down to a single hidden parameter. Rather than setting OPTIMIZER_FEATURES_ENABLED to 10.1.0.4, I could simply do this instead:

ALTER SYSTEM SET "_OPTIMIZER_UNDO_COST_CHANGE"=10.1.0.4;

Shortly after mentioning the above in the Metalink report the Metalink case (TAR?) was automatically closed. Because the hidden parameters should only be changed at the direction of Oracle support, I took the lack of a response as an indication that the change was not authorized – so I was forced to use the supported method in the production database that was experiencing the problem:

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLED=10.1.0.4;

Again, good points about the non-default values for the OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING parameters.

Honestly, I was not expecting an answer so quickly that immediately jumped to a hint – I thought that the discussion leading up to the adding of a hint might make for an interesting blog discussion. The following shows the change that I made to fix the performance of this particular SQL statement:

INSERT INTO
  MRP_FORECAST_PART_DATE_QTY
SELECT /*+ LEADING(E)      NO_QUERY_TRANSFORMATION       */

I admit that the solution that I used was a bit simplistic – basically telling the optimizer: *DO NOT CHANGE MY DARN SQL STATEMENT*

17 12 2010
Narendra

Charles,

Thanks for the explaination. Can’t wait to see your test cases to simulate this.
Would you mind validating my claim below?
While investigating this performance problem, if using hints (like NO_QUERY_TRANSFORMATION or NO_MERGE) result in original plan (for SELECT) being used and the estimated cost of the revised query is less than the estimated cost of the plan for the slow query, it is safe to conclude that it is an optimizer bug. This is because optimizer has clearly failed to select the cheapest plan to execute the query
While the estimated cost of the “fast” plan is visible above (157), the estimated cost of the “slow” plan is not visible. Would you mind sharing the estimated cost details for the “slow” plan above? Considering that the “slow” plan expects large TEMP usage as opposed to no TEMP usage expected by “fast” plan, I would be surprised if CBO has indeed considered the “slow” plan to be the cheapest plan.

17 12 2010
Charles Hooper

I have not had any time to investigate this particular SQL statement – the NO_QUERY_TRANSFORMATION hint generated the execution plan that I expected to see, so I moved on to other items.

For an experiment, I created the following script:

CREATE TABLE T1_DEST (
  PART_ID VARCHAR2(30),
  REQUIRED_DATE DATE,
  QTY NUMBER,
  PLAN_LEVEL NUMBER);

SET TIMING ON
SET ARRAYSIZE 1000

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_10.1.0.4';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 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
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.1.0.4';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

INSERT INTO T1_DEST
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
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

TRUNCATE TABLE T1_DEST;

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.2.0.2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 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
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.2.0.2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

INSERT INTO T1_DEST
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
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

TRUNCATE TABLE T1_DEST;

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';
ALTER SESSION SET "_OPTIMIZER_UNDO_COST_CHANGE"='10.2.0.2';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_UNDO_10.2.0.2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 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
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_UNDO_10.2.0.2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

INSERT INTO T1_DEST
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
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

(disconnect and reconnect)

SET TIMING ON
SET ARRAYSIZE 1000

TRUNCATE TABLE T1_DEST;

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100;
ALTER SESSION SET OPTIMIZER_INDEX_CACHING=0;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_10.1.0.4-2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 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 /*+ 2 */
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.1.0.4-2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

INSERT INTO T1_DEST
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 /*+ 2 */
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

TRUNCATE TABLE T1_DEST;

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.2.0.2-2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 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 /*+ 2 */
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.2.0.2-2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

INSERT INTO T1_DEST
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 /*+ 2 */
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

TRUNCATE TABLE T1_DEST;

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';
ALTER SESSION SET "_OPTIMIZER_UNDO_COST_CHANGE"='10.2.0.2';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_UNDO_10.2.0.2-2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 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 /*+ 2 */
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_UNDO_10.2.0.2-2';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

INSERT INTO T1_DEST
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 /*+ 2 */
      E.CUSTPO,
      MAX(RELID) RELID
    FROM
      TESTUSER.EDI830 E
    GROUP BY
      E.CUSTPO) E,
    TESTUSER.EDI830_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
    CUSTOMER_ORDER CO,
    CUST_ORDER_LINE 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
  COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'),
  E.FCDUEDATE;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The execution plans that were produced from the 10053 trace follow (note that if a hard parse was not performed, nothing will be written to a trace file, so that probably explains why some of the trace file results are missing):
select_10.1.0.4:

------------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |                |       |       |   157 |           |
| 1   |  SORT GROUP BY               |                |   204 |   17K |   157 |  00:00:01 |
| 2   |   FILTER                     |                |       |       |       |           |
| 3   |    HASH JOIN OUTER           |                |   374 |   31K |   156 |  00:00:01 |
| 4   |     NESTED LOOPS             |                |   194 |  9506 |    47 |  00:00:01 |
| 5   |      VIEW                    |                |  1993 |   35K |    46 |  00:00:01 |
| 6   |       SORT GROUP BY          |                |  1993 |   39K |    46 |  00:00:01 |
| 7   |        FILTER                |                |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 9   |      INDEX RANGE SCAN        | EDI830_LINES_PK|     1 |    31 |     1 |  00:00:01 |
| 10  |     VIEW                     |                |  6261 |  220K |   108 |  00:00:01 |
| 11  |      SORT GROUP BY           |                |  6261 |  251K |   108 |  00:00:01 |
| 12  |       HASH JOIN              |                |  8185 |  328K |   106 |  00:00:01 |
| 13  |        TABLE ACCESS FULL     | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 14  |        TABLE ACCESS FULL     | CUST_ORDER_LINE|  8550 |  200K |    92 |  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")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
9 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
9 - filter("EL"."FCSTQTY">0)
12 - access("CO"."ID"="COL"."CUST_ORDER_ID")
13 - filter("CUSTOMER_PO_REF" IS NOT NULL)
14 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

insert_10.1.0.4:

-------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name           | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT              |                |       |       |  272K |           |
| 1   |  SORT GROUP BY                |                |   204 |   13K |  272K |  00:22:07 |
| 2   |   FILTER                      |                |       |       |       |           |
| 3   |    MERGE JOIN OUTER           |                |   75K | 5046K |  272K |  00:22:07 |
| 4   |     VIEW                      |                |   69K | 2129K |  272K |  00:22:06 |
| 5   |      FILTER                   |                |       |       |       |           |
| 6   |       SORT GROUP BY           |                |   69K | 3502K |  272K |  00:22:06 |
| 7   |        FILTER                 |                |       |       |       |           |
| 8   |         HASH JOIN             |                |  104M | 5326M |  1154 |  00:00:06 |
| 9   |          INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 10  |          TABLE ACCESS FULL    | EDI830_LINES   |  236K | 7311K |   941 |  00:00:05 |
| 11  |     SORT JOIN                 |                |  6261 |  220K |   110 |  00:00:01 |
| 12  |      VIEW                     |                |  6261 |  220K |   108 |  00:00:01 |
| 13  |       SORT GROUP BY           |                |  6261 |  251K |   108 |  00:00:01 |
| 14  |        HASH JOIN              |                |  8185 |  328K |   106 |  00:00:01 |
| 15  |         TABLE ACCESS FULL     | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 16  |         TABLE ACCESS FULL     | CUST_ORDER_LINE|  8550 |  200K |    92 |  00:00:01 |
-------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
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"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200))
11 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
11 - filter("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
14 - access("CO"."ID"="COL"."CUST_ORDER_ID")
15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
16 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

insert_10.2.0.2:

------------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |                |       |       |   157 |           |
| 1   |  SORT GROUP BY               |                |   204 |   17K |   157 |  00:00:01 |
| 2   |   FILTER                     |                |       |       |       |           |
| 3   |    HASH JOIN OUTER           |                |   374 |   31K |   156 |  00:00:01 |
| 4   |     NESTED LOOPS             |                |   194 |  9506 |    47 |  00:00:01 |
| 5   |      VIEW                    |                |  1993 |   35K |    46 |  00:00:01 |
| 6   |       HASH GROUP BY          |                |  1993 |   39K |    46 |  00:00:01 |
| 7   |        FILTER                |                |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 9   |      INDEX RANGE SCAN        | EDI830_LINES_PK|     1 |    31 |     1 |  00:00:01 |
| 10  |     VIEW                     |                |  6261 |  220K |   108 |  00:00:01 |
| 11  |      HASH GROUP BY           |                |  6261 |  251K |   108 |  00:00:01 |
| 12  |       HASH JOIN              |                |  8185 |  328K |   106 |  00:00:01 |
| 13  |        TABLE ACCESS FULL     | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 14  |        TABLE ACCESS FULL     | CUST_ORDER_LINE|  8550 |  200K |    92 |  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")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
9 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
9 - filter("EL"."FCSTQTY">0)
12 - access("CO"."ID"="COL"."CUST_ORDER_ID")
13 - filter("CUSTOMER_PO_REF" IS NOT NULL)
14 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

insert_undo_10.2.0.2:

------------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |                |       |       |   157 |           |
| 1   |  SORT GROUP BY               |                |   204 |   17K |   157 |  00:00:01 |
| 2   |   FILTER                     |                |       |       |       |           |
| 3   |    HASH JOIN OUTER           |                |   375 |   31K |   156 |  00:00:01 |
| 4   |     NESTED LOOPS             |                |   194 |  9506 |    47 |  00:00:01 |
| 5   |      VIEW                    |                |  1993 |   35K |    46 |  00:00:01 |
| 6   |       HASH GROUP BY          |                |  1993 |   39K |    46 |  00:00:01 |
| 7   |        FILTER                |                |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 9   |      INDEX RANGE SCAN        | EDI830_LINES_PK|     1 |    31 |     1 |  00:00:01 |
| 10  |     VIEW                     |                |  6261 |  220K |   108 |  00:00:01 |
| 11  |      HASH GROUP BY           |                |  6261 |  251K |   108 |  00:00:01 |
| 12  |       HASH JOIN              |                |  8185 |  328K |   106 |  00:00:01 |
| 13  |        TABLE ACCESS FULL     | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 14  |        TABLE ACCESS FULL     | CUST_ORDER_LINE|  8550 |  200K |    92 |  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")
7 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
9 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
9 - filter("EL"."FCSTQTY">0)
12 - access("CO"."ID"="COL"."CUST_ORDER_ID")
13 - filter("CUSTOMER_PO_REF" IS NOT NULL)
14 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

select_10.1.0.4-2:

------------------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |                |       |       |  1101 |           |
| 1   |  SORT GROUP BY               |                |    11 |   935 |  1101 |  00:00:06 |
| 2   |   HASH JOIN                  |                |    11 |   935 |  1100 |  00:00:06 |
| 3   |    FILTER                    |                |       |       |       |           |
| 4   |     HASH JOIN OUTER          |                |  2186 |  115K |   155 |  00:00:01 |
| 5   |      VIEW                    |                |  1993 |   35K |    46 |  00:00:01 |
| 6   |       SORT GROUP BY          |                |  1993 |   39K |    46 |  00:00:01 |
| 7   |        FILTER                |                |       |       |       |           |
| 8   |         INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 9   |      VIEW                    |                |  6261 |  220K |   108 |  00:00:01 |
| 10  |       SORT GROUP BY          |                |  6261 |  251K |   108 |  00:00:01 |
| 11  |        HASH JOIN             |                |  8185 |  328K |   106 |  00:00:01 |
| 12  |         TABLE ACCESS FULL    | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 13  |         TABLE ACCESS FULL    | CUST_ORDER_LINE|  8550 |  200K |    92 |  00:00:01 |
| 14  |    TABLE ACCESS FULL         | EDI830_LINES   |  236K | 7311K |   941 |  00:00:05 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID")
2 - filter("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
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 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200))

insert_10.1.0.4-2:

-------------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name           | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT              |                |       |       |  272K |           |
| 1   |  SORT GROUP BY                |                |   204 |   13K |  272K |  00:22:07 |
| 2   |   FILTER                      |                |       |       |       |           |
| 3   |    MERGE JOIN OUTER           |                |   75K | 5046K |  272K |  00:22:07 |
| 4   |     VIEW                      |                |   69K | 2129K |  272K |  00:22:06 |
| 5   |      FILTER                   |                |       |       |       |           |
| 6   |       SORT GROUP BY           |                |   69K | 3502K |  272K |  00:22:06 |
| 7   |        FILTER                 |                |       |       |       |           |
| 8   |         HASH JOIN             |                |  104M | 5326M |  1154 |  00:00:06 |
| 9   |          INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 10  |          TABLE ACCESS FULL    | EDI830_LINES   |  236K | 7311K |   941 |  00:00:05 |
| 11  |     SORT JOIN                 |                |  6261 |  220K |   110 |  00:00:01 |
| 12  |      VIEW                     |                |  6261 |  220K |   108 |  00:00:01 |
| 13  |       SORT GROUP BY           |                |  6261 |  251K |   108 |  00:00:01 |
| 14  |        HASH JOIN              |                |  8185 |  328K |   106 |  00:00:01 |
| 15  |         TABLE ACCESS FULL     | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 16  |         TABLE ACCESS FULL     | CUST_ORDER_LINE|  8550 |  200K |    92 |  00:00:01 |
-------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
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"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200))
11 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
11 - filter("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
14 - access("CO"."ID"="COL"."CUST_ORDER_ID")
15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
16 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

insert_10.2.0.2-2:

--------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name           | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT               |                |       |       |  274K |           |
| 1   |  SORT GROUP BY                 |                |   204 |   13K |  274K |  00:22:15 |
| 2   |   FILTER                       |                |       |       |       |           |
| 3   |    MERGE JOIN OUTER            |                |   75K | 5046K |  274K |  00:22:15 |
| 4   |     SORT JOIN                  |                |   69K | 2129K |  274K |  00:22:14 |
| 5   |      VIEW                      |                |   69K | 2129K |  274K |  00:22:14 |
| 6   |       FILTER                   |                |       |       |       |           |
| 7   |        SORT GROUP BY           |                |   69K | 3502K |  274K |  00:22:14 |
| 8   |         FILTER                 |                |       |       |       |           |
| 9   |          HASH JOIN             |                |  104M | 5326M |  2876 |  00:00:14 |
| 10  |           INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 11  |           TABLE ACCESS FULL    | EDI830_LINES   |  236K | 7310K |   941 |  00:00:05 |
| 12  |     SORT JOIN                  |                |  6261 |  220K |   110 |  00:00:01 |
| 13  |      VIEW                      |                |  6261 |  220K |   108 |  00:00:01 |
| 14  |       SORT GROUP BY            |                |  6261 |  251K |   108 |  00:00:01 |
| 15  |        HASH JOIN               |                |  8184 |  328K |   106 |  00:00:01 |
| 16  |         TABLE ACCESS FULL      | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 17  |         TABLE ACCESS FULL      | CUST_ORDER_LINE|  8550 |  200K |    92 |  00:00:01 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
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"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200))
12 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
12 - filter("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
15 - access("CO"."ID"="COL"."CUST_ORDER_ID")
16 - filter("CUSTOMER_PO_REF" IS NOT NULL)
17 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

insert_undo_10.2.0.2-2:

--------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name           | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT               |                |       |       |  274K |           |
| 1   |  SORT GROUP BY                 |                |   204 |   13K |  274K |  00:22:15 |
| 2   |   FILTER                       |                |       |       |       |           |
| 3   |    MERGE JOIN OUTER            |                |   75K | 5046K |  274K |  00:22:15 |
| 4   |     SORT JOIN                  |                |   69K | 2129K |  274K |  00:22:14 |
| 5   |      VIEW                      |                |   69K | 2129K |  274K |  00:22:14 |
| 6   |       FILTER                   |                |       |       |       |           |
| 7   |        SORT GROUP BY           |                |   69K | 3502K |  274K |  00:22:14 |
| 8   |         FILTER                 |                |       |       |       |           |
| 9   |          HASH JOIN             |                |  104M | 5326M |  2876 |  00:00:14 |
| 10  |           INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 11  |           TABLE ACCESS FULL    | EDI830_LINES   |  236K | 7311K |   941 |  00:00:05 |
| 12  |     SORT JOIN                  |                |  6261 |  220K |   110 |  00:00:01 |
| 13  |      VIEW                      |                |  6261 |  220K |   108 |  00:00:01 |
| 14  |       SORT GROUP BY            |                |  6261 |  251K |   108 |  00:00:01 |
| 15  |        HASH JOIN               |                |  8184 |  328K |   106 |  00:00:01 |
| 16  |         TABLE ACCESS FULL      | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 17  |         TABLE ACCESS FULL      | CUST_ORDER_LINE|  8550 |  200K |    92 |  00:00:01 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
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"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200))
12 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
12 - filter("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
15 - access("CO"."ID"="COL"."CUST_ORDER_ID")
16 - filter("CUSTOMER_PO_REF" IS NOT NULL)
17 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

insert_undo_10.2.0.2-2:

--------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name           | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT               |                |       |       |  274K |           |
| 1   |  SORT GROUP BY                 |                |   204 |   13K |  274K |  00:22:15 |
| 2   |   FILTER                       |                |       |       |       |           |
| 3   |    MERGE JOIN OUTER            |                |   75K | 5046K |  274K |  00:22:15 |
| 4   |     SORT JOIN                  |                |   69K | 2129K |  274K |  00:22:14 |
| 5   |      VIEW                      |                |   69K | 2129K |  274K |  00:22:14 |
| 6   |       FILTER                   |                |       |       |       |           |
| 7   |        SORT GROUP BY           |                |   69K | 3502K |  274K |  00:22:14 |
| 8   |         FILTER                 |                |       |       |       |           |
| 9   |          HASH JOIN             |                |  104M | 5326M |  2876 |  00:00:14 |
| 10  |           INDEX FAST FULL SCAN | EDI830_PK      |  105K | 2092K |    33 |  00:00:01 |
| 11  |           TABLE ACCESS FULL    | EDI830_LINES   |  236K | 7310K |   941 |  00:00:05 |
| 12  |     SORT JOIN                  |                |  6261 |  220K |   110 |  00:00:01 |
| 13  |      VIEW                      |                |  6261 |  220K |   108 |  00:00:01 |
| 14  |       SORT GROUP BY            |                |  6261 |  251K |   108 |  00:00:01 |
| 15  |        HASH JOIN               |                |  8184 |  328K |   106 |  00:00:01 |
| 16  |         TABLE ACCESS FULL      | CUSTOMER_ORDER |  7562 |  126K |    14 |  00:00:01 |
| 17  |         TABLE ACCESS FULL      | CUST_ORDER_LINE|  8550 |  200K |    92 |  00:00:01 |
--------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
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"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200))
12 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
12 - filter("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
15 - access("CO"."ID"="COL"."CUST_ORDER_ID")
16 - filter("CUSTOMER_PO_REF" IS NOT NULL)
17 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)

According to the timing information, the SELECT statements with OPTIMIZER_FEATURES_ENABLE set to 10.1.0.4 completed in about 0.34 seconds, while the INSERT INTO completed in about 3 minutes and 16 seconds (I am not sure why I was seeing times of about 20 minutes before). With OPTIMIZER_FEATURES_ENABLE set to 10.2.0.2 with the default values for OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING the SELECT statements completed in about 1.3 seconds and the INSERT INTO statements completed in about 2 minutes and 40 seconds.

16 12 2010
Houri Mohamed

Charles

You found that there is a big difference in the execution time between the select statement and the corresponding insert/select and you asked what you would do to improve the insert execution time

When dealing with insert I always think first of the direct path load option (/*+ append */) to improve the performance and to generate less redo/undo. I, of course, will check first if the table has no triggers and no integrity constraints in place (as it seems to be the case here).

In addition you said that the table has been truncated, hence, at least for this insert, I will not ask myself if the table will have its data deleted during its life (for this insert at least there will be no problem of re-used space).

In addition, I will ask myself also, why I need to keep the order by clause during the insert? Would I want to have data correctly packed by those two columns so that when I will create an index on them this one will have a perfect clustering factor?

After all that, I will start to think of, despite the /*+ append */ by generating a 10046 trace event during this insert in order to see where time is spent (why guess when you can know?)

Best Regards
Mohamed

16 12 2010
Charles Hooper

Mohamed,

Great idea to use the APPEND hint, and extra credit (bonus points) for mentioning one of the potential issues with implementing the APPEND hint (inserting above the high watermark, triggers, integrity constraints, etc.). In this particular case only about 900 rows were being inserted into the table (I intentionally withheld that piece of information so that people responding to this blog article, and specifically to those people who later read the blog article, would see a broader range of suggestions, and the potential warnings that should accompany the suggestions).

The ORDER BY clause was left in the INSERT statement for a couple of reasons:
* Initially I forgot to remove it when converting the SELECT statement into an INSERT INTO … SELECT type statement.
* I left the ORDER BY clause in the INSERT INTO … SELECT statement to take advantage of the nearly pre-sorted ordering of the rows in the table’s blocks. For the initial pass through the table when it is actually selected from, this will be slightly helpful. However, the INSERT INTO … SELECT is only the starting point of what actually ends up in that table. Immediately after the INSERT INTO … SELECT is performed, a recursive routine is performed that explodes up to 7 levels of bills of material for those parts, and the rows are inserted back into the same table. The CLUSTERING_FACTOR for the index that will eventually be created on the PART_ID column was a consideration.
* If I had removed the ORDER BY clause, it *might* be expected that the execution plan for the standard SELECT statement would differ from the execution plan for the INSERT INTO … SELECT SQL statement – so there is a risk that I might cause the performance to change.

I am once again impressed with the quality of the comments that are posted to this blog.

18 12 2010
Jonathan Lewis

Charles,

I am a late arrival at this party – but my approach would be as follows:
Comparing lines 4 – 7 in the first plan with lines 4 – 10 in the second plan, there’s clearly an example of complex view merging taking place (“group by then join” turns into “join then group by”).

The complex view merging would have been unconditional in 9i – but should be costed in 10g: so (although I rarely use the 10053 trace this early in an investigation) I would assume in this case that there was a bug in the optimizer code somewhere in the handling of the “insert as select”.

I’d use the no_merge hint suggested by Flado to check that the behaviour changed to the desired behavious. And I’d look at three trace files (select, insert unhinted, insert hinted) to see if I could spot the place where CVM (complex view merging) failed to appear when expected, or appeared with an unsuitable cost; or where the cost of not merging appeared to be higher than the cost of merging.

19 12 2010
Charles Hooper

Great tips for troubleshooting the problem. I wonder if the OPTIMIZER_FEATURES_ENABLE value of 10.1.0.4 causes the optimizer to unconditionally perform the complex view merging like 9i. There is a definite behavior change in the optimization of some queries with the OPTIMIZER_FEATURES_ENABLE parameter set to 10.1.0.4 compared to 10.1.0.5 or 10.2.0.1. I need to spend some time digging through 10053 trace files to satisfy a curiosity about this unexpected performance issue.

18 12 2010
Jonathan Lewis

Charles,

I forgot to say – after checking what was going on, I’d try to model it with a simpler example – “insert as select from two table join which could be satisfied by index-only acceses” (similar to the original nested loop join section.

Regards
Jonathan Lewis

18 12 2010
Jonathan Lewis

Charles,

Just hogging things here, but I realised I missed an opportunity to advertise my extremely excellent blog ;) where there’s a little note that shows this type of CVM. http://jonathanlewis.wordpress.com/2007/03/08/transformation-and-optimisation/

18 12 2010
Charles Hooper

Part 2 of the blog article series is now on this blog. I am able to reproduce the execution plan change with a reproducible test case on Oracle Database 11.2.0.1 (see the execution plans at the end of the second article).

19 12 2010
Charles Hooper

Jonathan,

Thank you for providing the link to that article. I recall reading about that feedback loop in your “Cost-Based Oracle Fundamentals” book. Speaking of which, I am still waiting for volumes 2 and 3 of the book series. :-)

It is interesting that the performance problem is present in the test case script (found in the follow-up article) when the OPTIMIZER_FEATURES_ENABLE is set to 10.1.0.4 in both Oracle Database 10.2.0.2 and 11.2.0.1, but the problem is not present in Oracle Database 10.2.0.5 when the test is repeated in either the Standard Edition or the Enterprise Edition (that will be illustrated in part 3).

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 139 other followers

%d bloggers like this: