+ Répondre à la discussion Actualité déjà publiée
Affichage des résultats 1 à 13 sur 13
  1. #1
    Rédacteur/Modérateur

    Avatar de Vincent Rogier
    Inscrit en
    juillet 2007
    Messages
    2 373
    Détails du profil
    Informations personnelles :
    Âge : 36

    Informations forums :
    Inscription : juillet 2007
    Messages : 2 373
    Points : 5 204
    Points
    5 204

    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
    Invité de passage
    Inscrit en
    mai 2002
    Messages
    9
    Détails du profil
    Informations forums :
    Inscription : mai 2002
    Messages : 9
    Points : 2
    Points
    2

    Par défaut Félicitations

    Excellent article. Merci beaucoup

  3. #3
    Membre confirmé

    Inscrit en
    mars 2006
    Messages
    64
    Détails du profil
    Informations personnelles :
    Âge : 32

    Informations forums :
    Inscription : mars 2006
    Messages : 64
    Points : 281
    Points
    281

    Par défaut

    de rien, ça fait plaisir comme commentaire
    Pas de question technique par MP SVP.

  4. #4
    Membre du Club
    Inscrit en
    juin 2009
    Messages
    152
    Détails du profil
    Informations personnelles :
    Âge : 34

    Informations forums :
    Inscription : juin 2009
    Messages : 152
    Points : 63
    Points
    63

    Par défaut

    Je confirme, il est vraiment bien...

    Merci

  5. #5
    Membre confirmé

    Inscrit en
    mars 2006
    Messages
    64
    Détails du profil
    Informations personnelles :
    Âge : 32

    Informations forums :
    Inscription : mars 2006
    Messages : 64
    Points : 281
    Points
    281

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

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : décembre 2010
    Messages : 115
    Points : 117
    Points
    117

    Par défaut

    Bonjour,
    Vraiment merci pour le tutoriel.Cela va m'aider à démarrer mon projet

    Bonne continuation.

  7. #7
    Membre du Club
    Homme Profil pro
    Inscrit en
    octobre 2002
    Messages
    57
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : octobre 2002
    Messages : 57
    Points : 68
    Points
    68

    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 Expert Avatar de ojo77
    Homme Profil pro
    Support
    Inscrit en
    décembre 2010
    Messages
    634
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Seine et Marne (Île de France)

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

    Informations forums :
    Inscription : décembre 2010
    Messages : 634
    Points : 1 458
    Points
    1 458

    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 :
    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 :
    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 :
    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 :
    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 du Club
    Homme Profil pro
    Inscrit en
    octobre 2002
    Messages
    57
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : octobre 2002
    Messages : 57
    Points : 68
    Points
    68

    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 Expert Avatar de ojo77
    Homme Profil pro
    Support
    Inscrit en
    décembre 2010
    Messages
    634
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Seine et Marne (Île de France)

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

    Informations forums :
    Inscription : décembre 2010
    Messages : 634
    Points : 1 458
    Points
    1 458

    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

    Homme Profil pro
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 914
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 914
    Points : 14 393
    Points
    14 393

    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 Confirmé Sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    4 667
    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 : 4 667
    Points : 9 184
    Points
    9 184

    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 Expert Avatar de ojo77
    Homme Profil pro
    Support
    Inscrit en
    décembre 2010
    Messages
    634
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Seine et Marne (Île de France)

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

    Informations forums :
    Inscription : décembre 2010
    Messages : 634
    Points : 1 458
    Points
    1 458

    Par défaut

    Ok, comme je suis taquin, je le fais avec des tables, c'est encore plus criant ...

    Code :
    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 :
    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 :
    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 :
    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 :
    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 :
    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 :
    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.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •