In Anlehnung an Tanel Poders interessanten Artikel über Zeilenmigration hier eine Zusammenfassung.

Was ist Zeilenmigration.

Speziell bei Tabellen mit variablen Datentypen kann ein Problem auftreten, das als Zeilenmigration bekannt ist. Man stelle sich vor, eine Tabelle besitzt eine Spalte „Nachname“ des Datentyps VARCHAR2(100). Eine Zeile dieser Tabelle muss geändert werden, indem der Spaltenwert dieser Zeile von „Meier“ auf „Meier-Schmidt“ geändert wird. Das Problem ist allerdings, dass der Block, in dem sich diese Zeile befindet, voll ist und sich somit die Zeile nicht ausdehnen kann. Da aber eventuell Indizes auf die ROWID dieser Zeile verweisen, kann die Zeile nicht einfach in einen freien Block verschoben werden.

Entstehung von Zeilenmigration

Was geschieht: Dort, wo die Zeile ursprünglich war, wird ein Zeiger erzeugt, die Zeile wird in einen freien Block verschoben und der Zeiger zeigt dann auf den neuen Ort der Zeile.

Erfolgte Zeilenmigration

Erfolgt also eine Suche über einen Index, so zeigt der Index auf die alte ROWID der Zeile, an der sich aber nun der Zeiger befindet, der wiederum auf den neuen Block der Zeile zeigt.

Wichtig:

Problematisch hierbei ist, dass nun für das Auslesen dieser Zeile über einen Index zwei Blöcke (plus Index-Blöcke) gelesen werden müssen, nämlich der Block mit dem Zeiger und der neue Block der Zeile, was wiederum zwei I/O-Operationen nach sich zieht. Befindet sich ein hoher Grad an Zeilenmigration in einer Tabelle, so müssen mehr Blöcke gelesen werden als eigentlich notwendig, was einen großen Einfluss auf die Performance bei Lesevorgängen der Datenbank hat.

Werden variable Datentypen innerhalb einer Tabelle verwendet, so muss darauf geachtet werden, dass die Migration von Zeilen weitestgehend vermieden wird. Dies kann durch die Verwendung eines geeigneten Wertes für den Parameter PCTFREE realisiert werden, da dieser bewirkt, dass ein prozentualer Anteil des Blocks frei bleibt, wenn der Block als voll gekennzeichnet wird. Dieser übrig gebliebene freie Speicher innerhalb des Blocks kann dann für Änderungen an Spalten variabler Datentypen verwendet werden.

Um die Anzahl der migrierten Zeilen herauszufinden, müssen die Tabellen mit dem ANALYZE-Befehl analysiert werden, wodurch die Spalte CHAIN_CNT mit dessen Anzahl befüllt wird. Das Befüllen erfolgt leider nicht mit dem Package DBMS_STATS.

 

SQL> ANALYZE TABLE TABPCTTEST COMPUTE STATISTICS;
Tabelle wurde analysiert.

SQL> SELECT BLOCKS "BLOECKE", NUM_ROWS/BLOCKS "ZEILEN PRO BLOCK",
2 CHAIN_CNT KETTEN, PCT_FREE
3 FROM DBA_TABLES
4 WHERE TABLE_NAME='TABPCTTEST';

   BLOECKE ZEILEN PRO BLOCK     KETTEN   PCT_FREE
---------- ---------------- ---------- ----------
       13       153,846154       358         1

 

Da das Analysieren von großen Tabellen viel Zeit und Performance in Anspruch nimmt, sollte im Vorfeld eine Tendenz von migrierten Zeilen ermittelt werden. Herfür kann auf Sitzungsebene das Verhältnis von gelesenen Zeilen über einen Index und die davon abhängigen Weiterleitungen durch migrierte Zeilen bestimmt werden. Oracle besitzt dafür die Statistik TABLE FETCH BY ROWID, die die Zeilen bestimmt, die durch direkten Zugriff über die ROWID gelesen wurden und die Statistik TABLE FETCH CONTINUED ROW, die die Anzahl der Weiterleitungen wiedergibt.

Im folgenden Beispiel wird eine Tabelle TESTTAB erstellt, mit 100000 Zeilen befüllt und auf die Spalte S1 ein Index erstellt. Alle Werte der VARCHAR2-Spalte S2 werden dann im nächsten Schritt expandiert, so dass zwangsläufig eine hohe Anzahl an migrierten Zeilen entstehen muss. Danach wird dann über eine Schleife jeder Wert einzeln ausgelesen, um bei jedem Zugriff den Index zu verwenden und mögliche Weiterleitung zu erhalten.

Erstellung der Testtabelle:


SQL> CREATE TABLE HR.TESTTAB (S1 NUMBER, S2 VARCHAR2(100)) PCTFREE 1 TABLESPACE USERS;
Table created.

SQL> BEGIN
2 FOR I IN 1..100000
3 LOOP
4 INSERT INTO HR.TESTTAB VALUES (I, 'WERT' || I);
5 END LOOP;
6 COMMIT;
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX HR.IDX_TESTTAB ON HR.TESTTAB(S1) TABLESPACE USERS;

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TESTTAB')

PL/SQL procedure successfully completed.

 
Expandieren der Spaltenwerte zur Forcierung von Zeilenmigration:

 

SQL> UPDATE HR.TESTTAB
2 SET S2='DAS IST JETZT EIN GANZ LANGER WERT FÜR DAS TESTEN DER ZEILENMIGRATION' || S1;

100000 rows updated.

SQL> COMMIT;

Commit complete.

Auslesen jeder einzelnen Zeile über den Index:

 

SQL> DECLARE
2     V_S2 VARCHAR2(100);
3 BEGIN
4     FOR J IN 1..10
5     LOOP
6             FOR I IN 1..100000
7             LOOP
8                     SELECT S2
9                     INTO V_S2
10                    FROM HR.TESTTAB
11                     WHERE S1=I;
12             END LOOP;
13     END LOOP;
14 END;
15 /

PL/SQL procedure successfully completed.

SQL>

Bestimmen des Verhältnisses zwischen gelesenen Zeilen und den erfolgten Weiterleitungen:

SQL> SELECT
2     NVL(VSE.USERNAME,'HINTERGRUND') BENUTZER,
3     VSE.SID,
4     VSE.PROGRAM AS PROGRAMM,
5     TFCR.VALUE AS "ZEILENWEITERLEITUNG",
6     TFBR.VALUE AS "ZEILEN LESEN MIT ROWID",
7     ROUND(((1-(TFBR.VALUE-TFCR.VALUE)/TFBR.VALUE))*100,2) AS RATIO
8 FROM
9     (
10     SELECT
11             VSE.SID, VSE.VALUE
12     FROM
13             V$SESSTAT VSE
14                     INNER JOIN V$STATNAME VSN
15                            ON VSE.STATISTIC#=VSN.STATISTIC#
16     WHERE
17             UPPER(VSN.NAME)='TABLE FETCH CONTINUED ROW'
18     ) TFCR
19             INNER JOIN
20                     (
21                     SELECT
22                             VSE.SID, VSE.VALUE
23                     FROM
24                             V$SESSTAT VSE
25                                     INNER JOIN V$STATNAME VSN
26                                             ON VSE.STATISTIC#=VSN.STATISTIC#
27                   WHERE
28                             UPPER(VSN.NAME)='TABLE FETCH BY ROWID'
29                     ) TFBR
30                             ON TFCR.SID=TFBR.SID
31     INNER JOIN
32             V$SESSION VSE
33                     ON TFCR.SID=VSE.SID
34 WHERE
35     TFCR.VALUE>0
36* ORDER BY VSE.USERNAME

BENUTZER                  SID PROGRAMM            ZEILENWEITERLEITUNG ZEILEN LESEN MIT ROWID   RATIO
------------------ ---------- -------------------- ------------------- ---------------------- --------
HR                           15 sqlplus.exe                      118204                 134128   88,13
HINTERGRUND                 98 ORACLE.EXE (Q000)                     29                    360    8,06
HINTERGRUND                 94 ORACLE.EXE (MMON)                      2                   1605     ,12

 

Das Ergebnis dieses Experiments zeigt, dass in der Sitzung des Benutzers HR von 100 Zugriffen auf Zeilen über einen Index 88 Zeilen migriert waren.