Re: optimizer_features_enable parameter & query plan

News wrote:
> I have problem with some queries after upgrading from 9.2 to 10gr2
> under aix
>
> with optimizer_features_enable set to '10.2.0.1' this query takes
> eternity
>
> Select
> Distinct (e."ETAB_CLE"),
> e."HISA_CODE",
> e."HISA_CD_NAF",
> h.hisc_cpte_cle,
> e."UR_TRAITEMENT"
> From
> "SNV2"."LOTC_ETABLISSEMENT_V2" e,
> "SNV2"."LOTC_ETAB_HISC_SX2" h,
> (SELECT MAX (b."HISA_REFER") maxi,
> ETAB_CLE,
> HISA_CODE,
> UR_TRAITEMENT
> FROM "SNV2"."LOTC_ETABLISSEMENT_V2" b
> GROUP BY ETAB_CLE, HISA_CODE, UR_TRAITEMENT) Y
> Where
> e."ETAB_CLE" = h."SIAD_ETAB_CLE"
> AND e."ACTI_CLE" = h."HISC_ACTI_CLE"
> AND e."UR_TRAITEMENT" = h."UR_TRAITEMENT"
> AND e.hisa_code = '11'
> AND Y.maxi = e."HISA_REFER"
> AND Y."ETAB_CLE" = e."ETAB_CLE"
> AND Y."UR_TRAITEMENT" = e."UR_TRAITEMENT"
> AND Y."HISA_CODE" = '11'
>
>
> 13672 SELECT STATEMENT Cost= 13672
> 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_ETAB_HISC_SX2
> 2 TABLE ACCESS FULL LOTC_ETABLISSEMENT_V2
>
> when optimizer_features_enable set to '8.1.7' the plan is
>
> 2176 SELECT STATEMENT Cost= 2176
> 1 SORT UNIQUE
> 1 TABLE ACCESS BY INDEX ROWID LOTC_ETAB_HISC_SX2
> 1 NESTED LOOPS
> 1 HASH JOIN
> 1 VIEW
> 1 SORT GROUP BY
> 1 TABLE ACCESS FULL LOTC_ETABLISSEMENT_V2
> 2 TABLE ACCESS FULL LOTC_ETABLISSEMENT_V2
> 2 INDEX RANGE SCAN LOTC_ETAB_HISC_SX2_IDX2
> SQL>
>
> and the query takes 2 seconds to run
>
> I need optimizer_features_enable to be set to '10.2.0.1' because
> materialized views creation needs some 10g optimizer features to run
> faster (ratio 1/44 for some MVs). So how to fix this ? rewrite the
> query ? or disabling any 10g optimizer feature ?

Some questions for you:

Did you read the section of the migration gude that covers this? [No | Yes | What fine manual? ]
Did you gather statistics after the upgrade? [No | Yes | dbms_scheduler did it for me]
Did you change the method_opt via which stats were gathered? [No | Yes | Huh?]
Did you enable cpu costing? [No | Yes | Huh?]

-bdbafh Received on Wed Jun 07 2006 - 13:09:59 CDT