Optimisation d'une vue

**Superdub** · 19/05/2009, 15h35

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** · 19/05/2009, 15h45

Il vous faudra répondre aux questions suivantes !

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

**nuke_y** · 19/05/2009, 15h55

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

**Superdub** · 19/05/2009, 16h17

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.

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

Savez-vous si les statistiques sont à jour ?

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

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?

Et préciser votre version d'Oracle ?

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

**mnitu** · 19/05/2009, 16h34

Envoyé par Superdub

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

A ça!

**Superdub** · 19/05/2009, 17h12

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

**E.Bzz** · 19/05/2009, 17h27

Bonjour,

Envoyé par Superdub

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

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

A+

**13thFloor** · 19/05/2009, 18h27

"dynamic sampling used FOR this statement" nous dit le plan d'exécution (en général très rapide car ce n'est qu'une estimation de la façon dont va se prendre l'optimiseur pour rechercher les données) : des statistiques seraient manquantes.

Est-ce le plan réel ou estimé (explain plan for...)?

Calcule les statistiques sur toutes les tables utilisées par la requête.
Et reposte le nouveau plan (sauf s'il n'y a plus de problème).

PS : un plan réel d'exécution serait préférable.
Met la session en autotrace traceonly afin d'avoir le plan correspondant exactement à ta requête.

**mnitu** · 19/05/2009, 21h58

C'est juste pour vous dire que le nom des tables dans le plan ne corresponde pas au nom des tables que vous avez donné.
Exemple: RF_VOLUMEEQCPREAL et RF_VOLUMEEQCPCONSTEMP.
Peut être ça n'a pas d'importance, peut être oui.

Optimisation d'une vue

SQL Oracle

Discussions similaires

Partager

Partager