Query execution plans are different on 8i and 10g?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query execution plans are different on 8i and 10g?

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Query execution plans are different on 8i and 10g?

    Hello,

    We have migrated database from Oracle 8i to 10g and found following. Same query, same datastucture and data, but query executions plans are different on Oracle 8i and on 10g. On 8i we are using OPTIMIZER_MODE = CHOOSE and on 10g OPTIMIZER_MODE = all_rows which is default. Resultset on both the database version is same, but since execution plan are different, default sorting is not happening on 10g. So from application we see difference in sorting. Below I have given query and indexes on respective tables, plan on different versions.

    Indexes: XIE1MASTERTABLE - (VORGANGSID, LEISTUNG) and XPKKATALOGTABLE - CODE

    SELECT MASTERTABLE.EINRICHTUNG, MASTERTABLE.ITBRowID, ZAEHLER, LEISTUNG, ARTIKEL, BEZEICHNUNG, VERABREICHTDATUM, VERABREICHZEIT, ANZAHL, MENGENEINHEIT, DOSIS, DOSIERUNGSEINHEIT,
    VERABREICHUNGSART, CHARGENNREINGZWANG, HOSTTRANSAKTIONSID, IstZuUebertragen, MASTERTABLE.PREIS, VERBRAUCHSORT
    FROM MASTERTABLE, KATALOGTABLE
    WHERE MASTERTABLE.ARTIKEL = CODE (+) AND VORGANGSID = 3769
    AND MASTERTABLE.EINRICHTUNG = 'Ein1' ;

    On Oracle 8i:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=23 Bytes=2139)
    1 0 NESTED LOOPS (OUTER) (Cost=28 Card=23 Bytes=2139)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (Cost=5 Card=23 Bytes=1035)
    3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (NON-UNIQUE) (Cost=3 Card=23)
    4 1 TABLE ACCESS (BY INDEX ROWID) OF 'KATALOGTABLE' (Cost=1 Card=2309 Bytes=110832)
    5 4 INDEX (UNIQUE SCAN) OF 'XPKKATALOGTABLE' (UNIQUE)

    ----------------------------------------------------------------------

    On Oracle 10g:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=22 Bytes=2046)
    1 0 HASH JOIN (OUTER) (Cost=16 Card=22 Bytes=2046)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (TABLE) (Cost=5 Card=22 Bytes=990)
    3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (INDEX) (Cost=3 Card=22)
    4 1 TABLE ACCESS (FULL) OF 'KATALOGTABLE' (TABLE) (Cost=10 Card=2309 Bytes=110832)


    But when I set 'ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS' on 10g, query changed plan as shown below.

    SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=22 Bytes=2046)
    NESTED LOOPS (OUTER) (Cost=29 Card=22 Bytes=2046)
    TABLE ACCESS (BY INDEX ROWID) OF TBLVERBRAUCH (TABLE) (Cost=7 Card=22 Bytes=990)
    INDEX (RANGE SCAN) OF XPKVERBRAUCH (INDEX (UNIQUE)) (Cost=3 Card=22)
    TABLE ACCESS (BY INDEX ROWID) OF KATARTIKEL (TABLE) (Cost=1 Card=1 Bytes=48)
    INDEX (UNIQUE SCAN) OF XPKKATARTIKEL (INDEX (UNIQUE)) (Cost=0 Card=1)

    and result was sorted as it is on Oracle 8i.
    Does this mean to maintain default sorting of queries we need use optimizer first_rows and not all_rows on 10g? Why all_rows does HASH JOIN (OUTER)than NESTED LOOPS (OUTER).

    Why same data structure has different explain plan on 8i and 10g? and to maintain same query plans of 8i on 10g what we need to do?

    Please guide us and share your experiences.

    Thanks & Regards,

    Shailesh

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Shailesh if you need the rows sorted you should be using order by.The plan change is a expected behaviour i do not see anything wrong with it at all.

    Does this mean to maintain default sorting of queries we need use optimizer first_rows and not all_rows on 10g?
    Nope if you need sorting you should use order by period.
    optimzer_method should be what was there in 8i that is first_rows only


    Why all_rows does HASH JOIN (OUTER)than NESTED LOOPS (OUTER).

    Because all rows means optimize for all rows and hash join is the correct plan.If you make this change in 8i also i beleive you should get HASH JOIN (i would be surprised if you didnt)

    Why same data structure has different explain plan on 8i and 10g?
    Because of the parameter OPTIMIZER_MODE

    and to maintain same query plans of 8i on 10g what we need to do?
    set the optimzer_mode to same in 8i and 10g
    and also use optimzer_features_enable=8i

    But plans will change as with every version of oracle the optimzer is supposed to get smarter..;-)



    regards
    Hrishy

  3. #3
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Thanks Hrishy for your reply.

    As I have mentioned in my thread, on Oracle 8i optimizer is CHOOSE and on 10g ALL_ROWS.

    On 8i since query uses Nested loop outer join, uses index and so output shown in sorted order.

    Same query uses hash join outer on 10g because optimizer is ALL_ROWS, doesn't use index and query output is doesn't appear in sorted order.

    If I set optimizer mode first_rows on 10g, I get result in sorted order.

    Now since ours is OLTP environment we may benifit if we set optimizer first_rows on 10g, but can anyone elaborate pros/cons of first_rows over all_rows.

    Thanks & Regards,

    Shailesh

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by shailesh
    If I set optimizer mode first_rows on 10g, I get result in sorted order.

    Now since ours is OLTP environment we may benifit if we set optimizer first_rows on 10g, but can anyone elaborate pros/cons of first_rows over all_rows.

    Thanks & Regards,

    Shailesh
    when u use first_rows, optimizer is using index scans and hence u might be seeing ** ordered ** data ( though its not gauranteed unless u use order by )

    first_rows will look for optimization of getting first few records fast.. and is index prone.. it will effect the performance if you need complete set of results..

    all_rows is ideal and is * kinda * fts prone..

    hint : adjsut optimizer_index_cost_adj & optimizer_index_caching per your application needs..

    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"

  5. #5
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Thank you very much Abhay.

    Regards,

    Shailesh

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    As hrishy says, there is no such thing as default sorting. Without ORDER BY there are no guarantees (even with a GROUP BY clause).

    One task you ought to be doing is gathering system statistics with DBMS_STATS.GATHER_SYSTEM_STATS. This will give the optimizer much better information on your hardware and ought to improve your execution plans.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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