Re: optimizer_features_enable parameter & query plan

Mladen Gogala a écrit :

> On my web page,
> you can learn about quick & dirty way of making CBO behave like RBO but,

excellent article about CBO thanks !

We have adopted an undocumented method for upgrading from 9.2 to 10g.

1. full export of 9.2 DB
2. install 10g
3. create 10 g database
4. full import

it seems that this method lacks necessary steps as gathering stats. When I run gather_dictonary_stats the plan is

10404 SELECT STATEMENT Cost= 10404

1      HASH UNIQUE
1         FILTER
1            HASH GROUP BY
1               HASH JOIN
1                  HASH JOIN
1                     TABLE ACCESS FULL LOTC_ETABLISSEMENT_V2
2                     TABLE ACCESS FULL LOTC_ETABLISSEMENT_V2
2                  TABLE ACCESS FULL LOTC_ETAB_HISC_SX2

and the query gives results in few seconds

> there are queries in
> which the wrong indexes get used and which will cause you infinite
> amounts of grief and pain.

I have already run into this problem after upgrade. no grief and no pain :) just drooped indexes to force full scan

I just got lucky cause indexes were not necessary for other queries otherwise i don't know how to solve the problem. I tried hints but they don't help.. Oracle ignores them  

> Mladen Gogala
> http://www.mgogala.com
Received on Thu Jun 08 2006 - 06:18:22 CDT