Dieses Webinar finden Sie auch auf unserem Youtube Webinar Channel.
Chained Rows - was ist das, warum stört es und wie behebt man es?
In der Oracle Datenbank werden Datensätze in Datenbankblöcken abgelegt und bleiben - mit wenigen Ausnahmen, aber das ist eine andere Story - Ihr Leben lang in diesem Block. Sollte ein Datensatz - warum auch immer - nicht in einen einzigen Datenbankblock passen, so wird er mit Hilfe von Pointern (chains) verkettet.
Wann kommt es zu Chained Rows?
- Wenn die Datensätze größer als der Verfügbare Platz im Block sind.
Solange Datensätze in den Oracle Block passen und nicht zu sehr wachsen, werden sie auch immer komplett innerhalb eines einzigen Datenbank Blocks liegen. Ist ein Datensatz jedoch größer als der Platz in einem Datenbankblock, dann schreibt Oracle die ersten Spalten in einen Block und fügt dann einen Pointer (=Chain) ein, der auf das nächste Stück des Datensatzes in einem anderen Block zeigt. Das ist in der Regel nicht zu verhindern, außer man nutzt größere Datenbankblöcke - dazu später mehr.
- Wenn ein Datensatz durch UPDATES, die den Datensatz vergrößern, nicht mehr in einen Block passt.
Auch in diesem Fall, erfolgt Chaining. Ein kleiner Teil des Datensatzes bleibt im ursprünglichen Datenblock und ein großer Teil wandert in einen anderen und wird wieder mit Hilfe eines Pointers verkettet. Das ist der unangenehme Fall, der mitunter merklichen Einfluss auf die Performance haben kann. Greift man über einen Index auf einen solchen verketteten Datensatz zu, muss Oracle nicht nur einen sondern zumindest noch einen zweiten Tabellenblock lesen. Da Indizes sehr oft zu großen Teilen im Datenbank Cache liegen und oft nur die Tabellen Blöcke von der Disk gelesen werden müssen, bedeutet dies, dass eben nicht nur einer, sondern zumindest noch ein zweiter Block gelesen werden muss. Das verlängert die Laufzeit des Statements deutlich. Das gleiche passiert bei einen Full Table Scan, hier muss Oracle für jede Chain einen zusätzlichen Single Block Read einschieben. Noch schlimmer wird es im Fall des Oracle Real Application Clusters, weil Oracle hier zusätzlich noch das Cluster Resource Management bemühen muss.
Wie groß sind die Datenbank Blöcke bei Oracle?
Das kann man sich aussuchen - auf allen Plattformen sind 2k, 4k, 8k, 16k und 32k Blockgrößen möglich, auf einigen Plattformen auch 64k - jedoch nur als Default Blocksize. Aktuell (seit 20 Jahren) ist eine Default Blocksize von 8k üblich, lediglich in Data Warehouses und wenn man sehr viele LOBs (Large Objects) abspeichern muss, werden 16k oder 32k Blöcke genutzt.
Die Default Blocksize wird VOR dem Erzeugen der Datenbank für die Instance vorkonfiguriert und ist in DB_BLOCK_SIZE auszulesen:
select VALUE from V$PARAMETER where NAME='db_block_size';
VALUE
-----
8192
Damit man andere - non-default - Blocksizes nutzen kann, muss der DBA weitere Buffer Caches definieren, das ist aber eine andere Story. Ist man sich nicht sicher, ob alle Tablespaces die gleiche Blocksize haben, kann man dies mit folgender Abfrage überprüfen:
select TABLESPACE_NAME, BLOCK_SIZE from DBA_TABLESPACES;
TABLESPACE_NAME BLOCK_SIZE
--------------- ----------
DATA1 8192
SYSAUX 8192
SYSTEM 8192
TEMP 8192
UNDOTBS1 8192
USERS 8192
Grund für die Chain ermitteln
Passen die Datensätze jetzt nicht in den Block, oder liegt es am Update?
Wenn man die Oracle Blocksize kennt - am besten über DBA_TABLESPACES überprüft - muss man prüfen wie lange die einzelnen Datensätze durchschnittlich sind. Dazu schaut man einfach in DBA_TABLES nach, sofern es aktuelle Objekt Statistiken für die Tabellen gibt.
select LAST_ANALYZED, SAMPLE_SIZE, NUM_ROWS, AVG_ROW_LEN from DBA_TABLES where TABLE_NAME='EMP';
LAST_ANA SAMPLE_SIZE NUM_ROWS AVG_ROW_LEN
-------- ----------- ---------- -----------
12.08.20 14 14 38
In Last Analyzed steht der Zeitpunkt an dem diese Statistik zuletzt aktualisiert wurde. Auf Grund er SAMPLE_SIZE (wie viele Records wurden für die Statistik herangezogen) im Verhältnis zu NUM_ROWS (Anzahl der Datensätze in der Tabelle) erkennt man wie Datensätze der Tabelle angesehen wurden - in diesem Beispiel waren es alle. Somit wird die Aussage der AVG_ROW_LEN - wie lange ein Datensatz durchschnittlich ist - sehr genau stimmen. Sobald die AVG_ROW_LEN in die Größenordnung von 80% der Block Size kommen, wird es vermutlich an der Datensatzlänge liegen, warum es zu Chaines kommt. Ist die AVG_ROW_LEN viel kleiner als der Block lag es an UPDATES und die Reorganisation wird eine deutliche Verbesserung bringen. Zusätzlich sollte man dann die Einstellung von PCT_FREE für die Tabelle erhöhen, damit es in Zukunft weniger oft zu Chaines kommt.
Wie erkennt man nun, dass man ein Problem mit Chained Rows hat?
Oracle zählt den Event-Counter "table fetch continued row" jedesmal hoch, wenn einer Chain nachgegangen werden muss. Das kann man über verschiedene Quellen verifizieren:
In den System Statistiken:
select NAME, VALUE from V$SYSSTAT where NAME ='table fetch continued row';
NAME VALUE
-------------------------- ----------
table fetch continued row 228684
Oder in der aktuellen Session:
select sn.NAME, ms.VALUE from V$STATNAME sn join V$MYSTAT ms using (STATISTIC#) where sn.NAME='table fetch continued row';
NAME VALUE
-------------------------- ----------
table fetch continued row 577
Diese Zahlen alleine sagen aber noch nichts aus - erst im Verhältnis zu anderen Statistik-Werten kann man sagen, ob das relevant ist oder nicht. Beispielsweise kann die Statistik "session logical reads" zum Vergleich herangezogen werden.
select sn.name, ms.value from v$statname sn join v$mystat ms using (statistic#) where sn.name in ('table fetch continued row','session logical reads');
NAME VALUE
-------------------------- ----------
session logical reads 5358
table fetch continued row 577
In diesem Beispiel sieht man, dass ich in meiner Session 5358 Datenblöcke verarbeitet habe. Dabei ist es zu 577 zusätzlichen Reads auf Grund von Verkettung gekommen. Bei der Umrechnung in Prozent kommt man auf
100/(session logical reads-table fetch continued row)*table fetch continued row = 100/(5358-577)*577 = 12,1%.
Warum wurde hier durch "session logical reads-table fetch continued row" dividiert? Ganz einfach: Weil durch die Chains ja zumindest 577 zusätzliche logical reads erfolgt sind. Somit hat man einen Overhead von über 12%, der sich in der Regel durch zusätzliche I/O-Zeiten noch deutlicher auswirken wird.
Wie kann man dieses Problem jetzt beheben?
Zuerst muss herausgefunden weren, welche Tabellen betroffen sind. Der Ansatz von Oracle, mit folgender Query zu arbeiten, hat leider einige Probleme:
select OWNER, TABLE_NAME, AVG_ROW_LEN, NUM_ROWS, CHAIN_CNT from DBA_TABLES where CHAIN_CNT > 0;
Bei der Sammlung der Statistikdaten aktualisiert Oracle leider den CHAIN_CNT nicht! Dieser bleibt somit immer bei 0.
Die Chained Rows lassen sich nur mittels folgendem Befehl ermitteln:
analyze table EMP compute statistics;
Dann wird die Spalte chain_cnt ebenfalls befüllt. Allerdings ist die Berechnung von Statistiken mittels ANALYZE TABLE schon seit langem als "deprecated" gekennzeichnet - siehe auch SQL Reference Guide: ANALYZE - und hat auch in der Vergangenheit zu Fehlern geführt. Noch dazu verurscaht das Neuberechnen der Statistiken zu einer Invalidation aller Statements, in denen diese Tabelle vorkommt - und das kann auf einem Produktions-Server übel ausgehen.
Die einzige sichere Variante ist, eine Hilfstabelle namens CHAINED_ROWS mittels des Scripts in $ORACLE_HOME/rdbms/admin/utlchain.sql anzulegen und dann für jede Tabelle, die unter Verdacht steht, folgenden Befehl abzusetzen:
ANALYZE TABLE kunden LIST CHAINED ROWS;
select distinct owner_name, table_name from chained_rows;
OWNER_NAME TABLE_NAME
---------- ----------
SYSTEM KUNDEN
select count(*) from chained_rows where owner_name='SYSTEM' and table_name='KUNDEN';
COUNT(*)
--------
2000
Für alle, die keinen Zugriff auf das Script haben, hier das CREATE TABLE Kommando für die Tabelle chained_rows:
CREATE TABLE chained_rows (
owner_name VARCHAR2(30),
table_name VARCHAR2(30),
cluster_name VARCHAR2(30),
partition_name VARCHAR2(30),
subpartition_name VARCHAR2(30),
head_rowid ROWID,
analyze_timestamp DATE
);
Hat man die entsprechenden Kandidaten gefunden, dann gibt es mehrere Möglichkeiten, das Problem zu beheben:
- Die Tabelle mit ALTER TABLE MOVE reorganisieren, danach müssen aber alle Indexes der Tabelle mit REBUILD neu aufgebaut werden, weil sie in den Zustand UNUSABLE gehen.
- Die Tabelle mit ALTER TABLE MOVE ONLINE reorganisieren, das funktioniert ab Oracle 12.2, allerdings darf man es nur in der Enterprise Edition. Dafür verursacht es keine exklusive Sperre der Tabelle und hinterläßt auch keine zerstörten Indizes.
- Die Tabelle mit EXPDP/DROP/IMPDP reorganisieren - funktioniert in jeder Version/Edition, ist aber sicher die langsamste und aufwändigste Lösung.
- Auch eine reorganisation mit DBMS_REDEFINTIION ist möglich, macht aber nur Sinn in einer Oracle Enterprise Edition vor Version 12.2, da MOVE ONLINE einfacher und effizienter ist.
- Durch Reorganisationsscripts - und darauf wollen wir im weitere eingehen.
Für die folgenden Beispiel gehen wir von folgendem aus:
- Es gibt eine Tabelle SYSTEM.KUNDEN in der viele Chained Rows liegen - nur diese soll reorganisiert werden.
- Wir sind als Benutzer SYSTEM (also als Tabellen Owner) angemeldet.
- Die Scripts sind für SQLPLUS gedacht, können aber einfach angepasst werden.
/* Schema Name und Tabellen Name unbedingt gross schreiben! */
define tableowner='SYSTEM'
define tablename='KUNDEN'
ANALYZE TABLE &tableowner..&tablename LIST CHAINED ROWS INTO chained_rows;
select count(*) from chained_rows where owner_name='&tableowner' and table_name='&tablename';
COUNT(*)
--------
8725
Ab jetzt gibt es zwei Varianten, die einfache - die aber zur Sicherheit eine Downtime der Applikation braucht (es dürfen keine Rows gesperrt sein, sonst bleibt das Script hängen), oder eine etwas komplexerer Variante (weiter unten):
/* Easy way - may hang on a ROW LOCK during the DELETE */
DROP TABLE PROBLEMATIC_ROWS;
CREATE TABLE PROBLEMATIC_ROWS as
SELECT a.* from &tableowner..&tablename a where 1=2;
insert into PROBLEMATIC_ROWS
SELECT a.* from kunden a
WHERE rowid in (SELECT HEAD_ROWID from chained_rows);
DELETE FROM kunden
WHERE rowid in (SELECT HEAD_ROWID from chained_rows);
INSERT INTO kunden
SELECT * from PROBLEMATIC_ROWS;
COMMIT;
Optional kann man die Tabelle PROBLEMATIC_ROWS als GLOBAL TEMPORARY TABLE anlegen, das verringert Redo und damit I/Os.
Die komplexere Version hat den Vorteil, dass man sie auch bei laufender Applikation nutzen kann. Sind Datensätze aktuell gesperrt, dann werden diese einfach ausgelassen. Mit der Konstanten Batchsize kann man angeben, wie viele Datensätze auf einmal reorganisiert werden - je mehr um so schneller, allerdings sind dann auch immer mehr Rows gleichzeitig gesperrt.
/* COMPLEX but only WAY */
drop table PROBLEMATIC_ROWS;
CREATE TABLE PROBLEMATIC_ROWS as
SELECT a.* from kunden a where 1=2;
set serveroutput on
declare
batchsize number CONSTANT:= 10;
dummy rowid;
cnt PLS_INTEGER;
rows_done PLS_INTEGER;
cnt_locked PLS_INTEGER;
begin
cnt := 0;
rows_done := 0;
cnt_locked := 0;
for rows in (select head_rowid from chained_rows)
loop
begin
cnt := cnt+1;
select rowid into dummy from &tableowner..&tablename where rowid = rows.head_rowid for update skip locked;
insert into PROBLEMATIC_ROWS SELECT a.* from &tableowner..&tablename a WHERE rowid = rows.head_rowid;
delete from &tableowner..&tablename WHERE rowid = rows.head_rowid;
delete from chained_rows where head_rowid=rows.head_rowid;
if cnt = 10 then
insert into &tableowner..&tablename select * from PROBLEMATIC_ROWS;
delete from PROBLEMATIC_ROWS;
commit;
cnt := 0;
end if;
rows_done := rows_done+1;
exception
when NO_DATA_FOUND THEN cnt_locked := cnt_locked+1;
end;
end loop;
insert into &tableowner..&tablename select * from PROBLEMATIC_ROWS;
delete from PROBLEMATIC_ROWS;
commit;
dbms_output.put_line('Number of rows reorganized: ' || rows_done);
dbms_output.put_line('Number of rows skipped due to DML Locks: ' || cnt_locked);
end;
/
Number of rows reorganized: 1374
Number of rows skipped due to DML Locks: 1000
Damit sollten wir alle nicht aktuell gesperrten Chained Rows repariert haben. Wenn bei "Number of rows skipped due to DML Locks" eine Null steht, dann wurden alle Datensätze repariert.
Jetzt bleibt nur noch das Cleanup, damit wir nichts liegen lassen, was wir nicht mehr brauchen:
DROP TABLE chained_rows;
DROP TABLE PROBLEMATIC_ROWS;
Die folgenden Schritte sind optional, und müssen nicht gemacht werden:
- Index Rebuild aller Indizes auf der Tabelle - das geht in der Oracle Enterprise Edition auch online.
- Aktualisieren der Objekt Statistik für die betroffenen Tabellen
Interesse an diesem Webinar geweckt?
Dann schauen Sie es sich doch direkt auf unserem Youtube Channel an: DB Masters Webinar: Chained Rows in Oracle Datenbanken - verstehen, erkennen und beheben..
Vergessen Sie nicht unseren Youtube Channel zu abonnieren, damit Sie keines unserer Videos verpassen!