Re: optimizer_features_enable parameter & query plan

"News" <Contact_404_at_hotmail.com> wrote in message news:1149701590.526994.87810_at_i39g2000cwa.googlegroups.com...
>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 ?
>

This is an example of complex view merging taking place. To emulate the 8.1.7 behaviour for this query you will need to put the hint /*+ no_merge */ in the inline view.

Did you have optimizer_features_enable='8.1.7' when running 9i - I would have expected the view merge to take place under the 9i optimizer as well.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html