Optimisation requête Oracle 10 g

[yuhei]

Bonjour,

J'aurai une question concernant l'optimisation de requête sous oracle. J'aimerai optimiser le temps d'exécution d'une requête mais malgré mes certaines modifications soit disant qui devrait raccourcir le temps d'exécution, il reste inchangé et le plan d'exécution pour les 2 requêtes écrites différement est le même. voici les 2 requêtes :

Requête d'origine :

SELECT
to_char(E.DT_PHYSIQUE, 'dd/mm/yyyy') DT_SYNTHESE,
to_char(E.DT_PHYSIQUE, 'yyyy') || to_char(E.DT_PHYSIQUE, 'mm') || to_char(E.DT_PHYSIQUE, 'dd') ID_TEMPS,
E.DATAAREAID_MVT_STOCK AS DATAAREAID,
ELM_ORDRE_FAB.CD_POSTE_CHARGE,
(E.QUANTITE*UNIT_CONVERT.FACTOR) as QTE_EMBALLEE_SACHETS,
E.CD_ENTREPOT
FROM
ELM_MVT_STOCK E,
ELM_ORDRE_FAB,
UNIT_CONVERT
WHERE
to_char(E.CD_TYPE_TRANS) IN (SELECT FIELD_VALUE FROM PARAMETRE WHERE TYPE_PARAMETRE='TYPE_TRANS_PRODUCTION')
AND E.CD_ENTREPOT IN (SELECT FIELD_VALUE FROM PARAMETRE WHERE TYPE_PARAMETRE='ENTREPOT_5')
AND E.DATAAREAID_MVT_STOCK = UPPER(UNIT_CONVERT.DATAAREAID)
AND E.DATAAREAID_MVT_STOCK = ELM_ORDRE_FAB.DATAAREAID_OF
AND E.NUM_REFERENCE = ELM_ORDRE_FAB.NUM_OF
AND UNIT_CONVERT.FROMUNIT = 'EA'
AND UNIT_CONVERT.TOUNIT = 'BOX'
AND E.ID_ARTICLE = UNIT_CONVERT.ITEMID
AND to_date(to_char(E.DT_PHYSIQUE, 'mm/yyyy'), 'mm/yyyy') >= to_date(to_char(ADD_MONTHS (SYSDATE,-1), 'mm/yyyy'), 'mm/yyyy')

Requête modifiée :

SELECT
to_char(EMS.DT_PHYSIQUE, 'dd/mm/yyyy') DT_SYNTHESE,
to_char(EMS.DT_PHYSIQUE, 'yyyy') || to_char(EMS.DT_PHYSIQUE, 'mm') || to_char(EMS.DT_PHYSIQUE, 'dd') ID_TEMPS,
EMS.DATAAREAID_MVT_STOCK AS DATAAREAID,
EOF.CD_POSTE_CHARGE,
(EMS.QUANTITE*UCT.FACTOR) as QTE_EMBALLEE_SACHETS,
EMS.CD_ENTREPOT
FROM
ELM_MVT_STOCK EMS
INNER JOIN ELM_ORDRE_FAB EOF ON EOF.NUM_OF = EMS.NUM_PREFERENCE AND EOF.DATAAREAID_OF = EMS.DATAAREAID_MVT_STOCK
INNER JOIN UNIT_CONVERT UCT ON UCT.ITEMID = EMS.ID_ARTICLE AND UCT.FROMUNIT = 'EA' AND UCT.TOUNIT = 'BOX' AND UPPER(UCT.DATAAREAID) = EMS.DATAAREAID_MVT_STOCK
WHERE
to_date(to_char(EMS.DT_PHYSIQUE, 'mm/yyyy'), 'mm/yyyy') >= to_date(to_char(ADD_MONTHS (SYSDATE,-1), 'mm/yyyy'), 'mm/yyyy')
AND
EXISTS(SELECT FIELD_VALUE FROM PARAMETRE WHERE TYPE_PARAMETRE='TYPE_TRANS_PRODUCTION' AND FIELD_VALUE = to_char(EMS.CD_TYPE_TRANS))
AND
EXISTS(SELECT FIELD_VALUE FROM PARAMETRE WHERE TYPE_PARAMETRE='ENTREPOT_5 AND FIELD_VALUE = EMS.CD_ENTREPOT)
AND
to_date(to_char(EMS.DT_PHYSIQUE, 'mm/yyyy'), 'mm/yyyy') >= to_date(to_char(ADD_MONTHS (SYSDATE,-1), 'mm/yyyy'), 'mm/yyyy')

j'ai l'habitude de travailler sur SQL Server et la j'attaque pour la première fois une base Oracle, je sais qu'il interprète le code différement mais comment, malgré ma recherche sur quelques forum je ne vois pas trop.

Si vous pouvez m'aiguillé

merci.

Le plus important c'est la table ELM_MVT_STOCK qui retourne environ 997000 lignes, pour environ 2min30 d'exécution ... !!

[Mohamed.Houri]

Pourriez-vous faire ceci

(a) set linesize 150;
(b) executez votre requête
(c) immediatement après

Code :

1
2
 
   SELECT * FROM TABLE(dbms_xplan.display_cursor);

Et postez ici l'explain plan formaté pour qu'il soit analysé.

[yuhei]

Bonjour,

J'ai envoyé votre requête à notre dba, il est absent pour le moment je vous ferai un retour dès que possible sur le dbms_xplan. je ne suis pas en mesure d'exécuter de requête directement sur notre base.

Merci,

[Yanika_bzh]

pourquoi faites vous des doubles casts dans votre clause select

Code :

to_date(to_char(EMS.DT_PHYSIQUE, 'mm/yyyy'), 'mm/yyyy') >= to_date(to_char(ADD_MONTHS (SYSDATE,-1), 'mm/yyyy'), 'mm/yyyy')

Pas sur que l'indexation fonctionne avec ce genre de chose...

Avez vous essayer de les simplifier :

Code :

EMS.DT_PHYSIQUE>=ADD_MONTHS (SYSDATE,-1)

Bon courage

[yuhei]

Merci je vais essayer !

je n'ai toujours pas de retour de notre dba je vais essayer de vous sortir le xplan.

Cdt,

[mnitu]

L’idée que les joins ANSI sont plus optimales que les joins non ANSI est complètement farfelue en ce qui concerne Oracle.
Qu'est-ce que vous avez en termes de volumétrie et indexes ? Sur quelle version d’Oracle vous travaillez ?
Demandez à votre DBA de s’assurer que les statistiques des tables concernées sont à jour.

[EDIT]
Les Upper, to_char etc. sur les conditions de jointure ont une fâcheuse tendance de faire sauter des possibles indexes dans les plans d’exécution.
[/EDIT]