Comment optimiser requête SQL avec création Index

Version imprimable

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

10/12/2007, 15h23
schumi101

Le bon explain plan

Voici l'explain plan correct

Désolé pour le désagrément

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 ?

10/12/2007, 16h00
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
10/12/2007, 16h07
schumi101

1 pièce(s) jointe(s)

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)
10/12/2007, 16h13
orafrance

Citation:

Envoyé par schumi101

1 sur fk_gba_operateur

à ta place j'essayerai d'ajouter la colonne BAREME dedans :)
10/12/2007, 16h24
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 ?
10/12/2007, 16h44
schumi101

1 pièce(s) jointe(s)

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
10/12/2007, 17h04
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 :?
10/12/2007, 17h09
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
10/12/2007, 17h16
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 :?
10/12/2007, 17h57
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??
10/12/2007, 18h48
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'
10/12/2007, 21h40
mnitu
Citation:

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 ?

Citation:

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)
11/12/2007, 09h18
schumi101

1 pièce(s) jointe(s)

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
11/12/2007, 09h22
schumi101

1 pièce(s) jointe(s)

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.
11/12/2007, 09h44
orafrance

Citation:

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 :king:

PAr contre, en terme de méthodologie c'est très mauvais :mrgreen: 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 ? ;)

Citation:

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 :mrgreen:
11/12/2007, 09h47
orafrance
Citation:
Envoyé par mnitu

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 ?
8O pourquoi pas DBMS_STATS, ANALYZE est moins complet :?

Citation:

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 :ange:
11/12/2007, 10h03
schumi101

Voici l'explain plan avec un index double comme tu me l'avais suggéré hier sur la table p_bareme
11/12/2007, 10h07
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
11/12/2007, 10h11
schumi101

Cependant en ajoutant un index sur l'attribut bareme j'ai un coût total de 15.
11/12/2007, 10h16
orafrance

Citation:

Envoyé par schumi101

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

8O c'est exactement la même requête ? Ca vient d'où cette concaténation ? :koi:

Et en temps de réponse ça donne quoi ?
11/12/2007, 10h22
schumi101

C'est exactement la même requête c sûr où mon toad a eu du mal lol

Si je fait un count pour le nombre de ligne retournée, j'ai 2 minutes avec le double index

J'ai 1 ou 2 secondes avec l'index univque de coût 15.

Par contre avec Bo j'ai 2 minute pour exécuter la requête avec le double index et 1 minute avec l'index unique

Voila les différences
11/12/2007, 10h36
orafrance

la condition sur la 1° colonne n'est probablement pas suffisamment restrictive c'est pour ça ;)

Bah, 2 secondes, on aura du mal à faire mieux il me semble :mrgreen:
11/12/2007, 11h08
schumi101

Merci pour l'aide, avec tout ce que vous m'avez dit je vais essayer de prendre le meilleur et d'améliorer ces temps de réponses

Si quelqu'un a encore une idée, ne pas hésiter

Cordialement
11/12/2007, 21h28
mnitu

Citation:

Envoyé par orafrance

8O pourquoi pas DBMS_STATS, ANALYZE est moins complet :?

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 :ange:

Parce que c'est Oracle 8 et Schumi dit "prenez mois doucement". Sinon j'utilise dbms_stats.

Personnellement je préfère me concentrer sur les nombre des lignes manipulées. Quand on lit 45 000 lignes pour garder 2 on consomme des ressources pour rien. Bon, à chaque un son goût.
Cella étant dit je n'ai pas compris si la même requête s'exécute maintenat en 2 seconde ou pas (mais c'est pas grave).