Introduction
Si des performances dégradées sont remarquées sur une base de données Oracle, le chaînage et les migrations de ligne peuvent en être responsables en partie. Il est possible toutefois de détecter et de diagnostiquer ces phénomènes, et dans une plus grande mesure de reparamétrer la base de données pour empêcher le chaînage et la migration de lignes.
L’article se propose :
- de présenter le chaînage et la migration de lignes
- de montrer comment identifier les chaînage et la migration de lignes
- de montrer comment éviter le chaînage et la migration de lignes.
Les lignes migrées affectent les systèmes OLTP qui utilisent des indexes pour lire quelques lignes.
Les lignes chaînées affectent les lectures d’indexes et les scans de tables
(full table scan).
Dans le pire des cas, des I/O supplémentaires pour toutes les lectures peuvent apparaître à cause du chaînage et des migration de lignes, I/O supplémentaires dégradant les performances.
Généralités
Généralités sur le bloc Oracle
La taille du bloc OS (Operating System) est l’unité minimale de lecture /
écriture de l’OS (512K pour Solaris). Lors de la création de la base de données
Oracle, la taille du bloc de données Oracle (db_block_size) doit être un
multiple de la taille du bloc OS. Une fois que le paramètre db_block_size est
appliqué, ce dernier ne peut plus être modifié (sauf à partir de la version
9i).
Un bloc de données Oracle a la structure dans le schéma qui suit :
| Header (Entête) | L’entête d’un bloc Oracle contient des informations générales sur le bloc : block address, type de segment (table, index…). |
| Espace libre (Free-Space) | L’espace libre dans un bloc
est dédié aux futures opérations insert/update.
Cet espace est généralement gouverné par
les paramètres PCTFREE et PCTUSED.
|
FREELIST, PCTFREE et PCTUSED
Oracle maintient une structure qui liste tous les blocs disposant d’espace
libre, cette structure est appelée FREELIST.
Lors de la création ou de l’altération d’une table ou d’un index, Oracle utilise deux paramètres pour le contrôle de l’espace dans les blocs.
PCTFREE: pourcentage d’espace dans un bloc qui est réservé pour les futurs update sur des données existantes dans le bloc.PCTUSED: lorsque le pourcentage d’espace utilisé dans un bloc descend en dessous du seuilPCTUSED, le bloc retourne dans la structureFREELISTet Oracle considère que ce bloc est à nouveau disponible pour l’insertion de nouvelles données.
Oracle recherche d’abord un bloc disponible dans la structure FREELISTet
les données sont insérées dans ce bloc. La disponibilité du bloc dans la
structure FREELIST est gouvernée par la valeur PCTFREE. Initialement, un bloc
vide est listée dans la structure FREELIST et le demeure jusqu’à ce que le
pourcentage d’espace disponible dans le bloc atteigne la valeur PCTFREE.
Lorsque le pourcentage d’espace libre dans le bloc atteint la valeur
PCTFREE, le bloc est enlevé de la structure FREELIST puis il est remis dans
cette structure lorsque le pourcentage du volume de données dans le bloc
redescend en dessous de la valeur PCTUSED.
Oracle utilise la structure FREELIST pour améliorer les performances. Aussi
pour chaque opération d’insertion, Oracle a besoin de rechercher les blocs
libres uniquement dans la structure FREELIST au lieu de parcourir tous les
blocs.
Migrations de ligne
Une ligne sera migrée lorsqu’une commande update sur la ligne en question ne
permettra pas à cette dernière de demeurer sur le bloc, faute d’espace
disponible. Une migration d’une ligne implique que l’intégralité de la ligne
sera déplacée vers un autre bloc, ne laissant sur le bloc d’origine qu’une
adresse de redirection (forward address). Le bloc original ne dispose plus que
du RowID et la ligne entière est déplacée vers un autre bloc.
Impacts des migrations de ligne sur les "Full Scan Tables"
Lors des scans de tables, les adresses de redirection sont ignorées. Lors d’un full scan, le moteur traitera quoiqu’il arrive la ligne, aussi l’adresse de redirection peut être ignorée.
Impacts des migrations de ligne sur les lectures d’indexes
En revanche, lors d’une lecture d’une table à travers un index, des I/Os
supplémentaires sont générés. En effet l’index indiquera au moteur de se rendre
à l’adresse file_id X, block Y, slot Z pour trouver la ligne, mais à cette
adresse, le moteur est renvoyé vers une autre adresse file_id A, block B, slot
C : aussi une E/S (logique ou physique) supplémentaire est générée pour
effectivement trouver cette ligne.
Lignes chaînées
Dans le cas du chaînage de lignes, une ligne ne peut être contenue dans un seul bloc. Par exemple, si une base de données est paramétrée avec des blocs de 4K et qu’il est nécessaire d’insérer 8Kb dans une ligne d’une table, Oracle utilisera 3 blocs pour stocker la ligne. Certaines conditions engendrent le chaînage de lignes :
- les tables pour lesquelles la taille de la ligne excède la taille du bloc
- les tables ayant des colonnes de type
longetlong raw - les tables ayant plus de 255 colonnes.
Les données pour une ligne sont réparties sur plusieurs blocs.
Les lignes chaînées nous affectent de manière différente. Tout dépend de la donnée que l’on a besoin.
Dans le cas d’une ligne qui dispose de deux colonnes réparties sur deux blocs différents, dans ce cas la requête
select column1 from toù column1 est la première colonne ne causera pas d’évènement table fetch
continued row
Toutefois, pour la requête :
select column2 from tl’évènement table fetch continued row sera déclenché.
Cas pratique
Pour mettre en évidence la migration et le chaînage de lignes, la table
row_mig_chain_demo dépassant la capacité d’un bloc est créée. Dans le contexte
de ce cas pratique, la taille du bloc de la base de données est de 8K.
select name, value from v$parameter where name='db_block_size';db_block_size ------------- 8192
create table row_mig_chain_demo (
x int primary key,
a char(2000),
b char(2000),
c char(2000),
d char(2000),
e char(2000));Les colonnes de types char(2000) vont permettre d’engendrer des chaînages de
lignes ainsi que des migrations de lignes.
Initialement, 3 lignes sont insérées dans la table row_mig_chain_demo en ne
renseignant que la colonne clé primaire (x) :
insert into row_mig_chain_demo (x) values (1);
insert into row_mig_chain_demo (x) values (2);
insert into row_mig_chain_demo (x) values (3);
commit;pour des raisons d’affichage, les colonnes a, b, c, d, e ne seront pas affichées
et à présent une selection sur la table row_mig_chain_demo est réalisée :
column a noprint; column b noprint; column c noprint; column d noprint; column e noprint; select * from row_mig_chain_demo;X ---------- 1 2 3
À l’issue de cette sélection, pour savoir si des lignes chaînées ont été rencontrées :
select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) = 'table fetch continued row';NAME VALUE ---------------------------------------------------------------- ---------- table fetch continued row 0
La statistique table fetch continued row est à 0, ce qui montre que les
trois lignées insérées ont été mises dans un même et seul bloc :
Démonstration de la migration de ligne
À présent, réalisons des mises à jour sur ces lignes, en commençant par la
dernière (x=3) et en finissant par la première :
update row_mig_chain_demo set a = 'z1', b = 'z2', c = 'z3' where x = 3;
commit;
update row_mig_chain_demo set a = 'y1', b = 'y2', c = 'y3' where x = 2;
commit;
update row_mig_chain_demo set a = 'w1', b = 'w2', c = 'w3' where x = 1;
commit;Une sélection sur la table row_mig_chain_demo montre clairement que des
migrations de lignes se sont produites :
select * from row_mig_chain_demo;X ---------- 3 2 1
en effet Oracle trouve d’abord la ligne pour laquelle x=3, ensuite la ligne
pour laquelle x=2 et enfin la ligne pour laquelle x=1.
En revanche, il ne s’est toujours pas produit de chaînage de lignes en
interrogeant la statistique table fetch continued row
select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) = 'table fetch continued row';NAME VALUE ---------------------------------------------------------------- ---------- table fetch continued row 0
La mise à jour de la ligne 3 n’a pas subi de migration car l’espace était disponible dans le bloc 1. En revanche, la ligne 2 a été migrée sur le bloc 2 faute d’espace disponible dans le bloc 1 et la ligne 1 a été migrée sur le bloc 3 faute d’espace disponible sur le bloc 1 (occupée par la ligne 3) et sur le bloc 2 (occupée par la ligne 2).
À présent, observons l’effet d’une ligne migrée sur la statistique table
fetch continued row en interrogeant la table row_mig_chain_demo par sa clé
primaire :
|
|
|
|
|
Comme il s’agit d’une lecture de table par son index, lors de la sélection
de la table pour x=1 qui correspond à une ligne migrée, la statistique table
fetch continued row est donc incrémentée de 1.
Démonstration du chaînage de ligne
A présent, réalisons une mise à jour sur la ligne 3 qui est non migrée de telle sorte que la taille de la ligne ne puisse intégralement rentrer dans le bloc 1 qui a une taille de 8Kb. :
update row_mig_chain_demo set d = 'z4', e = 'z5' where x = 3;
commit;Avec une telle mise à jour, la taille de la ligne 3 est d’environ 5 * 2K,
soit 10K laquelle ne peut rentrer intégralement dans le bloc 1 de 8K : ainsi la
ligne 3 est réellement chaînée. La statistique table fetch continued row le
prouve par ailleurs, en effet une sélection sur les colonnes d et e pour la
ligne 3 (x=3) incrémente cette statistique.
|
|
|
|
|
Le chaînage de la ligne 3 peut alors être schématisée ainsi :
Dans ce cas pratique, la ligne 3 est réellement chaînée.
Détecter les tables avec des lignes migrées ou chaînées
Nombre total d’évènements 'table fetch continued row' depuis le démarrage de l’instance
La vue V$SYSSTAT indique combien de fois depuis le démarrage de l’instance
la statistique table fetch continued row a été incrémentée, ce qui revient à
rechercher le nombre de fois où l’instance a rencontré des lignes chaînées ou
migrées :
sqlplus system/<password>SELECT 'Chained or Migrated Rows = '||value FROM v$sysstat WHERE name = 'table fetch continued row';Chained or Migrated Rows = 378
L’interprétation de la requête peut signifier plusieurs choses :
- on peut avoir une table avec une ligne chaînée ou migrée qui a été extraite 378 fois.
- on peut avoir 378 tables avec une ligne chaînée ou migrée, ligne qui a été extraite.
Aussi : 378, ceci peut être dramatique ou pas ! Tout ceci est fonction :
- depuis combien de temps l’instance est démarrée
- combien de lignes cela représente en pourcentage par rapport au nombre total de lignes extraites.
La statistique table fetch continued row doit toujours être comparée en
pourcentage avec la statistique table fetch by rowid :
select name, value from v$sysstat where name like 'table fetch%';NAME VALUE ---------------------------------------------------------------- ---------- table fetch by rowid 20284 table fetch continued row 378
Calculer le nombre de lignes migrées ou chaînées dans une table
Les colonnes chain_cnt et num_rows des tables vues user_tables et dba_tables
permettent de déterminer simplement le pourcentage de lignes chaînées ou
migrées dans une table, à la seule condition que la commande ANALYZE TABLE
<table_name> COMPUTE STATISTICS soit lancée régulièrement sur les tables
car dans le cas contraire ces colonnes sont à nul.
analyze table row_mig_chain_demo compute statistics; select chain_cnt, round(chain_cnt/num_rows*100,2) pct_chained, avg_row_len, pct_free , pct_used from user_tables where table_name = 'ROW_MIG_CHAIN_DEMO';CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED ---------- ----------- ----------- ---------- ---------- 3 100 7358 10 40
PCT_CHAINED est à 100% ce qui indique que 100% des lignes dans la table
ROW_MIG_CHAIN_DEMO sont migrées ou chaînées.
Détecter toutes les tables ayant des lignes chaînées ou migrées
La table CHAINED_ROWS permet de déterminer avec exactitude le nombre de
lignes chaînées ou migrées dans une table.
- Le script ulchain.sql est fourni dans $ORACLE_HOME/rdbms/admin pour créér la table
CHAINED_ROWS :
cd $ORACLE_HOME/rdbms/admin
sqlplus system/<password>
@utlchain.sql- Il est ensuite nécessaire d’analyser toutes les tables avec la commande
analyze table list chained rows :
analyze table <owner>.<table_name> list chained rows into CHAINED_ROWS;Pour générer dynamiquement, ce script :
set pagesize 4000;
set linesize 1000;
set trimspool on;
set heading off;
set feedback off;
spool 'analyze_chdrows.sql';
select 'analyze table '||owner||'.'||table_name||' list chained rows into CHAINED_ROWS;'
from dba_tables
where owner not in ('SYS');L’interrogation de la table CHAINED_ROWS permettra de détecter rapidement
les tables avec des lignes chaînées ou migrées :
select owner_name, table_name, count(head_rowid) row_count from chained_rows group by owner_name,table_nameOWNER_NAME TABLE_NAME ROW_COUNT ------------------------------ ------------------------------ ---------- SYSTEM ROW_MIG_CHAIN_DEMO 3
Si le chaînage ou la migration de ligne est prédominante dans une table, cette dernière devrait être reconstruite avec une valeur PCTFREE plus élevée.
Éviter les lignes migrées et chaînées
En augmentant la valeur du paramètre PCTFREE, cela peut empêcher les
migrations de lignes dans le sens ou plus d’espace est réservé dans un bloc. Il
est également possible de réorganiser les tables et indexes ayant un fort taux
de suppressions.
La commande ALTER TABLE ... MOVE relocalise les données d’une
table non partitionnée ou d’une partition d’une table dans un nouveau segment,
et optionnellement dans un nouveau tablespace. Cette commande permet également
de modifier les attributs de stockage comme le paramètre PCTFREE.
Étape 1 : lancement
de la commande ALTER TABLE ... MOVE
ALTER TABLE row_mig_chain_demo MOVE
PCTFREE 20
PCTUSED 40
STORAGE (INITIAL 20K NEXT 40K MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 0);Dans la commande ci-dessus, le paramètre PCTFREE est modifié pour passer de
10 à 20.
Étape 2 : reconstruction des indexes de la table ou de la partition en question
Déplacer une table sur un nouveau segment modifie les rowids de la table.
Les indexes de la table sont alors marqués avec le statut UNUSABLE et
les commandes DML accédant à la table en utilisant ces indexes rencontrent
l’erreur ORA-01502. Aussi après une commande ALTER TABLE <table_name>
MOVE, les indexes doivent être recréées ou reconstruits :
select index_name, index_type, owner, status from dba_indexes where table_name='ROW_MIG_CHAIN_DEMO'INDEX_NAME INDEX_TYPE OWNER STATUS ------------------- ---------------- ------------------------------ -------- SYS_C002030 NORMAL SYSTEM UNUSABLE
alter index SYS_C002030 rebuild; select index_name, index_type, owner, status from dba_indexes where table_name='ROW_MIG_CHAIN_DEMO'INDEX_NAME INDEX_TYPE OWNER STATUS ------------------- ---------------- ------------------------------ -------- SYS_C002030 NORMAL SYSTEM VALID
Dans le cas pratique qui nous intéresse, avec cette reconstruction, il n’y a
plus q’une ligne migrée ou chaînée dans la table ROW_MIG_CHAIN_DEMO :
truncate table CHAINED_ROWS; analyze table ROW_MIG_CHAIN_DEMO list chained rows into CHAINED_ROWS;OWNER_NAME TABLE_NAME ROW_COUNT ------------------------------ ------------------------------ ---------- SYSTEM ROW_MIG_CHAIN_DEMO 1
Conclusion
Les lignes migrées affectent les systèmes OLTP qui utilisent des indexes pour lire des lignes singulières. Les lignes réellement chaînées affectent les lectures d’indexes et les full scans de tables.
- les migrations de lignes sont typiquement causées par des opérations
UPDATE - les chaînage de lignes sont typiquement causées par des opérations
INSERT - les commandes SQL qui crééent ou interrogent ces lignes migrées ou chaînées dégradent les performances à cause d’I/Os supplémentaires.
- Pour diagnostiquer les lignes migrées / chaînées, utiliser la commande
ANALYZEou interroger la vueV$SYSSTAT. - Pour supprimer les lignes chaînées ou migrées, utiliser une valeur plus
élevée du paramètre
PCTFREEen utilisant la commandeALTER TABLE MOVE.