Discussion :

[Superdub]

Bonjour à tous.

Encore un problème d'optimisation (cf. autre problème)...

Je dois optimiser une vue qui met trop de temps à retourner les informations qu'elle contient.

Celle-ci met en jeu trois tables.

La table RF_INFO_CP_PDV_VIEW :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE RF_INFO_CP_PDV_VIEW 
   (	"CODE_ETABLISSEMENT" VARCHAR2(3 BYTE), 
	"ID_REF_REAL" NUMBER(*,0) NOT NULL ENABLE, 
	"ID_REF_CONS" NUMBER(*,0) NOT NULL ENABLE, 
	"CODE_CP" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	"CODE_CPGTA" VARCHAR2(3 BYTE), 
	"LIBELLE_CP" VARCHAR2(30 BYTE), 
	"CODE_PDV" VARCHAR2(5 BYTE) NOT NULL ENABLE, 
	"LIBELLE_PDV" VARCHAR2(35 BYTE)
   ) ;

La table RF_VOLUMEPREPREAL

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
create table RF_VOLUMEPREPREAL (
REFERENTIEL integer not null, 
POINTDEVENTE VARCHAR(5) not null, 
CIRCUITPREPARATION VARCHAR(30) not null, J
OUR DATE not null, 
UO double precision, 
CODE_ETABLISSEMENT VARCHAR(3),
PROG_WRITER VARCHAR(5), 
primary key (REFERENTIEL, POINTDEVENTE, CIRCUITPREPARATION, JOUR));
);

La table RF_VOLUMEPREPCONSTEMP

Code :

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

1
2
3
4
5
6
7
8
9
 
create table RF_VOLUMEPREPCONSTEMP (
JOUR DATE not null, 
REFERENTIEL integer not null, 
POINTDEVENTE VARCHAR(5) not null, 
CIRCUITPREPARATION VARCHAR(30) not null, 
UO double precision, 
CODE_ETABLISSEMENT VARCHAR(3), 
primary key (JOUR, REFERENTIEL, POINTDEVENTE, CIRCUITPREPARATION));

En gros dans la première table, le champ ID_REF_REAL correspond au champ REFERENTIEL de la deuxième, et le champ ID_REF_REAL correspond au champ REFERENTIEL de la troisième.

Voici ce que doit faire ma vue :

Pour chaque ligne de la première table, je veux l'ensemble des uo de la deuxième, et, s'ils existent l'ensemble des uo de la troisième.

Voici le code de ma vue :

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
 
CREATE OR REPLACE FORCE VIEW RF_VWVOLUMEPREPBYJOUR AS 
  SELECT INFO.CODE_ETABLISSEMENT, INFO.CODE_CP, INFO.CODE_CPGTA, INFO.LIBELLE_CP, 
    INFO.CODE_PDV, INFO.LIBELLE_PDV, VPR.JOUR AS JOUR, 
    VPR.UO AS UO_REALISE, VPCT.UO AS UO_CONSOLIDE
  FROM RF_INFO_CP_PDV_VIEW INFO
  INNER JOIN RF_VOLUMEPREPREAL VPR ON VPR.REFERENTIEL = INFO.ID_REF_REAL
    AND VPR.CIRCUITPREPARATION = INFO.CODE_CP
    AND VPR.POINTDEVENTE = INFO.CODE_PDV
    AND VPR.CODE_ETABLISSEMENT = INFO.CODE_ETABLISSEMENT
  LEFT OUTER JOIN RF_VOLUMEPREPCONSTEMP VPCT ON VPCT.REFERENTIEL = INFO.ID_REF_CONS
    AND VPCT.CIRCUITPREPARATION = INFO.CODE_CP
    AND VPCT.POINTDEVENTE = INFO.CODE_PDV
    AND VPCT.CODE_ETABLISSEMENT = INFO.CODE_ETABLISSEMENT
    AND VPCT.JOUR = VPR.JOUR;

Pour information, la requete qui est faite dessus est :

Code :

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

1
2
3
4
5
6
 
SELECT * FROM VWVOLUMEPREPBYJOUR  
WHERE CODE_ETABLISSEMENT = 'A'
AND CODE_PDV = 'B'
AND CODE_CP = 'C'
AND JOUR BETWEEN 'DATE1' AND 'DATE2'

Cette requête met approximativement 3 minutes à s'exécuter alors que les tables 2 et 3 ne contiennent que 5% des enregistrements ...

[Waldar]

Il vous faudra répondre aux questions suivantes !

1. Avez-vous des index hormis vos clefs primaires ?
2. Quelle sont les volumétries de vos tables (en nombre de lignes en et Mo) ?
3. Savez-vous si les statistiques sont à jour ?
4. Pouvez-vous nous donner le plan d'exécution de votre requête sur la vue ?
5. Et préciser votre version d'Oracle ?

[nuke_y]

Waldar a tout dit, mais je me sentais obligé d'intervenir pour vous dire que je vous soutenais moralement

[Superdub]

1. Avez-vous des index hormis vos clefs primaires ?

Pour la table RF_INFO_CP_PDV_VIEW :

Code :

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

1
2
3
4
 
CREATE INDEX RF_IDX_INFO_CP_PDV_VIEW
	ON RF_INFO_CP_PDV_VIEW
(ID_REF_REAL, ID_REF_CONS, CODE_ETABLISSEMENT, CODE_CP, CODE_PDV) TABLESPACE IND;

Je n'ai pas d'index sur les deux autres tables.

1. Quelle sont les volumétries de vos tables (en nombre de lignes en et Mo) ?

Actuellement 3.5 Millions de lignes pour les tables RF_VOLUMEPREPREAL et RF_VOLUMEPREPCONSTEMP (chacune), et 55 000 pour RF_INFO_CP_PDV_VIEW

1. Savez-vous si les statistiques sont à jour ?

Je ne pense pas. A quoi cela sert-il?

1. Pouvez-vous nous donner le plan d'exécution de votre requête sur la vue ?

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
 
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 3214476159                                                                                                                                                                                                                                                                                  
 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
| Id  | Operation                        | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
|   0 | SELECT STATEMENT                 |                         |     1 |   190 |    18   (6)| 00:00:01 |                                                                                                                                                                                                 
|   1 |  SORT ORDER BY                   |                         |     1 |   190 |    18   (6)| 00:00:01 |                                                                                                                                                                                                 
|   2 |   NESTED LOOPS OUTER             |                         |     1 |   190 |    17   (0)| 00:00:01 |                                                                                                                                                                                                 
|   3 |    VIEW                          |                         |     1 |   131 |    16   (0)| 00:00:01 |                                                                                                                                                                                                 
|   4 |     TABLE ACCESS BY INDEX ROWID  | RF_INFO_CP_PDV_VIEW     |     1 |    89 |    14   (0)| 00:00:01 |                                                                                                                                                                                                 
|   5 |      NESTED LOOPS                |                         |     1 |   148 |    16   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  6 |       TABLE ACCESS BY INDEX ROWID| RF_VOLUMEEQCPREAL       |     1 |    59 |     2   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  7 |        INDEX SKIP SCAN           | SYS_C0013269            |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  8 |       INDEX RANGE SCAN           | RF_IDX_INFO_CP_PDV_VIEW |     6 |       |    10   (0)| 00:00:01 |                                                                                                                                                                                                 
|*  9 |    TABLE ACCESS BY INDEX ROWID   | RF_VOLUMEEQCPCONSTEMP   |     1 |    59 |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
|* 10 |     INDEX UNIQUE SCAN            | SYS_C0013264            |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   6 - filter("VER"."CODE_ETABLISSEMENT"='011')                                                                                                                                                                                                                                                              
   7 - access("VER"."JOUR">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND                                                                                                                                                                                                                      
              "VER"."JOUR"<=TO_DATE('2008-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))                                                                                                                                                                                                                         
       filter("VER"."JOUR">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND                                                                                                                                                                                                                      
              "VER"."JOUR"<=TO_DATE('2008-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))                                                                                                                                                                                                                         
   8 - access("VER"."REFERENTIEL"="INFO"."ID_REF_REAL" AND "INFO"."CODE_ETABLISSEMENT"='011' AND                                                                                                                                                                                                             
              "VER"."CIRCUITPREPARATION"="INFO"."CODE_CP" AND "VER"."POINTDEVENTE"="INFO"."CODE_PDV")                                                                                                                                                                                                        
       filter("INFO"."CODE_ETABLISSEMENT"='011' AND "VER"."CIRCUITPREPARATION"="INFO"."CODE_CP" AND                                                                                                                                                                                                          
              "VER"."POINTDEVENTE"="INFO"."CODE_PDV")                                                                                                                                                                                                                                                        
   9 - filter("VECT"."CODE_ETABLISSEMENT"(+)="INFO"."CODE_ETABLISSEMENT")                                                                                                                                                                                                                                    
  10 - access("VECT"."JOUR"(+)="VER"."JOUR" AND "VECT"."POINTDEVENTE"(+)="INFO"."CODE_PDV" AND                                                                                                                                                                                                               
              "VECT"."CIRCUITPREPARATION"(+)="INFO"."CODE_CP" AND "VECT"."REFERENTIEL"(+)="INFO"."ID_REF_CONS")                                                                                                                                                                                              
 
Note                                                                                                                                                                                                                                                                                                         
-----                                                                                                                                                                                                                                                                                                        
   - dynamic sampling used for this statement                                                                                                                                                                                                                                                                
 
37 rows selected

Je m'attendais a ce que le EXPLAIN PLAN soit long mais étrangement, il s'est exécuté quasiment instantanément ... Est-ce normal?

1. Et préciser votre version d'Oracle ?

C'est la version Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

[mnitu]

Je ne pense pas. A quoi cela sert-il?

A ça!

[Superdub]

Merci, j'irai jeter un coup d'œil des que je peux !

[E.Bzz]

Bonjour,

Merci, j'irai jeter un coup d'œil des que je peux !

c'est plutôt par là qu'il faudrait commencer

A+