Initiation à l'optimisation de requêtes SQL sous ORACLE [Résolu]

[Vincent Rogier]

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

[gwain]

Excellent article. Merci beaucoup

[jeepnc]

de rien, ça fait plaisir comme commentaire

[breizh76]

Je confirme, il est vraiment bien...

Merci

[jeepnc]

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

[Glauben]

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

Bonne continuation.

[Dajon]

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é

[ojo77]

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)

[Dajon]

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

[ojo77]

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)

[Waldar]

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.

[mnitu]

Envoyé par ojo77

...Prenons deux tables importantes : dba_tables et dba_segments

Ces sont des vues !

[ojo77]

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.