Optimisation de requête [Résolu]

cedrich · 12/12/2007, 13h17

Bonjour,

Voici l'explain plan d'une requête :

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
 
SELECT COUNT(*)
FROM DO_LOT_DOSSIER DOS
    ,SE_CAS CAS
    ,DO_LOT_ENTETE ENT
    ,DO_LOT_BON BON
    ,INT_IDENT_ROLE IDROLE
    ,AS_PRODUIT PROD
    ,AS_PRODUIT PROD2
  WHERE DOS.SE_CAS_ID = CAS.ID
AND BON.DO_LOT_ENT_ID = ENT.ID
AND BON.DO_LOT_ENT_ID = DOS.DO_LOT_ENT_ID
AND BON.SE_CAS_ID = DOS.SE_CAS_ID
AND BON.INT_TIDENT_CI = DOS.INT_TIDENT_CI
AND BON.INT_IDROLE_ID = DOS.INT_IDROLE_ID
AND IDROLE.ID = DOS.INT_IDROLE_ID
AND PROD.ID(+) = BON.AS_PROD_ID
AND PROD2.ID(+) = BON.AS_PROD_VEHI_ID
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.03       0.02          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      4.18       4.66       8826    2475294          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      4.21       4.69       8826    2475294          0           2
 
Misses IN library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23  (IDINFO)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
 221227   NESTED LOOPS  
 221227    NESTED LOOPS  
 221227     NESTED LOOPS  
 221244      NESTED LOOPS OUTER 
 221244       NESTED LOOPS OUTER 
 221244        NESTED LOOPS  
 221244         TABLE ACCESS FULL DO_LOT_BON 
 221244         INDEX UNIQUE SCAN DO_LOT_ENT_PK (object id 7773)
 221205        INDEX UNIQUE SCAN AS_PROD_PK (object id 6579)
 125737       INDEX UNIQUE SCAN AS_PROD_PK (object id 6579)
 221227      INDEX UNIQUE SCAN DO_LOTDOSS_UK (object id 7771)
 221227     INDEX UNIQUE SCAN INT_IDROLE_PK (object id 8154)
 221227    INDEX UNIQUE SCAN SE_INTERV_PK (object id 9027)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
 221227    NESTED LOOPS
 221227     NESTED LOOPS
 221227      NESTED LOOPS
 221244       NESTED LOOPS (OUTER)
 221244        NESTED LOOPS (OUTER)
 221244         NESTED LOOPS
 221244          TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                     'DO_LOT_BON'
 221244          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                     'DO_LOT_ENT_PK' (UNIQUE)
 221205         INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'AS_PROD_PK' 
                    (UNIQUE)
 125737        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'AS_PROD_PK' 
                   (UNIQUE)
 221227       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'DO_LOTDOSS_UK' 
                  (UNIQUE)
 221227      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'INT_IDROLE_PK' 
                 (UNIQUE)
 221227     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'SE_INTERV_PK' 
                (UNIQUE)

Comme vous pouvez le voir, il utilise presque partout des index, il n'y a que sur la table "DO_LOT_BON" qu'il fait un "ANALYZED (FULL)", est-il possible de diminuer le temps d'exécution de cette requête? Dans cette requête, je n'ai pas mis les colonnes que doit retourner le select, en effet ce select permet la création d'une vue.

Au niveau du nombre de ligne par table :

DO_LOT_BON : 247092
DO_LOT_ENTETE : 2636
DO_LOT_DOSSIER : 37568
SE_CAS : 18349
INT_IDENT_ROLE : 7246
AS_PRODUIT : 3822

Est-ce que l'ordre de déclaration de la clause FROM peut avoir une incidence sur les performances?

Merci d'avance pour vos conseils avisés.

Cédric

McM · 12/12/2007, 13h33

Quelle version de base ?

McM · 12/12/2007, 13h49

Est-ce que les stats sont à jour, parce qu'un acces full sur dos "serait" peut être mieux.

cedrich · 12/12/2007, 13h53

La base est en version 9.2.0.1, une migration est prévu en 10g mais pas dans l'immédiat.

Les stats sont mis à jour une fois par semaine.

orafrance · 12/12/2007, 14h16

Pour info, l'install du patchset 9.2.0.6 ou 9.2.0.7 serait déjà un bon début sachant que l'optimizer profite de quelques corrections intéressantes.

orafrance · 12/12/2007, 14h18

Citation:

Envoyé par cedrich

Est-ce que l'ordre de déclaration de la clause FROM peut avoir une incidence sur les performances?

Non, cela n'a d'importance qu'en mode RULE, ce qui n'est pas le cas (GOAL: CHOOSE).

cedrich · 12/12/2007, 15h16

Le schéma étant sur une base de test, j'ai migré en version 9.2.0.8.

Voici le nouveau plan :

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
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
 
 
SELECT CAS.TITRE CAS_TITRE
          ,CAS.DESIGNATION CAS_DESIGNATION
          ,CAS.CAS_NUMERO CAS_NUMERO
          ,BON.DB_ELEMENT_ID BON_DB_ELEMENT_ID
          ,BON.STATUS BON_STATUS
          ,BON.ID BON_ID
          ,BON.DO_LOT_ENT_ID BON_DO_LOT_ENT_ID
          ,BON.DATE_BON BON_DATE_BON
          ,BON.BON_NUMERO BON_BON_NUMERO
          ,BON.BON_SEQUENCE BON_BON_SEQUENCE
          ,BON.NOM_CLIENT BON_NOM_CLIENT
          ,BON.NUMERO_CLIENT BON_NUMERO_CLIENT
          ,BON.INT_TIDENT_CI BON_INT_TIDENT_CI
          ,BON.INT_IDROLE_ID BON_INT_IDROLE_ID
          ,BON.NUMERO_CARTE BON_NUMERO_CARTE
          ,BON.NUMERO_DOSSIER BON_NUMERO_DOSSIER
          ,BON.SE_CAS_ID BON_SE_CAS_ID
          ,BON.RECETTE_ARTICLE BON_RECETTE_ARTICLE
          ,BON.AS_PROD_ID BON_AS_PROD_ID
          ,BON.AS_PROD_VEHI_ID BON_AS_PROD_VEHI_ID
          ,BON.QUANTITE BON_QUANTITE
          ,BON.PRIX_UNITAIRE BON_PRIX_UNITAIRE
          ,BON.TAUX_RABAIS BON_TAUX_RABAIS
          ,BON.MONTANT_RABAIS BON_MONTANT_RABAIS
          ,BON.TAUX_MAJO BON_TAUX_MAJO
          ,BON.MONTANT_MAJO BON_MONTANT_MAJO
          ,BON.CO_TVATIER_ID BON_CO_TVATIER_ID
          ,BON.CO_TVACODE_ID BON_CO_TVACODE_ID
          ,BON.TAUX_TVA BON_TAUX_TVA
          ,BON.MONTANT_TVA BON_MONTANT_TVA
          ,BON.MONTANT_LIGNE BON_MONTANT_LIGNE
          ,BON.CO_COMPTE_FACTURATION_ID BON_CO_COMPTE_FACTURATION_ID
          ,BON.DO_DOCVTED_ID BON_DO_DOCVTED_ID
          ,BON.DB_UNIT_ID BON_DB_UNIT_ID
          ,BON.ETALON BON_ETALON
          ,BON.DO_REGLIG_CI BON_DO_REGLIG_CI
          ,BON.DESIGNATION_ARTICLE BON_DESIGNATION_ARTICLE
          ,BON.DESIGNATION_DOSSIER BON_DESIGNATION_DOSSIER
          ,BON.DB_BADGE_ID BON_DB_BADGE_ID
          ,BON.DB_BADGE_TABLE_CI BON_DB_BADGE_TABLE_CI
          ,BON.DB_CHAPITR_ID BON_DB_CHAPITR_ID
          ,BON.ETAT_BON BON_ETAT_BON
          ,BON.DO_LOTBON_ID BON_DO_LOTBON_ID
          ,BON.ETAT_DOSSIER BON_ETAT_DOSSIER
          ,DOS.ID DOS_ID
          ,DOS.DO_LOT_ENT_ID DOS_DO_LOT_ENT_ID
          ,DOS.SE_CAS_ID DOS_SE_CAS_ID
          ,DOS.INT_TIDENT_CI DOS_INT_TIDENT_CI
          ,DOS.INT_IDROLE_ID DOS_INT_IDROLE_ID
          ,DOS.AD_ADR_ID DOS_AD_ADR_ID
          ,DOS.TYPE_VALEUR_DOSS DOS_TYPE_VALEUR_DOSS
          ,DOS.CODE_RECAP DOS_CODE_RECAP
          ,DOS.TAUX_REMISE DOS_TAUX_REMISE
          ,DOS.AS_PROD_REMI_ID DOS_AS_PROD_REMI_ID
          ,DOS.TAUX_MAJO DOS_TAUX_MAJO
          ,DOS.AS_PROD_MAJO_ID DOS_AS_PROD_MAJO_ID
          ,DOS.AS_LIBPRIX_ID DOS_AS_LIBPRIX_ID
          ,DOS.CO_COND_PMT_ID DOS_CO_COND_PMT_ID
          ,DOS.ETAT_DOSSIER DOS_ETAT_DOSSIER
          ,ENT.ID ENT_ID
          ,ENT.STATUS ENT_STATUS
          ,ENT.DO_DOCPROP_ID ENT_DO_DOCPROP_ID
          ,ENT.DO_DCMPPER_VARIANTE ENT_DO_DCMPPER_VARIANTE
          ,ENT.ABREGE ENT_ABREGE
          ,ENT.NOM ENT_NOM
          ,ENT.DATE_LOT ENT_DATE_LOT
          ,ENT.CODE_SOLDE ENT_CODE_SOLDE
          ,IDROLE.NOM_AFFICHE IDROLE_NOM_AFFICHE
          ,IDROLE.INT_IDENT_ID IDROLE_INT_IDENT_ID
          ,IDROLE.AD_ADR_ID IDROLE_AD_ADR_ID
          ,PROD.AS_FAMILLE_ID PROD_AS_FAM_ID
          ,PROD2.REFERENCE_INT_2 PROD2_REFERENCE_INT_2
FROM DO_LOT_DOSSIER DOS
    ,SE_CAS CAS
    ,DO_LOT_ENTETE ENT
    ,DO_LOT_BON BON
    ,INT_IDENT_ROLE IDROLE
    ,AS_PRODUIT PROD
    ,AS_PRODUIT PROD2
  WHERE DOS.SE_CAS_ID = CAS.ID
AND BON.DO_LOT_ENT_ID = ENT.ID
AND BON.DO_LOT_ENT_ID = DOS.DO_LOT_ENT_ID
AND BON.SE_CAS_ID = DOS.SE_CAS_ID
AND BON.INT_TIDENT_CI = DOS.INT_TIDENT_CI
AND BON.INT_IDROLE_ID = DOS.INT_IDROLE_ID
AND IDROLE.ID = DOS.INT_IDROLE_ID
AND PROD.ID(+) = BON.AS_PROD_ID
AND PROD2.ID(+) = BON.AS_PROD_VEHI_ID
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.11          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      2.87       4.84      22568       7296          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      2.87       4.96      22568       7296          0          16
 
Misses IN library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23  (IDINFO)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     16  HASH JOIN  
   2636   TABLE ACCESS FULL DO_LOT_ENTETE 
     16   HASH JOIN OUTER 
 221227    HASH JOIN OUTER 
 221227     HASH JOIN  
  37568      HASH JOIN  
  18349       TABLE ACCESS FULL SE_CAS 
  37568       HASH JOIN  
   7246        TABLE ACCESS FULL INT_IDENT_ROLE 
  37568        TABLE ACCESS FULL DO_LOT_DOSSIER 
 221244      TABLE ACCESS FULL DO_LOT_BON 
   3822     TABLE ACCESS FULL AS_PRODUIT 
   3822    TABLE ACCESS FULL AS_PRODUIT 
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     16   HASH JOIN
   2636    TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'DO_LOT_ENTETE'
     16    HASH JOIN (OUTER)
 221227     HASH JOIN (OUTER)
 221227      HASH JOIN
  37568       HASH JOIN
  18349        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'SE_CAS'
  37568        HASH JOIN
   7246         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                    'INT_IDENT_ROLE'
  37568         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                    'DO_LOT_DOSSIER'
 221244       TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'DO_LOT_BON'
   3822      TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'AS_PRODUIT'
   3822     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'AS_PRODUIT'
 
********************************************************************************

Le temps est encore de presque 5s et j'ai recalulé les statistiques avec "compute" sur le schéma.

Comment puis-je descendre ce temps?

Quelqu'un a-t-il une idée?

Merci

orafrance · 12/12/2007, 15h39

c'est pas suffisant 5s ? Essaye avec le hint FIRST_ROWS pour voir ce que ça donne sans les HASH JOIN.

cedrich · 12/12/2007, 16h07

Je dois descendre le plus bas possible!

Avec la clause FIRST_ROW :

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
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
 
SELECT /*+ FIRST_ROWS*/ CAS.TITRE CAS_TITRE
          ,CAS.DESIGNATION CAS_DESIGNATION
          ,CAS.CAS_NUMERO CAS_NUMERO
          ,BON.DB_ELEMENT_ID BON_DB_ELEMENT_ID
          ,BON.STATUS BON_STATUS
          ,BON.ID BON_ID
          ,BON.DO_LOT_ENT_ID BON_DO_LOT_ENT_ID
          ,BON.DATE_BON BON_DATE_BON
          ,BON.BON_NUMERO BON_BON_NUMERO
          ,BON.BON_SEQUENCE BON_BON_SEQUENCE
          ,BON.NOM_CLIENT BON_NOM_CLIENT
          ,BON.NUMERO_CLIENT BON_NUMERO_CLIENT
          ,BON.INT_TIDENT_CI BON_INT_TIDENT_CI
          ,BON.INT_IDROLE_ID BON_INT_IDROLE_ID
          ,BON.NUMERO_CARTE BON_NUMERO_CARTE
          ,BON.NUMERO_DOSSIER BON_NUMERO_DOSSIER
          ,BON.SE_CAS_ID BON_SE_CAS_ID
          ,BON.RECETTE_ARTICLE BON_RECETTE_ARTICLE
          ,BON.AS_PROD_ID BON_AS_PROD_ID
          ,BON.AS_PROD_VEHI_ID BON_AS_PROD_VEHI_ID
          ,BON.QUANTITE BON_QUANTITE
          ,BON.PRIX_UNITAIRE BON_PRIX_UNITAIRE
          ,BON.TAUX_RABAIS BON_TAUX_RABAIS
          ,BON.MONTANT_RABAIS BON_MONTANT_RABAIS
          ,BON.TAUX_MAJO BON_TAUX_MAJO
          ,BON.MONTANT_MAJO BON_MONTANT_MAJO
          ,BON.CO_TVATIER_ID BON_CO_TVATIER_ID
          ,BON.CO_TVACODE_ID BON_CO_TVACODE_ID
          ,BON.TAUX_TVA BON_TAUX_TVA
          ,BON.MONTANT_TVA BON_MONTANT_TVA
          ,BON.MONTANT_LIGNE BON_MONTANT_LIGNE
          ,BON.CO_COMPTE_FACTURATION_ID BON_CO_COMPTE_FACTURATION_ID
          ,BON.DO_DOCVTED_ID BON_DO_DOCVTED_ID
          ,BON.DB_UNIT_ID BON_DB_UNIT_ID
          ,BON.ETALON BON_ETALON
          ,BON.DO_REGLIG_CI BON_DO_REGLIG_CI
          ,BON.DESIGNATION_ARTICLE BON_DESIGNATION_ARTICLE
          ,BON.DESIGNATION_DOSSIER BON_DESIGNATION_DOSSIER
          ,BON.DB_BADGE_ID BON_DB_BADGE_ID
          ,BON.DB_BADGE_TABLE_CI BON_DB_BADGE_TABLE_CI
          ,BON.DB_CHAPITR_ID BON_DB_CHAPITR_ID
          ,BON.ETAT_BON BON_ETAT_BON
          ,BON.DO_LOTBON_ID BON_DO_LOTBON_ID
          ,BON.ETAT_DOSSIER BON_ETAT_DOSSIER
          ,DOS.ID DOS_ID
          ,DOS.DO_LOT_ENT_ID DOS_DO_LOT_ENT_ID
          ,DOS.SE_CAS_ID DOS_SE_CAS_ID
          ,DOS.INT_TIDENT_CI DOS_INT_TIDENT_CI
          ,DOS.INT_IDROLE_ID DOS_INT_IDROLE_ID
          ,DOS.AD_ADR_ID DOS_AD_ADR_ID
          ,DOS.TYPE_VALEUR_DOSS DOS_TYPE_VALEUR_DOSS
          ,DOS.CODE_RECAP DOS_CODE_RECAP
          ,DOS.TAUX_REMISE DOS_TAUX_REMISE
          ,DOS.AS_PROD_REMI_ID DOS_AS_PROD_REMI_ID
          ,DOS.TAUX_MAJO DOS_TAUX_MAJO
          ,DOS.AS_PROD_MAJO_ID DOS_AS_PROD_MAJO_ID
          ,DOS.AS_LIBPRIX_ID DOS_AS_LIBPRIX_ID
          ,DOS.CO_COND_PMT_ID DOS_CO_COND_PMT_ID
          ,DOS.ETAT_DOSSIER DOS_ETAT_DOSSIER
          ,ENT.ID ENT_ID
          ,ENT.STATUS ENT_STATUS
          ,ENT.DO_DOCPROP_ID ENT_DO_DOCPROP_ID
          ,ENT.DO_DCMPPER_VARIANTE ENT_DO_DCMPPER_VARIANTE
          ,ENT.ABREGE ENT_ABREGE
          ,ENT.NOM ENT_NOM
          ,ENT.DATE_LOT ENT_DATE_LOT
          ,ENT.CODE_SOLDE ENT_CODE_SOLDE
          ,IDROLE.NOM_AFFICHE IDROLE_NOM_AFFICHE
          ,IDROLE.INT_IDENT_ID IDROLE_INT_IDENT_ID
          ,IDROLE.AD_ADR_ID IDROLE_AD_ADR_ID
          ,PROD.AS_FAMILLE_ID PROD_AS_FAM_ID
          ,PROD2.REFERENCE_INT_2 PROD2_REFERENCE_INT_2
FROM DO_LOT_DOSSIER DOS
    ,SE_CAS CAS
    ,DO_LOT_ENTETE ENT
    ,DO_LOT_BON BON
    ,INT_IDENT_ROLE IDROLE
    ,AS_PRODUIT PROD
    ,AS_PRODUIT PROD2
  WHERE DOS.SE_CAS_ID = CAS.ID
AND BON.DO_LOT_ENT_ID = ENT.ID
AND BON.DO_LOT_ENT_ID = DOS.DO_LOT_ENT_ID
AND BON.SE_CAS_ID = DOS.SE_CAS_ID
AND BON.INT_TIDENT_CI = DOS.INT_TIDENT_CI
AND BON.INT_IDROLE_ID = DOS.INT_IDROLE_ID
AND IDROLE.ID = DOS.INT_IDROLE_ID
AND PROD.ID(+) = BON.AS_PROD_ID
AND PROD2.ID(+) = BON.AS_PROD_VEHI_ID
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.09          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.10         43        218          0          16
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.19         43        218          0          16
 
Misses IN library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 23  (IDINFO)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     16  NESTED LOOPS OUTER 
     16   NESTED LOOPS OUTER 
     16    NESTED LOOPS  
     16     NESTED LOOPS  
      6      NESTED LOOPS  
      6       NESTED LOOPS  
      6        TABLE ACCESS FULL DO_LOT_DOSSIER 
      6        TABLE ACCESS BY INDEX ROWID INT_IDENT_ROLE 
      6         INDEX UNIQUE SCAN INT_IDROLE_PK (object id 8154)
      6       TABLE ACCESS BY INDEX ROWID SE_CAS 
      6        INDEX UNIQUE SCAN SE_INTERV_PK (object id 9027)
     16      TABLE ACCESS BY INDEX ROWID DO_LOT_BON 
   1225       INDEX RANGE SCAN DO_LOT_BON_LOT_ENT_IDX (object id 7764)
     16     TABLE ACCESS BY INDEX ROWID DO_LOT_ENTETE 
     16      INDEX UNIQUE SCAN DO_LOT_ENT_PK (object id 7773)
     16    TABLE ACCESS BY INDEX ROWID AS_PRODUIT 
     16     INDEX UNIQUE SCAN AS_PROD_PK (object id 6579)
     16   TABLE ACCESS BY INDEX ROWID AS_PRODUIT 
     16    INDEX UNIQUE SCAN AS_PROD_PK (object id 6579)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: HINT: FIRST_ROWS
     16   NESTED LOOPS (OUTER)
     16    NESTED LOOPS (OUTER)
     16     NESTED LOOPS
     16      NESTED LOOPS
      6       NESTED LOOPS
      6        NESTED LOOPS
      6         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                    'DO_LOT_DOSSIER'
      6         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                    'INT_IDENT_ROLE'
      6          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                     'INT_IDROLE_PK' (UNIQUE)
      6        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                   'SE_CAS'
      6         INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                    'SE_INTERV_PK' (UNIQUE)
     16       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                  'DO_LOT_BON'
   1225        INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                   'DO_LOT_BON_LOT_ENT_IDX' (NON-UNIQUE)
     16      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'DO_LOT_ENTETE'
     16       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'DO_LOT_ENT_PK' 
                  (UNIQUE)
     16     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                'AS_PRODUIT'
     16      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'AS_PROD_PK' 
                 (UNIQUE)
     16    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
               'AS_PRODUIT'
     16     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'AS_PROD_PK' (UNIQUE)
 
 
********************************************************************************

Le problème exacte est le suivant :

Il y a une vue principale constitué de deux vues (je suis actuellement en train de vérifier si on ne peut pas mettre les deux vues ensemble). La vue que l'on essaye d'optimiser est un des "composant" de la vue principal.

Michel SALAIS · 12/12/2007, 22h39

Citation:

Envoyé par cedrich

Le temps est encore de presque 5s et j'ai recalulé les statistiques avec "compute" sur le schéma.

Ceci veut dire que les stats ont été calculées en utilisant ANALYZE ?

Si la réponse est oui alors il est temps de basculer vers dbms_stats.

Michel SALAIS · 12/12/2007, 22h42

Citation:

Envoyé par McM

Est-ce que les stats sont à jour, parce qu'un acces full sur dos "serait" peut être mieux.

"bon" doit être accédé en entier de toute façon à cause de la jointure externe ...

orafrance · 13/12/2007, 09h13

Citation:

Envoyé par cedrich

Je dois descendre le plus bas possible!

Et comment tu arrives à savoir quand une requête ne peut pas être mieux optimisé ? Le plus rapide possible ça n'existe pas... sinon dans 2 mois t'es encore sur ta requête. Il faut absolument déterminer un temps acceptable.

Apparemment avec FIRST_ROWS c'est plus rapide non ?

Citation:

Envoyé par Michel SALAIS

Ceci veut dire que les stats ont été calculées en utilisant ANALYZE ?

Pas forcément, DBMS_STATS permet aussi de faire du compute

McM · 13/12/2007, 09h50

Citation:

Envoyé par Michel SALAIS

"bon" doit être accédé en entier de toute façon à cause de la jointure externe ...

Ah bon ?! Je ne vois aucune raison à ça.

Voici un exemple : Table FCLIENT et jointure externe sur parametre (PK)

Code :

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

1
2
3
4
5
6
SELECT *
FROM fclient a, param_sl p
WHERE a.ste = 18 AND a.client = 18157
AND p.ste (+) = a.ste
AND p.TYPE (+) = a.t44
AND p.num (+) = a.pays

Code :

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

1
2
3
4
5
6
7
8
Operation	Object Name	Rows	Bytes	Cost	Object Node	IN/Out	PStart	PStop
 
SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	3  	 	           
  NESTED LOOPS OUTER		1  	484  	3  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	SERCA.FCLIENT	1  	450  	2  	 	
      INDEX RANGE SCAN	SERCA.FCLIENT_I1	1  	 	1  	 	 
    TABLE ACCESS BY INDEX ROWID	SERCA.PARAM_SL	1  	34  	1  	 	
      INDEX UNIQUE SCAN	SERCA.PARAM_SL_PK	1

mnitu · 13/12/2007, 14h30

Je ne vois pas bien à quoi ça sert de optimiser la requête qui va créer la vue.

Cette optimisation n’est pas de toute une garantie d’un bon plan d’exécution au moment de l’utilisation de la vue par des autres requêtes. Peut être si c’était une vue matérialisée, mais il ne semble pas être le cas. Mettre des hints dans la vue c’est plutôt un moyen de chercher des ennuis (pour orafrance, cella ne dit pas que ta demande d’utiliser le hint n’est pas justifié à cette étape).

orafrance · 13/12/2007, 14h32

Citation:

Envoyé par mnitu

pour orafrance, cella ne dit pas que ta demande d’utiliser le hint n’est pas justifié à cette étape.

en effet, j'avais juste pour objectif de proscrire les hash joins pour voir ce que ça donne

Ceci étant, je suis d'accord, une mauvaise requête dans une vue peut être très performantes selon comment on joins la vue lors de son utilisation

pdelorme · 13/12/2007, 14h40

salut,

d'apres ce que je vois sur ta requette, il est normal que le plan d'execution te retourne un FULL car tu n'a pas de close where filtrante. en effet ta vue contient TOUT les elements de ta table DO_LOT_BON. Donc, il Oracle n'a aucune raison d'utiliser un index (vu que toute les lignes sont retenues).

par contre quand tu feras une requette sur ta vue avec une clause Where, le plan d'execution devrais changer pour utiliser l'index qui va bien (en relation avec ta clause where).

fait ta vue telle que tu l'a définie dans le premier post.
puis requette where DO_LOT_BON.X = z
avec un index sur X.

ton plan d'execution devrais utiliser l'index et le FULL devrais disparaitre...

P.

Michel SALAIS · 13/12/2007, 16h06

Citation:

Envoyé par orafrance

Et comment tu arrives à savoir quand une requête ne peut pas être mieux optimisé ? Le plus rapide possible ça n'existe pas... sinon dans 2 mois t'es encore sur ta requête. Il faut absolument déterminer un temps acceptable.

Apparemment avec FIRST_ROWS c'est plus rapide non ?

Pas forcément, DBMS_STATS permet aussi de faire du compute

Je le sais bien mais le mot n'est pas dans la syntaxe d'où ma question ...

Michel SALAIS · 13/12/2007, 16h10

Citation:

Envoyé par McM

Ah bon ?! Je ne vois aucune raison à ça.

Voici un exemple : Table FCLIENT et jointure externe sur parametre (PK)

Code :

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

1
2
3
4
5
6
SELECT *
FROM fclient a, param_sl p
WHERE a.ste = 18 AND a.client = 18157
AND p.ste (+) = a.ste
AND p.TYPE (+) = a.t44
AND p.num (+) = a.pays

Code :

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

1
2
3
4
5
6
7
8
Operation	Object Name	Rows	Bytes	Cost	Object Node	IN/Out	PStart	PStop
 
SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	3  	 	           
  NESTED LOOPS OUTER		1  	484  	3  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	SERCA.FCLIENT	1  	450  	2  	 	
      INDEX RANGE SCAN	SERCA.FCLIENT_I1	1  	 	1  	 	 
    TABLE ACCESS BY INDEX ROWID	SERCA.PARAM_SL	1  	34  	1  	 	
      INDEX UNIQUE SCAN	SERCA.PARAM_SL_PK	1

Dans l'exemple ici tu ajoute un critère sur la table concernée ce qui n'existe pas dans la requête traitée dans ce rubrique ...

cedrich · 14/12/2007, 08h32

Déjà merci à tous,

Le temps doit être le plus bas possible car cette vue entre dans processus de calcul. Ce processus actuellement prend 1h pour générer une 100 de facture d'une centaine de ligne ce qui tout simplement innacpetable pour le client.

J'ai également lancé une trace Oracle pour voir qu'elles seraient les requêtes qui pourraient poser problème. De là, j'en ai tiré un index qui manquait, malgré cela, c'est toujours trop lent.

Donc comme cette vue est lente, j'aimerais l'optimiser pour voir si elle a une influence significative.

@Orafrance tu proposes d'utiliser les hints clause tel que "FIRST_ROW" et n'est-il plus supporté par Oracle 10g?

orafrance · 14/12/2007, 09h00

Citation:

Envoyé par cedrich

@Orafrance tu proposes d'utiliser les hints clause tel que "FIRST_ROW" et n'est-il plus supporté par Oracle 10g?

Au contraire, en 10g il me semble que c'est même la valeur de l'OPTIMIZER_MODE par défaut

12/12/2007, 13h33	#2
McM Expert Confirmé Sénior Inscription : juillet 2003 Messages : 3 453 Détails du profil Informations forums : Inscription : juillet 2003 Messages : 3 453 Points : 4 215 Points : 4 215	Quelle version de base ? __________________ More Code : More Bugs. Less Code : Less Bugs
	00

12/12/2007, 13h49	#3
McM Expert Confirmé Sénior Inscription : juillet 2003 Messages : 3 453 Détails du profil Informations forums : Inscription : juillet 2003 Messages : 3 453 Points : 4 215 Points : 4 215	Est-ce que les stats sont à jour, parce qu'un acces full sur dos "serait" peut être mieux. __________________ More Code : More Bugs. Less Code : Less Bugs
	00

12/12/2007, 14h16	#5
orafrance Rédacteur/Modérateur Inscription : janvier 2004 Messages : 15 861 Détails du profil Informations personnelles : Âge : 35 Informations forums : Inscription : janvier 2004 Messages : 15 861 Points : 16 212 Points : 16 212	Pour info, l'install du patchset 9.2.0.6 ou 9.2.0.7 serait déjà un bon début sachant que l'optimizer profite de quelques corrections intéressantes. __________________ Règles et liens du forum Oracle Guide Oracle Pense-bête du DBA Oracle : Exemple
	00

12/12/2007, 15h39	#8
orafrance Rédacteur/Modérateur Inscription : janvier 2004 Messages : 15 861 Détails du profil Informations personnelles : Âge : 35 Informations forums : Inscription : janvier 2004 Messages : 15 861 Points : 16 212 Points : 16 212	c'est pas suffisant 5s ? Essaye avec le hint FIRST_ROWS pour voir ce que ça donne sans les HASH JOIN. __________________ Règles et liens du forum Oracle Guide Oracle Pense-bête du DBA Oracle : Exemple
	00

12/12/2007, 13h53	#4
cedrich Futur Membre du Club Inscription : septembre 2006 Messages : 65 Détails du profil Informations forums : Inscription : septembre 2006 Messages : 65 Points : 15 Points : 15	La base est en version 9.2.0.1, une migration est prévu en 10g mais pas dans l'immédiat. Les stats sont mis à jour une fois par semaine.
	00

13/12/2007, 14h30	#14
mnitu Expert Confirmé Sénior Marius Nitu Ingénieur développement logiciels Inscription : octobre 2007 Messages : 3 319 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 319 Points : 5 837 Points : 5 837	Je ne vois pas bien à quoi ça sert de optimiser la requête qui va créer la vue. Cette optimisation n’est pas de toute une garantie d’un bon plan d’exécution au moment de l’utilisation de la vue par des autres requêtes. Peut être si c’était une vue matérialisée, mais il ne semble pas être le cas. Mettre des hints dans la vue c’est plutôt un moyen de chercher des ennuis (pour orafrance, cella ne dit pas que ta demande d’utiliser le hint n’est pas justifié à cette étape).
	00

13/12/2007, 14h40	#16
pdelorme Membre régulier Inscription : mai 2007 Messages : 173 Détails du profil Informations personnelles : Âge : 42 Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur) Informations forums : Inscription : mai 2007 Messages : 173 Points : 70 Points : 70	salut, d'apres ce que je vois sur ta requette, il est normal que le plan d'execution te retourne un FULL car tu n'a pas de close where filtrante. en effet ta vue contient TOUT les elements de ta table DO_LOT_BON. Donc, il Oracle n'a aucune raison d'utiliser un index (vu que toute les lignes sont retenues). par contre quand tu feras une requette sur ta vue avec une clause Where, le plan d'execution devrais changer pour utiliser l'index qui va bien (en relation avec ta clause where). fait ta vue telle que tu l'a définie dans le premier post. puis requette where DO_LOT_BON.X = z avec un index sur X. ton plan d'execution devrais utiliser l'index et le FULL devrais disparaitre... P.
	00

14/12/2007, 08h32	#19
cedrich Futur Membre du Club Inscription : septembre 2006 Messages : 65 Détails du profil Informations forums : Inscription : septembre 2006 Messages : 65 Points : 15 Points : 15	Déjà merci à tous, Le temps doit être le plus bas possible car cette vue entre dans processus de calcul. Ce processus actuellement prend 1h pour générer une 100 de facture d'une centaine de ligne ce qui tout simplement innacpetable pour le client. J'ai également lancé une trace Oracle pour voir qu'elles seraient les requêtes qui pourraient poser problème. De là, j'en ai tiré un index qui manquait, malgré cela, c'est toujours trop lent. Donc comme cette vue est lente, j'aimerais l'optimiser pour voir si elle a une influence significative. @Orafrance tu proposes d'utiliser les hints clause tel que "FIRST_ROW" et n'est-il plus supporté par Oracle 10g?
	00

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