Oracle - Chainage et migrations de lignes


1- 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 :

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.

2- Généralités

2-1- 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.

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.

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

2-3- 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 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 t

où column1 est la première colonne ne causera pas d'événement table fetch continued row

Toutefois, pour la requête :

select column2 from t

l'événement table fetch continued row sera déclenché.

2-4- 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.

SQL> select name, value from v$parameter where name='db_block_size';
db_block_size
-------------
8192

SQL> 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înage 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 Primary key (x) :

SQL> insert into row_mig_chain_demo (x) values (1);
SQL> insert into row_mig_chain_demo (x) values (2);
SQL> insert into row_mig_chain_demo (x) values (3);
SQL> 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 :

SQL> column a noprint;
SQL> column b noprint;
SQL> column c noprint;
SQL> column d noprint;
SQL> column e noprint;
SQL> select * from row_mig_chain_demo;
X
----------
1
2
3

A 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 :

2-4-1- Démonstration de la migration de ligne

A 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 :

SQL> update row_mig_chain_demo set a = 'z1', b = 'z2', c = 'z3' where x = 3;
commit;
SQL> update row_mig_chain_demo set a = 'y1', b = 'y2', c = 'y3' where x = 2;
commit;
SQL> 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 :

SQL> 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 :

SQL> select *
from row_mig_chain_demo where x=3;
>
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
SQL> select *
from row_mig_chain_demo where x=1;
>
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          1

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.

2-4-2- 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. :

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

SQL> select x,a 
from row_mig_chain_demo where x=3;
>
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          1
SQL> select x,d,e 
from row_mig_chain_demo where x=3;
>
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          2

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.

3- Détecter les tables avec des lignes migrées ou chaînées

3-1- Nombre total d'évènements 'table fetch continued row' depuis ledé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>
SQL > SELECT 'Chained or Migrated Rows = '||value
SQL > FROM v$sysstat
SQL > WHERE name = 'table fetch continued row';
Chained or Migrated Rows = 378

L'interprétation de la requête peut signifier plusieurs choses :

Aussi : 378, ceci peut être dramatique ou pas ! Tout ceci est fonction :

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

3-2- 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.

3-3- 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 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_name
OWNER_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.

4- É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 permet de relocaliser 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.

Première étape : 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-dessous, le paramètre PCTFREE est modifié pour passer de 10 à 20.

Seconde étape : 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
SQL> 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

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


Annexe

Historique

Version Date Commentaires
1.0 02/2005 Version initiale

Liens

Oracle9i Database Administrator's Guide Release 2 (9.2), General Management of Schema Objects, Listing Chained Rows of Tables and Clusters
Books OnLine Oracle 9i R2
Oracle