Comment optimiser requête SQL avec création Index [Résolu]

[schumi101]

Bonjour,

Afin de satisfaire une demande client, j'essaye d'optimiser une requête SQL. Cette requête ramène 12 lignes seulement mais prend environ 3 minutes pour l'exécution (ce qui est très long car la volumétrie des tables va augmenter régulièrement)

Les plus grosse tables de ma requête sont : (ceci est une indication)

P_Tarif_com avec plus de 600 000 lignes
P_Tarif_palier avec plus de 600 000 lignes
P_assoc_bareme_prestation avec plus de 80000 lignes
P_bareme avec 15000 lignes

Ma requête est la suivante :

je met en gras dès qu'il y a un index:

Code :

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
SELECT   
  P_BAREME.FK_GBA_OPERATEUR,
  P_BAREME.BAREME,
  P_ASSOC_BAREME_PRESTATION.FK_PRESTATION,
  P_TYPE_TAXE.TYPE_TAXE,
  P_OPERATEUR_EMETTEUR.ID_OP_EMET,
  P_TYPE_ZONE.ID_TYPE_ZONE,
  to_date(to_char(P_TARIF_COM.DATE_DEBUT,'dd/mm/yyyy'),'dd/mm/yyyy'),
  to_date(to_char(P_TARIF_COM.DATE_FIN,'dd/mm/yyyy'),'dd/mm/yyyy'),
  P_MONNAIE.MONNAIE,
  P_TARIF_COM.MONTANT_MINIMUM,
  P_TARIF_COM.SURTAXE,
  P_TARIF_COM.CREDIT_TEMPS,
  P_TARIF_COM.CREDIT_TEMPS_COUT,
  P_TARIF_COM.VALO_DERNIER_PALIER,
  P_TARIF_PALIER.DEBUT,
  P_TARIF_PALIER.TARIF,
  P_TARIF_PALIER.COUT_FIXE,
  P_UNITE.UNITE,
  P_TARIF_PALIER.UNITE_INDIVISIBLE
FROM
  P_BAREME,
  P_ASSOC_BAREME_PRESTATION,
  P_TYPE_TAXE,
  P_OPERATEUR_EMETTEUR,
  P_TYPE_ZONE,
  P_TARIF_COM,
  P_MONNAIE,
  P_TARIF_PALIER,
  P_UNITE
WHERE
  ( P_ASSOC_BAREME_PRESTATION.FK_BAR_NUMERO_BAREME=P_BAREME.NUMERO_BAREME  )
  AND  ( P_TARIF_COM.FK_TYPE_ZONE=P_TYPE_ZONE.ID_TYPE_ZONE  )
  AND  ( P_TARIF_COM.FK_MONNAIE=P_MONNAIE.MONNAIE  )
  AND  ( P_OPERATEUR_EMETTEUR.ID_OP_EMET=P_TARIF_COM.FK_OP_EMET  )
  AND  ( P_TARIF_COM.FK_TYPE_TAXE=P_TYPE_TAXE.TYPE_TAXE  )
  AND  ( P_TARIF_PALIER.FK_NUMERO_TARIF=P_TARIF_COM.NUMERO_TARIF_COM  )
  AND  ( P_TARIF_PALIER.FK_UNITEID=P_UNITE.UNITE  )
  AND  ( P_ASSOC_BAREME_PRESTATION.DATE_DEBUT=P_TARIF_COM.FK_ABP_DATE_DEBUT and P_ASSOC_BAREME_PRESTATION.FK_BAR_NUMERO_BAREME=P_TARIF_COM.FK_ABP_NUMERO_BAREME and P_ASSOC_BAREME_PRESTATION.FK_PRESTATION=P_TARIF_COM.FK_ABP_PRESTATION  )
  AND  (
  to_date(to_char(P_TARIF_COM.DATE_DEBUT,'dd/mm/yyyy'),'dd/mm/yyyy')  <=  '12/10/2007')
  AND  P_TARIF_PALIER.DEBUT  <=  9999999999999999999
  AND  to_date(to_char(P_TARIF_COM.DATE_FIN,'dd/mm/yyyy'),'dd/mm/yyyy')  >=  '12/10/2007'
  AND  P_BAREME.FK_GBA_OPERATEUR  IN  ('9T','B3G','BYT','COLT','CPTEL','CWF','FFMV2','FREE','FTF','FTLD','FTLDH','FTLDM','FTLDO','FTMRD','MFS','MONAC','OCR','OGPRS','OMMS','OMSP','ORLCF','OVGT','OVT','OWHA','OWIFI','OZB','SFR','SIRIS','SMSC','SMSR','TD','TELE2')
  AND   P_BAREME.BAREME  IN  ('MMSBE')
  AND  P_ASSOC_BAREME_PRESTATION.FK_PRESTATION  IN  ('11C1','11C2','11F','12C1','12C2','12F','13C1','13C2','13F','20C1','20C2','20F','21C1','21C2','21F','TRCE','TRCO','TRF','TVC','TVF','WFC','WIC','WIF','WMMC','WSC','WSMC','WTC','WVC')
  AND  P_TARIF_COM.AUT_IND  IN  ('A')
  AND  P_OPERATEUR_EMETTEUR.ID_OP_EMET  IN ('MODEL','TE')
  AND  P_TYPE_ZONE.ID_TYPE_ZONE  IN  ('EZ','INDET','IZ','T1','T2','T3','TZ')

Désole pour la longueur des clauses where mais c'est pour vous donner une idée du nombre de sélection de chaque clause.
NB: Cett requête est exécuté par businness object, d'où un nombre important de sélection

Voici mon explain plan:
(voir la pièce jointe)

Liste des index:
P_ASSOC_BAREME_prest -> 1index sur 3 champs (fk_bar_numero_bareme,fk_prestation,date_debut)
P_TARIF_COM -> 1 index sur 3 champs
(FK_ABP_NUMERO_BAREME, FK_ABP_PRESTATION, FK_ABP_DATE_DEBUT)
P_tarif_palier -> 1 index sur (debut, fk_numero_tarif)
il y a des index sur toutes les jointures (voir en gras dans le code)

Il n'y a aucun index sur les dates (mais la volumétrie est faible, moins de 50 dates différentes)

Merci de m'éclaircir sur la démarche à suivre pour pouvoir diminuer mon temps de réponse, si cela est possible

Cordialement

[schumi101]

Voici l'explain plan correct

Désolé pour le désagrément

[orafrance]

Pas facile de répondre comme ça

Moi ce qui m'étonne c'est le coût de 81 pour un accès par ROWID. Peut-être bien que tu gagnerais avec un index sur ces 2 colonnes :

Code :

1
2
  AND  P_BAREME.FK_GBA_OPERATEUR  IN  ('9T','B3G','BYT','COLT','CPTEL','CWF','FFMV2','FREE','FTF','FTLD','FTLDH','FTLDM','FTLDO','FTMRD','MFS','MONAC','OCR','OGPRS','OMMS','OMSP','ORLCF','OVGT','OVT','OWHA','OWIFI','OZB','SFR','SIRIS','SMSC','SMSR','TD','TELE2')
  AND   P_BAREME.BAREME  IN  ('MMSBE')

Je suis aussi étonné de voir la colonne FK_GBA_OPERATEUR qui semble être une FK alors qu'elle n'est jointe à aucune table, est-ce normal ?

[schumi101]

C'est normal en ce qui concerne fk_gba_operateur car c'est une base de données d'un infocentre afin de faire des rapports Business Object dessus, donc il n'a pas de contrainte au niveau des foreign key, le contrôle est effectué au moment du peuplement de la base par des scripts (voila pour la petite explication

En ce qui concerne la Table P_bareme, il y a 3 indexs

1 sur le champ numero_bareme (pour la jointure)
1 sur fk_gba_operateur
1 sur fk_gba_groupe_bareme

Peut être que l'optimisation est maximale pour cette requête cependant je n'ai pas encore la compétence pour analyser correctement et interpréter correctement l'explain plan

Merci pour vos suggestions et vos explications



NB: le bon explain plan est le second

[schumi101]

Cela peut être une grande aide, j'ai essayé de mettre en surbrillance les jointures du schéma MPD (il manque certainement 1 table ou 2 table cause manque de place)

[orafrance]

Envoyé par schumi101

1 sur fk_gba_operateur

à ta place j'essayerai d'ajouter la colonne BAREME dedans

[mnitu]

Il faut examiner les accès de type index rang scan. A priori l’index BOINT.PK_P_TARIF_PALIER manipule 575 000 lignes (575K ?). De même BOINT.PK_P_ASSOC_BAREME_PRESTATION 52 000 lignes mais au bout du compte le plan estime qu’il y aura une seule ligne. Il se peut que ces indexes ne sont pas bons pour cette requête.
Exécute une trace SQL et examine le plan réel, est-ce que le nombre des lignes manipulées est du même ordre de grandeur ?
Quelle est la méthode de collecte des statistiques et à quel moment les statistiques sont calculées ? En fin quelle est la version d’Oracle ?

[schumi101]

Voici l'explain table ce qu'elle contient.

Sinon je suis sur du Oracle 8i et je vais être franc avec toi j'ai du mal avec tes 2 derniers points

Exécute une trace SQL et examine le plan réel, est-ce que le nombre des lignes manipulées est du même ordre de grandeur ?
Quelle est la méthode de collecte des statistiques et à quel moment les statistiques sont calculées ? En fin quelle est la version d’Oracle ?

Je suis novice dans la partie optimisation, merci de ta compréhension

[orafrance]

je persiste à penser que le problème vient de P_BAREME qui indique un coût élevé pour récupérer seulement 3 valeurs, comparer au cout de 3 pour les 575K valeurs

[schumi101]

Oki, je vais donc créer un index à double attribut sur la table p_Bareme;

Question annexe, faut-il modifier l'index existant en rajoutant le champ barême
ou garder cet index et rajouter un index avec le BAREME + FK_GBA_OPERATEUR

Cette demande soulève une question;
Si j'utilise une jointure sur 1 attribut mais que on ne dispose que d'index comportant plusieurs attribut est-ce optimal??

Quel est la meilleure solution si ma requête utilise que l'attribut toto

Index1(toto)
Index2(toto,titi)

Ou
Index1(toto,titi)

Merci

[orafrance]

A priori vaut mieux modifier l'index existant (à moins que le couple soit rarement utilisé) mais bon, on va avoir du mal à répondre sans connaitre la sélectivité des données et l'utilisation de ces colonnes dans les requêtes

[schumi101]

Pour information, mon rapport Business Object passe de 2 minutes à 1 minutes avec la solution de l'index double


D'après vous est-ce un temps raisonnable pour ce type de requête??

[Michel SALAIS]

La condition suivante et toutes les doubles conversions sont à revoir:

Code :

1
2
 
to_date(to_char(P_TARIF_COM.DATE_FIN,'dd/mm/yyyy'),'dd/mm/yyyy')  >=  '12/10/2007'

Il me semble que la colonne date_fin est de type DATE alors à quoi sert la conversion vers une chaine de caractères?

Si le but est juste de tronquer la date alors il y a d'autres moyens. d'un autre côté, si la partie horaire n'est pas pertinente dans les données alors il faut déjà tronquer à l'insertion des données et en suite fiaire tout simplement

Code :

1
2
 
P_TARIF_COM.DATE_FIN  >=  date'2007-10-12'

[mnitu]

schumi101
D'après vous est-ce un temps raisonnable pour ce type de requête??

Si ce temps de réponse te convient il est bon, si non probablement non!
Peut tu essayer de analyser toutes les tables concernée et exécuter ensuite la requête ?

Code :

1
2
3
4
5
 
analyze TABLE <table> compute statistics 
FOR TABLE
FOR ALL indexes
FOR ALL indexed COLUMNS;

Peut tu afficher le nouveau (!?) plan ?

orafrance
je persiste à penser que le problème vient de P_BAREME qui indique un coût élevé pour récupérer seulement 3 valeurs, comparer au cout de 3 pour les 575K valeurs

Ce n'est pas 3 lignes qui sortent c'est ce que l'optimiseur calcule qu'il va sortir. Le coût est calculé en suivant un algorithme qui prend en compte parmi autres choses les statistiques disponible. Dire que un coût est élevé dans ces conditions c'est un peu hasardé.
Des explications se trouvent ici (Papers)

[schumi101]

Bonjour,


J'ai enlevé l'index du modérateur orafrance afin de rester dans le même état que précédemment. De plus j'ai effectué un analyse de toutes les tables et enfin j'ai refait un explain plan qui est celui - ci

A titre d'information je vais vous joindre l'explain plan avec un index supplémentaire sur l'attribut bareme de la table p_bareme qui améliore le coût

Merci pour votre aide

[schumi101]

Voici l'explain plan avec un index supplémentaire sur bareme de la table p_bareme


Je tiens à préciser que je calcul le temps d'exécution de la requête en la lançant avec business object, car le client va tester à partir de cet outil.

[orafrance]

Envoyé par schumi101

D'après vous est-ce un temps raisonnable pour ce type de requête??

Au vu de la volumétrie, ça parait pas mal

PAr contre, en terme de méthodologie c'est très mauvais En effet, la 1° chose qu'on apprend en formation tuning, c'est qu'il faut un objectif. Tu peux pas dire : "j'optimise au maximum"... quand savoir que tu peux t'arrêter dans ce cas ?

Envoyé par schumi101

A titre d'information je vais vous joindre l'explain plan avec un index supplémentaire sur l'attribut bareme de la table p_bareme qui améliore le coût

Attention quand même, l'ajout d'une colonne à un index risque moins de perturber les autres requêtes... en créant un nouvel index tu peut changer complètement le comportement des requêtes qui utilisent P_BAREME... parfois en bien mais souvent en mal

[orafrance]

Envoyé par mnitu

Code :

Peut tu afficher le nouveau (!?) plan ?

pourquoi pas DBMS_STATS, ANALYZE est moins complet

Envoyé par mnitu

Ce n'est pas 3 lignes qui sortent c'est ce que l'optimiseur calcule qu'il va sortir. Le coût est calculé en suivant un algorithme qui prend en compte parmi autres choses les statistiques disponible. Dire que un coût est élevé dans ces conditions c'est un peu hasardé.

Je sais de toute façon, ce n'est qu'un indicateur, mais personnellement j'ai souvent obtenu de bons résultats en me concentrant sur les jointures qui montrent un cout plus élevé que les autres. Bien entendu, j'aurais pas les prétention de trouver LA solution du 1° coup sans être sur site

[schumi101]

Voici l'explain plan avec un index double comme tu me l'avais suggéré hier sur la table p_bareme

[schumi101]

Le cout indique sur la première ligne est extrêmement élevé avec 322. Ceci est du qu'il y a niveau de la hiérarchie plusieurs nested loops qui effectue le même parcours, comme si la requête faisait un produit cartésien