If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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?

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..;-)

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.