IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Optimisation de requête : recherche boule de crystal


Sujet :

SQL Oracle

  1. #1
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 197
    Billets dans le blog
    1
    Par défaut Optimisation de requête : recherche boule de crystal
    Bonjour,

    Depuis plusieurs jours, je m'arrache le peu de cheveux qu'il me reste afin d'optimiser une requête.

    Le contexte :
    - Base d'un ERP : impossible de modifier le modèle des données, ni les index
    - Requête basée sur une vue : je n'ai accès qu'à la vue, je ne peux pas modifier la requête "englobante"
    - Lors de la mise en production, le traitement durait 40 minutes
    - Dès la semaine suivante, il est passé en dessous de 30 minutes
    - Depuis le milieu de semaine dernière, il est passé à 2h40, alors qu'absolument rien n'a changé ni au niveau paramétrage, ni au niveau volumétrie
    - Sur la base de DEV, qui est une copie récente de la PROD (moins de 1 semaine), le plan d'exécution proposé est deux fois plus long qu'en PROD

    La vue à optimiser :
    Code sql : 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
     
    CREATE OR REPLACE VIEW SOC1.WV_QIFM30 (CODSOC, LIB1, LIB2, LIB3, LIB4, LIB5, LIB6, LIB7, LIB8, LIB9, LIB10, LIB11, LIB12, LIB13, LIB14, LIB15, LIB16, LIB17, LIB18, LIB19, LIB20, DAT1, DAT2, DAT3, DAT4, DAT5, NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09, NUM10, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20) AS 
    select cde.codsoc,
     cde.achvte,cde.typeve,
     decode(
            UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16),
                                           '000'||substr(tie.sigtie,2,5)),
     cde.codeta,tie.codett,
     SUBSTR(decode(nvl(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, decode(substr(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8), /*liv.datliv*/
    (
    select max(liv.datliv)
    from eve liv
    where liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve
    ),
     cdp.codpro,' ',pro.codzn10,
     cde.codctg,'XXX',
     ' ',' ',' ',
     ' ',' ',' ',' ',' ',
     /*liv.datliv*/
    (
    select max(liv.datliv)
    from eve liv
    where liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve
    )
     ,' ',' ',' ',' ',
     cde.numeve,
     nvl(to_number(trim(evt.codzn5)), cdp.qtecde),
     0,0,0,
     0,0,0,0,0,
     pru.coefuv,0,0,0,0,
     0,0,0,0,0,
     0,0,0,0,0,
     0,0,0,0,0
    from 
    eve cde 
    inner join tie on tie.codsoc = cde.codsoc and tie.typtie = 'CLI' and tie.sigtie = cde.sigtie
    inner join evp cdp on cdp.codsoc = cde.codsoc and cdp.achvte = cde.achvte and cdp.typeve = cde.typeve and cdp.numeve = cde.numeve
    inner join pro on pro.codsoc = cdp.codsoc and pro.codpro = cdp.codpro
    inner join prl on prl.codsoc = pro.codsoc and prl.codpro = pro.codpro and prl.typtie  = ' ' and prl.sigtie = ' ' and prl.codva1= '00' and prl.codva2 = SUBSTR(pro.codpro,8,2) and prl.cntcod = ' '
    inner join pru on pru.codsoc = prl.codsoc and pru.codpro = prl.codpro and pru.coduni = prl.coduni1
    inner join evt on evt.codsoc = cdp.codsoc and evt.achvte = cdp.achvte and evt.typeve = cdp.typeve and evt.numeve = cdp.numeve and evt.numpos = cdp.numpos and evt.numlig = 0 and evt.numspo = 0 and evt.numblo = 0
    left outer join prc on prc.codsoc = cde.codsoc and prc.typtie = 'CLI' and prc.sigfou = tie.sigtie and prc.codpro = pro.codpro
    left outer join pro p2 on p2.codsoc = prc.codsoc and p2.codpro = prc.codzn2
    where cde.codctg not in ('DE','GR','EC');

    Elle est utilisée, pour les tests, avec la requête :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    select count(*)  from wv_qifm30 where codsoc = 100 and lib1 = 'V' and lib2 = 'CDE' and dat1 BETWEEN '20120215' and '20120228' and lib8 = '212690000' and  (lib4 = 'V' or lib4 = 'S')  and  (lib5 = 'ME1' or lib5 = 'ME2');

    Sur la DEV, le coût estimé est de 405.

    La piste que j'ai choisi pour optimiser, c'est le remplacement du double sous-select dans les champs par un max() et l'ajout d'une jointure.

    On a donc à la place :
    Code sql : 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
     
    CREATE OR REPLACE VIEW SOC1.WV_QIFM30_BIS (CODSOC, LIB1, LIB2, LIB3, LIB4, LIB5, LIB6, LIB7, LIB8, LIB9, LIB10, LIB11, LIB12, LIB13, LIB14, LIB15, LIB16, LIB17, LIB18, LIB19, LIB20, DAT1, DAT2, DAT3, DAT4, DAT5, NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09, NUM10, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20) AS 
    select cde.codsoc,
     cde.achvte,cde.typeve,
     decode(
            UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16),
                                           '000'||substr(tie.sigtie,2,5)),
     cde.codeta,tie.codett,
     SUBSTR(decode(nvl(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, decode(substr(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8), /*liv.datliv*/
    nvl(max(liv.datliv), ' '),
     cdp.codpro,' ',pro.codzn10,
     cde.codctg,'XXX',
     ' ',' ',' ',
     ' ',' ',' ',' ',' ',
     /*liv.datliv*/
    nvl(max(liv.datliv), ' ')
    ,' ',' ',' ',' ',
     cde.numeve,
     nvl(to_number(trim(evt.codzn5)), cdp.qtecde),
     0,0,0,
     0,0,0,0,0,
     pru.coefuv,0,0,0,0,
     0,0,0,0,0,
     0,0,0,0,0,
     0,0,0,0,0
    from 
    eve cde 
    inner join tie on tie.codsoc = cde.codsoc and tie.typtie = 'CLI' and tie.sigtie = cde.sigtie
    inner join evp cdp on cdp.codsoc = cde.codsoc and cdp.achvte = cde.achvte and cdp.typeve = cde.typeve and cdp.numeve = cde.numeve
    inner join pro on pro.codsoc = cdp.codsoc and pro.codpro = cdp.codpro
    inner join prl on prl.codsoc = pro.codsoc and prl.codpro = pro.codpro and prl.typtie  = ' ' and prl.sigtie = ' ' and prl.codva1= '00' and prl.codva2 = SUBSTR(pro.codpro,8,2) and prl.cntcod = ' '
    inner join pru on pru.codsoc = prl.codsoc and pru.codpro = prl.codpro and pru.coduni = prl.coduni1
    inner join evt on evt.codsoc = cdp.codsoc and evt.achvte = cdp.achvte and evt.typeve = cdp.typeve and evt.numeve = cdp.numeve and evt.numpos = cdp.numpos and evt.numlig = 0 and evt.numspo = 0 and evt.numblo = 0
    left outer join prc on prc.codsoc = cde.codsoc and prc.typtie = 'CLI' and prc.sigfou = tie.sigtie and prc.codpro = pro.codpro
    left outer join pro p2 on p2.codsoc = prc.codsoc and p2.codpro = prc.codzn2
    left outer join eve liv on liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve
    where cde.codctg not in ('DE','GR','EC')
    group by cde.codsoc,
    cde.achvte,cde.typeve,
     decode(
            UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16),
                                           '000'||substr(tie.sigtie,2,5)),
     cde.codeta,tie.codett,
     SUBSTR(decode(nvl(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, decode(substr(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8),
     cdp.codpro,' ',pro.codzn10,
     cde.codctg,
     cde.numeve,
     nvl(to_number(trim(evt.codzn5)), cdp.qtecde),
     pru.coefuv;

    Je l'appelle avec la requête suivante :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    select count(*) from wv_qifm30_bis where codsoc = 100 and lib1 = 'V' and lib2 = 'CDE' and dat1 BETWEEN '20120215' and '20120228' and lib8 LIKE '212690000' and  (lib4 = 'V' or lib4 = 'S')  and  (lib5 = 'ME1' or lib5 = 'ME2');
    Coût estimé en DEV 401
    => Chouette me dis-je, c'est pas grand chose, mais c'est toujours ça de gagné !

    En revanche, quand je lance la première requête, elle donne 101 lignes pour 10 minutes et 20 secondes de traitement.
    Et quand le lance la seconde requête, elle donne bien 101 lignes, mais passe à 12 minutes et 20 secondes de traitement.

    Le serveur de DEV étant virtualisé, je ne peux pas garantir l'absence de charge. Mais j'ai fais tourner plusieurs fois les requêtes, et c'est le même ordre de grandeur à chaque fois.

    Et sur la production, les coûts estimés sont de 223 pour les deux requêtes. Je n'ai pas encore testé combien de temps ça durait.

    J'aimerais donc comprendre :
    - Comment il est possible que la même requête ait un plan d'exécution totalement différent entre PROD et DEV, alors que les bases sont identiques.
    - Comment une requête ayant un plan d'exécution moins coûteux puisse être plus lente
    - Et surtout, comment un left outer join avec un agrégat peut être plus lent qu'un sous-select de bourrin directement dans la clause select (réputé pourtant comme étant particulièrement lent !)

    Franchement, Oracle porte bien son nom... Demain je vais sacrifier un poulet sur mon clavier, j'y verrai peut-être plus clair dans ses entrailles !

  2. #2
    Expert confirmé Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    ...
    J'aimerais donc comprendre :
    - Comment il est possible que la même requête ait un plan d'exécution totalement différent entre PROD et DEV, alors que les bases sont identiques.
    - Comment une requête ayant un plan d'exécution moins coûteux puisse être plus lente
    - Et surtout, comment un left outer join avec un agrégat peut être plus lent qu'un sous-select de bourrin directement dans la clause select (réputé pourtant comme étant particulièrement lent !)

    Franchement, Oracle porte bien son nom... Demain je vais sacrifier un poulet sur mon clavier, j'y verrai peut-être plus clair dans ses entrailles !
    Il y a un certain nombre des explications rationnelles nulle besoin de sacrifier vos poulles
    • Divers différences dans le paramétrage, statistiques systèmes, statistiques objets entre les deux bases, voir quelques phénomènes comme l’introspection des variables de liaison (bind variable peeking)
    • Le calcul du coût se base sur des estimations et donc ne vaut pas l’exécution de la requête. Pour ne parler que si vous ne fait pas correctement les choses vous comparez des poules avec des dindons facilement
    • C’est un mythe que les requêtes scalaires sont toujours moins performantes. En fait Oracle utilise un cache pour ne exécuter les requêtes scalaires que si c'est nécessaire et donc dans certaines conditions elles peuvent être bien plus performantes.

    Il va falloir mettre de l’ordre dans votre tentative d’optimisation si vous voulez vous en sortir.

  3. #3
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 197
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par mnitu Voir le message
    • C’est un mythe que les requêtes scalaires sont toujours moins performantes. En fait Oracle utilise un cache pour ne exécuter les requêtes scalaires que si c'est nécessaire et donc dans certaines conditions elles peuvent être bien plus performantes.
    Dans mon cas, le sous-select est bien exécuté à chaque ligne, puisqu'il a forcément une valeur pour chaque ligne retournée : en effet, il s'agit du champ "dat1" de la vue, qui est ensuite filtré avec un between sur la requêtes "englobante". Oracle est donc obligé de se pallucher la requête pour toutes les lignes.

    Tiens, ça me fait penser qu'au lieu d'utiliser un left outer join, je peux utiliser un inner join et virer le nvl() autour du max ! Vais voir si ça change quelque chose...

    Citation Envoyé par mnitu Voir le message
    Il va falloir mettre de l’ordre dans votre tentative d’optimisation si vous voulez vous en sortir.
    Bah je vois pas ce que je peux faire de plus (mise à par le coup du inner join, m'a l'air pas mal lui).
    En effet, il s'agit d'une extraction de données, donc toutes les tables lues sont nécessaires, puisqu'elles permettent de récupérer les données en question.
    La chiée de decode() qui me sortent par les yeux, idem...
    Quand aux jointures, elles se font systématiquement sur des index, et la plupart du temps sur des index uniques (il n'y a pas de PK définies dans la base)

  4. #4
    Expert confirmé
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 197
    Billets dans le blog
    1
    Par défaut
    Bon, ben inner ou outer... même donne

    J'ai gagné deux minutes sur la version optimisée

    Mais j'ai gagné aussi deux minutes sur la version que j'ai pas touché

    Y'en a marre aussi de bosser sur un serveur virtualisé tout merdique.
    Je suis pourtant absolument seul sur ce serveur, y'a pas un seul traitement qui tourne... Mais non, d'une heure à l'autre le serveur est plus ou moins lent... Pfffff...

    Du coup j'ai toujours un écart de l'ordre de 2 minutes entre mes deux requêtes...
    Bon, demain je teste avec des sous-select partout, peut-être que finalement l'optimiseur fait son travail quand on écrit des requêtes pourries ? Plus ça va, plus j'en suis convaincu... Comme le jour où j'ai réécrit avec des "inner join" une requête où les tables étaient énumérées à la suite... 4 fois plus lent le résultat... tout ça parce que l'ordre des tables dans le from permet à Oracle d'utiliser un plan d'exécution différent, chose que l'optimiseur est incapable de faire avec la syntaxe standard ! Pffffff...

  5. #5
    Expert confirmé
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 822
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 822
    Billets dans le blog
    1
    Par défaut
    Bonjour,

    je vais essayer d'être plus court que sur http://www.developpez.net/forums/d11...is-suis-lourd/

    Le coût estimé n'a pas de sens car ce n'est qu'une estimation pour Oracle.
    Un coût de 405 c'est faible: le coût est une approximation du nombre d'i/o à faire. Dans les 400 c'est qu'il s'attend à faire moins de quelques secondes. Donc il y a probablement une erreur d'estimation.
    Il faut regarder le plan d'exécution complet.

    Le temps d'exécution n'a pas de sens sur un test. Il y a des données qui restent en cache et des plans qui sont parfois optimisés d'une requête à l'autre.
    Il faut regarder les statistiques (autotrace sous sqlplus par exemple)

    Avec statistics_level=all et dbms_xplan il assez facile de comparer les estimations avec le réel. SQLDeveloper offre une belle interface pour cela.

    - Base d'un ERP : impossible de modifier le modèle des données, ni les index
    Il est clair que l'appli est pourrie, à en voir:
    - Les LIB1, LIB2, LIB3, LIB4, LIB5, LIB6...
    - Les select max sur lesquels vont s'appliquer des prédicats
    - les dates au format chaîne de caractère
    - les ' '
    - les to_number sans préciser le format du nombre
    etc...

    Faut pas espérer des miracles

    Cordialement,
    Franck.

  6. #6
    Membre Expert
    Inscrit en
    Août 2009
    Messages
    1 073
    Détails du profil
    Informations forums :
    Inscription : Août 2009
    Messages : 1 073
    Par défaut
    Citation Envoyé par StringBuilder Voir le message
    - Comment il est possible que la même requête ait un plan d'exécution totalement différent entre PROD et DEV, alors que les bases sont identiques.
    Différences de statistiques au moment de la génération du plan.

    - Comment une requête ayant un plan d'exécution moins coûteux puisse être plus lente
    Le "coût" est une donnée estimée, ne pas s'y fier.

    - Et surtout, comment un left outer join avec un agrégat peut être plus lent qu'un sous-select de bourrin directement dans la clause select (réputé pourtant comme étant particulièrement lent !)
    L'aggrégation sur l'ensemble des résultats n'est forcément brillant non plus. Selon les volumétries mises en jeu - mais là encore, comme le signalent mnitu et pachot : il faut voir au cas par cas - il peut être plus intéressant de réaliser l'aggrégation avant la jointure.

    Franchement, Oracle porte bien son nom... Demain je vais sacrifier un poulet sur mon clavier, j'y verrai peut-être plus clair dans ses entrailles !
    Ça serait du gachis ! Alors que rôtis, mhhh

Discussions similaires

  1. [11gR2] Recherche conseil d'optimisation de requête : index possible
    Par prgasp77 dans le forum Oracle
    Réponses: 3
    Dernier message: 15/01/2015, 17h20
  2. Recherche d'informations pour optimiser une requête
    Par zaza78 dans le forum Administration
    Réponses: 2
    Dernier message: 20/08/2010, 15h00
  3. Optimiser une requête de recherche de date
    Par webtheque dans le forum Langage SQL
    Réponses: 2
    Dernier message: 29/08/2008, 22h48
  4. Optimiser une requête SQL d'un moteur de recherche
    Par kibodio dans le forum Langage SQL
    Réponses: 2
    Dernier message: 06/03/2005, 20h55
  5. Optimisation de requête
    Par olivierN dans le forum SQL
    Réponses: 10
    Dernier message: 16/12/2003, 10h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo