Discussion :

[ala-sarraj]

bonjour ,
je suis débutant en SQL , j'aimerais bien savoir comment faire pour optimiser une requête SQL , merci de me fournir des tuto facile à comprendre
merci

[mnitu]

Optimisation d'une requête SQL = supprimer le travail inutile effectué par la requête.
Il n'y a pas de tutorial difficile à comprendre, ce que c'est "difficile" tiens au niveau d'expérience du lecteur! Par voie de conséquence pour un débutant en SQL tout tutoriel sur l'optimisation des requêtes SQL est difficile.

[ala-sarraj]

merci pour votre réponse
est ce que vous pouvez m'aider a optimiser cette requête?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SELECT distinct
ofv.numero_of,
ofv.numero_lot,
mmt.subinventory_code  magasin,
OFV.CODE_LIGNE,
ofv.code_article,
ofv.description_article,
mmt.PRIOR_COST cout_actuelle,
(mmt.transaction_cost)Cout_production,
mmt.NEW_COST,
ofv.date_creation,
ofv.date_confirmation,
ofv.status,
ofv.status_detail,
ofv.duree_lot,
NVL(decode(ofv.QUANTITE_FABRIQUEE,0,mmt.transaction_quantity),ofv.quantite_fabriquee) quantite_fabriquee,
(fev.poids_total*NVL(ofv.nombre_operation,1) )Batch_formule,
(MIXAL.GET_COUT_GLOBAL(ofv.numero_of,ofv.numero_lot,ofv.id_article,ofv.quantite_fabriquee)) charge_input,
((MIXAL.GET_QTE_FABRIQUEE(ofv.numero_of,ofv.numero_lot,ofv.id_article)*MIXAL.GET_SALAIRE_LOT(ofv.numero_of,ofv.numero_lot))/MIXAL.GET_TOTAL_PRODUCTION(ofv.numero_of,ofv.numero_lot)) Cout_MOD, cp.nature_charge Nature,
decode(cp.nature_charge,'ACHAT',cp.COEFFICIENT_CHARGE*MIXAL.GET_COUT_GLOBAL(ofv.numero_of,ofv.numero_lot,ofv.id_article,ofv.quantite_fabriquee),cp.nature_charge,ROUND(cp.coefficient_charge* MIXAL.GET_QTE_FABRIQUEE(ofv.numero_of,ofv.numero_lot,ofv.id_article),5)) charge
from MIXAL.ORDRES_FABRICATION_V ofv, inv.MTL_MATERIAL_TRANSACTIONS mmt, MIXAL.FORMULES_ENTETES_V fev, MIXAL.CHARGE_PRODUCTION cp, MIXAL.CHARGE_PRODUCTION_OF cpo where 
  (ofv.id_formule=fev.id_formule_entete or (ofv.id_vrac=fev.id_output and fev.actif=1 and ( fev.FIN_VALIDITE >= SYSDATE OR fev.FIN_VALIDITE is null ))) and ofv.status not in ('ANNULER','ANNULATION O.F') and ofv.flag is null and mmt.inventory_item_id=ofv.id_article
and mmt.attribute2=ofv.numero_lot
and mmt.attribute3=ofv.numero_of
and mmt.source_code='CONSOMMATIONS'
and (mmt.organization_id=399 or mmt.organization_id=397) and 
mmt.transaction_cost is not null 
and mmt.transaction_quantity>0 
and ofv.id_ordre_fabrication_entete=cpo.id_ordre_fabrication_entete
and cpo.id_charge_production=cp.id_charge_production
group by
ofv.numero_of,
ofv.numero_lot,
ofv.id_article,
ofv.code_article,
ofv.description_article,
mmt.transaction_cost,
ofv.QUANTITE_FABRIQUEE,
mmt.transaction_quantity,
ofv.id_ordre_fabrication_entete,
fev.poids_total*NVL(ofv.nombre_operation,1),
ofv.date_creation,
ofv.date_confirmation,
ofv.status,
OFV.CODE_LIGNE,
ofv.status_detail,
ofv.numero_lot,
ofv.duree_lot,
cp.id_charge_production,
cp.nature_charge ,
cp.COEFFICIENT_CHARGE,
cp.coefficient_charge,
ofv.type_conditionnement,
mmt.subinventory_code,
mmt.PRIOR_COST,
mmt.NEW_COST
order by numero_of,code_article

[mnitu]

Ni Dieu ne peut optimiser une requête juste en la regardant donc par voie de conséquence il va falloir donner plus des informations en plus du texte de la requête!
Commençons déjà par que est c'est :MIXAL.GET_QTE_FABRIQUEE, MIXAL.GET_QTE_FABRIQUEE, MIXAL.GET_SALAIRE_LOT et MIXAL.GET_TOTAL_PRODUCTION ?
Est ce que je pense: c'est-à-dire des fonctions PL/SQL ? Si la réponse est oui vous avez déjà un problème!

[ala-sarraj]

oui , c'est quoi le problème? je peux vous communiquer le code si vous voulez

[mnitu]

Non merci!
Bon ces sont des mauvais habitudes mais tentons le coup: prenez votre requête et mettez en commentaire toutes les appel des fonctions PL/SQL similaires. Puis ré-exécutez la requête ainsi obtenu et dit-nous si vous obtenez des temps de réponse raisonnables. Mais ce n'est pas le bon démarche pour ce type de problèmes!

Le problème: dans la base Oracle il existent deux moteurs: le moteur SQL et celui en PL/SQL. Quand votre requête est exécutée c'est le moteur SQL qui la gère. Pour chaque appel de fonction PL/SQL contenu par votre requête le moteur SQL passe la main au moteur PL/SQL et ce passage est accompagné d'une changement du mode de représentation des données qui est appelé changement de contexte. Et cela se fait pour chaque enregistrement. Le changement de contexte prends un laps de temps qui est assez court pour un appel mais qui peut devenir important quant le nombre des enregistrement est assez important parce que les temps de chaque appel se cumulent. De plus si vos fonctions PL/SQL interrogent la base il se peut que des autres bugs subtils sont en attente de hisser leur moche tête.

Et cela montre bien que le démarche que je vous propose est une mauvais habitude: si vraiment la requête traine à cause des appels PL/SQL, bingo! je suis un magicien! Si ce n'est pas le cas quelle est le prochain coup a tenter ?

Le bon démarche est assez simple:

1. Collectez les données opérationnelles de l'exécution de la requête via une trace étendue ou autre modalité
2. Analysez et interprétez ces données pour comprendre où le temps passe. Vous pouvez éventuellement utiliser un outil de profilage pour cela.
3. Quand vous avez compris quelle est le travail inutile fait par la requête vous pouvez envisager les solutions adéquates ainsi que leur efficacité d'optimisation.
4. Mettez les en œuvre et testez le.

Répétez ces étapes jusqu'à l'attente de votre cible d'optimisation sinon cela est une activité sans cesse renouvelable.

Ce qui est difficile dans l'optimisation ce n'est pas le démarche mais les étapes 2) et 3) parce qu'elles impliquent une connaissance approfondie des mécanismes d'Oracle pour ne pas se tromper de cible d'optimisation. C'est pour cela qu'il n'y a pas de tutoriel simple.

[skuatamad]

Juste une remarque, votre requête ne semble pas utiliser de fonction d’agrégation
=> supprimez la clause GROUP BY d'autant que vous avez déjà un DISTINCT

[ala-sarraj]

je vous remercie infiniment pour votre réponse !
bah c'est vrai lorsque j'exécute la requête sans faire appel aux fonctions PL/SQL ,j'aurais des temps de réponse raisonnables.
bon je vais essayer de réécrire la requête autrement ,
merci encore une fois pour votre aide