optimisation d'une requete de jointure multiple

Version imprimable

26/12/2007, 17h41
M_Dandouna

optimisation d'une requete de jointure multiple

bonsoir,
je voulais optimiser une requête de jointure multiple:
en fait ma requte est la suivante:
SELECT diag_libelle,diag_id, cons_date, pat_nom,hosp_dataini FROM diagnostique,consultation,patient,hospitalisation WHERE diag_id = cons_diag_id and cons_pat_id = pat_id and pat_id = hosp_pat_id and pat_id=1;
--310786 rows selected.
--Elapsed: 00:04:19.27
j'ai lu sur un site est que l'étape la plus intensive du processus de préparation SQL est la génération du plan d'exécution, notamment dans le cas d'une requête à jointures multiples (l'évaluation de toutes les combainisons de jointure possibles dans mon cas 4*3*2=24 permutation possible).
donc j'ai pensé à travailler sur cet aspect .
donc j'ai essayé ca:
SELECT/*+ optimizer_max_permutation=1 */ diag_libelle,diag_id, cons_date, pat_nom,hosp_dataini FROM diagnostique,consultation,patient,hospitalisation-- WHERE diag_id = cons_diag_id and cons_pat_id = pat_id and pat_id = hosp_pat_id and pat_id=1;
--310786 rows selected.

--Elapsed: 00:04:25.17
le temps d'exécution de la requete augmente au lieu de diminuer j'ai même fixer le parametre optimizer_search_limit à 3 mais ce ci n'avait aucun impact.
quelqu'un puisse m'expliquer que dois -je faire pour optimiser une telle requete.
merci
26/12/2007, 18h41
Michel SALAIS

Vous empuntez le mauvais chemin ...
Les permutations permettront à l'optimiseur de trouver un meilleur choix indépendamment de l'ordre dans votre clause from. D'un autre côté, votre objectif doit être d'abord d'optimiser l'exécution et non la génération du plan d'exécution vu la durée de votre requête.

Essayer de nous donner plus de détails sur vos tables et le plan d'exécution utilisé par Oracle en enlevant le hint sur les permutations

Bonjour,

Je n'ai pas la maitrise du fonctionnel, mais j'ai l'impression qu'il y a une erreur sur les jointures de ta requête.

tu fait
Code:

1 2 3 4 5 6 7 SELECT diag_libelle,diag_id, cons_date, pat_nom,hosp_dataini FROM diagnostique,consultation,patient,hospitalisation WHERE diag_id = cons_diag_id and cons_pat_id = pat_id and pat_id = hosp_pat_id and pat_id=1;
A mon humble avis tu devrais faire :
Code:

1 2 3 4 5 6 7 SELECT diag_libelle,diag_id, cons_date, pat_nom,hosp_dataini FROM diagnostique,consultation,patient,hospitalisation WHERE diag_id = cons_diag_id and cons_pat_id = hosp_pat_id and pat_id = hosp_pat_id and pat_id=1;
Ce qui t'evitera un produit cartésien entre les tables 'consultation' et 'hospitalisation' et devrai par la même occasion améliorer les perf de ta requête...

Bon courage

A+

29/12/2007, 14h06
M_Dandouna

optimisation d'une requete de jointure multiple

bonjour,
merci d'abord de m'avoir répondu.
voici ce que j'ai fait:
SELECT diag_libelle,diag_id, cons_date, pat_nom,hosp_dataini FROM diagnostique,consultation,patient,hospitalisation WHERE diag_id = cons_diag_id and cons_pat_id = pat_id and pat_id = hosp_pat_id and pat_id=1;

--310786 rows selected.
--Elapsed: 00:02:07.09
vous remarquez ici que le temps d'exécution est diminué pourtant j'ai rien changé dans la requette ni dans la structure des tables.
voici le plan d'exécution :
ID OPERATION COST
---------- ------------------------------ ----------
0 SELECT STATEMENT 359
1 HASH JOIN 359
2 TABLE ACCESS 11
3 MERGE JOIN 347
4 NESTED LOOPS 5
5 TABLE ACCESS 2
6 INDEX 1
7 TABLE ACCESS 3
8 BUFFER 344
9 TABLE ACCESS 9
j'ai réecris après la requete autrement:
SELECT diag_libelle,diag_id, cons_date, pat_nom, hosp_dataini FROM diagnostique,consultation,patient,hospitalisation WHERE diag_id = cons_diag_id and cons_pat_id = pat_id and cons_pat_id = hosp_pat_id and pat_id=1;

310786 rows selected.
Elapsed: 00:02:02.17

voici le plan d'exécution:
ID OPERATION COST
---------- ------------------------------ ----------
0 SELECT STATEMENT 29
1 HASH JOIN 29
2 NESTED LOOPS 15
3 NESTED LOOPS 13
4 TABLE ACCESS 2
5 INDEX 1
6 TABLE ACCESS 11
7 TABLE ACCESS 1
8 INDEX 0
9 TABLE ACCESS 13
pouvez vous m'expliquer ici pourquoi la valeur de cost s'est changé de cette manière pour l'opération de sélection et pour le NESTED LOOPS?
et si j'ai bien compris ce que vous m'avez dit je dois d'abord réecrir la requete autrement jusqu'à avoir une plu performante et puis je peux agir sur le plan d'exécution.
NB:voici les cardinalités des tables:
patient 900
consultation 9982
diagnostique 30
hospitalisation 9825
29/12/2007, 15h37
Michel SALAIS

C'est illisible au niveau de la forme

Faudrait mettre les plan et les requêtes entre [ code ] et [ /code ] (sans les espaces).

Code:

1
2
3
4
5
6
7
SELECT D.diag_libelle,D.diag_id, C.cons_date,P.pat_nom,H.hosp_dataini
FROM diagnostique D,consultation C,patient P, hospitalisation H
WHERE D.diag_id = C.cons_diag_id and 
C.cons_pat_id = P.pat_id and P.pat_id = H.hosp_pat_id and P.pat_id=1;
--310786 rows selected.
--Elapsed: 00:02:07.09

voici le plan d'exécution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
       ID OPERATION                            COST
---------- ------------------------------ ----------
         0 SELECT STATEMENT                      359
         1 HASH JOIN                             359
         2 TABLE ACCESS                           11
         3 MERGE JOIN                            347
         4 NESTED LOOPS                            5
         5 TABLE ACCESS                            2
         6 INDEX                                   1
         7 TABLE ACCESS                            3
         8 BUFFER                                344
         9 TABLE ACCESS                           11

j'ai réecris après la requete autrement:

Code:

1
2
3
4
5
6
7
SELECT D.diag_libelle,D.diag_id, C.cons_date,P.pat_nom,H.hosp_dataini
FROM diagnostique D,consultation C,patient P, hospitalisation H
WHERE D.diag_id = C.cons_diag_id and C.cons_pat_id = P.pat_id
 and C.cons_pat_id = H.hosp_pat_id and P.pat_id=1;
310786 rows selected.
Elapsed: 00:02:02.17

voici le plan d'exécution:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
   ID OPERATION                            COST
---------- ------------------------------ ----------
         0 SELECT STATEMENT                       29
         1 HASH JOIN                              29
         2 NESTED LOOPS                           15
         3 NESTED LOOPS                           13
         4 TABLE ACCESS                            2
         5 INDEX                                   1
         6 TABLE ACCESS                           11
         7 TABLE ACCESS                            1
         8 INDEX                                   0
         9 TABLE ACCESS                           13

pouvez vous m'expliquer ici pourquoi la valeur de cost s'est changé de cette manière pour l'opération de sélection et pour le NESTED LOOPS?
et si j'ai bien compris ce que vous m'avez dit je dois d'abord réecrir la requete autrement jusqu'à avoir une plu performante et puis je peux agir sur le plan d'exécution.
NB:voici les cardinalités des tables:
patient 900
consultation 9982
diagnostique 30
hospitalisation 9825

30/12/2007, 12h40
Michel SALAIS

Encore une chose, En fait les noms des tables et les opérations exactes ne sont pas répertoriés dans votre plan puis l'indentation ou la filiation des opérations n'est pas présente ce qui rend difficile la compréhension du plan et puis la version Oracle.

Il est possible d'avoir le plan grâce à set autotrace de SQL*Plus ou explain plan
et c'est expliqué ici

bonjour,
j'espère que c'est ca ce que manquait:
pour la requete:

Code:

1
2
3
4
 
SELECT diag_libelle,diag_id, cons_date, pat_nom,hosp_dataini FROM diagnostique,consultation,patient,hospitalisation WHERE  diag_id = cons_diag_id and cons_pat_id = pat_id and pat_id = hosp_pat_id and  pat_id=1;
310786 rows selected.
Elapsed: 00:02:03.95

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3647692474
 
--------------------------------------------------------------------------------
------------------
 
| Id  | Operation                      | Name            | Rows  | Bytes | Cost
(%CPU)| Time     |
 
--------------------------------------------------------------------------------
------------------
 
|   0 | SELECT STATEMENT               |                 | 55200 |  3234K|   359
   (4)| 00:00:05 |
 
|*  1 |  HASH JOIN                     |                 | 55200 |  3234K|   359
   (4)| 00:00:05 |
 
|*  2 |   TABLE ACCESS FULL            | CONSULTATION    |   100 |  1500 |    11
   (0)| 00:00:01 |
 
|   3 |   MERGE JOIN CARTESIAN         |                 | 16590 |   729K|   347
   (4)| 00:00:05 |
 
|   4 |    NESTED LOOPS                |                 |    30 |   990 |     5
   (0)| 00:00:01 |
 
|   5 |     TABLE ACCESS BY INDEX ROWID| PATIENT         |     1 |    11 |     2
   (0)| 00:00:01 |
 
|*  6 |      INDEX UNIQUE SCAN         | SYS_C005660     |     1 |       |     1
   (0)| 00:00:01 |
 
|   7 |     TABLE ACCESS FULL          | DIAGNOSTIQUE    |    30 |   660 |     3
   (0)| 00:00:01 |
 
|   8 |    BUFFER SORT                 |                 |   553 |  6636 |   344
   (4)| 00:00:05 |
 
|*  9 |     TABLE ACCESS FULL          | HOSPITALISATION |   553 |  6636 |    11
   (0)| 00:00:01 |
 
--------------------------------------------------------------------------------
------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DIAG_ID"="CONS_DIAG_ID")
   2 - filter("CONS_PAT_ID"=1)
   6 - access("PAT_ID"=1)
   9 - filter("HOSP_PAT_ID"=1)
 
 
Statistics
----------------------------------------------------------
       1281  recursive calls
          0  db block gets
        388  consistent gets
        115  physical reads
          0  redo size
    7697693  bytes sent via SQL*Net to client
     228294  bytes received via SQL*Net from client
      20721  SQL*Net roundtrips to/from client
         30  sorts (memory)
          0  sorts (disk)
     310786  rows processed

mais comme vous remarquez à chaque fois j'exécute la requette le temps d'exécution change est ce que ce ci est due à la fréquence d'exécution de la requette?
je tiens à votre aide.
merci

pour la réecriture de le requete (requete2)

Code:

1
2
3
4
5
 
SELECT D.diag_libelle,D.diag_id, C.cons_date,P.pat_nom,H.hosp_dataini
FROM diagnostique D,consultation C,patient P, hospitalisation H
WHERE D.diag_id = C.cons_diag_id and C.cons_pat_id = P.pat_id
 and C.cons_pat_id = H.hosp_pat_id and P.pat_id=1;

le temps d'exécution n'est plus inférieur à la 1 ère requete voici ce que j'obtiens comme temps de réponse en exécutant cette requete plusieurs fois:
-00:03:46.24
-puis 00:04:14.38
-puis 00:02:50.53
cependant l'autre fois j'ai obtenu comme temps de réponse:00:02:02.17
je trouve aucune explication à ca:cry:
pourtant le plan d'exécution reste toujours le meme:

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
Execution Plan
----------------------------------------------------------
Plan hash value: 904610893
 
--------------------------------------------------------------------------------
------------------
 
| Id  | Operation                      | Name            | Rows  | Bytes | Cost
(%CPU)| Time     |
 
--------------------------------------------------------------------------------
------------------
 
|   0 | SELECT STATEMENT               |                 | 55200 |  3234K|    29
   (7)| 00:00:01 |
 
|*  1 |  HASH JOIN                     |                 | 55200 |  3234K|    29
   (7)| 00:00:01 |
 
|   2 |   NESTED LOOPS                 |                 |   100 |  4800 |    15
   (7)| 00:00:01 |
 
|   3 |    NESTED LOOPS                |                 |   100 |  2600 |    13
   (0)| 00:00:01 |
 
|   4 |     TABLE ACCESS BY INDEX ROWID| PATIENT         |     1 |    11 |     2
   (0)| 00:00:01 |
 
|*  5 |      INDEX UNIQUE SCAN         | SYS_C005660     |     1 |       |     1
   (0)| 00:00:01 |
 
|*  6 |     TABLE ACCESS FULL          | CONSULTATION    |   100 |  1500 |    11
   (0)| 00:00:01 |
 
|   7 |    TABLE ACCESS BY INDEX ROWID | DIAGNOSTIQUE    |     1 |    22 |     1
   (0)| 00:00:01 |
 
|*  8 |     INDEX UNIQUE SCAN          | SYS_C005652     |     1 |       |     0
   (0)| 00:00:01 |
 
|*  9 |   TABLE ACCESS FULL            | HOSPITALISATION |   553 |  6636 |    13
   (0)| 00:00:01 |
 
--------------------------------------------------------------------------------
------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("CONS_PAT_ID"="HOSP_PAT_ID")
   5 - access("PAT_ID"=1)
   6 - filter("CONS_PAT_ID"=1)
   8 - access("DIAG_ID"="CONS_DIAG_ID")
   9 - filter("HOSP_PAT_ID"=1)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1218  consistent gets
          0  physical reads
          0  redo size
   13658772  bytes sent via SQL*Net to client
     228294  bytes received via SQL*Net from client
      20721  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     310786  rows processed

02/01/2008, 12h35
Michel SALAIS

Un autre point que j'ai oublié de préciser : laisser le paramètre optimizer_search_limit à sa valeur par défaut ...
02/01/2008, 13h15
M_Dandouna

c tout con mais je savais pas comment le faire.
nb: j'ai oublié de vous dire que je travaille sur oracle 10G.
02/01/2008, 13h24
McM

Enfin la version ;)

Peux tu donner les index de tes tables sur les colonnes
diag_id, cons_diag_id, cons_pat_id, pat_id, hosp_pat_id

Sinon une requete lancée 1 fois, les données ne sont pas encore en mémoire (de la base ou du serveur), la seconde fois, les données peuvent être lues en mémoire => Accès plus rapide.

Les stats sont elles à jour ?
02/01/2008, 13h44
McM
Citation:
Envoyé par djeant

Bonjour,

Je n'ai pas la maitrise du fonctionnel, mais j'ai l'impression qu'il y a une erreur sur les jointures de ta requête.

Code:

1 2 3 4 5 6 7 and cons_pat_id = pat_id and pat_id = hosp_pat_id and pat_id=1; and cons_pat_id = hosp_pat_id and pat_id = hosp_pat_id and pat_id=1;

Ce qui t'evitera un produit cartésien entre les tables 'consultation' et 'hospitalisation'
Ca n'évitera rien du tout.... C'est la même chose.
Il y a un problème de produit cartésien entre ces 2 tables dans tous les cas.
Si un patient à 3 entrées dans Consultation et 2 dans Hospitalisation, ca fera 6 lignes !

Donc je pense que d'essayer d'optimiser ça ne sert à rien, faut corriger la requete.

bonjour,
ce qui concerne les index au début j'ai cru qu'oracle crée des index sur les clés primaires et étrangères de chaque table. Mais en listant les index sur mes tables j'ai trouvé qu'il y avait qu'un seul index sur chacune donc j'ai crée les index sur les clés étrangères:

Code:

1
2
3
4
5
 
create index index_cons_pat_id on consultation(cons_pat_id);
create index index_hosp_pat_id on hospitalisation(hosp_pat_id);
create index index_cons_pat_id on consultation(cons_pat_id);
create index index_cons_pat_id on consultation(cons_pat_id);

voici ce que j'ai comme index sur mes tables:

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
 
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
TABLE_NAME                     TABLE_TYPE  INCLUDE_COLUMN
------------------------------ ----------- --------------
SYS_C005742                    NORMAL
CONSULTATION                   TABLE
 
INDEX_CONS_PAT_ID              NORMAL
CONSULTATION                   TABLE
 
INDEX_HOSP_PAT_ID              NORMAL
HOSPITALISATION                TABLE
 
 
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
TABLE_NAME                     TABLE_TYPE  INCLUDE_COLUMN
------------------------------ ----------- --------------
SYS_C005678                    NORMAL
HOSPITALISATION                TABLE
 
SYS_C005660                    NORMAL
PATIENT                        TABLE
 
SYS_C005652                    NORMAL
DIAGNOSTIQUE                   TABLE

et puis pour les statistiques n'étaient pas mis à jour last_analyzed=25-dec-2007
est ce que vous pouvez m'expliquer à quoi servent excatement les statistiques sur mes tables dans ce cas et est ce que à chaque fois que j'exécute ma requete je jois
faire d'abord des satistiques sur mes tables:

Code:

1
2
3
4
5
 
analyze table consultation compute statistics;
analyze table patient compute statistics;
analyze table hospitalisation compute statistics;
analyze table diagnostique compute statistics;

pour le plan d'exécution de la première requete après avoir analyzer les tables:

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1195578431
 
--------------------------------------------------------------------------------
------------------
 
| Id  | Operation                      | Name            | Rows  | Bytes | Cost
(%CPU)| Time     |
 
--------------------------------------------------------------------------------
------------------
 
|   0 | SELECT STATEMENT               |                 |  9807 |   498K|   137
   (2)| 00:00:02 |
 
|*  1 |  HASH JOIN                     |                 |  9807 |   498K|   137
   (2)| 00:00:02 |
 
|*  2 |   TABLE ACCESS FULL            | CONSULTATION    |   100 |  1200 |    11
   (0)| 00:00:01 |
 
|   3 |   MERGE JOIN CARTESIAN         |                 |  2948 |   115K|   125
   (1)| 00:00:02 |
 
|   4 |    NESTED LOOPS                |                 |    98 |  1862 |    15
   (0)| 00:00:01 |
 
|   5 |     TABLE ACCESS BY INDEX ROWID| PATIENT         |     1 |     9 |     2
   (0)| 00:00:01 |
 
|*  6 |      INDEX UNIQUE SCAN         | SYS_C005660     |     1 |       |     1
   (0)| 00:00:01 |
 
|*  7 |     TABLE ACCESS FULL          | HOSPITALISATION |    98 |   980 |    13
   (0)| 00:00:01 |
 
|   8 |    BUFFER SORT                 |                 |    30 |   630 |   112
   (1)| 00:00:02 |
 
|   9 |     TABLE ACCESS FULL          | DIAGNOSTIQUE    |    30 |   630 |     1
   (0)| 00:00:01 |
 
--------------------------------------------------------------------------------
------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("DIAG_ID"="CONS_DIAG_ID")
   2 - filter("CONS_PAT_ID"=1)
   6 - access("PAT_ID"=1)
   7 - filter("HOSP_PAT_ID"=1)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        661  consistent gets
        117  physical reads
          0  redo size
    7841765  bytes sent via SQL*Net to client
     228294  bytes received via SQL*Net from client
      20721  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     310786  rows processed

pour le paramètre optimizer_search_limit je l'ai pas changé cette fois donc il devrait etre à sa valeur par défaut;
En fait est ce que c'est possible de connaitre son continue?si oui comment svp ?
merci

02/01/2008, 17h10
McM

Pour les stats, faut aller voir du côté de la faq ou des tuto DBA, doit y avoir un truc la dessus.

Si les données de ta table ne bougent pas, les stats ne bougent pas, donc pas besoin de les recalculer.
02/01/2008, 21h10
Michel SALAIS

Citation:

Envoyé par McM

Ca n'évitera rien du tout.... C'est la même chose.
Il y a un problème de produit cartésien entre ces 2 tables dans tous les cas.
Si un patient à 3 entrées dans Consultation et 2 dans Hospitalisation, ca fera 6 lignes !

Donc je pense que d'essayer d'optimiser ça ne sert à rien, faut corriger la requete.

D'ailleurs, il ne faut pas priver Oracle des prédicats "superflus" puisqu'il peut dans ce cas faire mieux! En gros si on a col1 = col2 and col2 = col3 alors il ne fait pas de mal d'y rajouter col1 = col3.
02/01/2008, 21h54
Michel SALAIS

Citation:

Envoyé par M_Dandouna

c tout con mais je savais pas comment le faire.
nb: j'ai oublié de vous dire que je travaille sur oracle 10G.

En fait dans Oracle 10g, le paramètre optimizer_search_limit n'est pas un paramètre d'initialisation mais il y a un paramètre de session modifiable avec alter session. Il y a également maintenant un paramètre caché _optimizer_search_limit. Ma demande était une réponse à une info qui signalait que ce paramètre était modifié.