News zu Oracle

Home → Oracle → DBA Tipp: Tablespacefreiplatz und Buffer Cache Hit Ratio erhöhen durch Defragmentierung von Tabellen

DBA Tipp: Tablespacefreiplatz und Buffer Cache Hit Ratio erhöhen durch Defragmentierung von Tabellen

Verfasst von Thilo Solbrig, Oracle Certified Master
04 Juni 2025
09:40
Datenbank, DBA Tipp, Oracle, Performance Tuning, Praxistipp

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 wird in diesem DBA-Tipp vorgestellt.

Warum überhaupt defragmentieren?

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. Das heißt es gibt dann also viele Blöcke, die nur wenige Daten enthalten. Diese Fragmentierung hat zwei wesentliche Nachteile:

Verschwendung von Speicherplatz, 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 kannst du dir 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 kannst du 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 rechnest du mit geeigneten Prozeduren des dbms_stats-Package die Statistiken einfach selbst neu.

Vergleich erwarteter Platzbedarf und tatsächlich belegter Platz

Zur Abschätzung des Platzes, den wir durch das Shrinken voraussichtlich einsparen, 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 können sie also als einen geeigneten Kandidaten für eine Online-Shrink-Operation 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 findest du die Inline-View

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

Hierüber kannst du 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

Größere Löschoperationen führen häufig zu Fragmentierung in Oracle-Tabellen – mit negativen Auswirkungen auf Speicherplatz und Performance. Der vorgestellte Ansatz ermöglicht es dir als DBA, stark fragmentierte Tabellen systematisch zu identifizieren und gezielt per Online-Shrink zu optimieren. So lassen sich ungenutzte Reserven im Tablespace freigeben und die Effizienz des Buffer Caches nachhaltig verbessern.

Hier findest du weitere Infos rund um Oracle aus unserem News & Insights Bereich.

Share this article

DBA Tipp: Ta­b­le­space­frei­platz und Buffer Cache Hit Ratio erhöhen durch De­frag­men­tie­rung von Tabellen

Warum überhaupt defragmentieren?

Es kann also nach größeren Lösch­ope­ra­tio­nen in­ter­es­sant sein, welche Tabellen mittels:

Vergleich er­war­te­ter Platz­be­darf und tat­säch­lich belegter Platz

Fazit

DBA Tipp: Tablespacefreiplatz und Buffer Cache Hit Ratio erhöhen durch Defragmentierung von Tabellen

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

Vergleich erwarteter Platzbedarf und tatsächlich belegter Platz