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

PL/SQL Oracle Discussion :

Optimisation de procédure


Sujet :

PL/SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Décembre 2006
    Messages
    28
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2006
    Messages : 28
    Par défaut Optimisation de procédure
    Bonjour à tous.

    J'ai besoin de votre aide sur une procédure qui met un certain temps à s'exécuter.

    Je pense avoir repérer l'instruction qui pose problème :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    SELECT
      MAX(VOL.JOUR), MIN(VOL.JOUR) INTO LD$MAXDATE, LD$MINDATE 
    FROM 
      RF_VOLUMEPREPREAL VOL
    WHERE 
      VOL.CODE_ETABLISSEMENT = curETB.CODE
      AND VOL.CIRCUITPREPARATION = curCP.CODE
      AND VOL.POINTDEVENTE = curPDV.CODE
      AND VOL.JOUR > LD$MINDATE
      AND VOL.REFERENTIEL = LN$IDREF;
    Puis :

    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
     
    INSERT INTO RF_VOLUMEPREPREAL
    (CIRCUITPREPARATION, POINTDEVENTE, REFERENTIEL, UO,   CODE_ETABLISSEMENT, PROG_WRITER, JOUR)
     
    SELECT curCP.CODE, curPDV.CODE, LN$IDREF, LN$VALEUR, curETB.CODE, LC$TAG_UPDATABLE, JOUR.DATE_CALENDRIER 
     
    FROM RF_JOUR JOUR
    WHERE 
      JOUR.DATE_CALENDRIER NOT IN(
         SELECT VOL.JOUR FROM RF_VOLUMEPREPREAL VOL
         WHERE VOL.CODE_ETABLISSEMENT = curETB.CODE
         AND VOL.CIRCUITPREPARATION = curCP.CODE
         AND VOL.POINTDEVENTE = curPDV.CODE
         AND VOL.JOUR BETWEEN LD$MINDATE AND LD$MAXDATE
         AND VOL.REFERENTIEL = LN$IDREF)
    AND JOUR.DATE_CALENDRIER BETWEEN LD$MINDATE AND LD$MAXDATE;
    Je vous explique un peu la philosophie de la chose :
    La table RF_VOLUMEPREPREAL contient un grand nombre d'enregistrements (environ 10 millions). Elle contient des UO par jour. Or, tous les jours ne sont pas renseignés.
    Ma procédure consiste donc à "boucher les trous" par des 0 entre les dates retournées par la première requête.
    Je crois que le problème vient du fait que je fais un insert sur un table qui est elle même interrogée par le select...

    Merci de votre aide.

  2. #2
    Membre expérimenté
    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    178
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations forums :
    Inscription : Janvier 2006
    Messages : 178
    Par défaut
    En général, Oracle n'aime pas les NOT IN ... Et si on essayait un NOT EXISTS ?

    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
    INSERT INTO RF_VOLUMEPREPREAL
    (CIRCUITPREPARATION, POINTDEVENTE, REFERENTIEL, UO,   CODE_ETABLISSEMENT, PROG_WRITER, JOUR)
     
    SELECT curCP.CODE, curPDV.CODE, LN$IDREF, LN$VALEUR, curETB.CODE, LC$TAG_UPDATABLE, JOUR.DATE_CALENDRIER 
     
    FROM RF_JOUR JOUR
    WHERE NOT EXISTS(
         SELECT NULL 
         FROM RF_VOLUMEPREPREAL VOL
         WHERE VOL.JOUR = JOUR.DATE_CALENDRIER
         AND VOL.CODE_ETABLISSEMENT = curETB.CODE
         AND VOL.CIRCUITPREPARATION = curCP.CODE
         AND VOL.POINTDEVENTE = curPDV.CODE
         AND VOL.JOUR BETWEEN LD$MINDATE AND LD$MAXDATE
         AND VOL.REFERENTIEL = LN$IDREF)
    AND JOUR.DATE_CALENDRIER BETWEEN LD$MINDATE AND LD$MAXDATE;
    Si ça ne suffit pas, il faudra peut-être ajouter des indexes, pourriez-vous afficher le plan d'exécution des 2 versions ? (par exemple en lançant un EXPLAIN PLAN FOR SELECT ... ou bien en lançant un EXPLAIN depuis TOAD ou SQL Developer)

  3. #3
    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 rbaraer Voir le message
    En général, Oracle n'aime pas les NOT IN ... Et si on essayait un NOT EXISTS ?
    ...
    Salut Rbaraer,

    Ce genre de considérations non suivi par des explications et surtout des exemples qui montre quand cella est vrai ne servent qu’a engendrer des mythes.

  4. #4
    Membre expérimenté
    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    178
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations forums :
    Inscription : Janvier 2006
    Messages : 178
    Par défaut
    Citation Envoyé par mnitu Voir le message
    Salut Rbaraer,

    Ce genre de considérations non suivi par des explications et surtout des exemples qui montre quand cella est vrai ne servent qu’a engendrer des mythes.
    Je suis d'accord et je suis également contre les règles "immuables" et les "mythes" sur les SGBD. J'ai mal tourné ma phrase.

    Cela ne s'appuie effectivement que sur mon expérience personnelle (sous Oracle 10gR1 et R2). Dans un cas comme celui-ci, il est bon d'essayer le NOT IN et le NOT EXISTS et de les comparer. De ma propre expérience, Oracle a mieux géré le NOT EXISTS de l'ordre de 9 fois sur 10, je préfère donc maintenant commencer par le NOT EXISTS et me tourner vers le NOT IN si je rencontre un souci, plutôt que l'inverse .

    Maintenant cela dépend bien entendu de la requête, de la version d'Oracle, des données, des indexes...

    D'une manière générale quand une requête rencontre des problèmes de performance, il est bon d'essayer d'autres façons de l'écrire (tout en analysant le plan d'exécution et en indexant mieux si nécessaire).

    Je reformule :

    Et si on essayait un NOT EXISTS ? (tout court )

  5. #5
    Membre averti
    Profil pro
    Inscrit en
    Décembre 2006
    Messages
    28
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2006
    Messages : 28
    Par défaut
    Merci beaucoup pour la réponse, je commence à désespérer avec mes problèmes de performances (eh oui, ce n'est pas le seul, new topic coming soon )

    Voici le EXPLAIN PLAN pour le NOT IN :
    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
     
    --------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
    | Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                           
    --------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
    |   0 | SELECT STATEMENT             |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
    |*  1 |  HASH JOIN RIGHT ANTI        |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
    |*  2 |   TABLE ACCESS BY INDEX ROWID| RF_VOLUMEPREPREAL |     1 |    46 |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
    |*  3 |    INDEX RANGE SCAN          | SYS_C0013284      |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
    |*  4 |   INDEX RANGE SCAN           | SYS_C0013202      |   567 |  4536 |     3   (0)| 00:00:01 |                                                                                                                                                                                                           
    --------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
     
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
     
       1 - access("JOUR"."DATE_CALENDRIER"="VOL"."JOUR")                                                                                                                                                                                                                                                         
       2 - filter("VOL"."CODE_ETABLISSEMENT"='011')                                                                                                                                                                                                                                                              
       3 - access("VOL"."REFERENTIEL"=6 AND "VOL"."POINTDEVENTE"='01105' AND                                                                                                                                                                                                                                     
                  "VOL"."CIRCUITPREPARATION"='000001415' AND "VOL"."JOUR">=TO_DATE('2007-11-01 00:00:00',                                                                                                                                                                                                        
                  'yyyy-mm-dd hh24:mi:ss') AND "VOL"."JOUR"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
                  hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
       4 - access("JOUR"."DATE_CALENDRIER">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                                           
                  hh24:mi:ss') AND "JOUR"."DATE_CALENDRIER"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
                  hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
     
    24 rows selected
    Celui du NOT EXISTS :

    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
     
    --------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
    | Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                           
    --------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
    |   0 | SELECT STATEMENT             |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
    |*  1 |  HASH JOIN RIGHT ANTI        |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
    |*  2 |   TABLE ACCESS BY INDEX ROWID| RF_VOLUMEPREPREAL |     1 |    46 |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
    |*  3 |    INDEX RANGE SCAN          | SYS_C0013284      |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
    |*  4 |   INDEX RANGE SCAN           | SYS_C0013202      |   567 |  4536 |     3   (0)| 00:00:01 |                                                                                                                                                                                                           
    --------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
     
    Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
    ---------------------------------------------------                                                                                                                                                                                                                                                          
     
       1 - access("VOL"."JOUR"="JOUR"."DATE_CALENDRIER")                                                                                                                                                                                                                                                         
       2 - filter("VOL"."CODE_ETABLISSEMENT"='011')                                                                                                                                                                                                                                                              
       3 - access("VOL"."REFERENTIEL"=6 AND "VOL"."POINTDEVENTE"='01105' AND                                                                                                                                                                                                                                     
                  "VOL"."CIRCUITPREPARATION"='000001415' AND "VOL"."JOUR">=TO_DATE('2007-11-01 00:00:00',                                                                                                                                                                                                        
                  'yyyy-mm-dd hh24:mi:ss') AND "VOL"."JOUR"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
                  hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
       4 - access("JOUR"."DATE_CALENDRIER">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                                           
                  hh24:mi:ss') AND "JOUR"."DATE_CALENDRIER"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
                  hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
     
    24 rows selected

    Mais j'ai bien l'impression que rien ne change entre les deux versions ...

  6. #6
    Membre émérite
    Profil pro
    Inscrit en
    Juillet 2007
    Messages
    500
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : Juillet 2007
    Messages : 500
    Par défaut
    Est-ce que tourné comme ça, ça fait améliorer le schmilblcik ?

    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
     
    INSERT INTO RF_VOLUMEPREPREAL
    (CIRCUITPREPARATION, POINTDEVENTE, REFERENTIEL, UO,   CODE_ETABLISSEMENT, PROG_WRITER, JOUR)
     
    SELECT curCP.CODE, curPDV.CODE, LN$IDREF, LN$VALEUR, curETB.CODE, LC$TAG_UPDATABLE, JOUR.DATE_CALENDRIER 
     
    FROM RF_JOUR JOUR
    WHERE 
      JOUR.DATE_CALENDRIER IN (SELECT DATE_CALENDRIER  FROM RF_JOUR
         MINUS SELECT VOL.JOUR FROM RF_VOLUMEPREPREAL VOL
         WHERE VOL.CODE_ETABLISSEMENT = curETB.CODE
         AND VOL.CIRCUITPREPARATION = curCP.CODE
         AND VOL.POINTDEVENTE = curPDV.CODE
         AND VOL.JOUR BETWEEN LD$MINDATE AND LD$MAXDATE
         AND VOL.REFERENTIEL = LN$IDREF)
    AND JOUR.DATE_CALENDRIER BETWEEN LD$MINDATE AND LD$MAXDATE;

  7. #7
    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 Superdub Voir le message
    ...
    Mais j'ai bien l'impression que rien ne change entre les deux versions ...
    Vous est sur une base 10 ou 11 et Oracle a "appris des choses" sur le Not In et Not Exists.
    Mais à priori cette requête s’exécute assez vite, donc c’est quoi votre problème ?

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Optimiser une procédure utilisant un curseur
    Par TizDei dans le forum Développement
    Réponses: 6
    Dernier message: 03/12/2010, 13h48
  2. optimiser une procédure stockée
    Par ed222 dans le forum Développement
    Réponses: 8
    Dernier message: 15/06/2010, 17h30
  3. [optimisation] arguments procédure stocké
    Par freuh94 dans le forum Développement
    Réponses: 8
    Dernier message: 12/05/2010, 02h32
  4. [SQL2005] Optimiser une procédure stockée
    Par david_chardonnet dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 20/12/2006, 15h48
  5. Réponses: 5
    Dernier message: 09/05/2005, 12h24

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