1. Introduction
1.1- Migrations des lignes
1.2- Lignes chaînées
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
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
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.
Aucun commentaire:
Enregistrer un commentaire