10g / CBO stat problems

From: News <Contact_404_at_hotmail.com>
Date: 2 Nov 2006 09:36:13 -0800
Message-ID: <1162488973.873346.130300@f16g2000cwb.googlegroups.com>

could someone please explain ?

" This package is concerned with optimizer statistics only. Given that
Oracle sets automatic statistics collection of this kind on by default, this package is intended for only specialized cases "

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#ARPLS059

Does that mean I don't need to run DBMS_STATS against tables and indexes since they are set automatically ?

I'm trying to understand what happens with the query below.. It runs only when I delete all stats (generated using dmbs_stats and analyze) or if I set optimizer_features_enable to 8.1.7 (prior to 10g migration) instead of 10.2.0.1.
Otherwise plan progresses very slowly when scanning LOTC_ETABLISSEMENT_V2 table (query takes many months to run vs few seconds after deleting stats)

P.S: this is a sample problem among huge other ones encountered by CBO and 10g optimizer that avoid setting optimizer_features_enable to 10.2.0.1. 10g migration is fiasco because of CBO .. ETL jobs performance are getting worse and worse every day.

Some ideas ? I suggested taking the bull by the horns and using full 10g: turning on optimizer_features_enable to 10.2.0.1 and replacing analyze by dbms_stats (3 months working) and facing problems with queries like the one below.

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'

Received on Thu Nov 02 2006 - 11:36:13 CST