Re: optimizer_features_enable parameter & query plan

On 8 Jun 2006 04:18:22 -0700, "News" <Contact_404_at_hotmail.com> wrote:

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

Sure it doesn't, provided you set the correct export and import parameters.
exp help=y informs you about the various values of the statistics= parameter

>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

Such sweeping generalization is simply incorrect. And remember hints are just what they are: *hints*. They aren't *directives*.

>
>> Mladen Gogala
>> http://www.mgogala.com

--
Sybrand Bakker, Senior Oracle DBA