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 !