Discussion :

[zephyde]

Bonjour,

je reprends un package PL/SQL et je m'interroge sur le comportement de plusieurs sections.

Je vous en donne un exemple simplifié.

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
 
 
select F11, F21, F31 bulk collect into var1, var2, var3 from MaTable1
 
Begin
 
Forall i in var1.First...var1.Last
 
insert into MaTable2
(F12, F22, F32)
values (var1(i), var2(i), var3(i));
 
Exception when others then
	Rollback;
End;
Commit;

Donc ma question est la suivante: combien de transactions sont ouvertes par l'exécution de ce segment de code? Je ne parviens pas à déterminer quel est le comportement d'Oracle lorsque des ordres "LMD" sont appelés dans une boucle.

Chaque ordre Insert ouvre implicitement une nouvelle transaction mais quand est elle fermée (Insert suivant, ordre Commit final)?

Ou alors n'y a t'il qu'une transaction globale ouverte par le begin puis terminée par le rollback ou le commit?



L'idée est de savoir si une telle requête peut faire grossir le tablespace UNDO de manière importante ou non.

Merci d'avance!

Note: la base est sous Oracle 10g

[SQLpro]

La notion de transactions imbriquées n'existe pas par définition. Il n'y a jamais qu'une seule transaction.

A +

[pifor]

Il n'y a jamais qu'une seule transaction.

En fait, une session Oracle peut avoir 2 transactions indépendantes démarrées grâce aux transactions autonomes.

[zephyde]

Merci pour cette réponse mais je ne suis toujours pas sûr de ce que cela implique en pratique.

Dans l'exemple que je présente, si une exception est levée au troisième tour de boucle et qu'un rollback est déclenché la table sera-t-elle:
1/ identique à ce qu'elle était avant le forall?
2/ modifiée par les deux insert précédents?

D'un point de vue "gestion mémoire", y a t'il une "sauvegarde" de la table au moment du Begin puis une nouvelle avant chacun des insert ou alors uniquement au moment du begin? Si plusieurs images de la table sont faites, les précédentes sont elles supprimées?

Désolé d'insister mais j'aimerai vraiment comprendre le comportement du moteur dans ce cas....

Merci d'avance

[brassouille]

Ni le fait d'avoir un forall, ni le fait de faire des insert ne vont lancer un commit.... Donc je pense qu'un rollback lancé par la suite va annuler tous les insert qui ont été faits.

C'est pas très dur à vérifier, après ton BULK COLLECT tu peux faire

Code :

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

var1(2) := une_valeur_incohérente

et vérifier ensuite que l'insertion de ta première ligne a bien été annulée.

Accessoirement avec le forall, tu fais tous les insert en une seule opération (c'est son intérêt par rapport à une simple boucle for) au lieu de faire des aller-retours SQL <--> PL/SQL. Mais je ne pense pas que ça change quoique ce soit au niveau du rollback.

[Mathias44]

Bonjour,

D'après moi tu n'as qu'une transaction, commitée à la fin du traitement si tu sors correctement du forall. Les inserts sont les ordres qui consomment le moins d'UNDO mais je ne connais ni la volumétrie de ta table, ni celle de ton tbs UNDO

[zephyde]

Merci pour vos réponses, on est donc bien dans un comportement de type "tout ou rien" avec une transaction unique.

Par contre pour ce qui est de l'augmentation de la taille de l'UNDO, est ce que ce type de requête avec des ordres DELETE ou UPDATE peut induire une augmentation notable de ce tablespace?

D'ailleurs certaines boucles n'utilisent pas la commande FORALL (qui isole le PL/SQL du moteur SQL comme l'a rappelé Brassouille) mais de "simples" FOR?

Dans le cas qui m'intéresse, le tablespace UNDO occupe autant d'espace que les data...

Sinon, y a t'il une solution pour réduire la taille (purger?) ce tablespace en cours de traitement autrement que par des commit/rollback?

[Mathias44]

Oui, tu peux consommer massivement des undo, surtout avec des delete.
Et non il n'y a pas d'autre moyens de "libérer" de l'espace undo autrement qu'avec un commit ou un rollback (et heureusement, l'undo ne servirait à rien sinon )
une transaction rollbackée implique la libération de l'espace undo associé, une transaction commitée non (sauf au bout de undo_retention secondes ou si besoin par manque d'espace)