How can i force optimizer to use hash anit join
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: How can i force optimizer to use hash anit join

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    How can i force optimizer to use hash anit join

    Guys...

    I tried all sorts of hints but still oracle goes for filter and FTS on a temp table.. I dont want to create an index for this reason..

    Is there any way to force Oracle to use Hash Aj in this case..

    Below is the code and plan..

    Code:
    PRO> ed
    Wrote file afiedt.buf
    
      1  explain plan set statement_id = 'ABHAY' for
      2  MERGE /*+ Ordered */ INTO  Shipment_Line X
      3  USING
      4  (
      5  select /*+ Ordered */ * from shipment_line sd
      6  where
      7    (
      8      Sd.SALES_ORGANIZATION_CD       ,
      9      Sd.REFERENCE_DOCUMENT_NO       ,
     10      Sd.REFRENCE_DOCUMENT_ITEM_NO   ,
     11      SUBSTR(sd.SOURCE_NM,-3,3)
     12    )
     13    not in (select /*+ Hash_Aj */
     14                      S.SALES_ORGANIZATION_CD       ,
     15                      S.SALES_ORDER_NO              ,
     16                      S.sales_order_item_no         ,
     17                      SUBSTR(s.SOURCE_NM,-3,3)
     18            from temp_slordt s) and
     19    sales_document_type <> '??' and rownum > 0 and
     20    exists (select null from temp_slsord s where
     21                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
     22                      S.SALES_ORDER_NO             =    Sd.REFERENCE_DOCUMENT_NO And
     23                      SUBSTR(s.SOURCE_NM,-3,3)     =    SUBSTR(sd.SOURCE_NM,-3,3))
     24  ) Y
     25  ON
     26  (
     27    X.SALES_ORGANIZATION_CD   =    Y.SALES_ORGANIZATION_CD      AND
     28    X.SHIPMENT_NO             =    Y.SHIPMENT_NO                AND
     29    X.SHIPMENT_ITEM_NO        =    Y.SHIPMENT_ITEM_NO           AND
     30    X.SOURCE_NM               =    Y.SOURCE_NM
     31  )
     32  WHEN MATCHED THEN
     33  UPDATE  SET
     34    x.sales_document_type = '??', x.reportable_flag = '?',
     35    x.sab101_flag = '?', x.country_cd = '?', x.Ship_To_Customer = '?',
     36    x.So_Entry_Date = '31-Dec-2999', x.SALES_ORDER_ITEM_SHORT_TX = '??'
     37  WHEN NOT MATCHED THEN
     38  INSERT
     39   (
     40    X.SALES_ORGANIZATION_CD          ,
     41    X.SHIPMENT_NO                    ,
     42    X.SHIPMENT_ITEM_NO               ,
     43    X.SOURCE_NM
     44   )
     45  VALUES
     46   (
     47    Y.SALES_ORGANIZATION_CD          ,
     48    Y.SHIPMENT_NO                    ,
     49    Y.SHIPMENT_ITEM_NO               ,
     50    Y.SOURCE_NM
     51*  )
    PRO> /
    
    Explained.
    
    Elapsed: 00:00:01.39
    PRO> @display_plan ABHAY
    
      Id  Par  Pos  Ins Plan
    ---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
       0      ####        MERGE STATEMENT (choose)     Cost (4938,1,164)
       1    0    1          MERGE  WWCIW_DBA SHIPMENT_LINE
       2    1    1   12       VIEW  WWCIW_DBA
       3    2    1              NESTED LOOPS    (outer)  Cost (4938,1,303)
       4    3    1    7           VIEW  WWCIW_DBA   Cost (4937,1,177)
       5    4    1                  COUNT
       6    5    1                    FILTER
       7    6    1                      HASH JOIN    PARALLEL_TO_SERIAL Cost (4046,1,150)
       8    7    1                        SORT    (unique) PARALLEL_FROM_SERIAL
       9    8    1   10                     TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full)  Cost (165,79119,1898856)
      10    7    2                        PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 10 Pt Range: 1 - 4
      11   10    1    8                     TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_TO_PARALLEL Pt id: 10 Pt Range: 1 - 4 Cost (3501,32155,4051530)
      12    6    2    9                 TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full)  Cost (891,2,58)
      13    3    2    2           TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (by global index rowid)  Pt id: 13 Pt Range: ROW LOCATION - ROW LOCATION Cost (3,1,126)
      14   13    1                  INDEX (analyzed) UNIQUE WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4  Cost (2,1,)
    Thx
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    It is not so much a question of forcing the optimizer to do an anti-join as one of allowing it to.

    Because of the logic of anti-join you must exclude the possibility of NULLs on either side of the join.

    For columns or expressions not obviously NULL to the optimizer this is typically done by adding IS NOT NULL conditions to all columns or expressions referenced on BOTH sides of the in the NOT IN.

    For example, the first query below does not guarantee that ename and loc are not null, hence hash anti-join is not an option. In the second query we explicitly state that ename and loc are NULL and anti-join results.

    Code:
    Personal Oracle Database 10g Release 10.1.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SET AUTOTRACE ON EXPLAIN;
    SQL> SELECT empno
      2  FROM   emp
      3  WHERE  ename NOT IN (
      4         SELECT loc
      5         FROM   dept);
    
         EMPNO
    ----------
          7369
          7499
          7521
          7566
          7654
          7698
          7782
          7788
          7839
          7844
          7876
          7900
          7902
          7934
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=76 Card=13 Bytes 130)
       1    0   FILTER
       2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=55 Card=14 Bytes=140)
       3    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=7)
    
    SQL> SELECT empno
      2  FROM   emp
      3  WHERE  ename IS NOT NULL
      4  AND    ename NOT IN (
      5         SELECT loc
      6         FROM   dept 
      7         WHERE  loc IS NOT NULL);
    
         EMPNO
    ----------
          7698
          7654
          7902
          7499
          7521
          7934
          7900
          7369
          7844
          7782
          7876
          7788
          7839
          7566
    
    14 rows selected.
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=10 Bytes=170)
       1    0   HASH JOIN (ANTI) (Cost=59 Card=10 Bytes=170)
       2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=55 Card=14 Bytes=140)
       3    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=28)
    
    SQL>

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by padders
    Because of the logic of anti-join you must exclude the possibility of NULLs on either side of the join.

    For columns or expressions not obviously NULL to the optimizer this is typically done by adding IS NOT NULL conditions to all columns or expressions referenced on BOTH sides of the in the NOT IN.
    Its not the Q of "not null" at all, for the hash aj to be an option..

    PS below.. the same query uses hash_aj if at all its run as query.. but when made as subqry in the update/merge statement it uses filter..

    ( here ofcourse i used not exists insead of not in, which should not make much difference ).. I tried with this not exists also in the merge statement still it goes for filter there....

    Code:
    PRO> explain plan set statement_id = 'ABHAY' for
      2  select * from shipment_line sd
      3  where
      4    not exists (select /*+ hash_aj */ null from temp_slordt s where
      5                      S.SALES_ORGANIZATION_CD       =    Sd.SALES_ORGANIZATION_CD  And
      6                      S.SALES_ORDER_NO              =    Sd.REFERENCE_DOCUMENT_NO And
      7                      Sd.REFRENCE_DOCUMENT_ITEM_NO  =    S.sales_order_item_no And
      8                      SUBSTR(s.SOURCE_NM,-3,3)      =    SUBSTR(sd.SOURCE_NM,-3,3)) and
      9    sales_document_type <> '??' and --rownum > 0 and
     10    exists (select null from temp_slsord s where
     11                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
     12                      S.SALES_ORDER_NO             =    Sd.REFERENCE_DOCUMENT_NO And
     13                      SUBSTR(s.SOURCE_NM,-3,3)     =    SUBSTR(sd.SOURCE_NM,-3,3));
    
    Explained.
    
    Elapsed: 00:00:01.40
    PRO> @display_plan ABHAY
    
      Id  Par  Pos  Ins Plan
    ---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
       0      ####        SELECT STATEMENT (choose)     Cost (4743,6367,1139693)
       1    0    1          HASH JOIN    (anti) PARALLEL_TO_SERIAL Cost (4743,6367,1139693)
       2    1    1            HASH JOIN    (semi) PARALLEL_TO_PARALLEL Cost (3848,6431,964650)
       3    2    1              PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 3 Pt Range: 1 - 4
       4    3    1    1           TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_COMBINED_WITH_PARENT Pt id: 3 Pt Range: 1 - 4 Cost (3501,643102,81030852)
       5    2    2    3         TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full) PARALLEL_FROM_SERIAL Cost (165,79119,1898856)
       6    1    2    2       TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full) PARALLEL_FROM_SERIAL Cost (891,310268,8997772)
    PS : In the table temp_slordt none of the columns are with "NOT NULL"..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Jan 2004
    Posts
    162
    > here ofcourse i used not exists insead of not in, which should not make much difference

    Ah but it does. NOT IN and NOT EXISTS are not the same thing, and the difference revolves around NULLs.

    Granted I do not have your tables or data but I can get your MERGE to go from FILTER to HASH_AJ simply by adding 8 IS NOT NULLs. Note that the FILTER plan is actually cheaper here because there is no data in the tables.
    Code:
    Personal Oracle Database 10g Release 10.1.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> CREATE TABLE shipment_line (
      2    sales_organization_cd NUMBER,
      3    reference_document_no NUMBER,
      4    reference_document_item_no NUMBER,
      5    source_nm VARCHAR2 (10),
      6    shipment_no NUMBER,
      7    shipment_item_no NUMBER,
      8    sales_document_type VARCHAR2 (2),
      9    reportable_flag VARCHAR2 (1),
     10    sab101_flag VARCHAR2 (1),
     11    country_cd VARCHAR2 (1),
     12    ship_to_customer VARCHAR2 (1),
     13    so_entry_date DATE,
     14    sales_order_item_short_tx VARCHAR2 (2));
    
    Table created.
    
    SQL> CREATE TABLE temp_slordt (
      2    sales_organization_cd NUMBER,
      3    sales_order_no NUMBER,
      4    sales_order_item_no NUMBER,
      5    source_nm VARCHAR2 (10));
    
    Table created.
    
    SQL> CREATE TABLE temp_slsord (
      2    sales_organization_cd NUMBER,
      3    sales_order_no NUMBER,
      4    sales_order_item_no NUMBER,
      5    source_nm VARCHAR2 (10));
    
    Table created.
    
    SQL> SET AUTOTRACE ON EXPLAIN;
    SQL> MERGE INTO shipment_line x
      2  USING (SELECT *
      3         FROM   shipment_line sd
      4         WHERE (sd.sales_organization_cd,
      5                sd.reference_document_no,
      6                sd.reference_document_item_no,
      7                SUBSTR (sd.source_nm, -3, 3)) NOT IN (
      8                SELECT /*+ HASH_AJ */
      9                       s.sales_organization_cd, s.sales_order_no,
     10                       s.sales_order_item_no, SUBSTR (s.source_nm, -3, 3)
     11                FROM   temp_slordt s)
     12         AND    sales_document_type <> '??'
     13         AND    EXISTS (
     14                SELECT NULL
     15                FROM   temp_slsord s
     16                WHERE  s.sales_organization_cd = sd.sales_organization_cd
     17                AND    s.sales_order_no = sd.reference_document_no
     18                AND    SUBSTR (s.source_nm, -3, 3) = SUBSTR (sd.source_nm, -3,  3))) y
     19  ON   (x.sales_organization_cd = y.sales_organization_cd
     20  AND   x.shipment_no = y.shipment_no
     21  AND   x.shipment_item_no = y.shipment_item_no
     22  AND   x.source_nm = y.source_nm)
     23     WHEN MATCHED THEN UPDATE
     24          SET   x.sales_document_type = '??', x.reportable_flag = '?',
     25                x.sab101_flag = '?', x.country_cd = '?',
     26                x.ship_to_customer = '?', x.so_entry_date = '31-Dec-2999',
     27                x.sales_order_item_short_tx = '??'
     28     WHEN NOT MATCHED THEN INSERT (
     29                x.sales_organization_cd, x.shipment_no, 
     30                x.shipment_item_no, x.source_nm)
     31     VALUES    (y.sales_organization_cd, y.shipment_no, 
     32                y.shipment_item_no, y.source_nm);
    
    0 rows merged.
    
    Execution Plan
    ----------------------------------------------------------
       0      MERGE STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=133)
       1    0   MERGE OF 'SHIPMENT_LINE'
       2    1     VIEW
       3    2       FILTER
       4    3         HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=230)
       5    4           HASH JOIN (OUTER) (Cost=5 Card=1 Bytes=197)
       6    5             TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=95)
       7    5             TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=102)
       8    4           TABLE ACCESS (FULL) OF 'TEMP_SLSORD' (TABLE) (Cost=2 Card=1 Bytes=33)
       9    3         TABLE ACCESS (FULL) OF 'TEMP_SLORDT' (TABLE) (Cost=2 Card=1 Bytes=46)
    
    SQL> MERGE INTO shipment_line x
      2  USING (SELECT *
      3         FROM   shipment_line sd
      4         WHERE  sd.sales_organization_cd IS NOT NULL
      5         AND    sd.reference_document_no IS NOT NULL
      6         AND    sd.reference_document_item_no IS NOT NULL
      7         AND    sd.source_nm IS NOT NULL
      8         AND   (sd.sales_organization_cd,
      9                sd.reference_document_no,
     10                sd.reference_document_item_no,
     11                SUBSTR (sd.source_nm, -3, 3)) NOT IN (
     12                SELECT /*+ HASH_AJ */
     13                       s.sales_organization_cd, s.sales_order_no,
     14                       s.sales_order_item_no, SUBSTR (s.source_nm, -3, 3)
     15                FROM   temp_slordt s
     16              WHERE  s.sales_organization_cd IS NOT NULL
     17              AND    s.sales_order_no IS NOT NULL
     18              AND    s.sales_order_item_no IS NOT NULL
     19              AND    s.source_nm IS NOT NULL)
     20         AND    sales_document_type <> '??'
     21         AND    EXISTS (
     22                SELECT NULL
     23                FROM   temp_slsord s
     24                WHERE  s.sales_organization_cd = sd.sales_organization_cd
     25                AND    s.sales_order_no = sd.reference_document_no
     26                AND    SUBSTR (s.source_nm, -3, 3) = SUBSTR (sd.source_nm, -3,  3))) y
     27  ON   (x.sales_organization_cd = y.sales_organization_cd
     28  AND   x.shipment_no = y.shipment_no
     29  AND   x.shipment_item_no = y.shipment_item_no
     30  AND   x.source_nm = y.source_nm)
     31     WHEN MATCHED THEN UPDATE
     32          SET   x.sales_document_type = '??', x.reportable_flag = '?',
     33                x.sab101_flag = '?', x.country_cd = '?',
     34                x.ship_to_customer = '?', x.so_entry_date = '31-Dec-2999',
     35                x.sales_order_item_short_tx = '??'
     36     WHEN NOT MATCHED THEN INSERT (
     37                x.sales_organization_cd, x.shipment_no, 
     38                x.shipment_item_no, x.source_nm)
     39     VALUES    (y.sales_organization_cd, y.shipment_no, 
     40                y.shipment_item_no, y.source_nm);
    
    0 rows merged.
    
    Execution Plan
    ----------------------------------------------------------
       0      MERGE STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=141)
       1    0   MERGE OF 'SHIPMENT_LINE'
       2    1     VIEW
       3    2       HASH JOIN (ANTI) (Cost=10 Card=1 Bytes=276)
       4    3         HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=230)
       5    4           HASH JOIN (OUTER) (Cost=5 Card=1 Bytes=197)
       6    5             TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=95)
       7    5             TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=102)
       8    4           TABLE ACCESS (FULL) OF 'TEMP_SLSORD' (TABLE) (Cost=2 Card=1 Bytes=33)
       9    3         TABLE ACCESS (FULL) OF 'TEMP_SLORDT' (TABLE) (Cost=2 Card=1 Bytes=46)
    
    SQL>
    Please do me the honour of posting your plan for MERGE statement with IS NOT NULLs added as above.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Yes indeed there is a difference in not in and not exists logically also.. NOT IN filters NULLS also but NOT EXISTS wont.

    The merge is working with hash anti join after i put not null condition as you mentioned... Thanks a lot...

    But I still dont get the point as to why Oracle didnt use Hash Aj when i used NOT EXISTS in merge statement.. but used hash aj if i just queried..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    > Thanks a lot

    You're welcome.

    > why Oracle didnt use Hash Aj when i used NOT EXISTS in merge statement

    Not 100% sure on that one. NOT EXISTS appears to use HASH_AJ when I try it. Perhaps it is related to your use of the /*+ ORDERED */ hint. Can you post a full example of that case.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    execution plan and run time execution plan can differ, it happened to me quite a few times , I think the real plan you can see in the tkprof file and v$sql_plan, what it happened to me was I had cursor_sharing set to FORCE

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Yes i know... Run time plan is what the exact plan that oracle takes for execution... but my Q is Why does it change when we explain and at run time.. my cursor sharing is exact.. btw, i execute as SQL rather then in a PL/SQL block.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Pinging the thread once more.. if some one can throw logical input as to why Oracle behaves erratic..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width