Problème de performance sur SELECT avec jointures

**jalaval** · 15/12/2011, 15h45

Bonjour,

Je travaille sur une appli Web qui accède à une base oracle 10G sous Unix.

J'ai un problème de performance sur la requête suivante :

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
SELECT
        * 
    FROM
        ( SELECT
            DISTINCT PENSIONNE.ID_PENSIONNE ,
            BLOC_NOTE,
            DATE_DECES,
            DATE_NAISS,
            ID_PENSIONNE_TMP ,
            ID_SERVICE ,
            NOM_MARITAL ,
            NOM_PATRONYME ,
            PRENOMS
        FROM
            PENSIONNE,SOUS_DOSSIER,DOCUMENT  
        WHERE
            ID_SERVICE='059000'
	    AND SOUS_DOSSIER.ID_PENSIONNE = PENSIONNE.ID_PENSIONNE
	    AND DOCUMENT.ID_SOUS_DOSSIER = SOUS_DOSSIER.ID_SOUS_DOSSIER 
            AND ID_TYPE_DOCUMENT='10' 
        ORDER BY
            ID_PENSIONNE ) 
    WHERE
        ROWNUM <= 100

Le modèle de données pour les tables concernées est le suivant :

La structure des tables concernée est :

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
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
72
73
74
75
76
77
78
79
80
CREATE TABLE PENSIONNE ( 
  ID_PENSIONNE      VARCHAR2 (15)  NOT NULL, 
  ID_PENSIONNE_TMP  VARCHAR2 (15), 
  NOM_PATRONYME     VARCHAR2 (25)  NOT NULL, 
  PRENOMS           VARCHAR2 (20), 
  NOM_MARITAL       VARCHAR2 (25), 
  DATE_NAISS        DATE          NOT NULL, 
  DATE_DECES        DATE, 
  BLOC_NOTE         VARCHAR2 (500), 
  ID_SERVICE        VARCHAR2 (6)  NOT NULL, 
  CONSTRAINT PK_PENSIONNE
  PRIMARY KEY ( ID_PENSIONNE ) ) ; 
 
ALTER TABLE PENSIONNE ADD  CONSTRAINT FK_PENS_SERV
 FOREIGN KEY (ID_SERVICE) 
  REFERENCES GDPV8.SERVICE (ID_SERVICE) ;
 
CREATE INDEX IN_PENSIONNE_SERV ON 
  PENSIONNE(ID_SERVICE) ;
*****************************************
CREATE TABLE SOUS_DOSSIER ( 
  ID_SOUS_DOSSIER       NUMBER        NOT NULL, 
  ID_PENSIONNE          VARCHAR2 (15)  NOT NULL, 
  ID_PENSION            VARCHAR2 (9), 
  ID_TYPE_SOUS_DOSSIER  NUMBER (3)    NOT NULL, 
  NOM_SSDOS_SPECIFIQUE  VARCHAR2 (32), 
  CONSTRAINT UK_SOUS_DOSS
    UNIQUE (ID_PENSIONNE, ID_PENSION, ID_TYPE_SOUS_DOSSIER, NOM_SSDOS_SPECIFIQUE), 
  CONSTRAINT PK_PENSIONNES_TYPESSDOS
  PRIMARY KEY ( ID_SOUS_DOSSIER ) ) ; 
 
ALTER TABLE SOUS_DOSSIER ADD  CONSTRAINT FK_SSDOS_TYPESSDOS
 FOREIGN KEY (ID_TYPE_SOUS_DOSSIER) 
  REFERENCES GDPV8.TYPE_SOUS_DOSSIER (ID_TYPE_SOUS_DOSSIER) ;
 
ALTER TABLE SOUS_DOSSIER ADD  CONSTRAINT FK_SSDOS_PENS
 FOREIGN KEY (ID_PENSIONNE) 
  REFERENCES GDPV8.PENSIONNE (ID_PENSIONNE) ;
 
CREATE INDEX IN_SSDOS_TYPESSDOS ON 
  SOUS_DOSSIER(ID_TYPE_SOUS_DOSSIER) ;
 
CREATE INDEX IN_SSDOS_PENS ON 
  SOUS_DOSSIER(ID_PENSIONNE) ;
************************************************
CREATE TABLE DOCUMENT ( 
  ID_DOCUMENT       VARCHAR2 (240)  NOT NULL, 
  DATE_DEMAT        DATE          NOT NULL, 
  FORMDOC           VARCHAR2 (4)  NOT NULL, 
  FLAG_SUPPRIME     CHAR (1), 
  DATE_INT          DATE          NOT NULL, 
  DATE_SUP          DATE, 
  POSTIT            VARCHAR2 (255), 
  ID_TYPE_DOCUMENT  NUMBER (3), 
  ID_SOUS_DOSSIER   NUMBER, 
  ID_EVENEMENT      VARCHAR2 (30)  NOT NULL, 
  CONSTRAINT PK_DOCUMENT
  PRIMARY KEY ( ID_DOCUMENT ) ) ; 
 
ALTER TABLE DOCUMENT ADD  CONSTRAINT FK_DOC_SOUSDOSSIER
 FOREIGN KEY (ID_SOUS_DOSSIER) 
  REFERENCES GDPV8.SOUS_DOSSIER (ID_SOUS_DOSSIER) ;
 
ALTER TABLE DOCUMENT ADD  CONSTRAINT FK_DOC_TYPEDOC
 FOREIGN KEY (ID_TYPE_DOCUMENT) 
  REFERENCES GDPV8.TYPE_DOCUMENT (ID_TYPE_DOCUMENT) ;
 
ALTER TABLE DOCUMENT ADD  CONSTRAINT FK_DOC_EVEN
 FOREIGN KEY (ID_EVENEMENT) 
  REFERENCES GDPV8.EVENEMENT (ID_EVENEMENT) ;
 
 
CREATE INDEX IN_DOC_EVEN ON 
  DOCUMENT(ID_EVENEMENT) ;
 
CREATE INDEX IN_DOC_SSDOS ON 
  DOCUMENT(ID_SOUS_DOSSIER) ;
 
CREATE INDEX IN_DOC_TYPEDOC ON 
  DOCUMENT(ID_TYPE_DOCUMENT) ;

****************************************
La volumétrie des tables est :
PENSIONNE : 4.710.770 lignes
SOUS_DOSSIER : 8.345.646 lignes
DOCUMENT : 14.314.205 lignes
*********************************************

Le plan d’exécution d'Oracle est :

Plan hash value: 528657054

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |   305K|    32M|       |   174K  (1)| 00:32:00 |
|   1 |  HASH UNIQUE                   |                   |   305K|    32M|    72M|   174K  (1)| 00:32:00 |
|*  2 |   HASH JOIN                    |                   |   305K|    32M|    65M|   161K  (1)| 00:29:38 |
|*  3 |    HASH JOIN                   |                   |   611K|    58M|    29M| 58175   (1)| 00:10:40 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PENSIONNE         |   345K|    26M|       | 23172   (1)| 00:04:15 |
|*  5 |      INDEX RANGE SCAN          | IN_PENSIONNE_SERV |   345K|       |       |  2769   (1)| 00:00:31 |
|   6 |     TABLE ACCESS FULL          | SOUS_DOSSIER      |  8345K|   175M|       | 18611   (2)| 00:03:25 |
|*  7 |    TABLE ACCESS FULL           | DOCUMENT          |  4187K|    39M|       | 94977   (1)| 00:17:25 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DOCUMENTCO2_"."ID_SOUS_DOSSIER"="SOUSDOSSIE1_"."ID_SOUS_DOSSIER")
   3 - access("PENSIONNE0_"."ID_PENSIONNE"="SOUSDOSSIE1_"."ID_PENSIONNE")
   5 - access("PENSIONNE0_"."ID_SERVICE"='059000')
   7 - filter("DOCUMENTCO2_"."ID_TYPE_DOCUMENT"=10)

Le but de cette requête est de ramener la liste des pensionnés possédant au moins 1 document dont le type=10 (Un pensionné possède plusieurs sous dossiers dans lesquels sont classés des documents ayant chacun un type).

Cette requête met plus de 50s pour s’exécuter, ce qui n'est pas acceptable pour les utilisateurs.

Pourriez vous m'indiquer comment l'optimiser ?

Merci d'avance pour votre aide !

**Waldar** · 15/12/2011, 16h28

Votre colonne ID_TYPE_DOCUMENT est un nombre mais dans votre requête vous lui indiquez une chaîne de caractère : même si l'index était pertinent il n'est pas utilisé.

Globalement, la requête est mal écrite.
Pour tester une existence, on utilise... 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
With SR AS
(
  SELECT pns.ID_PENSIONNE
       , pns.BLOC_NOTE
       , pns.DATE_DECES
       , pns.DATE_NAISS
       , pns.ID_PENSIONNE_TMP
       , pns.ID_SERVICE
       , pns.NOM_MARITAL
       , pns.NOM_PATRONYME
       , pns.PRENOMS
    FROM PENSIONNE pns  
   WHERE pns.ID_SERVICE = '059000'
     AND EXISTS (SELECT null
                   FROM SOUS_DOSSIER sdo
                        INNER JOIN DOCUMENT doc
                          ON doc.ID_SOUS_DOSSIER = sdo.ID_SOUS_DOSSIER  
                  WHERE sdo.ID_PENSIONNE = pns.ID_PENSIONNE
                    AND doc.ID_TYPE_DOCUMENT = 10)
ORDER BY pns.ID_PENSIONNE ASC
)
SELECT ID_PENSIONNE
     , BLOC_NOTE
     , DATE_DECES
     , DATE_NAISS
     , ID_PENSIONNE_TMP
     , ID_SERVICE
     , NOM_MARITAL
     , NOM_PATRONYME
     , PRENOMS
  FROM SR
 WHERE ROWNUM <= 100;

**jalaval** · 15/12/2011, 16h57

Je vous remercie pour votre réponse.

Votre requête s’exécute en 25s, ce qui est mieux que 50s !

Cependant, la requête que j'ai postée étant générée automatiquement par Hibernate, je dois maintenant trouver comment forcer cette génération...

Une petite remarque : Je n'ai pas vu de différences dans le plan d’exécution en passant un NUMBER plutôt qu'une chaine de caractère pour la colonne ID_TYPE_DOCUMENT

**Mohamed.Houri** · 15/12/2011, 19h56

L'indentation du plan d'exécution n'est pas claire. Je ne peux pas distinguer clairement quelle opération est fille et quelle opération est mère.
Pourriez vous exécuter de nouveau la requête en mettant au début de celle-ci le hint /*+ gather_plan_statistics */.

Ensuite, immédiatement après l'exécution de la requête extraire son plan d'exécution à partir de la mémoire en faisant ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Pourriez vous aussi qualifier tous champs de votre select i.e faire précéder chaque champs de l'alias de la table d'où il provient.
Remarque : le distinct, est significatif d'un problème de design.

**jalaval** · 16/12/2011, 09h59

Bonjour,

Voici le plan d'execution comme demandé :

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
 
SQL_ID  fx4rnu828sqau, child number 0
-------------------------------------
select username from sys.all_users  order by username
 
Plan hash value: 2621262827
 
--------------------------------------------------------------------------
| Id  | Operation            | Name  | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------
|   1 |  SORT ORDER BY       |       |      9 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN          |       |      9 |  1324K|  1324K|  625K (0)|
|*  3 |    HASH JOIN         |       |      9 |  1707K|  1707K| 1151K (0)|
|   4 |     TABLE ACCESS FULL| TS$   |      6 |       |       |          |
|*  5 |     TABLE ACCESS FULL| USER$ |      9 |       |       |          |
|   6 |    TABLE ACCESS FULL | TS$   |      6 |       |       |          |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("U"."TEMPTS#"="TTS"."TS#")
   3 - access("U"."DATATS#"="DTS"."TS#")
   5 - filter("U"."TYPE#"=1)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

Et voici la requete telle que générée par Hibernate :

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
 
    select
        * 
    from
        ( select
            distinct pensionne0_.ID_PENSIONNE as ID1_5_,
            pensionne0_.BLOC_NOTE as BLOC2_5_,
            pensionne0_.DATE_DECES as DATE3_5_,
            pensionne0_.DATE_NAISS as DATE4_5_,
            pensionne0_.ID_PENSIONNE_TMP as ID5_5_,
            pensionne0_.ID_SERVICE as ID6_5_,
            pensionne0_.NOM_MARITAL as NOM7_5_,
            pensionne0_.NOM_PATRONYME as NOM8_5_,
            pensionne0_.PRENOMS as PRENOMS5_ 
        from
            PENSIONNE pensionne0_ 
        left outer join
            SOUS_DOSSIER sousdossie1_ 
                on pensionne0_.ID_PENSIONNE=sousdossie1_.ID_PENSIONNE 
        left outer join
            DOCUMENT documentco2_ 
                on sousdossie1_.ID_SOUS_DOSSIER=documentco2_.ID_SOUS_DOSSIER 
        where
            pensionne0_.ID_SERVICE='059000' 
            and documentco2_.ID_TYPE_DOCUMENT='10' 
        order by
            pensionne0_.ID_PENSIONNE ) 
    where
        rownum <= ?

Merci par avance pour vos réponses et votre aide.

**Mohamed.Houri** · 16/12/2011, 10h39

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
 
SQL_ID  fx4rnu828sqau, child number 0
-------------------------------------
SELECT username FROM sys.all_users  ORDER BY username
 
Plan hash value: 2621262827
 
--------------------------------------------------------------------------
| Id  | Operation            | Name  | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------
|   1 |  SORT ORDER BY       |       |      9 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN          |       |      9 |  1324K|  1324K|  625K (0)|
|*  3 |    HASH JOIN         |       |      9 |  1707K|  1707K| 1151K (0)|
|   4 |     TABLE ACCESS FULL| TS$   |      6 |       |       |          |
|*  5 |     TABLE ACCESS FULL| USER$ |      9 |       |       |          |
|   6 |    TABLE ACCESS FULL | TS$   |      6 |       |       |          |

Le plan d'exécution que vous avez fourni ne correpond pas au plan d'exécution de votre requête. Pour faire simple, procedez comme suit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
alter session set statistics_level=ALL;
executez votre requete et suivez la immediatement de la commande suivante
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

J'aimerai déjà savoir si Oracle est en train de faire de bonnes estimations lorsqu'il procède à la génération du plan d'exécution.

Combien de lignes sont produites par votre requête? 305.000? Si ce nombre est exact à quoi cela sert-il d'afficher 305.000 lignes aux yeux d'un être humain?

**mnitu** · 16/12/2011, 10h05

Envoyé par Mohamed.Houri

L'indentation du plan d'exécution n'est pas claire. Je ne peux pas distinguer clairement quelle opération est fille et quelle opération est mère.
...

C'est tout à fait vrai. D'autre parte je pense il n'y a qy'ne seule interprétation possible

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
------------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name              | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT               |                   | 305K | 32M   |       | 174K (1)   | 00:32:00 |
| 1  | HASH UNIQUE                    |                   | 305K | 32M   | 72M   | 174K (1)   | 00:32:00 |
|* 2 |  HASH JOIN                     |                   | 305K | 32M   | 65M   | 161K (1)   | 00:29:38 |
|* 3 |   HASH JOIN                    |                   | 611K | 58M   | 29M   | 58175 (1)  | 00:10:40 |
| 4  |     TABLE ACCESS BY INDEX ROWID| PENSIONNE         | 345K | 26M   |       | 23172 (1)  | 00:04:15 |
|* 5 |       INDEX RANGE SCAN         | IN_PENSIONNE_SERV | 345K |       |       | 2769 (1)   | 00:00:31 |
| 6  |   TABLE ACCESS FULL            | SOUS_DOSSIER      | 8345K| 175M  |       | 18611 (2)  | 00:03:25 |
|* 7 |  TABLE ACCESS FULL             | DOCUMENT          | 4187K| 39M   |       | 94977 (1)  | 00:17:25 |
------------------------------------------------------------------------------------------------------------

**jalaval** · 16/12/2011, 10h08

Envoyé par Waldar

Votre colonne ID_TYPE_DOCUMENT est un nombre mais dans votre requête vous lui indiquez une chaîne de caractère : même si l'index était pertinent il n'est pas utilisé.

Globalement, la requête est mal écrite.
Pour tester une existence, on utilise... 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
With SR AS
(
  SELECT pns.ID_PENSIONNE
       , pns.BLOC_NOTE
       , pns.DATE_DECES
       , pns.DATE_NAISS
       , pns.ID_PENSIONNE_TMP
       , pns.ID_SERVICE
       , pns.NOM_MARITAL
       , pns.NOM_PATRONYME
       , pns.PRENOMS
    FROM PENSIONNE pns  
   WHERE pns.ID_SERVICE = '059000'
     AND EXISTS (SELECT null
                   FROM SOUS_DOSSIER sdo
                        INNER JOIN DOCUMENT doc
                          ON doc.ID_SOUS_DOSSIER = sdo.ID_SOUS_DOSSIER  
                  WHERE sdo.ID_PENSIONNE = pns.ID_PENSIONNE
                    AND doc.ID_TYPE_DOCUMENT = 10)
ORDER BY pns.ID_PENSIONNE ASC
)
SELECT ID_PENSIONNE
     , BLOC_NOTE
     , DATE_DECES
     , DATE_NAISS
     , ID_PENSIONNE_TMP
     , ID_SERVICE
     , NOM_MARITAL
     , NOM_PATRONYME
     , PRENOMS
  FROM SR
 WHERE ROWNUM <= 100;

En fait j'ai répondu un peu vite : Votre requête s’exécute bien en 25s, mais ne renvoie aucune ligne... Je vais essayer de comprendre pourquoi.

**jalaval** · 16/12/2011, 11h18

Envoyé par jalaval

En fait j'ai répondu un peu vite : Votre requête s’exécute bien en 25s, mais ne renvoie aucune ligne... Je vais essayer de comprendre pourquoi.

Etrange : J'utilisais une vieille version de TOAD (V6.x) pour exécuter votre requête et elle ne ramenait aucune ligne. En utilisant TOAD V10 elle fonctionne parfaitement !

Problème de performance sur SELECT avec jointures

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager