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

Contribuez Oracle Discussion :

Initiation à l'optimisation de requêtes SQL sous ORACLE


Sujet :

Contribuez Oracle

  1. #1
    Rédacteur
    Avatar de Vincent Rogier
    Profil pro
    Inscrit en
    Juillet 2007
    Messages
    2 373
    Détails du profil
    Informations personnelles :
    Âge : 45
    Localisation : France

    Informations forums :
    Inscription : Juillet 2007
    Messages : 2 373
    Points : 5 307
    Points
    5 307
    Par défaut Initiation à l'optimisation de requêtes SQL sous ORACLE
    Bonjour,

    Jean-Philippe Guilloux vous propose un tutoriel : Initiation à l'optimisation de requêtes SQL sous ORACLE

    Ce tutoriel présente les notions de plan d'exécution, d'optimiseur, de coût, d'index, statistiques...

    Bonne lecture... et n'hésitez pas à laisser vos remarques et commentaires dans cette discussion
    Vincent Rogier.

    Rubrique ORACLE : Accueil - Forum - Tutoriels - FAQ - Livres - Blog

    Vous voulez contribuer à la rubrique Oracle ? Contactez la rubrique !

    OCILIB (C Driver for Oracle)

    Librairie C Open Source multi-plateformes pour accéder et manipuler des bases de données Oracle

  2. #2
    Futur Membre du Club
    Inscrit en
    Mai 2002
    Messages
    9
    Détails du profil
    Informations forums :
    Inscription : Mai 2002
    Messages : 9
    Points : 7
    Points
    7
    Par défaut Félicitations
    Excellent article. Merci beaucoup

  3. #3
    Membre averti

    Inscrit en
    Mars 2006
    Messages
    64
    Détails du profil
    Informations personnelles :
    Âge : 41

    Informations forums :
    Inscription : Mars 2006
    Messages : 64
    Points : 306
    Points
    306
    Par défaut
    de rien, ça fait plaisir comme commentaire
    Pas de question technique par MP SVP.

  4. #4
    Membre régulier
    Inscrit en
    Juin 2009
    Messages
    152
    Détails du profil
    Informations personnelles :
    Âge : 43

    Informations forums :
    Inscription : Juin 2009
    Messages : 152
    Points : 90
    Points
    90
    Par défaut
    Je confirme, il est vraiment bien...

    Merci

  5. #5
    Membre averti

    Inscrit en
    Mars 2006
    Messages
    64
    Détails du profil
    Informations personnelles :
    Âge : 41

    Informations forums :
    Inscription : Mars 2006
    Messages : 64
    Points : 306
    Points
    306
    Par défaut
    bah merci aussi alors

    Sinon, si j'ai le temps, gloups ... j'approfondirai un de ces 4 certaines parties comme les clusters, le précalcul d'éléments , le parallellisme et parfois des retour d'exp sur des bizzareries ... j'espère avoir le temps et la motiv pour cela, mais là je change de boulot en devenant architecte système donc c'est pas gagné pour de suite enfin, qui sait ...
    Pas de question technique par MP SVP.

  6. #6
    Membre habitué Avatar de Glauben
    Étudiant
    Inscrit en
    Décembre 2010
    Messages
    115
    Détails du profil
    Informations personnelles :
    Âge : 35

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Décembre 2010
    Messages : 115
    Points : 151
    Points
    151
    Par défaut
    Bonjour,
    Vraiment merci pour le tutoriel.Cela va m'aider à démarrer mon projet

    Bonne continuation.
    Si Seulement On Pouvait Revenir En Arrière

  7. #7
    Membre régulier
    Homme Profil pro
    Inscrit en
    Octobre 2002
    Messages
    58
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : Octobre 2002
    Messages : 58
    Points : 86
    Points
    86
    Par défaut
    Bonjour,

    En parcourant le tutoriel, j'ai remarqué une petite erreur:

    Select TA.a,TA.b,TA.c from TA,TB where exists (select 1 from TB where TA.a=TB.a);
    Je pense que le produit cartésien entre TA et TB dans la requête principale n'est pas souhaité
    Tuning and optimization are not a fix to a bad design. A good design is a fix to a bad design.

  8. #8
    Membre expérimenté Avatar de ojo77
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Décembre 2010
    Messages
    680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 49
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Décembre 2010
    Messages : 680
    Points : 1 597
    Points
    1 597
    Par défaut
    De plus l'exemple donné n'est pas forcément si probant que ça

    Prenons deux tables importantes : dba_tables et dba_segments et les requêtes suivantes :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    select s.OWNER, s.SEGMENT_NAME, s.BLOCKS, s.BYTES
    from dba_segments s, dba_tables o
    where s.owner=o.owner
      and s.SEGMENT_NAME=o.table_name
    et
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    select s.OWNER, s.SEGMENT_NAME, s.BLOCKS, s.BYTES
    from dba_segments s
    where exists (select 1 from dba_tables o
                           where s.owner=o.owner
                             and s.SEGMENT_NAME=o.table_name)
    On s'amuse et on lance les deux requêtes deux fois de suite chacune en mode timing on et autotrace traceonly (je vous épargne les plans d'exécution)

    On obtient après la seconde exécution :

    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
    select s.OWNER, s.SEGMENT_NAME, s.BLOCKS, s.BYTES
    from dba_segments s, dba_tables o
    where s.owner=o.owner
      and s.SEGMENT_NAME=o.table_name
    /
    
    1097 rows selected.
    
    Elapsed: 00:00:02.22
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4120749628
    
    
    [ Je passe le plan ]
    
    
    Statistics
    ----------------------------------------------------------
            192  recursive calls
              0  db block gets
         334539  consistent gets
              0  physical reads
              0  redo size
          35738  bytes sent via SQL*Net to client
           1291  bytes received via SQL*Net from client
             75  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
           1097  rows processed
    et
    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
    select s.OWNER, s.SEGMENT_NAME, s.BLOCKS, s.BYTES
    from dba_segments s
    where exists (select 1 from dba_tables o
                           where s.owner=o.owner
                             and s.SEGMENT_NAME=o.table_name)
    /
    
    1097 rows selected.
    
    Elapsed: 00:00:03.07
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 592611141
    
    [ Je passe le plan ]
    
    Statistics
    ----------------------------------------------------------
           1626  recursive calls
              0  db block gets
         179747  consistent gets
              0  physical reads
              0  redo size
          40566  bytes sent via SQL*Net to client
           1291  bytes received via SQL*Net from client
             75  SQL*Net roundtrips to/from client
           1046  sorts (memory)
              0  sorts (disk)
           1097  rows processed
    Donc la première est plus rapide, ce qui contredit l'exemple donné dans le tuto et ce malgré un nombre de "consistent gets" en faveur de la seconde requête.

    Mais je suis en 10gR2 (10.2.0.5 pour être précis)

  9. #9
    Membre régulier
    Homme Profil pro
    Inscrit en
    Octobre 2002
    Messages
    58
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 44
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : Octobre 2002
    Messages : 58
    Points : 86
    Points
    86
    Par défaut
    Le join est également plus rapide chez moi, en utilisant tes requêtes.

    Mais j'imagine que c'est au cas par cas, je me rappelle avoir vu des cas ou le exists était plus performant. Savoir que les deux méthodes existent ne peut être que bénéfique

    Par ailleurs, je ne sais pas si se baser sur le "elapsed time" est très pertinent, beaucoup de paramètres entrent en ligne de compte: charge générale de la machine, cache (oracle + disque + FS), éventuellement le réseau etc...
    Tuning and optimization are not a fix to a bad design. A good design is a fix to a bad design.

  10. #10
    Membre expérimenté Avatar de ojo77
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Décembre 2010
    Messages
    680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 49
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Décembre 2010
    Messages : 680
    Points : 1 597
    Points
    1 597
    Par défaut
    En effet, le but de ma remarque était surtout de dire remplacer une jointure par un existe n'est pas une optimisation à effectuer systématiquement car dans certain cas la jointure est plus rapide:

    Donc l'assertion "Vous constaterez lors de tables avec de nombreux enregistrements, que celà est quand même bien plus rapide ..." est fausse en 10g (et en 11g)

  11. #11
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 811
    Points
    17 811
    Par défaut
    J'ajouterai que les requêtes ne sont équivalentes que lorsque certaines conditions sont remplies : il faut d'abord veiller à écrire la requête qui donnera le bon résultat.

  12. #12
    Expert éminent sénior 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
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par ojo77 Voir le message
    ...Prenons deux tables importantes : dba_tables et dba_segments
    Ces sont des vues !

  13. #13
    Membre expérimenté Avatar de ojo77
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Décembre 2010
    Messages
    680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 49
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Décembre 2010
    Messages : 680
    Points : 1 597
    Points
    1 597
    Par défaut
    Ok, comme je suis taquin, je le fais avec des tables, c'est encore plus criant ...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SQL> select object_name, object_type from dba_objects where object_name in ('SEG$', 'TAB$');
    SQL> col object_name for a30
    SQL> col object_type for a30
    SQL> /
     
    OBJECT_NAME                    OBJECT_TYPE
    ------------------------------ ------------------------------
    SEG$                           TABLE
    TAB$                           TABLE
     
    2 rows selected.
    Je désactive les QT et passe les deux requêtes :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select /*+ NO_QUERY_TRANSFORMATION */ s.BLOCKS, s.USER#, s.BLOCKS
    from seg$ s, tab$ t
    where s.FILE#=t.FILE#
      and s.BLOCK#=t.BLOCK#
      and s.TS#=t.TS#
    /
    et

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    select /*+ NO_QUERY_TRANSFORMATION */ s.BLOCKS, s.USER#, s.BLOCKS
    from seg$ s
    where exists ( select 1
                   from tab$ t
                   where s.FILE#=t.FILE#
                     and s.BLOCK#=t.BLOCK#
                     and s.TS#=t.TS# )
    /
    c'est parti et cette fois je garde les plans d'exécution (même conditions : autotrace traceonly, timing on et mesure sur la seconde exécution :


    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
    select /*+ NO_QUERY_TRANSFORMATION */ s.BLOCKS, s.USER#, s.BLOCKS
    from seg$ s, tab$ t
    where s.FILE#=t.FILE#
      and s.BLOCK#=t.BLOCK#
      and s.TS#=t.TS#
    SQL> /
    Elapsed: 00:00:00.01
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4123876503
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |   906 | 25368 |   237   (1)| 00:00:03 |
    |*  1 |  HASH JOIN         |      |   906 | 25368 |   237   (1)| 00:00:03 |
    |   2 |   TABLE ACCESS FULL| TAB$ |   924 |  9240 |   175   (0)| 00:00:03 |
    |   3 |   TABLE ACCESS FULL| SEG$ |  7444 |   130K|    61   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("S"."FILE#"="T"."FILE#" AND "S"."BLOCK#"="T"."BLOCK#" AND
                  "S"."TS#"="T"."TS#")
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            912  consistent gets
              0  physical reads
              0  redo size
          14774  bytes sent via SQL*Net to client
           1038  bytes received via SQL*Net from client
             52  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            756  rows processed
    et donc avec 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
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    select /*+ NO_QUERY_TRANSFORMATION */ s.BLOCKS, s.USER#, s.BLOCKS
    from seg$ s
    where exists ( select 1
                   from tab$ t
                   where s.FILE#=t.FILE#
                     and s.BLOCK#=t.BLOCK#
                     and s.TS#=t.TS# )
    SQL> /
    Elapsed: 00:00:11.57
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1880985370
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    18 |   652K  (1)| 02:10:33 |
    |*  1 |  FILTER            |      |       |       |            |          |
    |   2 |   TABLE ACCESS FULL| SEG$ |  7444 |   130K|    61   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| TAB$ |     1 |    10 |   175   (0)| 00:00:03 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter( EXISTS (SELECT 0 FROM "TAB$" "T" WHERE "T"."BLOCK#"=:B1
                  AND "T"."FILE#"=:B2 AND "T"."TS#"=:B3))
       3 - filter("T"."BLOCK#"=:B1 AND "T"."FILE#"=:B2 AND "T"."TS#"=:B3)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
        4538051  consistent gets
              0  physical reads
              0  redo size
          14278  bytes sent via SQL*Net to client
           1016  bytes received via SQL*Net from client
             50  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
            730  rows processed
    fichtre non seulement c'est vachement plus long, mais en plus, comme le signalait Waldar plus haut, les deux requêtes ne sont pas équivalentes puisqu'une ramène 756 lignes et l'autre 730

    bon on continue avec nos deux requêtes mais cette fois ci on se débrouille pour qu'elles soient effectivement équivalentes.

    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
    select /*+ NO_QUERY_TRANSFORMATION */ distinct s.BLOCKS, s.USER#, s.BLOCKS
    from seg$ s, tab$ t
    where s.FILE#=t.FILE#
     and s.BLOCK#=t.BLOCK#
     and s.TS#=t.TS#
    /
     
    Elapsed: 00:00:00.02
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1946129361
     
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |   906 | 25368 |   238   (1)| 00:00:03 |
    |   1 |  HASH UNIQUE        |      |   906 | 25368 |   238   (1)| 00:00:03 |
    |*  2 |   HASH JOIN         |      |   906 | 25368 |   237   (1)| 00:00:03 |
    |   3 |    TABLE ACCESS FULL| TAB$ |   924 |  9240 |   175   (0)| 00:00:03 |
    |   4 |    TABLE ACCESS FULL| SEG$ |  7444 |   130K|    61   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("S"."FILE#"="T"."FILE#" AND "S"."BLOCK#"="T"."BLOCK#" AND
                  "S"."TS#"="T"."TS#")
     
     
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            864  consistent gets
              0  physical reads
              0  redo size
           1892  bytes sent via SQL*Net to client
            521  bytes received via SQL*Net from client
              5  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             49  rows processed
    et

    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
    select /*+ NO_QUERY_TRANSFORMATION */ distinct s.BLOCKS, s.USER#, s.BLOCKS
    from seg$ s
    where exists ( select 1
                   from tab$ t
                   where s.FILE#=t.FILE#
                     and s.BLOCK#=t.BLOCK#
                     and s.TS#=t.TS# )
    /
     
    Elapsed: 00:00:12.09
     
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2808210304
     
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |     1 |    18 |   652K  (1)| 02:10:33 |
    |   1 |  HASH UNIQUE        |      |     1 |    18 |   652K  (1)| 02:10:33 |
    |*  2 |   FILTER            |      |       |       |            |          |
    |   3 |    TABLE ACCESS FULL| SEG$ |  7444 |   130K|    61   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL| TAB$ |     1 |    10 |   175   (0)| 00:00:03 |
    ----------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter( EXISTS (SELECT 0 FROM "TAB$" "T" WHERE "T"."BLOCK#"=:B1
                  AND "T"."FILE#"=:B2 AND "T"."TS#"=:B3))
       4 - filter("T"."BLOCK#"=:B1 AND "T"."FILE#"=:B2 AND "T"."TS#"=:B3)
     
     
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
        4538003  consistent gets
              0  physical reads
              0  redo size
           1892  bytes sent via SQL*Net to client
            521  bytes received via SQL*Net from client
              5  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             49  rows processed
    Donc là on a des requêtes équivelentes, sur des tables et le exists est vraiment tout sauf une optimisation.

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

Discussions similaires

  1. Réponses: 5
    Dernier message: 10/10/2013, 17h38
  2. Réponses: 0
    Dernier message: 15/10/2010, 22h48
  3. Optimisation des vues SQL sous Oracle
    Par ammah dans le forum Langage SQL
    Réponses: 7
    Dernier message: 10/02/2010, 18h01
  4. Requête SQL sous oracle
    Par milki1 dans le forum SQL
    Réponses: 2
    Dernier message: 12/06/2008, 16h13
  5. Optimisation de requetes SQL sous oracle
    Par santana2006 dans le forum Oracle
    Réponses: 5
    Dernier message: 28/08/2006, 20h26

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