La tentation d’utiliser des tables pour stocker des calculs et de faire plusieurs passes avec des ordres DML (Insrt/Update et Delete) pour sortir un résultat est grande… Ca permet :

1. d’utiliser des ordres SQL pour modifier en n étapes des jeux de données très important
2. de ne pas gérer les problèmes d’allocation de la mémoire (le malloc en C) et si vous n’avez pas assez d’espace, l’instance est capable de gérer les débordements sur disques sans que les développeurs s’en soucie !

Seulement voilà… avec une base de données Oracle (classique, oublions TimesTen !), ce genre d’approche peut poser des problèmes de montée en charge et notamment parce qu’Oracle 10g (9i, 8i, 8 et 7) journalise a priori l’ensemble des ordres DML ; Elle génère des redologs ! Si 50 utilisateurs font ce type de traitements simultanément, vous aurez probablement une forte contention sur les I/O d’autant plus que vos jeux de données sont importants. En outre, si votre base de données est en archivelog, vous en génèrerez autant d’archives. Enfin, cette approche est, par nature séquentielle !

Ci-dessous vous trouverez plusieurs réflexions, si vous voulez utiliser ce type d’approche malgré tout ou simplement pour essayer d’atténuer d’éventuels problèmes liés au fait que quelqu’un a trouvé que c’est une bonne idée ! mais d’abord, vous aurez compris, même si ça simplifie le développement, ce n’est pas forcément une brillante idée ! Enfin, pour ce que j’en sais au moins jusqu’en 10.2… Qui sait avec la 11 ?

Pour limiter la génération de redologs, vous pouvez préférer des tables temporaires aux autres type de tables. Dans ce cas, les données ne sont pas partagées entre sessions et l’activité Undo reste journalisée. LogMiner ou les traces des RedoLogs permettent de bien visualiser ce dernier phénomène.

Les tables temporaires n’ont donc pas d’influence sur les delete et ne réduisent que de 50% environ l’activité des Redo sur les updates (il garde le ROWID et l’image avant de la colonne modifiée : Biensûr si la colonne est NULL, je vous laisse faire les calculs). Si vous voulez encore limiter les effets de ces ordres, il y a quelques astuces :

• Vous pouvez ne plus faire de delete : utilisez Truncate… si c’est possible et mêm si ça a d’autres inconvénients puisque vous ne pouvez pas mettre de clauses WHERE, ça génère des enqueues (Sur des tables temporaire, chaque session ayant son segment, c’est moins grave) et ça génère une sérialisation dans le cas de RAC jusqu’en 10g (au moins) ! Vous pouvez également utiliser des table temporaires « on commit delete rows »
• Vous pouvez ne plus faire d’Updates ! Utilisez « Insert (select) » dans une autre table temporaire ou même dans une vrai table en NOLOGGING pour permettre de partager les données entre des sessions et réduire malgré tout l’activité des Redo
• Gardez la même session n’a pas que des inconvénients. Si vous utilisez RAC, ça assure l’affinité à un noeud pour votre batch; Ca facilite l’identification d’un batch et donc les traces et la supervision des activités. Pour partager les données, avec d’autres sessions terminez vos calculs par un insert (select) dans une table normale. Si vous voulez, pour cette opération aussi ne pas générer de redologs, vous pouvez mettre votre table de résultat en nologging et utilisez le hint /*+append*/

Quoiqu’il en soit (c’est toujours vrai)… faites des tests de performances de vos applications ! C’est plus facile à dire qu’à faire, d’accord mais il faut développer dans l’optique de tester.

Avant de conclure, un petit mot sur des alternatives qui peuvent s’avérer redoutable, dans un sens comme dans l’autre… (1) faîtes des « select », ça changera votre vie et (2) ne montez pas des usines à gaz avec plusieurs bases de données ou alors utilisez des moteurs dédiés sur des serveurs d’applications comme Timesten et (3) si vous voulez conserver les données dans des objets PL/SQL, attention à la consommation de la mémoire (ils ne sont pas comme des tables stockés dans des tablespaces en cas de problème d’espace en mémoire), enfin (4) les fonctions de tables permettent d’intégrer des calculs très complexes (en PL/SQL) dans un select (n’oubliez pas le (1))

GarK!