Blog 
Automatisches Segment Space Management

Automatisches Segment Space Management (ASSM) hilft Ihnen, Ihre Daten auf die Daten Blocks zu verteilen. Es ist nicht mehr länger notwendig, sich um den Parameter PCTUSED zu kümmern, der in früheren Jahren für den Platz innerhalb eines Oracle Blocks verantwortlich war. Damals wurde ein Block gefüllt bis er die Hochwassermarke PCTFREE (Standard 10%) erreicht hat. An diesem Punkt wurde der Block von der Liste der freien Blocks entfernt und bevor die Niedrigwassermarke PCTUSED (Standard 40%) nicht wieder erreicht war, wurden in dem Block keine Daten mehr gespeichert. Also nur wenn zuerst eine riesige Datenmenge aus der ersten Tabelle gelöscht würde, würde der Block wieder mit neuen Daten gefüllt.

Mit ASSM kümmert sich ein bitmap darum, die Blocks aufzufüllen. Wenn also eine Zeile aus einem Block gelöscht werden soll, könnte die nächste, neue Zeile wieder eingefügt werden und helfen, den freien Platz eines Blocks klein zu halten und Fragmentierungen zu vermeiden. Aber stimmt das?

Beispiel

Die Tabelle, die wir benutzen, heißt Orders und enthält – kein Wunder – Informationen zu Aufträgen, die über ein paar Jahre verarbeitet wurden. Aufträge werden jeden Tag hinzugefügt (wir sind ein Internet Unternehmen) und über die letzten Jahre ist die Tabelle gewachsen und gewachsen. Mit dem alten Befehl „ANALYZE“ können wir die aktuelle Blocknutzung der Tabelle prüfen:

ANALYZE TABLE orders COMPUTE STATISTICS;

SELECT table_name, num_rows, blocks, 
       empty_blocks, avg_space, avg_row_len 
  FROM user_tables 
 WHERE table_name = 'ORDERS';

TABLE_NAME   NUM_ROWS    BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
----------- --------- --------- ------------ ---------- -----------
ORDERS        4000000    129309          611        861         231

Dies ist die Größe und das Layout des Order Segments vor jeglicher Art von Reorganisation Ende November 2014. Gerade vor der Weihnachtssaison haben wir bemerkt, dass wir mal alte Daten loswerden müssten, da deren Größe ungefähr 1 GB ist. Okay, das ist nicht wirklich riesig, aber für dieses Beispiel sollte es ausreichen.

Also werden wir die Tabelle archivieren und alle Aufträge gelöscht, die vor dem 1. Januar 2014 eingegangen sind oder den Auftragsstatus „G“, also abgeschlossen, haben.

DELETE FROM orders
 WHERE orderdate < to_date('01.01.2014','DD.MM.YYYY')
   AND orderstatus = 'G';

COMMIT;

Wenn man sich die Platznutzung der Tabelle mit ANALYZE TABLE ansieht, erkennt man, dass die Gesamtzahl an Blocks sich, wie erwartet, nicht geändert hat. Aber der durchschnittliche Platz (AVG_SPACE) ist jetzt ungefähr 4 KB, d.h. durchschnittlich die Hälfte jedes Blocks ist leer. Es könnte also ein paar Blocks geben, die immer noch bis zum PCTFREE gefüllt sind (so wie die Blocks mit den Daten von 2014) und ein paar Blocks die fast leer sind (so wie die von 2010, wo wahrscheinlich alle Aufträge abgeschlossen sind).

TABLE_NAME   NUM_ROWS   BLOCKS EMPTY_BLOCKS   AVG_SPACE AVG_ROW_LEN
----------- ---------- -------- ------------ ---------- -----------
ORDERS         2166150   129309          611       4165         229

Neue Aufträge werden kontinuierlich in das System eingetragen und Ende Januar 2015 – Verzeihung, wir sind der aktuellen Zeit ein wenig voraus – werden wir die Platznutzung wieder überprüfen (ANALYZE TABLE):

TABLE_NAME    NUM_ROWS   BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
----------- ---------- -------- ------------ ---------- -----------
ORDERS         2336150   129309          611       3867         229

Ungefähr 200 000 Aufträge wurden seit November 2014 eingetragen, aber die Größe des Segments hat sich nicht erhöht (immer noch 129 309 Blocks). Allerdings hat sich der durchschnittliche Platz um 10 % reduziert.

Das ist gut zu wissen und war zu erwarten, da wir die Platznutzung in diesem Segment verringern wollten. Aber hat das einen Einfluss auf die Performance der Anwendung? – JA!

Machen wir drei simple Abfragen:

Abfrage 1:

SELECT *
  FROM orders
 WHERE orderdate >= to_date('12.10.2014','DD.MM.YYYY')
   AND orderdate < to_date('13.10.2014','DD.MM.YYYY');
   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      201      0.00       0.00         10        502          0        2996
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      203      0.00       0.01         10        503          0        2996

Abfrage 2:

SELECT *
  FROM orders
 WHERE orderdate >= to_date('14.12.2014','DD.MM.YYYY')
   AND orderdate < to_date('15.12.2014','DD.MM.YYYY');
   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ---------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           
Fetch      185      0.00       0.01        155        516          0        2760
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      187      0.00       0.01        155        517          0        2760

Abfrage 3:

SELECT *
  FROM orders
 WHERE orderdate >= to_date('14.01.2015','DD.MM.YYYY')
   AND orderdate < to_date('15.01.2015','DD.MM.YYYY');
   
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      202      0.00       0.00          0        563          0        3002
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      204      0.00       0.00          0        563          0        3002

Es gibt einen Index auf dem Orderdate, also erwarten wir, dass mehrere Blocks durch diesen Index gelesen werden.

Erklärung

Wenn Sie insbesondere Abfrage 1 mit Abfrage 3 vergleichen, sehen Sie eine Erhöhung von mehr als 10 % bei der Anzahl der Blocks (von 503 auf 563), während die Anzahl der Zeilen fast die gleiche ist.

Die erste Abfrage läuft gegen Aufträge, die vor unserer Archivierung eingegeben wurden und ohne den Einfluss des Löschens. Die zweite und dritte Abfrage läuft gegen Aufträge, die nach der Reorganisation eingegeben wurden.

Mit den Aufträgen von Januar 2014 bis November 2014 sind die Blocks zu 90 % der aktuellen Daten gefüllt (PCTFREE 10%). Aber angefangen im Dezember 2014 werden die Blocks wiederverwendet, sodass es eine größere Wahrscheinlichkeit gibt, dass alte Daten (von 2013, 2012 oder noch älter) im gleichen Block sind wie die brandneuen, „heißen“ Daten, die im Tagesgeschäft verwendet werden. Nach einer Weile oder nachdem alle „alten“ Blocks wieder zu 90% gefüllt sind, wird die Performance wieder steigen, da dann neue, leere Blocks für aktuelle Daten bereitgestellt werden.

Fazit

Dieses Beispiel zeigt, dass es keine Pauschallösung gibt, selbst mit neuen Oracle Features. Wenn man Tabellen wie die im Beispiel hat, gibt es die Möglichkeit von Daten-Defragmentierung durch die Wiederverwendung von Blöcken. In dem Fall könnte es sinnvoll sein, die Tabelle gleich nach dem Löschen der alten Daten zu reorganisieren.
Ich würde empfehlen, diese Art von Tabellen in einem eigenen Tablespace zu speichern. Für die Reorganisation kann man sie einfach in einen neuen Tablespace verschieben und den alten löschen. Ja, man braucht zeitweise den doppelten Platz für die Tabelle, aber das ist der sauberste Weg.

Dieses Beispiel ist natürlich nur gültig für gewöhnliche Tabellen, nicht für partitionierte – solange sie basierend auf dem Auftragsdatum partitioniert sind.

Eine Frage an die Leserschaft:
Gibt es einen Weg Informationen über avg_space, avg_row_lenght und chain_cnt zu bekommen ohne ANALYZE TABLE zu benutzen? Ideen und natürlich Kommentare zum Blog sind immer gern gesehen!