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.

mnitu · 16/12/2011, 10h05

Citation:

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

Citation:

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.

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?

Waldar · 16/12/2011, 10h45

Citation:

Envoyé par Mohamed.Houri

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?

Si vous regardez bien la première requête, il s'arrête à 100.
C'était peut-être pour le test cela dit.

jalaval · 16/12/2011, 10h53

Citation:

Envoyé par Waldar

Si vous regardez bien la première requête, il s'arrête à 100.
C'était peut-être pour le test cela dit.

En fait il s'agit d'une application de recherche et visualisation de documents stockés dans un conteneur Geide.

L'interface de recherche permet une interrogation multi critères sur la base d'index. Seuls les 100 premiers résultats de la recherche sont proposés à l'utilisateur, qui peut alors affiner ses critères de recherche si nécessaire.

mnitu · 16/12/2011, 10h57

Votre première requête utilise des jointures, la deuxième des jointures externes. Le résultat n'est pas identique.

Mohamed.Houri · 16/12/2011, 11h07

Citation:

Envoyé par Waldar

Si vous regardez bien la première requête, il s'arrête à 100.
C'était peut-être pour le test cela dit.

C'est certainement un système de pagination qu'il est en train de faire. Attendons qu'il nous fournisse le vrai plan d'exécution avant de faire les remarques adéquates. Sur la base de ce qu'il a fourni jusqu'à présent, sur les 32 secondes de la requête 17 secondes sont consommées par l'opération

Code :

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

1
2
 
* 7 |  TABLE ACCESS FULL             | DOCUMENT          | 4187K| 39M   |       | 94977 (1)  | 00:17:25

avec le filtre 7 correspondant

Code :

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

1
2
 
 7 - filter("DOCUMENTCO2_"."ID_TYPE_DOCUMENT"=10)

Attendons de voir si l'utilisation d'un index s'impose ou pas dans ce cas. Je pense.
Aussi, qu'il ne selectionne que d'une seule table alors que la clause from en contient deux autres. Il est peut-ête plus judicieux dans ce cas d'utiliser des EXISTS, comme vous l'avez signalé.

Il utilise aussi un order by alors que l'explain plan fourni ne montre ni une operation SORT ORDER BY ni NO SORT OPERATION en cas d'utilisation d'un index approprié pour éviter le tri.

jalaval · 16/12/2011, 11h08

Citation:

Envoyé par Mohamed.Houri

[code]
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?

Voici le résultat des commandes demandées :

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
PLAN_TABLE_OUTPUT
SQL_ID  092tb2db3k8kb, child number 0
-------------------------------------
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
 
Plan hash value: 4143410828
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                    |                   |      1 |        |    100 |00:00:43.89 |     567K|    667K|    105K|       |       |          |         |
|   2 |   VIEW                            |                   |      1 |    305K|    100 |00:00:43.89 |     567K|    667K|    105K|       |       |          |         |
|*  3 |    SORT ORDER BY STOPKEY          |                   |      1 |    305K|    100 |00:00:43.89 |     567K|    667K|    105K| 22528 | 22528 |20480  (0)|         |
|   4 |     SORT UNIQUE                   |                   |      1 |    305K|    313K|00:00:43.04 |     567K|    667K|    105K|    32M|  2328K| 8316K (1)|   36864 |
|*  5 |      HASH JOIN                    |                   |      1 |    305K|    384K|00:00:40.89 |     567K|    658K|  96416 |    86M|  8728K| 6633K (1)|   98304 |
|*  6 |       HASH JOIN                   |                   |      1 |    611K|    668K|00:00:18.44 |     107K|    178K|  72639 |    42M|  4432K| 6781K (1)|     289K|
|   7 |        TABLE ACCESS BY INDEX ROWID| PENSIONNE         |      1 |    345K|    360K|00:00:00.72 |   18734 |  18734 |      0 |       |       |          |         |
|*  8 |         INDEX RANGE SCAN          | IN_PENSIONNE_SERV |      1 |    345K|    360K|00:00:00.01 |    2403 |   2403 |      0 |       |       |          |         |
|   9 |        TABLE ACCESS FULL          | SOUS_DOSSIER      |      1 |   8345K|   8345K|00:00:00.01 |   88371 |  87191 |      0 |       |       |          |         |
|* 10 |       TABLE ACCESS FULL           | DOCUMENT          |      1 |   4187K|   4173K|00:00:12.52 |     460K|    455K|      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   5 - access("DOCUMENT"."ID_SOUS_DOSSIER"="SOUS_DOSSIER"."ID_SOUS_DOSSIER")
   6 - access("SOUS_DOSSIER"."ID_PENSIONNE"="PENSIONNE"."ID_PENSIONNE")
   8 - access("ID_SERVICE"='059000')
  10 - filter("ID_TYPE_DOCUMENT"=10)

jalaval · 16/12/2011, 11h18

Citation:

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 !

Mohamed.Houri · 16/12/2011, 11h36

Voilà qui est bien.

12,52 secondes pour

Code :

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

1
2
 
TABLE ACCESS FULL | DOCUMENT

18,44 secondes pour

Code :

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

1
2
 
* 6 | HASH JOIN | | 1 | 611K| 668K|00:00:18.44

et
10 secondes pour

Code :

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

1
2
 
|* 5 | HASH JOIN | | 1 | 305K| 384K|00:00:40.89

Ce qui fait 40,96 secondes pour ces 3 operations. Sachant que le temps de réponse total est de 43, 86 secondes vous savez déjà où focaliser vos efforts.

En passant, vous disposez d'excellentes statistiques puisqu'Oracle est en train de faire d'excellentes estimations.

Je vais consacrer encore un peu de temps pour voir si on peut quand même faire quelque chose. En effet selectionner 4.173.000 lignes en 12 secondes je me demande si ce n'est pas assez rapide

jalaval · 16/12/2011, 11h50

Citation:

Envoyé par Mohamed.Houri

Voilà qui est bien.

Je vais consacrer encore un peu de temps pour voir si on peut quand même faire quelque chose. En effet selectionner 4.173.000 lignes en 12 secondes je me demande si ce n'est pas assez rapide

Voulez vous dire par la que les performances affichées sont normales au regard de la volumétrie et du modèle de données ?

Si oui, devons nous envisager une dé-normalisation si ces performances ne sont pas jugées acceptables ?

Merci encore pour votre aide.

Bien cordialement,
J.Laval

Waldar · 16/12/2011, 11h58

Citation:

Envoyé par Mohamed.Houri

En passant, vous disposez d'excellentes statistiques puisqu'Oracle est en train de faire d'excellentes estimations.

Vous trouvez ?
DOCUMENT : 14.314.205 lignes
TABLE ACCESS FULL | DOCUMENT | 1 | 4187K

L'index sur DOCUMENT.IN_DOC_TYPEDOC n'est pas utilisé.
C'est soit parce qu'il y a trop de IN_DOC_TYPEDOC à 10, soit parce que les stats ne reflètent pas la réalité.

jalaval, que donne cette requête :

Code :

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

1
2
SELECT count(*), count(case IN_DOC_TYPEDOC when 10 then 1 end)
  FROM document;

Mohamed.Houri · 16/12/2011, 12h23

Citation:

Envoyé par Waldar

Vous trouvez ?
DOCUMENT : 14.314.205 lignes
TABLE ACCESS FULL | DOCUMENT | 1 | 4187K

L'index sur DOCUMENT.IN_DOC_TYPEDOC n'est pas utilisé.
C'est soit parce qu'il y a trop de IN_DOC_TYPEDOC à 10, soit parce que les stats ne reflètent pas la réalité.

Observez bien l'opération suivante avec sa partie prédicate

Code :

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

1
2
3
4
5
6
7
8
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 10 |       TABLE ACCESS FULL           | DOCUMENT          |      1 |   4187K|   4173K|00:00:12.52 |     460K|    455K|      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
10 - filter("ID_TYPE_DOCUMENT"=10)

Ce qui veut dire qu'Oracle fait un full table scan sur la table document(c'est vrai qu'il faut savoir combien de records il existe dans cette table) puis applique le filtre 10 pour n'en retenir que 4.173.000 lignes.

Quel pourcentage représentent ces 4.173.000 lignes par rapport au nombre total de lignes dans la table DOCUMENT?

Mais comme E-Rows et A-Rows de cette opération sont pratiquement identiques ceci veut dire que le CBO a une bonne vue de la table et a bien estimé que l'utilisation de l'index n'est pas appropriée.

ORA-007 · 16/12/2011, 12h58

Bonjour,

Un indicateur que j'utilise pour mesurer la pertinence d'un index est :

Code :

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

1
2
3
4
SELECT table_name,index_name,distinct_keys/num_rows AS selectivite
FROM user_indexes
WHERE table_name = 'MA_TABLE'
;

Un indice de 1 est la meilleur selectivité possible.

PS : évidement tout ceci n'a de sens qu'une fois les stats calculées.

PS2 : Quelqu'un sait il le seuil à partir duquel Oracle fait un FULL SCAN et quel parametre le régit ?

mnitu · 16/12/2011, 13h24

Citation:

Envoyé par Mohamed.Houri

...
Quel pourcentage représentent ces 4.173.000 lignes par rapport au nombre total de lignes dans la table DOCUMENT?
....

C'est écrit de le début 4.173.000 /

Citation:

DOCUMENT : 14.314.205 lignes

Pas d'index parce que c'est un Hash Join.
C'est un Hash Join parce qu’on estime que ça ramènera 305 K!
Mais on en garde que 100!
Peut être qu’en changeant de stratégie (= de requête) on pourrait gagner quelque chose de plus.

15/12/2011, 16h57	#3
jalaval Invité de passage jean LAVAL Chef de projet NTIC Inscription : décembre 2011 Messages : 15 Détails du profil Informations personnelles : Nom : jean LAVAL Localisation : France, Somme (Picardie) Informations professionnelles : Activité : Chef de projet NTIC Secteur : Administration - Collectivité locale Informations forums : Inscription : décembre 2011 Messages : 15 Points : 1 Points : 1	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
	00

16/12/2011, 10h57	#11
mnitu Expert Confirmé Sénior Marius Nitu Ingénieur développement logiciels Inscription : octobre 2007 Messages : 3 313 Détails du profil Informations personnelles : Nom : Marius Nitu Localisation : France, Marne (Champagne Ardenne) Informations professionnelles : Activité : Ingénieur développement logiciels Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : octobre 2007 Messages : 3 313 Points : 5 817 Points : 5 817	Votre première requête utilise des jointures, la deuxième des jointures externes. Le résultat n'est pas identique.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email