Optimisation de procédure

**Superdub** · 14/05/2009, 13h30

Bonjour à tous.

J'ai besoin de votre aide sur une procédure qui met un certain temps à s'exécuter.

Je pense avoir repérer l'instruction qui pose problème :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT
  MAX(VOL.JOUR), MIN(VOL.JOUR) INTO LD$MAXDATE, LD$MINDATE 
FROM 
  RF_VOLUMEPREPREAL VOL
WHERE 
  VOL.CODE_ETABLISSEMENT = curETB.CODE
  AND VOL.CIRCUITPREPARATION = curCP.CODE
  AND VOL.POINTDEVENTE = curPDV.CODE
  AND VOL.JOUR > LD$MINDATE
  AND VOL.REFERENTIEL = LN$IDREF;

Puis :

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
 
INSERT INTO RF_VOLUMEPREPREAL
(CIRCUITPREPARATION, POINTDEVENTE, REFERENTIEL, UO,   CODE_ETABLISSEMENT, PROG_WRITER, JOUR)
 
SELECT curCP.CODE, curPDV.CODE, LN$IDREF, LN$VALEUR, curETB.CODE, LC$TAG_UPDATABLE, JOUR.DATE_CALENDRIER 
 
FROM RF_JOUR JOUR
WHERE 
  JOUR.DATE_CALENDRIER NOT IN(
     SELECT VOL.JOUR FROM RF_VOLUMEPREPREAL VOL
     WHERE VOL.CODE_ETABLISSEMENT = curETB.CODE
     AND VOL.CIRCUITPREPARATION = curCP.CODE
     AND VOL.POINTDEVENTE = curPDV.CODE
     AND VOL.JOUR BETWEEN LD$MINDATE AND LD$MAXDATE
     AND VOL.REFERENTIEL = LN$IDREF)
AND JOUR.DATE_CALENDRIER BETWEEN LD$MINDATE AND LD$MAXDATE;

Je vous explique un peu la philosophie de la chose :
La table RF_VOLUMEPREPREAL contient un grand nombre d'enregistrements (environ 10 millions). Elle contient des UO par jour. Or, tous les jours ne sont pas renseignés.
Ma procédure consiste donc à "boucher les trous" par des 0 entre les dates retournées par la première requête.
Je crois que le problème vient du fait que je fais un insert sur un table qui est elle même interrogée par le select...

Merci de votre aide.

**rbaraer** · 19/05/2009, 10h53

En général, Oracle n'aime pas les NOT IN ... Et si on essayait un NOT EXISTS ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO RF_VOLUMEPREPREAL
(CIRCUITPREPARATION, POINTDEVENTE, REFERENTIEL, UO,   CODE_ETABLISSEMENT, PROG_WRITER, JOUR)
 
SELECT curCP.CODE, curPDV.CODE, LN$IDREF, LN$VALEUR, curETB.CODE, LC$TAG_UPDATABLE, JOUR.DATE_CALENDRIER 
 
FROM RF_JOUR JOUR
WHERE NOT EXISTS(
     SELECT NULL 
     FROM RF_VOLUMEPREPREAL VOL
     WHERE VOL.JOUR = JOUR.DATE_CALENDRIER
     AND VOL.CODE_ETABLISSEMENT = curETB.CODE
     AND VOL.CIRCUITPREPARATION = curCP.CODE
     AND VOL.POINTDEVENTE = curPDV.CODE
     AND VOL.JOUR BETWEEN LD$MINDATE AND LD$MAXDATE
     AND VOL.REFERENTIEL = LN$IDREF)
AND JOUR.DATE_CALENDRIER BETWEEN LD$MINDATE AND LD$MAXDATE;

Si ça ne suffit pas, il faudra peut-être ajouter des indexes, pourriez-vous afficher le plan d'exécution des 2 versions ? (par exemple en lançant un EXPLAIN PLAN FOR SELECT ... ou bien en lançant un EXPLAIN depuis TOAD ou SQL Developer)

**mnitu** · 19/05/2009, 11h38

Envoyé par rbaraer

En général, Oracle n'aime pas les NOT IN ... Et si on essayait un NOT EXISTS ?
...

Salut Rbaraer,

Ce genre de considérations non suivi par des explications et surtout des exemples qui montre quand cella est vrai ne servent qu’a engendrer des mythes.

**rbaraer** · 19/05/2009, 13h28

Envoyé par mnitu

Salut Rbaraer,

Ce genre de considérations non suivi par des explications et surtout des exemples qui montre quand cella est vrai ne servent qu’a engendrer des mythes.

Je suis d'accord et je suis également contre les règles "immuables" et les "mythes" sur les SGBD. J'ai mal tourné ma phrase.

Cela ne s'appuie effectivement que sur mon expérience personnelle (sous Oracle 10gR1 et R2). Dans un cas comme celui-ci, il est bon d'essayer le NOT IN et le NOT EXISTS et de les comparer. De ma propre expérience, Oracle a mieux géré le NOT EXISTS de l'ordre de 9 fois sur 10, je préfère donc maintenant commencer par le NOT EXISTS et me tourner vers le NOT IN si je rencontre un souci, plutôt que l'inverse

.

Maintenant cela dépend bien entendu de la requête, de la version d'Oracle, des données, des indexes...

D'une manière générale quand une requête rencontre des problèmes de performance, il est bon d'essayer d'autres façons de l'écrire (tout en analysant le plan d'exécution et en indexant mieux si nécessaire).

Je reformule :

Et si on essayait un NOT EXISTS ? (tout court

)

**Superdub** · 19/05/2009, 14h54

Merci beaucoup pour la réponse, je commence à désespérer avec mes problèmes de performances (eh oui, ce n'est pas le seul, new topic coming soon

)

Voici le EXPLAIN PLAN pour le NOT IN :

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
 
--------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                           
--------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
|   0 | SELECT STATEMENT             |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
|*  1 |  HASH JOIN RIGHT ANTI        |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
|*  2 |   TABLE ACCESS BY INDEX ROWID| RF_VOLUMEPREPREAL |     1 |    46 |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
|*  3 |    INDEX RANGE SCAN          | SYS_C0013284      |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
|*  4 |   INDEX RANGE SCAN           | SYS_C0013202      |   567 |  4536 |     3   (0)| 00:00:01 |                                                                                                                                                                                                           
--------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   1 - access("JOUR"."DATE_CALENDRIER"="VOL"."JOUR")                                                                                                                                                                                                                                                         
   2 - filter("VOL"."CODE_ETABLISSEMENT"='011')                                                                                                                                                                                                                                                              
   3 - access("VOL"."REFERENTIEL"=6 AND "VOL"."POINTDEVENTE"='01105' AND                                                                                                                                                                                                                                     
              "VOL"."CIRCUITPREPARATION"='000001415' AND "VOL"."JOUR">=TO_DATE('2007-11-01 00:00:00',                                                                                                                                                                                                        
              'yyyy-mm-dd hh24:mi:ss') AND "VOL"."JOUR"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
              hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
   4 - access("JOUR"."DATE_CALENDRIER">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                                           
              hh24:mi:ss') AND "JOUR"."DATE_CALENDRIER"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
              hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
 
24 rows selected

Celui du NOT EXISTS :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
--------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                           
--------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
|   0 | SELECT STATEMENT             |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
|*  1 |  HASH JOIN RIGHT ANTI        |                   |   566 | 30564 |     6  (17)| 00:00:01 |                                                                                                                                                                                                           
|*  2 |   TABLE ACCESS BY INDEX ROWID| RF_VOLUMEPREPREAL |     1 |    46 |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
|*  3 |    INDEX RANGE SCAN          | SYS_C0013284      |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                           
|*  4 |   INDEX RANGE SCAN           | SYS_C0013202      |   567 |  4536 |     3   (0)| 00:00:01 |                                                                                                                                                                                                           
--------------------------------------------------------------------------------------------------                                                                                                                                                                                                           
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   1 - access("VOL"."JOUR"="JOUR"."DATE_CALENDRIER")                                                                                                                                                                                                                                                         
   2 - filter("VOL"."CODE_ETABLISSEMENT"='011')                                                                                                                                                                                                                                                              
   3 - access("VOL"."REFERENTIEL"=6 AND "VOL"."POINTDEVENTE"='01105' AND                                                                                                                                                                                                                                     
              "VOL"."CIRCUITPREPARATION"='000001415' AND "VOL"."JOUR">=TO_DATE('2007-11-01 00:00:00',                                                                                                                                                                                                        
              'yyyy-mm-dd hh24:mi:ss') AND "VOL"."JOUR"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
              hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
   4 - access("JOUR"."DATE_CALENDRIER">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                                           
              hh24:mi:ss') AND "JOUR"."DATE_CALENDRIER"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
              hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
 
24 rows selected

Mais j'ai bien l'impression que rien ne change entre les deux versions ...

**dgi77** · 19/05/2009, 15h03

Est-ce que tourné comme ça, ça fait améliorer le schmilblcik ?

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
 
INSERT INTO RF_VOLUMEPREPREAL
(CIRCUITPREPARATION, POINTDEVENTE, REFERENTIEL, UO,   CODE_ETABLISSEMENT, PROG_WRITER, JOUR)
 
SELECT curCP.CODE, curPDV.CODE, LN$IDREF, LN$VALEUR, curETB.CODE, LC$TAG_UPDATABLE, JOUR.DATE_CALENDRIER 
 
FROM RF_JOUR JOUR
WHERE 
  JOUR.DATE_CALENDRIER IN (SELECT DATE_CALENDRIER  FROM RF_JOUR
     MINUS SELECT VOL.JOUR FROM RF_VOLUMEPREPREAL VOL
     WHERE VOL.CODE_ETABLISSEMENT = curETB.CODE
     AND VOL.CIRCUITPREPARATION = curCP.CODE
     AND VOL.POINTDEVENTE = curPDV.CODE
     AND VOL.JOUR BETWEEN LD$MINDATE AND LD$MAXDATE
     AND VOL.REFERENTIEL = LN$IDREF)
AND JOUR.DATE_CALENDRIER BETWEEN LD$MINDATE AND LD$MAXDATE;

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

Merci pour la réponse.

D'après le EXPLAIN PLAN, cette méthode n'améliore pas les performances mais c'était bien vu !

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
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
|   0 | SELECT STATEMENT                |                   |   220 |  3740 |     7  (29)| 00:00:01 |                                                                                                                                                                                                        
|   1 |  NESTED LOOPS                   |                   |   220 |  3740 |     7  (29)| 00:00:01 |                                                                                                                                                                                                        
|   2 |   VIEW                          | VW_NSO_1          |   567 |  5103 |     7  (29)| 00:00:01 |                                                                                                                                                                                                        
|   3 |    MINUS                        |                   |       |       |            |          |                                                                                                                                                                                                        
|   4 |     SORT UNIQUE                 |                   |   567 |  4536 |            |          |                                                                                                                                                                                                        
|*  5 |      INDEX RANGE SCAN           | SYS_C0013202      |   567 |  4536 |     3   (0)| 00:00:01 |                                                                                                                                                                                                        
|   6 |     SORT UNIQUE                 |                   |     1 |    46 |            |          |                                                                                                                                                                                                        
|*  7 |      TABLE ACCESS BY INDEX ROWID| RF_VOLUMEPREPREAL |     1 |    46 |     2   (0)| 00:00:01 |                                                                                                                                                                                                        
|*  8 |       INDEX RANGE SCAN          | SYS_C0013284      |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                        
|*  9 |   INDEX UNIQUE SCAN             | SYS_C0013202      |     1 |     8 |     0   (0)| 00:00:01 |                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   5 - access("DATE_CALENDRIER">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND                                                                                                                                                                                                                 
              "DATE_CALENDRIER"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))                                                                                                                                                                                                                    
   7 - filter("VOL"."CODE_ETABLISSEMENT"='011')                                                                                                                                                                                                                                                              
   8 - access("VOL"."REFERENTIEL"=6 AND "VOL"."POINTDEVENTE"='01105' AND                                                                                                                                                                                                                                     
              "VOL"."CIRCUITPREPARATION"='000001415' AND "VOL"."JOUR">=TO_DATE('2007-11-01 00:00:00',                                                                                                                                                                                                        
              'yyyy-mm-dd hh24:mi:ss') AND "VOL"."JOUR"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
              hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
   9 - access("JOUR"."DATE_CALENDRIER"="$nso_col_1")                                                                                                                                                                                                                                                         
       filter("JOUR"."DATE_CALENDRIER"<=TO_DATE('2009-05-19 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                                           
              hh24:mi:ss') AND "JOUR"."DATE_CALENDRIER">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd                                                                                                                                                                                                          
              hh24:mi:ss'))                                                                                                                                                                                                                                                                                  
 
31 rows selected

**mnitu** · 19/05/2009, 15h17

Envoyé par Superdub

...
Mais j'ai bien l'impression que rien ne change entre les deux versions ...

Vous est sur une base 10 ou 11 et Oracle a "appris des choses" sur le Not In et Not Exists.
Mais à priori cette requête s’exécute assez vite, donc c’est quoi votre problème ?

**skuatamad** · 19/05/2009, 15h31

Salut,

Il nous faudrait plus d'information. C'est quoi "une procédure qui met un certain temps à s'exécuter" et quel est le temps maximal souhaité.

Sinon concernant tes requêtes, c'est quoi curETB, curCP, curPDV, des curseurs ?
Si c'est le cas en as tu vraiment besoin, parceque des curseurs sur des tables de 10 millions de lignes c'est très contre performant.

**Waldar** · 19/05/2009, 15h36

Envoyé par mnitu

Mais à priori cette requête s’exécute assez vite, donc c’est quoi votre problème ?

Je mise sur un curseur qui va récupérer tous les triplets (CODE_ETABLISSEMENT, CIRCUITPREPARATION, POINTDEVENTE) et qui va ensuite faire l'insert pour chacun de ces triplet.

Comme il y en a beaucoup ça prend du temps même si l'insert en lui-même se passe bien.

Solution : faire de l'ensembliste !

Edit : grilled !

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

Pour expliquer, ma base de données contient des établissements (ETB), qui possède des circuits de préparation (CP), qui sont reliés à des points de vente (PDV). Pour chaque triplet ETB, CP, PDV sont associés un UO par JOUR.

Ces UO sont renseignés partiellement dans la table RF_VOLUMEPREPREAL par chargement de fichier. (Tous les jours ne sont pas renseignés pour chaque triplet). Il faut donc combler les jours manquant par des 0.

Je fais donc :

Pour chaque ETB
Pour chaque CP(ETBCourant)
Pour chaque PDV(CPCourant)
Insertion de 0 pour les dates manquantes.

Pour les temps de traitement, au terme du remplissage partiel, 10 Millions de lignes sont renseignées dans la table RF_VOLUMEPREPREAL, ce qui représente environ 20%, les 80% restants étant complétés par cette procédure.

Actuellement cette procédure s'effectue en une dizaine d' heures ...

**Waldar** · 19/05/2009, 16h20

Ce qui plombe vos performances c'est le triple curseur.
Vous pouvez (devez) probablement arriver à vos fins en une seule et unique requête.

Essayez ceci en étant raisonnable sur la plage de date :

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
SELECT
    v.circuitpreparation,
    v.pointdevente
    ln$idref,
    ln$valeur,
    v.code_etablissement,
    lc$tag_updatable,
    j.date_calendrier 
FROM
    rf_volumeprepreal v
    PARTITION BY (code_etablissement, circuitpreparation, pointdevente) 
    RIGHT OUTER JOIN rf_jour j
      ON j.date_calendrier = v.jour
WHERE
    j.date_calendrier between ld$mindate
                          and ld$maxdate
and v.jour is null

**skuatamad** · 19/05/2009, 16h22

Envoyé par Superdub

Actuellement cette procédure s'effectue en une dizaine d' heures ...

C'était donc bien un euphémisme

Envoyé par Waldar

Solution : faire de l'ensembliste !

Suis les conseils de Waldar, ton problème sur la vue me dis que tu peux adapter ta procédure seul, mais si tu as des difficultés, post le code de la proc.

[EDIT]:Y a même plus qu'un conseil

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

Envoyé par Waldar

Je mise sur un curseur qui va récupérer tous les triplets (CODE_ETABLISSEMENT, CIRCUITPREPARATION, POINTDEVENTE) et qui va ensuite faire l'insert pour chacun de ces triplet.

Comme il y en a beaucoup ça prend du temps même si l'insert en lui-même se passe bien.

Solution : faire de l'ensembliste !

Edit : grilled !

Bingo!
C'est un peu à ça que je pensait moi aussi, mais je voudrais que quelque'un le dise. Et ce n'est pas ça ?
Sinon, j'ai une autre question (après avoir lu le réponse): Et pour quoi veut-on inserer des 0 pour les dates manquantes ?
[Edit]
Ca va vite sur ce sujet

[/Edit]

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

Merci waldar pour cette requête. Je viens de la tester et elle s'exécute très vite (environ 10 secondes). Il ne me reste plus qu'à la coupler avec le insert et le tour est joué. Je reviens vers vous plus tard pour vous donner mes conclusions.

Merci.

[EDIT]

Arf, après étude, ça correspond en partie à ce que je voulais ...

Je vais rajouter deux difficultés

:

Il se peut que pour un triplet, aucun enregistrement soit présent dans la table rf_volumeprepreal ... mais il faut quand même combler les trous ...
La min date et la max date dépendent des enregistrements du triplet. Exemple :
ETB1 CP1 PDV1, MINDATE = 01/01/2008 MAXDATE = '01/02/2008'
ETB1 CP1 PDV2, MINDATE = 01/01/2008 MAXDATE = '31/12/2008'

De plus, la valeur de LC$TAG_UPDATABLE change en fonction de la date.
Pour les dates entre MINDATE et MAXDATE , LC$TAG_UPDATABLE = 'Valeur1'
Pour les dates entre MAXDATE + 1 et SYSDATE, LC$TAG_UPDATABLE = 'Valeur2'

Désolé, j'aurais du donner ces précisions un peu plus tot...

**Waldar** · 19/05/2009, 17h32

Mon tarif est de 1 € par seconde gagnée

**mnitu** · 19/05/2009, 17h44

Mais, je reste persuadé qu'en fait il n'y nul besoin de cette procédure

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

Houlala

Sinon, avec l'ajout d'une nouvelle page sur le topic, je précise que j'ai édité le message d'avant pour corser l'affaire

@mnitu : si, si rassure toi, elle a son utilité

**Waldar** · 19/05/2009, 17h49

Est-ce que vous utilisez toujours la table RF_VOLUMEPREPREAL pour faire votre triplet ou bien avez-vous des tables de référence dédiées ?

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

Il y a une table ETABLISSEMENT. Et une table pour les couples CP et PDV selon un referentiel (il y plusieurs référentiels par établissement, ici, un seul nous interesse).

Je pense que je peux faire une vue répertoriant l'ensemble des triplets pour le référentiel qui nous intéresse. Je posterai demain cette vue.

Optimisation de procédure

PL/SQL Oracle

Discussions similaires

Partager

Partager