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

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

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

Quote:

---

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.

Thank you very much Abhay.

Regards,

Shailesh

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.