We are preparing the migration of our Data Warehouse to 9i (from 8.1.6).
We performed tests and we noticed a perfomance degradation for some request (not all). this is an example :


SELECT
To_char(DEPARTEMENT.CD_DEP)||'-'||DEPARTEMENT.LB_DEP,
To_char(RAYON.CD_RAY) ||'-'||RAYON.LB_RAY,
sum(AG_REFERENCES.QTE_TOT_CMD),
sum(AG_REFERENCES.QTE_LIV_SEM),
sum(AG_REFERENCES.QTE_STK_MAG),
sum(AG_REFERENCES.QTE_STK_RAL),
sum(AG_REFERENCES.QTE_VTE_SEM),
sum((((AG_REFERENCES.QTE_VTE_SEM_1+AG_REFERENCES.Q TE_VTE_SEM_2)/2)+AG_REFERENCES.QTE_VTE_SEM)/2),
decode (sum(AG_REFERENCES.QTE_VTE_SEM+AG_REFERENCES.QTE_S TK_MAG),0,0,(sum(AG_REFERENCES.QTE_VTE_SEM)/sum(AG_REFERENCES.QTE_VTE_SEM+AG_REFERENCES.QTE_ST K_MAG))*100),
decode ( ((( sum(AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_ST K_CD) )+( sum(AG_REFERENCES.QTE_VTE_SEM) ))),0,0, (( sum(AG_REFERENCES.QTE_VTE_SEM) ) / ((( sum(AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_ST K_CD) )+( sum(AG_REFERENCES.QTE_VTE_SEM) ))))*100),
decode ((( sum(AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_ST K_CD) )+( sum(AG_REFERENCES.QTE_VE_CUM) ) ),0,0, ((( sum(AG_REFERENCES.QTE_VE_CUM) ) )/( ( sum(AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_ST K_CD) )+( sum(AG_REFERENCES.QTE_VE_CUM) ))*100)),
decode ( sum(AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_ST K_CD+AG_REFERENCES.QTE_VTE_SEM+AG_REFERENCES.QTE_V TE_SEM_1+AG_REFERENCES.QTE_VTE_SEM_2),0,0,
52*sum(AG_REFERENCES.QTE_VTE_SEM_2+AG_REFERENCES.Q TE_VTE_SEM_1+AG_REFERENCES.QTE_VTE_SEM)
/ ( (2* sum (AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_STK_C D ) + sum(AG_REFERENCES.QTE_VTE_SEM+AG_REFERENCES.QTE_VT E_SEM_1+AG_REFERENCES.QTE_VTE_SEM_2)) *3) )
,
decode (( sum(AG_REFERENCES.QTE_VTE_SEM*AG_REFERENCES.PRMP) ),0,0,( sum(AG_REFERENCES.QTE_VTE_SEM*
AG_REFERENCES.PVO ) )/( sum(AG_REFERENCES.QTE_VTE_SEM*AG_REFERENCES.PRMP) )) ,
decode (( sum((AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_S TK_CD)*AG_REFERENCES.PRMP) ),0, 0,( sum( (AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_STK_C D)* AG_REFERENCES.PVO) )/( sum((AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_S TK_CD)*AG_REFERENCES.PRMP) )),
decode (( sum((AG_REFERENCES.QTE_STK_RAL*AG_REFERENCES.PRMP) ) ),0, 0,( sum( AG_REFERENCES.QTE_STK_RAL*AG_REFERENCES.PVO ) )/( sum((AG_REFERENCES.QTE_STK_RAL*AG_REFERENCES.PRMP) ) )),
sum(AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_ST K_CD),
sum(AG_REFERENCES.QTE_VTE_SEM_1),
sum(AG_REFERENCES.QTE_VTE_SEM_2),
sum(AG_REFERENCES.QTE_VE_CUM),
sum(AG_REFERENCES.QTE_VTE_SEM*AG_REFERENCES.PRMP),
sum(AG_REFERENCES.QTE_VTE_SEM*AG_REFERENCES.PVO ),
sum((AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_S TK_CD)*AG_REFERENCES.PRMP),
sum( (AG_REFERENCES.QTE_STK_MAG+AG_REFERENCES.QTE_STK_C D)* AG_REFERENCES.PVO),
sum((AG_REFERENCES.QTE_STK_RAL*AG_REFERENCES.PRMP) ),
sum( AG_REFERENCES.QTE_STK_RAL*AG_REFERENCES.PVO ),
sum(AG_REFERENCES.QTE_VTE_PRD),
sum(AG_REFERENCES.VAL_VTE_NPV)
FROM
DEPARTEMENT,
RAYON,
AG_REFERENCES,
CALENDRIER CALENDRIER_REF,
REFERENCE,
MODELE,
FAMILLE,
METAFAMILLE
WHERE
( DEPARTEMENT.CD_DEP=RAYON.CD_DEP )
AND ( RAYON.CD_RAY=METAFAMILLE.CD_RAY )
AND ( FAMILLE.CD_MFAM=METAFAMILLE.CD_MFAM )
AND ( MODELE.CD_FAM=FAMILLE.CD_FAM )
AND ( REFERENCE.CD_MOD=MODELE.CD_MOD )
AND ( AG_REFERENCES.CD_REF=REFERENCE.CD_REF )
AND ( AG_REFERENCES.SEM_CIALE=CALENDRIER_REF.SEM_CIALE )
AND ( DEPARTEMENT.CD_DEP IN (1,2,4) )
AND ( DEPARTEMENT.CD_DEP in (1,2,4) )
AND (
CALENDRIER_REF.SEM_CIALE BETWEEN 200427 and 200427
AND RAYON.CD_RAY IN (1, 2, 3, 4, 6, 7)
)
GROUP BY
To_char(DEPARTEMENT.CD_DEP)||'-'||DEPARTEMENT.LB_DEP,
To_char(RAYON.CD_RAY) ||'-'||RAYON.LB_RAY