dimanche 14 juin 2015

Chaînage et Migration des données ORACLE


1.  Introduction. 3
    1.1- Migrations des lignes. 3
    1.2- Lignes chaînées
2. Détecter les tables avec des lignes migrées ou chaînées. 4
    2-1- Nombre total d’événements 'table fetch continued row' depuis le démarrage de l'instance. 4
    2.2 - Détecter toutes les tables ayant des lignes chaînées ou migrées
3- Comment éviter les lignes migrées et chaînées

4- Conclusion. 7


1.  Introduction


Dans le cadre de l’optimisation préventive et curative des traitements et accès aux données, et après avoir remarqué quelques dégradations de performances sur notre base production, 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, ce qui est le notre cas aujourd’hui.
L'article se propose :

§  de présenter le chaînage et la migration de lignes
§  d’identifier le chaînage et la migration de lignes au niveau de notre base PRODDB.
§  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 migrations de lignes, I/O supplémentaires dégradant les performances.


1.1- Migrations des lignes

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 au niveau du bloc). 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.

1.2- Lignes chaînées

Dans le cas du chaînage de lignes, une ligne ne peut être contenue dans un seul bloc. 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 long et long raw
§  les tables ayant plus de 255 colonnes.

Les données pour une ligne sont réparties sur plusieurs blocs.

2. Détecter les tables avec des lignes migrées ou chaînées

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

   SELECT 'Lignes migrées ou chainée = '||VALUE
   FROM v$sysstat
   WHERE lower (NAME) = 'table fetch continued row';

Lignes migrées ou chainée = 54 101 159

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 54101159 fois.
§  on peut avoir 54101159 tables avec une ligne chaînée ou migrée, ligne qui a été extraite.

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

§  depuis combien de temps l'instance est démarrée  (notre cas : 2015-03-04 18:03:22)
§  combien de lignes cela représente en pourcentage par rapport au nombre total de lignes extraites.

 Pour cela on doit comparer les statistiques table fetch continued row  en pourcentage avec les statistiques table fetch by rowid :

SELECT NAME, VALUE
FROM v$sysstat
WHERE lower(name) like 'table fetch%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch by rowid                                             3,3659E+10
table fetch continued row                                          54101159


On remarque que 16.5% des lignes sont chainées ou migrées, chiffre qu’il faut essayer de réduire.

2.2 - Détecter toutes les tables ayant des lignes chaînées ou migrées

Le diagnostique a été effectué sur les schémas ci-dessous, et sera généraliser sur tous les schémas de la BD : 'TBAADM','CUSTOM','CRMUSER','TBAGEN'

SELECT owner_name,
       table_name,
       count(head_rowid) row_count
FROM chained_rows
WHERE owner_name  in ('TBAADM','CUSTOM','CRMUSER','TBAGEN')
GROUP BY owner_name,table_name
ORDER BY 3 DESC

Résultats :

OWNER_NAME
TABLE_NAME
ROW_COUNT
TBAADM
INT_ADJUSTMENT_RGTR_TABLE
37717
CRMUSER
SALEBACKEND
6995
CRMUSER
ACCOUNTS
5055
TBAADM
BATCH_JOB_MONITOR_TBL
861
CRMUSER
AGENT_ENTITY_DETAILS
168
CRMUSER
AUDITTRAIL
142
TBAADM
AUDIT_TABLE
72
TBAADM
HOLIDAY_MAST_TABLE
62
TBAADM
IN_TRAN_DETAILS_TABLE
44
CRMUSER
CREDITBUREAU
20
CRMUSER
SALE_DOCUMENT
10
CRMUSER
SUSPECTS
6
TBAADM
GENERAL_ACCT_MAST_TABLE
3
TBAADM
GEN_SCHM_PARM_TABLE
2
CRMUSER
DEMOGRAPHIC
1

3- Comment é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
Exemple :

ALTER TABLE AUDITTRAIL 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='AUDITTRAIL'

Récupérer les index dont le statu est : UNUSABLE, puis exécuter la commande ci-dessous :

alter index index_name rebuild;

4- 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 supprimer les lignes chaînées ou migrées, utiliser une valeur plus élevée du paramètre PCTFREE en utilisant la commande ALTER TABLE MOVE.