News & Events
DBA-Tipp: Defragmentierung von Tabellen – Tablespacefreiplatz und Buffer Cache Hit Ratio erhöhen

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Das Löschen einer größeren Menge von Zeilen aus einer Tabelle führt in aller Regel dazu, dass die Datenblöcke der Tabelle stark fragmentiert werden, es dann also viele Blöcke gibt, die nur wenige Daten enthalten. Diese Fragmentierung hat zwei wesentliche Nachteile:

  • Speicherplatz wird nachhaltig verschwendet, wenn die Lücken nicht im Laufe der Zeit wieder durch neue Zeilen gefüllt werden. Da der Freiplatz fest von einer bestimmten Tabelle allokiert ist, steht er auch keinen anderen Objekten in der Datenbank zur Verfügung.
  • Auch wenn der Platz nach und nach wiederverwendet wird, wirkt sich diese Fragmentierung bis dahin negativ auf die Performance aus. Der Buffer Cache der Datenbank, eine der wichtigsten Komponenten im Bezug auf Datenbankperformance, arbeitet rein blockbasiert. Folglich verschwenden Datenblöcke einer Tabelle Platz im Buffer Cache, wenn sie fragmentiert sind, da auch der leere Anteil im Block auf den Buffer Cache angerechnet wird. Die „Nutzdatenrate“ ist damit schlechter als bei unfragmentierten Blöcken, die Performance sinkt durch eine geringere Buffer Cache Hit Ratio.

Es kann also nach größeren Löschoperationen interessant sein, welche Tabellen mittels

alter table … shrink space cascade;

geshrinkt werden können, um zum einen unbelegten Platz freizugeben und zum anderen die Buffer Cache Effizienz hoch zu halten. Dafür kann man sich zwei Tabellenstatistiken zu Nutze machen – die Zeilenzahl und die durchschnittliche Zeilenlänge – und diese mit der tatsächlichen Platzbelegung in Bezug setzen. Es ist zu beachten, dass es sich dabei nicht um exakte Werte handelt. Wenn die Statistiken jedoch aktuell sind, stellen sie für diesen Zweck recht zuverlässige Schätzungen dar. Grundsätzlich kann man davon ausgehen, dass Tabellen, in denen große Datenmengen gelöscht wurden, auch vom nächtlichen Autotask „auto optimizer stats collection“ erkannt und analysiert wurden. Andernfalls rechnet man mit geeigneten Prozeduren des dbms_stats-Package die Statistiken einfach selbst neu.

Zur Abschätzung des Platzes, den wir durch das Shrinken voraussichtlich einsparen werden können, stellen wir nun einen Vergleich zwischen dem erwarteten Platzbedarf (Zeilenzahl * durchschnittliche Zeilenlänge) und dem tatsächlich belegten Platz (Summe der Segmentgrößen) an:

WITH table_subset AS
  ( SELECT owner,table_name
    FROM dba_tables
    WHERE owner='SOE'
  )
SELECT exp.owner,
  exp.table_name,
  ROUND((used_space-expected_space)/power(1024,2)) estimated_savings_mb,
  used_space,
  expected_space,
  ROUND((1-expected_space/used_space)*100) estimated_savings_pct
FROM
  (SELECT owner,
    table_name,
    num_rows*avg_row_len expected_space
  FROM dba_tables t
  WHERE (owner,table_name) IN
    (SELECT * FROM table_subset s)
  AND num_rows    IS NOT NULL
  AND avg_row_len IS NOT NULL
  ) exp,
  (SELECT owner,
    segment_name,
    SUM(bytes) used_space
  FROM dba_segments
  WHERE (owner,segment_name) IN
    (SELECT * FROM table_subset s)
  GROUP BY owner,
    segment_name
  HAVING SUM(bytes)>0
  ) used
WHERE exp.owner   =used.owner
AND exp.table_name=used.segment_name
ORDER BY estimated_savings_mb DESC

 

Wie wir sehen, hat in unserem Beispiel die Tabelle SOE.ORDER_ITEMS ein Einsparungspotenzial von ca. 1.1GB. Sie belegt aktuell 1.4 GB Platz, obwohl sie gemäß ihrer Statistiken nur 209 MB belegen müsste. Wir würden sie also als einen geeigneten Kandidaten für eine Online-Shrinkoperation ansehen:

ALTER TABLE SOE.ORDER_ITEMS ENABLE ROW MOVEMENT;
ALTER TABLE SOE.ORDER_ITEMS SHRINK SPACE CASCADE;

Eine erneute Auswertung zeigt, dass die Tabelle, wie erwartet, ihren ungenutzten Platz freigegeben hat. Im Tablespace stehen nun 1.1GB mehr Platz zur freien Verfügung.

 

Am Beginn des SELECT-Statements finden Sie die Inline-View

WITH table_subset AS
  ( SELECT owner,table_name
    FROM dba_tables
    WHERE owner='SOE'
  )

Hierüber können Sie einstellen, welche Tabellen in die Betrachtung einbezogen werden sollen. Wir beschränken uns hier zum Beispiel auf das Schema SOE. Eine Auswertung über alle Tabellen einer Datenbank dürfte meist zu lange dauern und auch nur selten erforderlich sein. So ist zum Beispiel das Shrinken von SYS-Objekten ohnehin nicht empfohlen und in vielen Fällen auch nicht möglich.

Fazit

Nach größeren Löschoperationen entstehen Lücken in Datenblöcken, die sich negativ auf Performance und Platzbelegung auswirken. Werden sie in der Tabelle nicht zeitnah durch neue Daten aufgefüllt, empfiehlt sich ein Online-Shrink der betroffenen Tabellen. Ein Ansatz zur Abschätzung stark fragmentierter Tabellen wurde in diesem DBA-Tipp vorgestellt.


Newsletter-Archiv

Das Löschen einer größeren Menge von Zeilen aus einer Tabelle führt in aller Regel dazu, dass die Datenblöcke der Tabelle stark fragmentiert werden, es dann also viele Blöcke gibt, die nur wenige Daten enthalten. Diese Fragmentierung hat zwei wesentliche Nachteile:

  • Speicherplatz wird nachhaltig verschwendet, wenn die Lücken nicht im Laufe der Zeit wieder durch neue Zeilen gefüllt werden. Da der Freiplatz fest von einer bestimmten Tabelle allokiert ist, steht er auch keinen anderen Objekten in der Datenbank zur Verfügung.
  • Auch wenn der Platz nach und nach wiederverwendet wird, wirkt sich diese Fragmentierung bis dahin negativ auf die Performance aus. Der Buffer Cache der Datenbank, eine der wichtigsten Komponenten im Bezug auf Datenbankperformance, arbeitet rein blockbasiert. Folglich verschwenden Datenblöcke einer Tabelle Platz im Buffer Cache, wenn sie fragmentiert sind, da auch der leere Anteil im Block auf den Buffer Cache angerechnet wird. Die „Nutzdatenrate“ ist damit schlechter als bei unfragmentierten Blöcken, die Performance sinkt durch eine geringere Buffer Cache Hit Ratio.

Es kann also nach größeren Löschoperationen interessant sein, welche Tabellen mittels

alter table … shrink space cascade;

geshrinkt werden können, um zum einen unbelegten Platz freizugeben und zum anderen die Buffer Cache Effizienz hoch zu halten. Dafür kann man sich zwei Tabellenstatistiken zu Nutze machen – die Zeilenzahl und die durchschnittliche Zeilenlänge – und diese mit der tatsächlichen Platzbelegung in Bezug setzen. Es ist zu beachten, dass es sich dabei nicht um exakte Werte handelt. Wenn die Statistiken jedoch aktuell sind, stellen sie für diesen Zweck recht zuverlässige Schätzungen dar. Grundsätzlich kann man davon ausgehen, dass Tabellen, in denen große Datenmengen gelöscht wurden, auch vom nächtlichen Autotask „auto optimizer stats collection“ erkannt und analysiert wurden. Andernfalls rechnet man mit geeigneten Prozeduren des dbms_stats-Package die Statistiken einfach selbst neu.

Zur Abschätzung des Platzes, den wir durch das Shrinken voraussichtlich einsparen werden können, stellen wir nun einen Vergleich zwischen dem erwarteten Platzbedarf (Zeilenzahl * durchschnittliche Zeilenlänge) und dem tatsächlich belegten Platz (Summe der Segmentgrößen) an:

WITH table_subset AS
  ( SELECT owner,table_name
    FROM dba_tables
    WHERE owner='SOE'
  )
SELECT exp.owner,
  exp.table_name,
  ROUND((used_space-expected_space)/power(1024,2)) estimated_savings_mb,
  used_space,
  expected_space,
  ROUND((1-expected_space/used_space)*100) estimated_savings_pct
FROM
  (SELECT owner,
    table_name,
    num_rows*avg_row_len expected_space
  FROM dba_tables t
  WHERE (owner,table_name) IN
    (SELECT * FROM table_subset s)
  AND num_rows    IS NOT NULL
  AND avg_row_len IS NOT NULL
  ) exp,
  (SELECT owner,
    segment_name,
    SUM(bytes) used_space
  FROM dba_segments
  WHERE (owner,segment_name) IN
    (SELECT * FROM table_subset s)
  GROUP BY owner,
    segment_name
  HAVING SUM(bytes)>0
  ) used
WHERE exp.owner   =used.owner
AND exp.table_name=used.segment_name
ORDER BY estimated_savings_mb DESC

 

Wie wir sehen, hat in unserem Beispiel die Tabelle SOE.ORDER_ITEMS ein Einsparungspotenzial von ca. 1.1GB. Sie belegt aktuell 1.4 GB Platz, obwohl sie gemäß ihrer Statistiken nur 209 MB belegen müsste. Wir würden sie also als einen geeigneten Kandidaten für eine Online-Shrinkoperation ansehen:

ALTER TABLE SOE.ORDER_ITEMS ENABLE ROW MOVEMENT;
ALTER TABLE SOE.ORDER_ITEMS SHRINK SPACE CASCADE;

Eine erneute Auswertung zeigt, dass die Tabelle, wie erwartet, ihren ungenutzten Platz freigegeben hat. Im Tablespace stehen nun 1.1GB mehr Platz zur freien Verfügung.

 

Am Beginn des SELECT-Statements finden Sie die Inline-View

WITH table_subset AS
  ( SELECT owner,table_name
    FROM dba_tables
    WHERE owner='SOE'
  )

Hierüber können Sie einstellen, welche Tabellen in die Betrachtung einbezogen werden sollen. Wir beschränken uns hier zum Beispiel auf das Schema SOE. Eine Auswertung über alle Tabellen einer Datenbank dürfte meist zu lange dauern und auch nur selten erforderlich sein. So ist zum Beispiel das Shrinken von SYS-Objekten ohnehin nicht empfohlen und in vielen Fällen auch nicht möglich.

Fazit

Nach größeren Löschoperationen entstehen Lücken in Datenblöcken, die sich negativ auf Performance und Platzbelegung auswirken. Werden sie in der Tabelle nicht zeitnah durch neue Daten aufgefüllt, empfiehlt sich ein Online-Shrink der betroffenen Tabellen. Ein Ansatz zur Abschätzung stark fragmentierter Tabellen wurde in diesem DBA-Tipp vorgestellt.