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 +

[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)

[brassouille]

une transaction rollbackée implique la libération de l'espace undo associé, une transaction commitée non

?? Je pensais que quand tu commitais, tu vidais aussi le UNDO associé?? à quoi ça peut servir de le conserver...?

[Mathias44]

Pour une flashback query : si tu souhaites pouvoir exécuter une requête dans un état transactionnel antérieur. Sachant qu'en 9i tu n'as pas de moyen de garantir cette rétention à part surdimensionner ton tablespace.

[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.

[pifor]

Pour une flashback query : si tu souhaites pouvoir exécuter une requête dans un état transactionnel antérieur. Sachant qu'en 9i tu n'as pas de moyen de garantir cette rétention à part surdimensionner ton tablespace.

Le undo sert aussi à fournir les images avant des blocs modifiés par la transaction qui se termine par COMMIT aux requêtes qui ont démarré avant cette transaction: c'est le principe du read consistency.

[zephyde]

Merci beaucoup pour ces informations notamment sur le fonctionnement du COMMIT.

[Mathias44]

Le undo sert aussi à fournir les images avant des blocs modifiés par la transaction qui se termine par COMMIT aux requêtes qui ont démarré avant cette transaction: c'est le principe du read consistency.

Nous parlions des blocs undo des transactions commitées
(et si la transaction est commitée, il n'y a plus lieu d'utiliser l'image avant )

[mnitu]

Nous parlions des blocs undo des transactions commitées
(et si la transaction est commitée, il n'y a plus lieu d'utiliser l'image avant )

T'es certain ?

Session 1

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL> create table t_commit
  2  as
  3  select *
  4    from all_objects
  5   where rownum <= 10;
 
Table créée.
 
SQL> variable rc refcursor;
SQL> exec open :rc for select * from t_commit;
 
Procédure PL/SQL terminée avec succès.

Session 2

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SQL> delete t_commit;
 
10 ligne(s) supprimée(s).
 
SQL> commit;
 
Validation effectuée.
 
SQL>

Session 1

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
 
SQL> print rc
 
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -----------------
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S  NAMESPACE
-------- -------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYS                            ICOL$
                                       20              2 TABLE
15/10/07 15/10/07 2007-10-15:10:09:08 VALID   N N N          1
 
 
 
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -----------------
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S  NAMESPACE
-------- -------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
...

Où pense tu qu'il a trouvé les données supprimées et "commitée" par la Session 2 ?

[Mathias44]

Bonsoir,

Effectivement ma remarque ne s'applique qu'aux transactions qui démarrent après, je n'ai pas tenu compte du cas que tu exposes - et en le relisant que pifor également du reste