Code:
explain plan set statement_id = 'ABHAY' for
MERGE INTO  Shipment_Line X
USING
(
select * from shipment_line sd
where
  not exists (select /*+ hash_aj */ null from temp_slordt s where
                    S.SALES_ORGANIZATION_CD       =    Sd.SALES_ORGANIZATION_CD  And
                    S.SALES_ORDER_NO              =    Sd.REFERENCE_DOCUMENT_NO And
                    Sd.REFRENCE_DOCUMENT_ITEM_NO  =    S.sales_order_item_no And
                    SUBSTR(s.SOURCE_NM,-3,3)      =    SUBSTR(sd.SOURCE_NM,-3,3)) and
  sales_document_type <> '??' and --rownum > 0
  exists (select null from temp_slsord s where
                    S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
                    S.SALES_ORDER_NO             =    Sd.REFERENCE_DOCUMENT_NO And
                    SUBSTR(s.SOURCE_NM,-3,3)     =    SUBSTR(sd.SOURCE_NM,-3,3))
) Y
ON
(
  X.SALES_ORGANIZATION_CD   =    Y.SALES_ORGANIZATION_CD      AND
  X.SHIPMENT_NO             =    Y.SHIPMENT_NO                AND
  X.SHIPMENT_ITEM_NO        =    Y.SHIPMENT_ITEM_NO           AND
  X.SOURCE_NM               =    Y.SOURCE_NM
)
WHEN MATCHED THEN
UPDATE  SET
  x.sales_document_type = '??', x.reportable_flag = '?',
  x.sab101_flag = '?', x.country_cd = '?', x.Ship_To_Customer = '?',
  x.So_Entry_Date = '31-Dec-2999', x.SALES_ORDER_ITEM_SHORT_TX = '??'
WHEN NOT MATCHED THEN
INSERT
 (
  X.SALES_ORGANIZATION_CD          ,
  X.SHIPMENT_NO                    ,
  X.SHIPMENT_ITEM_NO               ,
  X.SOURCE_NM
 )
VALUES
 (
  Y.SALES_ORGANIZATION_CD          ,
  Y.SHIPMENT_NO                    ,
  Y.SHIPMENT_ITEM_NO               ,
  Y.SOURCE_NM
 )

The plan as shown after explaining.

PRO> @display_plan ABHAY

  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
   0      ####        MERGE STATEMENT (choose)     Cost (4976,24,3936)
   1    0    1          MERGE  WWCIW_DBA SHIPMENT_LINE
   2    1    1   12       VIEW  WWCIW_DBA  PARALLEL_TO_SERIAL
   3    2    1              NESTED LOOPS    (outer) PARALLEL_COMBINED_WITH_PARENT Cost (4976,24,7320)
   4    3    1                HASH JOIN    (anti) PARALLEL_COMBINED_WITH_PARENT Cost (4958,24,4296)
   5    4    1                  HASH JOIN    PARALLEL_TO_PARALLEL Cost (4066,25,3750)
   6    5    1                    SORT    (unique) PARALLEL_FROM_SERIAL
   7    6    1   10                 TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full)  Cost (165,79119,1898856)
   8    5    2                    PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 8 Pt Range: 1 - 4
   9    8    1    8                 TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_COMBINED_WITH_PARENT Pt id: 8 Pt Range: 1 - 4 Cost (3501,643102,81030852)
  10    4    2    9             TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full) PARALLEL_FROM_SERIAL Cost (891,310268,8997772)
  11    3    2    2           TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (by global index rowid) PARALLEL_COMBINED_WITH_PARENT Pt id: 11 Pt Range: ROW LOCATION - ROW LOCATION Cost (3,1,126)
  12   11    1                  INDEX (analyzed) UNIQUE WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4 PARALLEL_COMBINED_WITH_PARENT Cost (2,1,)


Run time plan taken from v$sql_plan

PRO> @display_plan_by_address 1CCE821C

  Id  Par  Pos Plan
---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
   0         0   MERGE STATEMENT (choose)   (Columns 0  Cost (290820,,)
   1    0    1     MERGE   (Columns 0
   2    1    1       VIEW   (Columns 0
   3    2    1         FILTER   (Columns 0
   4    3    1           NESTED LOOPS   (outer) (Columns 0 PARALLEL_TO_SERIAL Cost (3918,322,88872)
   5    4    1             HASH JOIN   (semi) (Columns 0 PARALLEL_COMBINED_WITH_PARENT Cost (3676,322,48300)
   6    5    1               PARTITION RANGE   (all) (Columns 0 PARALLEL_COMBINED_WITH_PARENT Pt id: 6 Pt Range: 1 - 4
   7    6    1                 TABLE ACCESS (analyzed) WWCIW_DBA SHIPMENT_LINE (full) (Columns 0 PARALLEL_TO_PARALLEL Pt id: 6 Pt Range: 1 - 4 Cost (3501,32155,4051530)
   8    5    2               TABLE ACCESS (analyzed) WWCIW_SAP TEMP_SLSORD (full) (Columns 0 PARALLEL_FROM_SERIAL Cost (165,79119,1898856)
   9    4    2             TABLE ACCESS (analyzed) WWCIW_DBA SHIPMENT_LINE (by global index rowid) (Columns 0 PARALLEL_COMBINED_WITH_PARENT Pt id: 9 Pt Range: ROW L - ROW L Cost (3,1,126)
  10    9    1               INDEX (analyzed) WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4 PARALLEL_COMBINED_WITH_PARENT Cost (2,1,)
  11    3    2           TABLE ACCESS (analyzed) WWCIW_SAP TEMP_SLORDT (full) (Columns 0  Cost (891,1,29)
This is where i go crazy.. why on earth the run time plan is changing..


Abhay.