Problème de performance sur SELECT avec jointures

Version imprimable

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:

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 :
http://i48.servimg.com/u/f48/11/98/61/03/esxtra10.jpg

La structure des tables concernée est :

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

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:

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;

15/12/2011, 17h57
jalaval

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
15/12/2011, 20h56
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.
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:

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.

Bonjour,

Voici le plan d'execution comme demandé :

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

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.

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:

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

Citation:

Envoyé par Waldar

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

Code:

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:

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?

16/12/2011, 11h45
Waldar

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.
16/12/2011, 11h53
jalaval

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.
16/12/2011, 11h57
mnitu

Votre première requête utilise des jointures, la deuxième des jointures externes. Le résultat n'est pas identique.
16/12/2011, 12h07
Mohamed.Houri
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:

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

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.

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:

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'));

Voici le résultat des commandes demandées :

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

16/12/2011, 12h18
jalaval

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 !
16/12/2011, 12h36
Mohamed.Houri
Voilà qui est bien.

12,52 secondes pour
Code:

1 2 TABLE ACCESS FULL | DOCUMENT
18,44 secondes pour
Code:

1 2 * 6 | HASH JOIN | | 1 | 611K| 668K|00:00:18.44
et
10 secondes pour
Code:

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 :)
16/12/2011, 12h50
jalaval

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
16/12/2011, 12h58
Waldar
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:

1 2 select count(*), count(case IN_DOC_TYPEDOC when 10 then 1 end) from document;

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:

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.

16/12/2011, 13h58
ORA-007
Bonjour,

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

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 ?
16/12/2011, 14h24
mnitu

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.

Citation:

Envoyé par mnitu

C'est écrit de le début 4.173.000 /
Peut être qu’en changeant de stratégie (= de requête) on pourrait gagner quelque chose de plus.

Voici le plan d'execution pour la même requete avec un type de document différent, beaucoup moins représenté dans la table Document :

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
PLAN_TABLE_OUTPUT
SQL_ID  2r597zddqdwd9, child number 0
-------------------------------------
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='150'          ORDER BY             pensionne0_.ID_PENSIONNE )      WHERE         rownum <
 
Plan hash value: 778032787
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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:19.02 |     179K|    177K|    403 |       |       |          |         |
|   2 |   VIEW                            |                   |      1 |   8535 |    100 |00:00:19.02 |     179K|    177K|    403 |       |       |          |         |
|*  3 |    SORT ORDER BY STOPKEY          |                   |      1 |   8535 |    100 |00:00:19.02 |     179K|    177K|    403 | 22528 | 22528 |20480  (0)|         |
|   4 |     SORT UNIQUE                   |                   |      1 |   8535 |  13793 |00:00:18.99 |     179K|    177K|    403 |  2210K|   780K| 1964K (0)|         |
|*  5 |      HASH JOIN                    |                   |      1 |   8535 |  17196 |00:00:18.90 |     179K|    177K|    403 |  4740K|  1518K| 7739K (0)|    3072 |
|*  6 |       HASH JOIN                   |                   |      1 |    116K|    100K|00:00:17.39 |     160K|    158K|      0 |  3861K|  1994K| 6715K (0)|         |
|   7 |        TABLE ACCESS BY INDEX ROWID| DOCUMENT          |      1 |    116K|    101K|00:00:04.46 |   72317 |  71902 |      0 |       |       |          |         |
|*  8 |         INDEX RANGE SCAN          | IN_DOC_TYPEDOC    |      1 |    116K|    101K|00:00:03.25 |     638 |    638 |      0 |       |       |          |         |
|   9 |        TABLE ACCESS FULL          | SOUS_DOSSIER      |      1 |   8345K|   8345K|00:00:00.01 |   88371 |  86837 |      0 |       |       |          |         |
|  10 |       TABLE ACCESS BY INDEX ROWID | PENSIONNE         |      1 |    345K|    360K|00:00:00.72 |   18734 |  18587 |      0 |       |       |          |         |
|* 11 |        INDEX RANGE SCAN           | IN_PENSIONNE_SERV |      1 |    345K|    360K|00:00:00.01 |    2403 |   2403 |      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=100)
   3 - filter(ROWNUM<=100)
   5 - access("PENSIONNE0_"."ID_PENSIONNE"="SOUSDOSSIE1_"."ID_PENSIONNE")
   6 - access("DOCUMENTCO2_"."ID_SOUS_DOSSIER"="SOUSDOSSIE1_"."ID_SOUS_DOSSIER")
   8 - access("DOCUMENTCO2_"."ID_TYPE_DOCUMENT"=150)
  11 - access("PENSIONNE0_"."ID_SERVICE"='059000')

Le nombre d'occurence de la valeur id_type_document=150 dans la table document est de :

Code:

SELECT count(*), count(case Id_TYPE_DOCument when 150 then 1 end) FROM document ==> 101073

Nous pourrions envisager de demander à l'utilisateur d'affiner ses critères de recherches dans le cas ou sa demande renverrait plus de 100 réponses.

La question est : Comment savoir à l'avance combien de lignes la requete va renvoyer ?
Faire un count coute quasiment aussi cher que ramener les lignes.

Voyez vous un un moyen de résoudre cette problématique ?

Par exemple, est il possible d'interroger dans un programme Java le plan d’exécution d'une requête ou bien les données statistiques ?

16/12/2011, 15h08
Rams7s

Perso, je tenterai bien un index sur documents(id_type_document,id_sous_dossier) au lieu de l'index sur documents(id_type_document).

Pour le requetes ou le numero de type_document est peu present, on aurait le meme effet. Et pour les requetes genre celle ou vous avec id_type_document=10 qui renvoie tout plein de resultats, ca permettrait peut-etre d'utiliser l'index, en un "fast full-index scan" vu que toutes les infos seraient dedans.
Parce que la concretement, il peut pas l'utiliser pour id_type_document=10.

Edit:Et d'ailleurs, tant qu'on y est: :P
SOUS_DOSSIER(ID_PENSIONNE,ID_SOUS_DOSSIER )Mais pour l'ordre des colonnes, la je suis pas tres convaincu. Mais bon, visiblement c'est pas ca qui prends du temps, ce qui est marrant d'ailleurs...
16/12/2011, 15h39
jalaval

Merci pour cette piste.
Cependant, j'ai créé l'index documents(id_type_document,id_sous_dossier), mais cela ne change rien au plan d’exécution : ce nouvel index n'est pas utilisé.
16/12/2011, 15h39
Mohamed.Houri

Citation:

Envoyé par ORA-007

Bonjour,

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

Vous avez votre réponse dans l'article suivant de jonathan Lewis

http://jonathanlewis.wordpress.com/category/philosophy/

Que je viens de traduire à l'instant

Traduction

Si vous exécutez une requête qui est supposée retourner un seul enregistrement à partir d'une très large table, et, vous disposez d'un index approprié sur cette table, vous vous attendrez probablement à ce que l'optimisateur d'Oracle identifie l'index et qu'il l'utilise. Si vous changez votre requête de telle sorte qu'elle renvoi tous les enregistrements de cette table (sans tri) vous vous attendrez probablement à ce que l'optimisateur d'Oracle choisisse un full table scan.

Ceci conduit à la très simple idée qui est souvent ignorée

"Quelques fois il suffit juste d'un enregistrement supplémentaire pour passer d'un plan d'exécution utilisant un index à un plan utilisant un full table scan"

Il existe un point où l'optimisateur change d'un accès indexé à un seul enregistrement vers un accès à toute la table pour tous les enregistrements.

Si vous êtes assez chanceux et le modèle de l'optimisateur est parfait il n'y aura aucun effet significatif sur la performance, bien sûr. Mais, nous ne sommes pas aussi chanceux que ça, et c'est pour cette raison que les gens finissent par poser la question : 'Comment le plan d'exécution est devenu subitement non performant, il n'y a eu aucun changement .... sauf pour un petit supplément de données?". Tout ce qu'il faut c'est un seul enregistrement (que l'optimisateur reconnait) pour changer d'un plan d'exécution à un autre- et parfois l'optimisateur trouve le mauvais moment pour opérer ce changement.
16/12/2011, 17h04
jalaval

Je reformule ma question : Est il possible dans un programme (java ou autre) de connaitre le nombre de lignes qu'une requête va ramener, mais sans exécuter la requête (ie en se basant sur les statistiques) ?
16/12/2011, 17h12
Mohamed.Houri

Si tant est que cela soit possible, comment être sûr dans ce cas que les statistiques sont à jour????

Je ne pense pas que ce que vous proposez soit possible. Il faudrait plutôt se concentrer sur l'amélioration de votre requête.

J'ai une question quand même : Est-ce que le client se plaint de la performance parce qu'il s'est habitué à voir cette requête s'exécuter rapidement ou bien c'est du premier coup qu'il s'est plaint de la performance?
16/12/2011, 17h19
jalaval

Le client est en phase de recette de cette application, et considère comme une anomalie les temps de réponse de ce cas d'utilisation.

Il accepterait que le système renvoie un message à l'utilisateur lui précisant que le résultat de sa recherche va dépasser les 100 réponses et lui demandant d'affiner ses critères de recherches (sans que la recherche soit effectivement faite).

En ce qui concerne la validité des statistiques, ne sont elles pas maintenues à jour en permanence par Oracle ?
16/12/2011, 17h20
Mohamed.Houri

Citation:

Envoyé par mnitu

C'est écrit de le début 4.173.000 /
Pas d'index parce que c'est un Hash Join.

Ce n'est certainement pas une nécessité ni une règle que la jointure HASH JOIN se fasse toujours via un FULL TABLE SCAN. Jonathan Lewis dans son livre Cost Based Oracle Fundamentals au Chapitre 12, Hash Join, page 321, parle(par l'exemple) justement de cette pas toujours vraie règle mais néanmoins largement répandue dans le monde Oracle

Citation:

Envoyé par Mohamed.Houri

Je ne pense pas que ce que vous proposez soit possible. Il faudrait plutôt se concentrer sur l'amélioration de votre requête.

AMHA, un truc comme ca peut fonctionner, ca copie juste le fonctionnement de autotrace traceonly. Mais bon, qui dit explain plan, dit erreur possible aussi. Justement avec les statistiques.
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 --explain plan set statement_id='toto' for select * from tmp2; select cardinality, id,depth, position from plan_table where statement_id='toto' order by id; create or replace function tmp_func(p_param number) return number is begin dbms_lock.sleep(0.1); return 1; end; / associate statistics with functions tmp_func default selectivity 100; drop table tmp2; create table tmp2 as select level as n from dual connect by level<=100; set timing on; --set autotrace on --select * from tmp2 where tmp_func(n)=1; --Temps: 1000*0.1 = 10s --set autotrace off --set autotrace traceonly --select * from tmp2 where tmp_func(n)=1; --Temps: 1000*0.1 = 10s --set autotrace off explain plan set statement_id='miouf2' for select * from tmp2 where tmp_func(n)=1; select cardinality from plan_table where statement_id='miouf2' and id=0;
Bien entendu, dans ce cas la il faudra soit changer le statement_id, soit faire un tri sur le plan qui vous interesse. ;)

Il faut bien définir votre besoin.
Si votre seuil d'affichage est de cent lignes, la première requête que je vous ai donné peut répondre rapidement : le calcul du tri avait un certain coût.
On peut aussi envisager un hint - je ne suis pas certain de l'impact cela dit :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SELECT /*+ FIRST_ROWS */ 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) AND ROWNUM <= 101;
Si vous atteignez la ligne 101, c'est que vous avez plus de cent lignes et vous pouvez afficher un message adéquat à l'utilisateur.

En y réfléchissant, même avec le type 150 vous arrivez à 13.000 lignes, il faut repenser votre besoin je pense.

En soit ça ne me choque pas que la requête puisse ramener dans les 100 000 lignes tant qu'elle est paginée pour ne fetch que 100 lignes.
Après je ne vois pas pourquoi essayer de conserver la requête très innéficace d'hibernate.
En effet DISTINCT + LEFT JOIN avec filtre WHERE sur la table en LEFT JOIN (ce qui revient à faire un INNER JOIN...) montre que cette requête ne convient pas du tout.

Il faut partir de la requête de Waldar (hibernate peux executer des requêtes directement) et regarder son plan.
Il peut être intéressant de faire une trace level 12 + TKPROF pour voir ce qui se passe car apparemment le FULL SCAN de DOCUMENTS est qu'en même très lent.

Sinon j'utiliserais bien aussi le hint FIRST_ROWS(N) dans la requête de Waldar (de toute façon ça fournie juste plus d'info à l'optimiseur, ça ne fait donc pas de mal):
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 WITH SR AS ( SELECT /*+ FIRST_ROWS(100) */ 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;
Après si c'est toujours très lent il est peut être envisageable de s'orienter vers une vue matérialisée qui pourrait réduire le travail à fournir pour la requête.

Juste un point, il me semble que tu disais que tu étais en recette, le serveur de recette est il ISO future PROD ? notemment concernant les disques.

19/12/2011, 20h34
pacmann

Salut,

Je suis bien d'accord avec toi Waldar. Cela dit ce n'est pas forcément le besoin qui est mal exprimé, ça peut très bien être le modèle ou le "process".

Finalement, on voit souvent ce genre de problème : ton modèle est totalement normalisé, et tes critères de recherche (sans dépendances) sont éparpillés. T'es obligé de faire une VM comme le suggère Skuat.

La seule question qui reste c'est : est-il rationnel de faire une recherche sur la possession d'un type de document ? Quel est la fréquence d'une telle recherche ?

- Si c'est un cas d'exception, 50 secondes ne sont vraiment pas choquantes.
- Si c'est le quotidien du chargé, il faut faire une VM, et surtout se demander pourquoi on doit sortir en interactif la liste des gens qui ont eu un certain type de document...
20/12/2011, 12h28
jalaval

Un grand merci à tous pour vos réponses pertinentes.
Je vais tester les différentes solutions/approches proposées et vous ferais un retour.

Concernant les différentes questions posées :

Citation:

Envoyé par skuatamad

Juste un point, il me semble que tu disais que tu étais en recette, le serveur de recette est il ISO future PROD ? notemment concernant les disques.

Notre serveur de recette est configuré pour être "aussi proche" de la production que possible, on peut donc s'attendre à des performances similaires.

Citation:

Envoyé par pacmann

La seule question qui reste c'est : est-il rationnel de faire une recherche sur la possession d'un type de document ? Quel est la fréquence d'une telle recherche ?

- Si c'est un cas d'exception, 50 secondes ne sont vraiment pas choquantes.
- Si c'est le quotidien du chargé, il faut faire une VM, et surtout se demander pourquoi on doit sortir en interactif la liste des gens qui ont eu un certain type de document...

C'est effectivement la question que devra se poser la MOA si l'on ne trouve pas de solution d'optimisation efficace.
20/12/2011, 13h00
pacmann

Citation:

Envoyé par jalaval

C'est effectivement la question que devra se poser la MOA si l'on ne trouve pas de solution d'optimisation efficace.

Si je peux me permettre, à mon sens, la MOA pourrait (devrait ?) se poser cette question même si on trouve une optimisation.

A quoi ça sert ? Dans quel contexte ? Est-ce vraiment ce qu'on cherche dans notre process ? Notre process est-il bien design-gné ?
=> Ca a l'air intéressant en tant que MOA, non ? ;)

Citation:

Envoyé par skuatamad

Il peut être intéressant de faire une trace level 12 + TKPROF pour voir ce qui se passe car apparemment le FULL SCAN de DOCUMENTS est qu'en même très lent.

Voici le résultat de la trace demandée :

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
"jla.txt" 122 lignes, 5099 caractères
 
TKPROF: Release 10.2.0.3.0 - Production on Mar. Déc. 20 14:24:47 2011
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Trace file: gm2011_ora_1646672.trc
Sort options: exeela
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
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
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          0          0           0
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 30
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02
  db file sequential read                     48515        0.00          0.31
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0          0          0           0
 
Misses in library cache during parse: 1
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     2        9.88          9.91
  db file sequential read                     48515        0.00          0.31
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
 
    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: gm2011_ora_1646672.trc
Trace file compatibility: 10.01.00
Sort options: exeela
       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       1  unique SQL statements in trace file.
   48621  lines in trace file.
       0  elapsed seconds in trace file.

20/12/2011, 16h47
mnitu

Deux parse un execute ce n’est pas bon mais ça c’est une autre histoire.
Sinon ça va tellement vite sauf que apparemment ça n’a rien ramené (rows = 0).
Mais, il se peut aussi que la session n'a pas été fermée et que les statististique ne sont pas disponible.

Citation:

Envoyé par mnitu

Deux parse un execute ce n’est pas bon mais ça c’est une autre histoire.
Sinon ça va tellement vite sauf que apparemment ça n’a rien ramené (rows = 0).
Mais, il se peut aussi que la session n'a pas été fermée et que les statististique ne sont pas disponible.

OK voici la trace session fermée (c'est un peu long) :

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
 
 
TKPROF: Release 10.2.0.3.0 - Production on Mer. Déc. 21 09:40:25 2011
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Trace file: /u02/admin/GM2011/udump/gm2011_ora_jla.trc
Sort options: exeela  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
select /*+ rule */ bucket, endpoint, col#, epvalue 
from
 histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     97      0.02       0.01          0          0          0           0
Fetch       97      0.02       0.09         17        395          0        1862
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      195      0.04       0.11         17        395          0        1862
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     20  SORT ORDER BY (cr=4 pr=2 pw=0 time=12030 us)
     20   TABLE ACCESS CLUSTER HISTGRM$ (cr=4 pr=2 pw=0 time=11916 us)
      1    INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=22 us)(object id 252)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        17        0.00          0.07
********************************************************************************
 
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, 
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, 
  spare1, spare2, avgcln 
from
 hist_head$ where obj#=:1 and intcol#=:2
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     16      0.00       0.00          0          0          0           0
Fetch       16      0.00       0.01          2         48          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       33      0.00       0.02          2         48          0          16
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=1 pw=0 time=8853 us)
      1   INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=61 us)(object id 257)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         2        0.00          0.01
********************************************************************************
 
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
  o.dataobj#,o.flags 
from
 obj$ o where o.obj#=:1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.01       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         30          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.01       0.00          0         30          0          10
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=92 us)
      1   INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=56 us)(object id 36)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1      119.34        119.34
********************************************************************************
 
begin :id := sys.dbms_transaction.local_transaction_id; end;
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1
 
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 30  
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02
********************************************************************************
 
select 'x' 
from
 dual
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 30  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.02          0.02
********************************************************************************
 
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
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     41.30     244.17     542230    4183975          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     41.32     244.20     542230    4183975          0         100
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 30  
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       59.49         59.52
  db file sequential read                    542230        0.17        209.16
  SQL*Net more data to client                     5        0.00          0.00
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.02       0.02          0          0          0           0
Execute      3      0.00       0.00          0          0          0           1
Fetch        2     41.30     244.17     542230    4183975          0         101
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     41.32     244.20     542230    4183975          0         102
 
Misses in library cache during parse: 2
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net message from client                     6      119.34        179.59
  db file sequential read                    542230        0.17        209.16
  SQL*Net more data to client                     5        0.00          0.00
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute    123      0.03       0.02          0          0          0           0
Fetch      123      0.02       0.10         19        473          0        1888
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      249      0.05       0.13         19        473          0        1888
 
Misses in library cache during parse: 3
Misses in library cache during execute: 3
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        19        0.00          0.09
 
    4  user  SQL statements in session.
  123  internal SQL statements in session.
  127  SQL statements in session.
********************************************************************************
Trace file: /u02/admin/GM2011/udump/gm2011_ora_jla.trc
Trace file compatibility: 10.01.00
Sort options: exeela  
       1  session in tracefile.
       4  user  SQL statements in trace file.
     123  internal SQL statements in trace file.
     127  SQL statements in trace file.
       6  unique SQL statements in trace file.
  545371  lines in trace file.
     303  elapsed seconds in trace file.

21/12/2011, 18h47
mnitu
Bref, on y voit la même chose :
- Requêtes récursives induites par le parsing hard de la requête, mais sans importance.
- Un nombre important de lecture physique
- Un nombre important de lecture en mode consistent
- Temps d’attente important dans la lecture en mode monobloc des données, très probablement d’un(des) index(es).
Peut-on avoir aussi le plan d’exécution pour cette requête ?

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.02 0.02 0 0 0 0 Execute 3 0.00 0.00 0 0 0 1 Fetch 2 41.30 244.17 542230 4183975 0 101 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 41.32 244.20 542230 4183975 0 102 Misses IN library cache during parse: 2 Elapsed times include waiting ON following events: Event waited ON Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message TO client 6 0.00 0.00 SQL*Net message FROM client 6 119.34 179.59 db file sequential READ 542230 0.17 209.16 SQL*Net more DATA TO client 5 0.00 0.00
Lorsqu'il existe une grande différence entre elapsed et cpu (224,20-41,32 = 182,88 secondes) comme c'est le cas ici, cela veut dire que vous avez passé du temps à attendre quelque chose. Ce quelque chose est représenté par db file sequential READ. Cet événement est souvent synonyme d'un accès via index. Ce qui, manifestement, est en contradiction avec le premier vrai plan d’exécution que vous avez présenté pour le id_type_document = 10 où la majeure partie du temps était consommée dans un FULL TABLE SCAN (synonyme de db file scattered READ) de la table document.
Code:

1 2 3 4 5 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------ |* 10 | TABLE ACCESS FULL | DOCUMENT | 1 | 4187K| 4173K|00:00:12.52 | 460K
Votre requête s'est-elle mise à générer 4.183.975 en utilisant un INDEX sur la table DOCUMENT? ce qui expliquerait peut-être la différence dans le temps de réponse contenue dans la trace(244,20) et celui fournit dans le premier vrai plan d’exécution (12,52 secondes pour le FULL TABLE SCAN et 43 secondes en tout)?

Dommage que le plan d’exécution n'a pas été inclus dans cette trace.

Si vous n'êtes pas satisfait par le temps de réponse il faudra faire une optimisation manuelle comme l'a fait Jonathan Lewis dans un contexte qui ressemble un peu au votre mais où pour lui c'était l'opération ORDER BY qui posait le plus problème ce qui n'est pas votre cas ici.

http://jonathanlewis.wordpress.com/2...l-optimisation

En rédigeant ces quelques lignes j'ai revu votre requête et il me semble que la jointure externe suivante
Code:

1 2 3 4 LEFT OUTER JOIN DOCUMENT documentco2_ ON sousdossie1_.ID_SOUS_DOSSIER=documentco2_.ID_SOUS_DOSSIER
doit être remplacée par une simple jointure à cause de
Code:

1 2 AND documentco2_.ID_TYPE_DOCUMENT='10'

21/12/2011, 21h01
skuatamad

Juste pour te donner quelques liens qui peuvent t'intéresser :
db file sequential read

Et sur asktom, pour quelques pistes de reflexions :
Wait event: DB file sequential read

Mohamed, Il a utilisé la requête de Waldar, il est donc impossible de comparer le tkprof avec les précédents plans.
jalaval, normalement le tkprof devrait contenir le plan réel dans la section Row Source Operation, sinon regénère un plan pour cette requête, par contre on a l'air loin des 25s dont tu parlais.