optimizer_features_enable parameter & query plan

From: News <Contact_404_at_hotmail.com>
Date: 7 Jun 2006 10:33:10 -0700
Message-ID: <1149701590.526994.87810@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 ? Received on Wed Jun 07 2006 - 12:33:10 CDT