La migration et le chaînage
Le paramètre « PCTFREE » indique le pourcentage d’espace réservé à l’extension due à la mise à jour future des lignes dans le bloc. Les tables stockent les enregistrements dans les blocs, un enregistrement ne peut pas changer de « ROWID », et ainsi un enregistrement n’est pas déplacé d’un bloc vers un autre. Rappelez-vous qu’un « ROWID » est la description de l’emplacement physique du bloc.
Voici le script pour la création de la table « T1 » avec deux champs de type « NVARCHAR2(2000) » pour pouvoir facilement augmenter la taille d’un enregistrement.
La page de code utilisée par défaut est « AL16UTF16 » ; ainsi chaque caractère d’un champ utilise pour être stocké « 2b » du bloc de la table d’une taille de « 8kb ». La table est créée avec le paramètre de stockage « PCTFREE=0 ».
STAG@agate>select property_name, property_value from database_properties
2 where property_name = 'NLS_NCHAR_CHARACTERSET';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
STAG@agate>select tablespace_name, block_size
2 from dba_tablespaces where tablespace_name ='A_SMA';
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
A_SMA 8192
STAG@agate>create table t1 ( a nvarchar2(2000), b nvarchar2(2000))
2 tablespace a_sma pctfree 0;
Table créée.
STAG@agate>exec for i in 1..7 loop -
> insert into t1 values(rpad('*',256,'*'), rpad('*',256,'*')); -
> commit; end loop;
Procédure PL/SQL terminée avec succès.
STAG@agate>select substr(rowid,1,15) bloc,count(*) enregistrements
2 from t1 group by substr(rowid,1,15);
BLOC ENREGISTREMENTS
--------------- ---------------
AAATzmAAIAAAADO 7
STAG@agate>select sum(lengthb(a)+lengthb(b)) "Taille b" from t1;
Taille b
----------
7168
Mais lorsqu’un enregistrement est modifié, il peut avoir besoin de plus d’espace pour stocker les modifications. Par exemple, pour notre table, si vous augmentez chaque champ jusqu'à « 640 » caractères uniquement pour le premier enregistrement, les informations ne peuvent pas être stockées dans le même bloc car la taille d’un bloc n’est pas assez grande.
STAG@agate>update t1 set a = rpad('*',640,'*'), b = rpad('*',640,'*')
2 where rowid = 'AAATzmAAIAAAADOAAA';
1 ligne mise à jour.
STAG@agate>select substr(rowid,1,15) bloc,count(*) enregistrements
2 from t1 group by substr(rowid,1,15);
BLOC ENREGISTREMENTS
--------------- ---------------
AAATzmAAIAAAADO 7
STAG@agate>select sum(lengthb(a)+lengthb(b)) "Taille b" from t1;
Taille b
----------
8704
Une fois que les champs ont été modifiés, vous pouvez voir que le « ROWID » n’a pas changé ; dans le premier bloc il y a toujours « 7 » d’enregistrements. L’espace d’un bloc ne peut pas excéder « 8Kb », pourtant l’ensemble des données a besoin de plus de « 8704b » pour le stockage.
Migration d’enregistrements
Dans le cas d’agrandissement d’un enregistrement suite à une modification, si l’espace libre dans le bloc n’est pas suffisant, Oracle commence par fusionner les espaces vides générés par les effacements d’enregistrements ou par les diminutions de taille des enregistrements. Si l’espace n’est toujours pas suffisant, Oracle déplace la ligne dans un autre bloc, laissant dans le bloc d’origine un pointeur vers le « ROWID » de l’emplacement de l’enregistrement. Le « ROWID » de la ligne modifiée n'a pas changé, mais pour lire cette ligne, Oracle a besoin d’abord de lire le bloc d’origine et ensuite le bloc où l’enregistrement est situé. L’opération de déplacement d’enregistrement d’un bloc vers en autre porte le nom de migration d’enregistrements.
L’avantage de cette opération est qu'Oracle n'a pas besoin de modifier le « ROWID » de l’enregistrement dans les index lors d'une mise à jour de la ligne.
L’inconvénient majeur de la migration d’enregistrements est que chaque fois qu’Oracle doit lire ou modifier un bloc, il va lire ou modifier deux, pour le « Buffer Cache », mais également pour les segments « UNDO », pour les fichiers journaux et pour les fichiers des journaux archivés.
Les performances de la base s’en ressentent grandement.
Il est difficile d’empêcher le phénomène de migration d’enregistrements ; cependant vous pouvez :
augmenter la taille des blocs ; il faut savoir que la taille d’un bloc doit être nettement supérieure à un enregistrement ; toutefois il y a des tables qui ne peuvent pas respecter cette contrainte, car il peut y avoir des enregistrements qui sont largement supérieurs à « 32Kb », la plus grande taille de bloc ;
configurer le paramètre de stockage « PCTFREE » suivant les traitements sur tables.
Chaînage d’enregistrements
Un autre mécanisme qui peut être apparenté aux migrations d’enregistrements est le chaînage des enregistrements. Si un enregistrement est trop grand pour tenir dans un seul bloc, Oracle le stocke dans plusieurs blocs chaînés par des pointeurs : c'est le phénomène de chaînage d'enregistrements. Pour lire ces enregistrements, Oracle a alors besoin de lire plusieurs blocs. Il peut apparaître pendant l’insertion ou la mise à jour des enregistrements de grande taille ou qui contiennent des champs de type large objets « LOB ».
STAG@agate>create table t2 ( a nvarchar2(2000),b nvarchar2(2000),
2 c nvarchar2(2000)) tablespace a_sma pctfree 0;
Table créée.
STAG@agate>insert into t2 values(rpad('*',2000,'*'),
2 rpad('*',2000,'*'),rpad('*',2000,'*'));
1 ligne créée.
STAG@agate>select sum(lengthb(a)+lengthb(b)+lengthb(c)) "Taille b" from t2;
Taille b
----------
12000
STAG@agate>select substr(rowid,1,15) bloc,count(*) enregistrements
2 from t2 group by substr(rowid,1,15);
BLOC ENREGISTREMENTS
--------------- ---------------
AAATzsAAIAAAADX 1
Dans le cas de chaînage des enregistrements, il n’est pas possible d’avoir une action correctrice car généralement les enregistrements sont beaucoup plus grands que les blocs respectifs.
Si la taille est inférieure à la taille maximale des blocs autorisés, vous pouvez créer un nouveau tablespace avec une taille de bloc supérieure et déplacer la table dans le nouveau tablespace.
STAG@agate>alter system set db_16k_cache_size=16M;
Système modifié.
STAG@agate>create tablespace a_sma_16k datafile
2 size 10M autoextend on next 10M blocksize 16k;
Tablespace créé.
STAG@agate>alter table t2 move tablespace a_sma_16k;
Table modifiée.
Le problème n’est pas résolu pour autant car les tailles des enregistrements sont comparables aux tailles des blocs ; il s’en suit que les modifications des enregistrements génèrent aisément des migrations d’enregistrements.
L’élimination de migrations
Avant de corriger la migration des enregistrements, il faut d’abord les détecter dans les tables. Rappelons-nous que les « ROWID » ne changent pas automatiquement et qu’Oracle ne répertorie pas la migration ou le chaînage d’enregistrements. Il y a lieu d’abord d’analyser la ou les tables pour pouvoir retrouver ces informations.
L’ensemble des informations sur la migration ou le chaînage d’enregistrements est stocké, après l’analyse d’une table, dans la table « CHAINED_ROWS » que vous devez créer si elle n’existe pas déjà. Pour créer cette table vous pouvez utiliser le script :
$ORACLE_HOME/rdbms/admin/utlchain.sql
Une fois que vous avez créé la table « CHAINED_ROWS », vous pouvez analyser une table pour avoir les informations sur la migration ou le chaînage d’enregistrements. Attention chaque analyse ajoute un ensemble de lignes dans la table si des enregistrements migrés ou chaînes sont trouvés.
Pour analyser une table à la recherche des informations sur la migration ou le chaînage d’enregistrements vous pouvez utiliser la syntaxe suivante :
ANALYZE TABLE nom_table LIST CHAINED ROWS ;
STAG@agate>create table CHAINED_ROWS (
2 owner_name varchar2(30),
3 table_name varchar2(30),
4 cluster_name varchar2(30),
5 partition_name varchar2(30),
6 subpartition_name varchar2(30),
7 head_rowid rowid,
8 analyze_timestamp date
9 );
Table créée.
STAG@agate>analyze table T1 list chained rows;
Table analysée.
STAG@agate>select table_name, head_rowid from chained_rows
2 where table_name = 'T1';
TABLE_NAME HEAD_ROWID
------------------------------ ------------------
T1 AAATzmAAIAAAADOAAA
STAG@agate>select lengthb(a)+lengthb(b) "Taille b" from t1
2 where rowid = 'AAATzmAAIAAAADOAAA';
Taille b
----------
2560
Les informations écrites dans la table « CHAINED_ROWS » vous signalent des enregistrements qui sont stockés sur plusieurs blocs. Mais il n’y a pas de distinction entre la migration et le chaînage d’enregistrements ; il va falloir trouver par vous-même quels sont les enregistrements migrés et quels sont les enregistrements chaînés.
Une fois l’analyse effectuée, vous pouvez voir le premier enregistrement de la table « T1 » comme étant un enregistrement stocké sur plusieurs blocs. A ce stade, il va falloir tester que sa taille est inférieure à la taille du bloc dans lequel il est stocké. Ainsi on peut déterminer s’il s’agit d’une migration ou d’un chaînage d’enregistrement.
Les migrations d’enregistrements peuvent être corrigées par les opérations suivantes :
Le déplacement de la table à l’aide de la commande « ALTER TABLE…MOVE ».
La redéfinition en ligne des tables.
La copie des enregistrements migrés dans une table de sauvegarde, l’effacement des enregistrements de la table d’origine et l’insertion des enregistrements à partir de la table de sauvegarde.
STAG@agate>alter table t1 move;
Table modifiée.
STAG@agate>truncate table chained_rows;
Table tronquée.
STAG@agate>analyze table T1 list chained rows;
Table analysée.
STAG@agate>select table_name, head_rowid from chained_rows
2 where table_name = 'T1';
aucune ligne sélectionnée