[PL/SQL] Temps de mis à jour trop long

**Mella** · 17/07/2007, 16h59

Bonjour à tou(te)s,

Environnement : Oracle 8i/Toad/Unix

J'essaye de mettre à jour environ 800.000 lignes dans une table de 17.000.000 de lignes environ. La mise à jour s'effectue suivant deux champs différents mais, sur des lignes comprises dans le même intervalle de temps. Pour le faire, j'utilise 2 curseurs A et B : le curseur A met à jour plusieurs champs et le curseur A un seul champ (déjà mis à jour par le curseur A => écrasement le cas échéant).
Le curseur A retourne plus de ligne que le curseur B; les deux curseurs retournent plus de lignes que le nombre de ligne de la plage à mettre à jour. J'ai plusieurs index (Bitmap et B-tree) sur la table; dont des index sur les deux clés de mise à jour. Le curseur B est beaucoup plus complexe que le curseur A

Mon problème est le suivant : la mise à jour avec le curseur A prend entre 18mn et 1h30 en fonction de la charge du réseau. Tandis qu'avec le curseur B, j'en ai pour 18h minimum!!!
Toutes les pistes pour comprendre d'où vient le problèmes sont les bienvenues.

Merci pour votre aide

Curseur A

Code :

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

1
2
3
4
5
6
7
SELECT VR, PA, VK,
     AN,VER, SEQ_P,
     SEQ_V,  LPAD(SUBSTR(SIR,1,14),14,'0') SIR,
     IND,ABREGE,  SUBSTR(NIVEAU,1,4) NIVEAU,
     AUS
    FROM  CORR  
    ORDER BY VER

Curseur B

Code :

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

1
2
3
4
5
6
7
SELECT AN,ABREGE
    FROM   CORR C
    WHERE  LOE IS NULL
    AND    AUS=(SELECT MAX(AUS)
             FROM CORR C2
        WHERE C2.ANLAGE=C.ANLAGE
       )

**Garuda** · 17/07/2007, 17h07

ET ton code ???

**McM** · 17/07/2007, 17h10

Tu peux donner les UPDATE ?
Ensuite, faut voir les trigger sur la table, et surtout les Index bitmap.. ça peut locker à donf ces trucs.

Qu'est ce qui est long, les UPDATES ou les curseurs ?

Pour info un LPAD fait un SUBSTR automatique
tu peux remplacer

Code :

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

1
2
3
LPAD(SUBSTR(SIR,1,14),14,'0') SIR 
par 
LPAD(SIR,14,'0') SIR

**Mella** · 17/07/2007, 17h24

Envoyé par Garuda

ET ton code ???

Désolée pour l'oubli

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
 
OPEN A();
      LOOP
         BEGIN
            FETCH A INTO ListVer;
            EXIT WHEN A%NOTFOUND;
            lu := lu + 1;
            traite := lu; 
 
    	    UPDATE AGR SET VR=ListVer.VR,
				    PA=ListVer.PA,
 				    VK=ListVer.VK,
				    SEQ_P =ListVer.SEQ_P ,
				    SEQ_V=ListVer.SEQ_V,
				    SIR=ListVer.SIR,
				    IND=ListVer.IND,
				    ABREGE=ListVer.ABREGE,
				    NIVEAU=ListVer.NIVEAU
 	    WHERE VER=ListVer.VER
	              AND   APPL IN ('S','Q')
	              AND ( ( MOIS_R >= pMOIS  AND  ANNEE_R = pANNEE)
			        OR
					( ANNEE_R > pANNEE )
				    )
				   AND ( (ANNEE_R < 2050)
				         OR
				  	     (ANNEE_R = 2050) AND (MOIS_R < 99)
				       );
 
	ecrit := ecrit + 1;
        IF MOD (ecrit, 10) = 0 THEN COMMIT; END IF;
   EXCEPTION
            WHEN OTHERS
            THEN
               alim_generale.msg (nom2, phase, 'LOOP', 'SG'||SQLCODE, ListVer.vertrag);
         END;
   END LOOP;
CLOSE A;
 
	  lu:=0;
 
 
OPEN B();
      LOOP
         BEGIN
            FETCH BINTO ListB;
            EXIT WHEN B%NOTFOUND;
            lu := lu + 1;
            traite := lu;
 
    	    UPDATE G_AGR SET   ABREGE=ListB.ABREGE
	    WHERE AN=ListB.AN
	              AND ( ( MOIS_R >= pMOIS  AND  ANNEE_R = pANNEE)
			        OR
					( ANNEE_R > pANNEE )
				    )
				   AND ( (ANNEE_R < 2050)
				         OR
				  	     (ANNEE_R = 2050) AND (MOIS_ < 99)
				       );
 
	          lu := lu + 1;
            IF MOD (lu, 100) = 0 THEN COMMIT; END IF;
  EXCEPTION
            WHEN OTHERS
            THEN
               alim_generale.msg (nom2, phase, 'LOOP', 'AB'||SQLCODE, ListB.AN);
      END;
   END LOOP;
CLOSE B;

Note : dans la période choise, le champ 'APPL' est toujours dans ('S','Q')

**Mella** · 17/07/2007, 17h30

Envoyé par McM

Tu peux donner les UPDATE ?
Ensuite, faut voir les trigger sur la table, et surtout les Index bitmap.. ça peut locker à donf ces trucs.

Qu'est ce qui est long, les UPDATES ou les curseurs ?

Pour info un LPAD fait un SUBSTR automatique
tu peux remplacer

Code :

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

1
2
3
LPAD(SUBSTR(SIR,1,14),14,'0') SIR 
par 
LPAD(SIR,14,'0') SIR

Merci. je met à jour le code.

A priori, c'est le second update qui est lent (celui qui utilise le curseur B) (J'ai mis des relève de temps entre les deux update afin de voir lequel prend du temps.). Lorsque j'exécute le script des curseurs, j'ai des résultats très rapidement. Il n'y a pas de trigger sur la table (sur aucune des tables de la BDD).
J'ai aussi pensé aux index BITMAP . Il y a en 1 sur le champ "ABREGE" :
- j'ai essayé de tous les supprimer avant la mise à jour sans résultat probant
- qu'est ce qui expliquerait que les Index Bitmap ne posent pas de problème lors du premier update?

**Garuda** · 17/07/2007, 18h08

REMARQUE : il te manque un commit en sortie de boucle (apres le close A) , des fois qu'il en reste à commiter (Nb de mises à jour pas multiple de 10) !!!!

Idem pour B

ET fais des boucles de curseur , c'est + lisible !

Code :

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

1
2
3
4
5
6
 
FOR C1 in (select .....) LOOP
 
.....
 
END LOOP

**McM** · 17/07/2007, 18h36

Je sais pas si c'est mieux (vu la volumétrie) de ne faire qu'un seul update plutôt qu'une boucle.

Code :

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

1
2
3
4
5
6
7
8
9
10
 UPDATE AGR 
SET (vr, pa, vk, seq_p, seq_v, sir, ind, abrege, niveau)
	= (SELECT vr, pa, vk, seq_p, seq_v, LPAD(sir,14,'0') sir,
		ind, abrege, SUBSTR(niveau,1,4) niveau
	FROM  CORR  
	WHERE ver = a.ver)
WHERE appl IN ('S','Q')
AND ( (mois_r >= pmois  AND annee_r = pannee) OR( annee_r > pannee ))	
AND ( (annee_r < 2050) OR (annee_r = 2050) AND (mois_r < 99))
AND EXISTS (SELECT 1 FROM CORR WHERE ver = a.ver);

**Mella** · 18/07/2007, 09h25

Merci à tous les deux.
J'ajoute les commit la où il en manque. McM, est ce qu'un seul update ne re-rxécute pas la sous requête pour chaque ligne de la table à mettre à jour? Idem pour une boucle de curseur garuda. (Désolée si mes questions sont stupides, je n'ai ps trouvé de tutoriel qui m'explique techniquement le curseur)

**McM** · 18/07/2007, 09h32

La réexécution, c'est pas dit. Ca dépend de l'Explain plan.
Mais 1 requete en base est généralement plus rapide qu'un boucle qui fait des allers-retours entre le moteur sql et la base.

**Mella** · 18/07/2007, 10h48

Lorsque je modifie le script (2nd script) afin de supprimer la boucle, l'explain plan (donné par TOAD) ressemble à ceci:

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
Operation	                        Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
 
UPDATE STATEMENT Optimizer Mode=CHOOSE		        49 K	 	3647,95485413357  	 	      	             	 
  UPDATE	PILOT.AGR	  	 	 	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	PILOT.AGR	        49 K	 875 K	3647,95485413357  	 	      	             	 
      BITMAP CONVERSION TO ROWIDS		  	 	 	 	      	             	 
        BITMAP AND		  	 	 	 	      	             	 
          BITMAP OR		  	 	 	 	      	             	 
            BITMAP MERGE		  	 	 	 	      	             	 
              BITMAP INDEX RANGE SCAN	PILOT.IDX_AGR_DATE	  	 	 	 	      	             	 
            BITMAP MERGE		  	 	 	 	      	             	 
              BITMAP INDEX RANGE SCAN	PILOT.IDX_AGR_DATE	  	 	 	 	      	             	 
          BITMAP OR		  	 	 	 	      	             	 
            BITMAP MERGE		  	 	 	 	      	             	 
              BITMAP INDEX RANGE SCAN	PILOT.IDX_AGR_DATE	  	 	 	 	      	             	 
            BITMAP MERGE		  	 	 	 	      	             	 
              BITMAP INDEX RANGE SCAN	PILOT.IDX_AGR_DATE	  	 	 	 	      	             	 
  FILTER		  	 	 	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	PILOT.CORR	        1  	24  	2  	 	      	             	 
      INDEX RANGE SCAN	PILOT.IDX_CORR_AN	        1  	 	3  	 	      	             	 
    SORT AGGREGATE		                        1  	18  	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PILOT.CORR	2  	36  	2  	 	      	             	 
        INDEX RANGE SCAN	PILOT.IDX_CORR_AN	2  	 	3

Si je considère l'explain plan du curseur (B) et de la requête exécutée dans la boucle j'obtiens:
update:

Code :

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

1
2
3
4
5
6
7
 
Operation	                Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
 
UPDATE STATEMENT Optimizer Mode=CHOOSE		1  	 	1  	 	      	             	 
  UPDATE	PILOT.AGR	  	 	 	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	PILOT.AGR	1  	18  	1  	 	      	             	 
      INDEX RANGE SCAN	PILOT.IDX_AGR_AN_AB	1  	 	2

Curseur:

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	            794  	 	6540  	 	      	             	 
  FILTER		  	 	 	 	      	             	 
    TABLE ACCESS FULL	PILOT.CORR	            794  	18 K	6540  	 	      	             	 
    SORT AGGREGATE		             	     1  	18  	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	PILOT.CORR   2  	36  	2  	 	      	             	 
        INDEX RANGE SCAN	PILOT.IDX_CORR_AN    2  	 	3

[PL/SQL] Temps de mis à jour trop long

SQL Oracle

Discussions similaires

Partager

Partager