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

Sounds like a job for plan stability. Set your the OPTIMIZER_FEATURES_ENABLE to 8.1.7 in just your session, create the stored outline, and then set the param back. Oracle will use the stored outline whenever it gets this query and use the faster execution plan.

Check out the following doc for more information on plan stability:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm#sthref1787

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown