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

    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
    Félicitations
    Excellent article. Merci beaucoup

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

  4. #4
    Membre régulier
    Je confirme, il est vraiment bien...

    Merci

  5. #5
    Membre averti
    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é
    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
    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é
    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
    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é
    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

    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
    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é
    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.

###raw>template_hook.ano_emploi###