Discussion :

[Fabien Celaia]

Bonjour,

J'ai 2 tables, dont une assez conséquente:

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
CREATE TABLE FUSASS 
(NODOSSIER NUMBER(10), 
CDCAISSE  CHAR(2), 
NOASS CHAR(6), 
CDCAISSENEW CHAR(2), 
NOASSNEW  CHAR(6))  ;
 
-- 71'503 lignes
 
CREATE TABLE GFUCVTP
(TXCPTNOM CHAR(10), 
CDOLDCAI    CHAR(2), 
NBOLDVAL NUMBER, 
CDNEWCAI CHAR(2), 
NBNEWVAL    NUMBER, 
CDAMIGRE CHAR(1))
 
-- 633'501 lignes

J'y ai lancé un update massif, mais Oracle ne m'a jamais rendu la main

Code :

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

1
2
3
4
5
6
7
update FUSASS a set a.noAssNew =
	(select b.nbnewval
	 from GFUCVTP b 
	 where a.cdCaisse = b.cdoldcai
    and  a.noAss = to_char(b.nboldval)
    and  b.txcptnom = 'MACLE'
	)

Si par conter je lance le select correspondant, la requête se fait tout de suite, et me retourne quelques 60'000 lignes.

Code :

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

1
2
3
4
5
SELECT a.noAssNew ,  b.nbnewval
FROM  FUSASS a, GFUCVTP b
where a.cdCaisse = b.cdoldcai
and  a.noAss = to_char(b.nboldval)
and  b.txcptnom = 'MACLE'

Le plan d'exécution me retourne ces infos :

Etapes du plan d'exécution:

Etape n° Nom de l'étape
5 UPDATE STATEMENT
2 UPDATE
1 FUSASS TABLE ACCESS [FULL]
4 GFUCVTP TABLE ACCESS [BY INDEX ROWID]
3 FC_GFUCVTP INDEX [RANGE SCAN]

Etape n° Description Coût estimé Nombre estimé de lignes renvoyées Nombre estimé de kilo-octets renvoyés
1 Cette étape du plan extrait toutes les lignes de la table FUSASS. 81 71'503 1'256.889
2 Cette étape du plan met à jour les lignes de la table FUSASS qui remplissent la clause WHERE de l'instruction UPDATE.
3 Cette étape du plan extrait plusieurs ROWID par ordre croissant en balayant l'index B*-tree FC_GFUCVTP. 5 1 --
4 Cette étape du plan extrait des lignes de la table GFUCVTP via les ROWID renvoyés par un index. 33 1 0.025
5 Cette étape du plan définit cette instruction comme instruction UPDATE. 81 71'503 1'256.889

Comment expliquer ces lenteurs sachant qu'aucun fichier n'est saturé et que l'instance est de plus en noarchivelogs ?

Sous Oracle, n'y a-t-il pas moyen d'écrire l'update de manière plus conviviale, du genre

Code :

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

1
2
3
4
5
6
UPDATE FUSASS 
set a.noAssNew =  b.nbnewval
FROM  FUSASS a, GFUCVTP b
where a.cdCaisse = b.cdoldcai
and  a.noAss = to_char(b.nboldval)
and  b.txcptnom = 'MACLE'

[orafrance]

ça peut être un index sur la table FUSASS qui est long a mettre à jour...

[Fabien Celaia]

Long, je veux bien, mais de combien ??? J'ai déjà laisé tourner cette requête près de 10 heures, sans résultat probant !

PS : je vais essayer la même sans index

[lalystar]

Si l'update est très long mais pas le select, tu peux toujours stocker tous le résultat du select dans une nouvelle table et à la fin remplacer l'ancienne table par la nouvelle.

Est-ce-que la taille réelle de la nouvelle colonne (nbnewval) est bcp plus grande que l'ancienne (noAssNew). C'est peut être un problème de PCTFREE trop bas qui force Oracle à chainer les blocs lors de l'update ?

Laly.

[txouki]

C'est peut etre un fausse piste , mais il y a quelques années lorsque j'utilise la 8i je me suis fait avoir avec update sur une table crée avec du // , ca sortait jamais!
En 8i oracle ne gere pas correctement le// en update , j'avais resolu le cas avec un simple alter table no parrallel

[lalystar]

sous 8i, il me semble que tu ne peux utiliser le // que pour des tables partitionnées, et ici vu la taille des tables, ca m'étonnerait que ce soit le cas.


Laly.

[txouki]

j'ai verifié:

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
 
select * from V$version
BANNER                                                          
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production      
PL/SQL Release 8.1.7.3.0 - Production                           
CORE	8.1.7.0.0	Production                                       
TNS for Solaris: Version 8.1.7.3.0 - Production                 
NLSRTL Version 3.4.1.0.0 - Production                           
5 rows selected
 
 
create table test(col1 number(1))
parallel 4
Table created

A priori on peut creer des tables avec // en 8i
mais il est possible que ces pb viennent plus des idx que du //
et dans ce cas pourquoi pas les mettre unused avec l'update et rebuild apres?

[lalystar]

J'ai été tout à fait imprécis, je voulais dire qu'un update sur 8i ne peut se faire en parallèle que si la table est partitionnée...

Sinon, oui tu as raison, on peut très bien faire un create table as select ... en //

Laly.

[Fabien Celaia]

Le alter table noparallel n'a rien changé.
J'ai tout passé en mode CACHE et NOLOGGING... mais sans plus de succès

[orafrance]

pt1 c'est quand même bizare ce truc...

là il y a une méthode mais c'est très contraignant :
http://asktom.oracle.com/pls/ask/f?p=4950:8:1546518225566070532::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330,

je continue de creuser

[orafrance]

et les plan d'exécution sont exactement identiques ?

[orafrance]

mais attend... dans le select à tout les coups, il ne fait pas le FULL SCAN sur FUSASS grace aux critères : a.cdCaisse = b.cdoldcai ou a.noAss = to_char(b.nboldval)

Alors que pour l'update il fait le FULL SCAN puisqu'a priori Oracle est incapable de dire quelles lignes doivent être mise à jour. Si il y a peu de ligne à updater par rapport au nombre de ligne dans FUSASS, il vaudra mieux passer par du PLSQL peut-être ou essaye :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
update FUSASS a set a.noAssNew =
   (select b.nbnewval
    from GFUCVTP b
    where a.cdCaisse = b.cdoldcai
    and  a.noAss = to_char(b.nboldval)
    and  b.txcptnom = 'MACLE'
   )
WHERE a.noAss IN (select to_char(b.nboldval)
    from GFUCVTP b
    where a.cdCaisse = b.cdoldcai
    and  a.noAss = to_char(b.nboldval)
    and  b.txcptnom = 'MACLE'
    );

EDIT : je suis en train de me dire que c'est peut-être voulu d'updater toutes les lignes

[txouki]

As tu essaye en pl/sql en utilisant du bulk
comme ci dessous

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
 
declare
	TYPE    T_NOASSNE       IS TABLE OF          FUSASS.NOASSNEW%TYPE;
....
 
	bc_NOASSNE              T_NOASSNE ;
 
	Lvc_Heure_Err                           VARCHAR2(50);       -- Heure systeme
	Lvc_Code_Err                            VARCHAR2(8);        -- Code erreur Oracle
	Lvc_Lib_Err                             VARCHAR2(1000);     -- Libelle erreur Oracle
	Ln_Nombre_Lignes_Lues                   NUMBER(10);
	Ln_Nombre_Lignes_Inserees               NUMBER(10);
 
 
 
CURSOR c_ben is select /*+ parallel (b 4) (a 4) */ b.nbnewval 
    from GFUCVTP b 
    where a.cdCaisse = b.cdoldcai 
    and  a.noAss = to_char(b.nboldval) 
    and  b.txcptnom = 'MACLE' ;
	  begin
 
  open c_ben;
  Loop
	FETCH c_ben bulk collect into bc_NOASSNE,
	 Limit 10000;
		IF bc_NOASSNE.COUNT = 0 THEN
		   Ln_Nombre_Lignes_Lues:=0;
		   dbms_output.put_line('Nombre_de_lignes_Lues:'||Ln_Nombre_Lignes_Lues);
		   exit;
		END IF;
	 Ln_Nombre_Lignes_Lues:=bc_NOASSNE%COUNT;
	FORALL i INbc_NOASSNE.FIRST..bc_NOASSNE.LAST
	update FUSASS
	set a.noAssNew=bc_NOASSNE(i),
	    	where .....;
	EXIT WHEN c_ben%NOTFOUND;
	COMMIT;
  END LOOP;
  CLOSE c_ben;
  COMMIT;
END;

attention ceci n'est qu'un exemple , je te laisse completer les "....",
normalement ca tourne pas mal

[orafrance]

Et que donne la requête suivante par rapport à l'update :

Code :

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

1
2
3
4
5
SELECT /*+ FULL(a) */ a.noAssNew ,  b.nbnewval
FROM  FUSASS a, GFUCVTP b
where a.cdCaisse = b.cdoldcai
and  a.noAss = to_char(b.nboldval)
and  b.txcptnom = 'MACLE'

[Fabien Celaia]

Le select ci-dessus est "instantané" (le temps de l'affichage )

68190 lignes


... et oui, pour répondre à orafrance, l'update doit mettre à jour toutes les lignes de la table fusass.

[lalystar]

Bonjour Fadace,


Je viens d'y penser, j'avais déjà eu un cas similaire et après une longue enquête, on a découvert qu'il y avait plusieurs triggers sur la table (genre 1h pour modifier 3000 lignes...). Est-ce-c'est le cas ?


Laly.

[orafrance]

Le select ci-dessus est "instantané" (le temps de l'affichage )

68190 lignes


... et oui, pour répondre à orafrance, l'update doit mettre à jour toutes les lignes de la table fusass.

bon bah là il faut que j'appelle Fox Mulder sur cette affaire

L'explain plan montre bien un FULL, je ne me serais pas tromper dans le hint par hasard ?

[orafrance]

n'y aurait-il pas un snapshot log sur cette table ?

là je commence sérieusement à caler

[Fabien Celaia]

Aucun trigger sur ces tables

[orafrance]

t'as ouvert un appel chez Oracle ?