Update massif vs Curseur + commit régulier

Version imprimable

27/11/2008, 21h35
pacmann

Update massif vs Curseur + commit régulier

Salut !

Je dois faire un UPDATE basic, mais sur beaaaaaucoup de lignes.
On m'a conseillé de mettre ça dans une boucle avec des commits réguliers.
(Avec des bulk collect et des for all, bien sûr).

Mais je me suis souvenu avoir lu un article quelqu'un de chez developpez.net (pour ne pas le citer, c'est un gars qui essaie à chaque post de vous revendre ses bouquins), expliquant que pour SQL SERVER, les curseurs étaient "gourmands en ressources et instables par nature".

Ben du coup, j'ai cherché un peu sur le net :
Je crois que beaucoup de gens sont pour la version curseur...

... Mais pas TOM de chez Oracle !
Enfin, comme je suis vraiment très mauvais en Anglais, je ne voudrais pas lui faire dire ce qu'il n'a pas dit. :oops:

Voilà, votre avis, vos arguments sur le sujet m'intéressent au plus haut point !

Merci d'avance.
27/11/2008, 21h39
pacmann

Bon, je sais qu'il ne faut pas multi-poster, mais je voulais séparer clairement la présentation de la problèmatique de ma première réponse à moi-même. Désolé.

Les arguments pour l'UPDATE "en un coup" que j'ai pour l'instant sont :
- La transaction définit un truc atomique (d'un point de vue logique)... donc découper des petits des morceaux juste pour s'économiser de l'undo, c'est nul.

- Faire des commits dans un curseur ouvert, ça donne des risques de ORA 01555 Snapshot too old, parce qu'on peut être amené à relire des blocs déjà commités...

j'utilise (mais très rarement):

a/

Code:

1
2
3
4
5
6
7
8
9
10
begin
  loop
    update test
      set a = 0
      where a is null and rowcount <= 10000;
    exit when sql%rowcount < 10000;
    commit;
  end loop;
  commit;
end;

b/
diviser la table en morceaux (d'àpres la clé primaire)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
declare 
  ...
begin
  ... récupérer max pk dans v_nMax
  ...
  v_nAct := 1;
 
  loop
    exit when v_nAct > v_nMax;
 
    update x
      set a = 1
      where pkcol between v_nAct and (v_nAct + v_nStep - 1);
    commit;
 
    v_nAct := v_nAct + v_nStep;
  end loop;
  ...
end;

28/11/2008, 09h59
fatsora

Internet est un fourre-tout ...
pas que pour Oracle ;)

Tom Kytes et certains specialistes oracle disent justement de se mefier de tout
ca

Ils essaient au moins de demontrer par ex TKPROf ,statspack ce qu'ils avancent
ce qui est loin d'etre le cas de tout le monde

Pour TOM si tu peux faire en un seul update , sql suffit

sinon bulk

....

et comme ils disent test ,test test .....
28/11/2008, 10h24
fatsora

il y a justement celui ci , en additif

CTAS

http://asktom.oracle.com/pls/asktom/...:6407993912330
28/11/2008, 10h58
Waldar

Si l'update est basique et que la volumétrie reste contenue par les rollbacks segments, il n'y a pas de soucis à le faire une une seule passe.

Il vous faut impérativement vérifier le plan d'exécution par contre !
Et la présence d'index sur la table à mettre à jour : si l'update est massif je vous conseillerai très fortement de dropper tous les index - sauf celui de la clef d'update - et de les recréer après la mise à jour.
28/11/2008, 11h10
fatsora

Citation:

Envoyé par pacmann

Salut !

Je dois faire un UPDATE basic, mais sur beaaaaaucoup de lignes.
On m'a conseillé de mettre ça dans une boucle avec des commits réguliers.
(Avec des bulk collect et des for all, bien sûr).

Mais je me suis souvenu avoir lu un article quelqu'un de chez developpez.net (pour ne pas le citer, c'est un gars qui essaie à chaque post de vous revendre ses bouquins), expliquant que pour SQL SERVER, les curseurs étaient "gourmands en ressources et instables par nature".

Ben du coup, j'ai cherché un peu sur le net :
Je crois que beaucoup de gens sont pour la version curseur...

... Mais pas TOM de chez Oracle !
Enfin, comme je suis vraiment très mauvais en Anglais, je ne voudrais pas lui faire dire ce qu'il n'a pas dit. :oops:

Voilà, votre avis, vos arguments sur le sujet m'intéressent au plus haut point !

Merci d'avance.

Au fait qu'es ce que tu entends par massif et beaaaaaaaacoup

un trilliard , quelques lignes ...

peux tu te permettre un non acces a ta table

quelle version Oracle tu as ....
parce qu'un ORA-15555 sous ORACLE 10G est assez balot !
29/11/2008, 09h38
Drawingrom

Ca dépend aussi des valeurs que tu updates, je pense que la conservation d'un clob de xMo est moins couteuse que celle d'un number en terme de rollback segment...
C'est à tester, personnellement je n'utilise des boucle pl/sql que lors de delete de masse. En effet, lors d'un delete, toute la ligne est stockée en rollback
30/11/2008, 18h32
Pomalaix

Citation:

Envoyé par fatsora

parce qu'un ORA-15555 sous ORACLE 10G est assez balot !

Bonjour

Vous pouvez développer la chose ?
01/12/2008, 08h33
jmguiche

Les curseurs consommateurs et instable "par nature", c'est peut etre vrai pour sql server, mais pas pour oracle.

la réponse à ta question est : si tu peux le faire en une requette, fait le, sinon fait des cursors avec des bulks.

Les raisons pour lesquelles tu pourrais ne pas faire sont de type
taille des rollbacks, problèmes de cohabitation avec d'autres traitements...
01/12/2008, 14h08
pacmann

Pom pom pom.
Merci pour vos réponses !

@Drawingrom : Si j'ai bien compris le système de rollback, c'est TOUJOURS toute la ligne qui est en rollback segment.
En fait, c'est même tout le bloc.

@Pomalaix : +1 !
Fastora : Si j'ai bien compris, la cause du ORA-01555, c'est :
"Vous avez SUBMITé votre transaction, la rollback entry associée et le slot dans le rollback segment sont disponibles, et donc potentiellement écrasables"
=> Du coup, la version précédente peut être perdue pour les transaction aillant un SCN inférieur à celui de la transaction qui a commité...

@Waldar :
Oui, sur les petites volumétries, je ne me pose même pas la question.
Par contre, dropper les indexes n'est pas vraiment possible dans l'état actuel, puisque pleins de traitements tournent en parallèle, notamment certains qui ne survivraient que difficilement au drop...
Cela dit, j'y pense quand même : si trop de difficultés sont rencontrées, on pourra envisager d'isoler certains traitements. Et à ce moment là, on pourra se marrer avec les CTAS, /*+append*/, drop des indexes...

@jmguiche :
Bon, je suppose qu'il faudrait plus en parler avec quelqu'un de "chez" SQL Server, mais ce qui m'intéresserait, c'est en quoi les curseurs SQL Server sont instables et consommateurs, afin que tu puisses me confimer que ce n'est pas le cas pour Oracle.

@fastora :
- Internet est certes un fourre-tout, mais le Tom Kyte me semblait connaître pas mal de trucs, non ? (enfin moi, j'ai été assez impressioné)
- Quand je parle de grosse volumétrie, c'est 10 millions de lignes.
Quand il s'agit de 10000 à 100000, je ne me pose même pas la question.
- Mais plus que tout, ce qui m'importait, c'était de voir s'il y a d'autres gens que ça chagrine d'utiliser des curseurs pour des updates simples.
Ou de manière générale, qui partagent la conception "row by row" = "slow by slow" du gars sus-cité

Allez, petite question bonus pour tout le monde : est-ce que quelqu'un s'est déjà mangé un ORA-01555 ?
Si oui, a-t-il pu identifier que ça vient de commits trop fréquents sur son gros curseur ? (à priori, sur des transactions courtes, il y a très peu de chances que ça arrive)
Et est-ce que quelqu'un considère que c'est un argument dans le choix de sa stratégie d'implémentation ?

Merci encore !
01/12/2008, 17h19
fatsora

rendons a Tom ce qui est a Kytes

http://asktom.oracle.com/pls/asktom/...D:546822742166

apres si on n'est pas convaincu

http://www.orafaq.com/faq/how_often_...ommit_strategy

http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

pour finir

metalink

Subject: ORA-01555 "Snapshot too old" in Very Large Databases (if using Rollback Segments)
Doc ID: NOTE:45895.1 Type: BULLETIN
Last Revision Date: 03-OCT-2008 Status: PUBLISHED

FYI
01/12/2008, 18h00
jmguiche

Citation:

Envoyé par pacmann

@jmguiche :
Bon, je suppose qu'il faudrait plus en parler avec quelqu'un de "chez" SQL Server, mais ce qui m'intéresserait, c'est en quoi les curseurs SQL Server sont instables et consommateurs, afin que tu puisses me confimer que ce n'est pas le cas pour Oracle.

8O
Il y a longtemps, j'ai cotoyé sqlserver de sybase, c'est de ce moteur qu'est parti celui de microsoft.

Les curseurs y étaient un infâme bricolage qui marchait mal.
Je ne m'interesse pas trop à savoir en quoi les curseurs de SQL server sont instables, si les utilisateurs de ce moteur le disent, je le crois.
Par contre, je sais que c'est stable sous oracle et je n'ai pas besoin de pro de sqlserver pour le savoir !
Le curseur, c'est la "façon normale d'acceder à Oracle", cela existe depuis que cette base existe. Même quand on n'écrit pas de curseur pour faire un select, Oracle en fait un !

En effet, Tom Kytes n'est pas un "bLéRô", mais une source fiable !
01/12/2008, 21h16
pacmann

@fastora : hmmm, j'ai pas accès à Metalink.
Du coup, je me permets de te re-poser la question : comment le problème ORA-01555 (notamment sur fetch across commit) est éliminé en 10i ?

Toujours limité par ma compréhension de la langue anglaise :

Citation:

Envoyé par Tom

ALL versions of Oracle (even 9i if you use rollback segments or set the
undo_retention too small) will give you this. 100%.

@jmguiche : ok, c'était naze comme question.
Merci pour ta réponse !
02/12/2008, 09h59
fatsora

[QUOTE=pacmann;3831528]@fastora : hmmm, j'ai pas accès à Metalink.
Du coup, je me permets de te re-poser la question : comment le problème ORA-01555 (notamment sur fetch across commit) est éliminé en 10i ?

Citation:

Envoyé par Pomalaix

Bonjour

Vous pouvez développer la chose ?

Evidemment c'est de la provoc et du 95e degre :oops:

Tom Kyte dit la meilleure requete est la requete que tu ne lances pas

Tu peux reduire ,attenuer les SNAPSHOT TOO OLD ... mais 100% n'existe pas sur terre

encore moins sur Oracle

Cependant on peut reduire le nombre de ora-1555

en procedant ainsi :
ON peut mettre

le parametre UNDO_RETENTION assez grand pour contenir la plus grosse requete

En 10G tu as UNDO GUARANTEE

mais actif seulement si autoextend on

tu peux mettre MAXSIZE .... pour eviter des problems

http://download.oracle.com/docs/cd/B...htm#sthref1482

En 10G on a undo advisor
qui aide a sizer l'undo tablespace

Il faut tuner les requetes
en evitant de faire des "fetch across commit" !

voir T Kyte

http://books.google.com/books?id=TmP...um=9&ct=result

Ici il y a un lien ORA-01555 "Snapshot too old" in Very Large Databases
http://www.alise.lv/ALISE/technolog....d?OpenDocument

mais bon ... FYI
04/12/2008, 19h29
Drawingrom

Citation:

Envoyé par pacmann

@Drawingrom : Si j'ai bien compris le système de rollback, c'est TOUJOURS toute la ligne qui est en rollback segment.
En fait, c'est même tout le bloc.

Ah ouais tu dois avoir raison ça doit se faire au niveau du bloc

Merci Fastora.
Je sens qu'avec l'Undo retention, on se mord un peu la queue... vu que l'idée à la base, c'est de permettre le fetch across commit pour libérer de l'Undo :)

Cela dit, si je ne suis pas complètement à côté de la plaque, ce n'est pas uniquement le fetch across commit qui est mis en cause.
A priori, si tu fais :
Citation:
Envoyé par DAB.cz
Code:

1 2 3 4 5 6 7 8 9 10 11 loop exit when v_nAct > v_nMax; UPDATE x SET a = 1 WHERE pkcol BETWEEN v_nAct AND (v_nAct + v_nStep - 1); commit; v_nAct := v_nAct + v_nStep; end loop;
Tu as autant de chances de ORA 1555 ! (Puisque le problème, c'est juste de réaccéder à un bloc déjà commité au sein d'une même transaction).
A priori, on peut juste dire que les probabilités seront inversement proportionnelles au clustering factor de l'index sur pkcol...

En fait, la parade ultime serait de piloter à partir de l'extérieur, afin d'exécuter les tranches dans des transactions distinctes (attribution d'un nouvel SCN, plus de problème de consistence). Mais c'est laid, et ce n'est peut être pas possible dans tous les cas...

Enfin bon, je pense que tout ça ne passionne pas les foules.
Merci à tous, et résolu !