Ceci est une traduction de d’un post de Jonathan Lewis sur son blog – la troisième partie d’une série de quatre sur la fragmentation (original en anglais). Il est conseillé de lire avant: Fragmentation – Introduction, Fragmentation – Disque et Tablespace

Fragmentation Table

Dans l’introduction nous avons parlé d’un type de fragmentation au niveau table qui, en général, ne pose pas de problème: la fragmentation d’une table en plusieurs extents. Et il y a une chose amusante, c’est que ASSM (Automatic Segment Space Management – la gestion automatique de l’espace libre dans les segments) a introduit une nouvelle forme de fragmentation, mais qui ne pose généralement pas de problème non plus.

En ASSM, lorsqu’un processus Oracle vérifie le bitmap qui garde la trace de l’espace libre d’un objet, et qu’il ne trouve pas assez d’espace libre pour insérer de nouvelles données, alors il va formater 16 blocs quelque part dans l’extent en cours (après avoir alloué un nouvel extent si nécessaire). Ces 16 blocs peuvent être n’importe où dans l’extent (du moment que le numéro du bloc de départ par rapport à l’extent soit un multiple de 16).
L’emplacement de cet ensemble de 16 blocs est déterminé par l’identifiant du processus (le PID – process id), de même que le choix du bloc à utiliser parmi ces 16. Ce qui veut dire si vous créez une table dans un tablespace qui a des extents uniformes de 1Mo, vous pouvez vous retrouver à ce que la première ligne que vous insérez se retrouve dans le tout dernier bloc de son extent.
Cela n’a généralement pas d’importance parce que:

• la plupart des accès I/O se font bloc par bloc plutot que par full scan, et donc l’emplacement du bloc dans l’extent n’a pas beaucoup d’importance.
• ce ‘désordre’ ne se retrouve en principe que sur le dernier extent de la table
• en cas de full scan, pour savoir quel morceaux de 16 blocs doivent être traités de manière spéciale, Oracle utilise un mécanisme qui minimise le surcoût de cette vérification, en utilisant les LHWM (low high water mark) and HHWM (high high water mark).

Le type de fragmentation de table le plus important, et le plus courant, vient des données qui sont supprimées, et on peut alors se préoccuper des blocs qui ont un faible taux de remplissage. Lorsqu’on crée une table, on précise l’espace qui doit être réservé dans chaque bloc afin de garder une marge pour les lignes qui vont être modifiées (UPDATE) et – explicitement en freelist ou implicitement en ASSM – on précise aussi l’espace libre que doit contenir un bloc pour qu’il puisse être à nouveau la cible d’une nouvelle insertion de données (INSERT).

Si on regarde tous les scénarios possibles qui doivent être pris en compte avec avec des insertions de données, des modifications et des suppressions, on se rend compte à quel point il peut être difficile pour Oracle d’écrire du code qui gère l’espace libre de façon efficace et opportune. On voit aussi à quel point il est difficile, en tant que développeur ou DBA, de préciser des limites raisonnables pour la gestion de l’espace afin de minimiser les problèmes de performances dans des cas extrêmes. Au final, il est possible de se retrouver avec une table qui a une quantité importante d’espace libre dans chaque bloc, et il faut alors se poser les questions suivantes: Comment ce espace libre est arrivé là ? Est-ce qu’il pose un problème de performance ? Est-ce que vous allez pouvoir le réutiliser ? Est-ce que vous allez pouvoir le réutiliser en temps opportun ? Si vous arrivez à le réutiliser, est-ce que ca n’introduit pas un autre type de problème de performance ?

Prenez le cas où vous purgez la première année de données après que votre système ait tourné pendant 5 ans. Cela va probalement faire que les 20% premiers blocs de la table seront complètement vides. Il iront en freelist. Ou en ASSM, à l’exception de quelques bugs, ils seront marqués comme ayant de l’espace libre. Et cet espace libre pourra donc être réutilisé plus tard. De plus, si les performances de votre application dépendent du fait que les données qui arrivent au même moment se retrouvent ensembles (clustering par rapport à la date/heure d’arrivée) alors de la manière dont l’espace libre est rempli, le clustering va normalement rester intact.
Par une bizarrerie de l’implémentation, les blocs vont être utilisés dans l’ordre inverse en gestion freelist et dans l’ordre normal en ASSM (et une question me traverse l’esprit à ce moment à propos de l’impact de cet ordre inverse sur les indexes non-uniques qui ont peu de valeurs distinctes).

Cependant, pour un bon moment, avant cette réutilisation, les full scan de la table vont prendre 20% de temps en plus de ce qui est nécessaire. Et vous pouvez vous retrouver à faire un backup d’un fichier plus gros que ce dont vous avez réellement besoin. Ces considérations peuvent alors vous décider à faire une réorganisation de la table (et d’un rebuild des index) en la déplaçant vers un autre tablespace. Et elles peuvent aussi vous amener a réfléchir au partitionnement de la table.

Imaginez le cas d’un système de vente par internet où les utilisateurs remplissent leur panier, paniers qui à la fin deviennent une commande. Inévitablement, certains utilisateurs vont remplir un panier sans passer la commande par la suite. Alors vous avez peut-être un programme en tâche de fond qui regarde les vieux paniers et le supprime de la base. Il y a deux imperfections dans cette implémentation, mais ce genre d’erreur se voit souvent sur des systèmes en production. Alors dans ce cas, vous vous retrouvez avec une table qui subit constamment des delete sur le passé récent, de telle sorte que si vous analysez la table, vous verrez qu’en moyenne elle a, disons, 20% d’espace libre dans chaque bloc, à l’exception des quelques blocs qui contiennent les paniers récents.

Si vous êtes en ASSM, ces 20% ne seront jamais réutilisés (sauf si en recréant la table) parce que la granularité de ASSM fait qu’il faut au moins 25% d’espace libre dans un bloc avant que son statut ne passe de ‘plein’ à ‘0% – 25% libre’. Mais si vous êtes en freelist alors vous avez peut-être prévu le coup en mettant PCTUSED à un peu plus que 80. Dans ce cas, les blocs reviennent en freelist dès que l’espace libre atteint 20%, et l’espace va être réutilisé.
Cela semble être une bonne idée, sauf qu’il y a un autre problème à prendre en considération.

Peut-être que les bonnes performances de votre application venaient du fait que les paniers des derniers jours sont stockés dans les N derniers blocs de la table. Du fait que vous libérez 20% de l’espace des blocs du passé, alors les paniers qui étaient stockés sur N blocs sont maintenant répartis sur 5 fois plus de blocs. Et cela veut dire que, si vous voulez garder le même niveau de performance, vous allez avoir besoin de plus de mémoire en buffer cache.

La conclusion générale est la suivante. Une fois que vous avez résolu une fois pour toutes le problème de la taille des extents d’une table, le seul problème de ‘fragmentation’ qui vous reste à prendre en compte est celui des blocs qui ne sont que partiellement remplis. Et il y a des patterns de suppression de données qui peuvent amener à cette situation de blocs sous-utilisés. Dans certains cas, il n’est possible de récupérer cet espace que par une réorganisation de la table (et dans tous les cas, c’est le partitionnement de la table qui peut faire que cette option est viable). Mais il y a aussi des patterns de suppression de données qui font que l’espace libre est réutilisable, mais que vous ne voulez pas toujours réutiliser cet espace, car cela peut amener un autre type de problème.

La suite: Fragmentation – Index