Sortieren mit begrenzten Mitteln

03.
Mai
2006
Veröffentlicht von: Marco Patzwahl

Was passiert eigentlich bei einem Sortiervorgang in Ihrer Datenbank?

Was passiert eigentlich bei einem Sortiervorgang in Ihrer Datenbank? 

Das hängt von den Einstellungen ab, welche Sie in der Initialisierungsdatei Ihrer Datenbank vorgenommen haben. Bei manueller Work_Area_Size_Policy lässt sich die Allokierung und Verwaltung von temporärem Sortierspeicher über die Parameter sort_area_size u. sort_area_retained_size steuern.

sort_area_size

Obergrenze für den RAM-Verbrauch eines Sortiervorgangs. Darüber hinausgehender Sortierspeicherbedarf wird durch Allokierung von Segmenten im temporären Tablespace gedeckt, die Daten werden also auf eine Platte geschrieben.

sort_area_retained_size

Legt fest, wie viel Arbeitsspeicher nach der Beendigung des eigentlichen Sortiervorgangs für die Speicherung des Sortierergebnisses maximal verwendet werden soll. Der Sortierbereich wird also nach der Sortierung geshrinkt. Sollte der auf die durch diesen Parameter angegebene Größe geshrinkte Sortierbereich für das komplette Sortierergebnis zu klein sein, wird der Rest in den temporären Tablespace und damit auf die Platte geschrieben.

Wie funktioniert das?

Sortieroperationen in der Datenbank laufen in zwei Phasen ab.

In Phase I werden die zuvor selektierten Daten unter Anwendung eines im Datenbankserver implementierten Sortieralgorithmus in die gewünschte Reihenfolge gebracht. Diese Phase benötigt je nach Datenmenge viel Zeit und Platz. Der benötigte Sortierspeicherplatz kann die zu sortierende Datenmenge dabei überschreiten. Wer mehr Zeit hat, kommt mit weniger Platz hin und umgekehrt. Weniger Platz heißt weniger Arbeitsspeicherverbrauch zum Preis von mehr Auslagerung auf Platte durch Zwischenlagerung der Daten in temporären Tablespace-Segmenten mit relativ langsamen physikalischen Schreib- und Leseoperationen.

In Phase II werden nun die sortierten Daten an den Client ausgeliefert. Dieser nimmt die Ergebnissätze entgegen und verarbeitet sie, die Verweilzeit der sortierten Ergebnisse auf dem Server hängt in der Phase II nun auch von der Arbeitsgeschwindigkeit des Clients ab.

Laufen mehrere Sortierprozesse parallel ab, so ergibt sich der zu einem konkreten Zeitpunkt benötigte Gesamtarbeitsspeicherbedarf aus der Summe der Speichernutzung der Prozesse in Phase I plus der Summe der Speicherbelegung der Prozesse in Phase II. Um den Gesamtbedarf zu reduzieren, lässt sich die Speichernutzung für die Phasen einzeln einstellen: Phase I über die sort_area_size, Phase II über die sort_area_retained size.

Beispiel:

Beginnen wir mit einer kleinen sort_area_size ohne sort_area_retained_size. Mit diesen Werten muss die Datenbank für den Sortiervorgang auf den temporären Tablespace zurückgreifen. Das zeigt sich in den physikalischen Schreib- und Lesezugriffen:

sort_area_size    integer 65536
sort_area_retained_size    integer 0
SQL> select * from t order by 1,2,3,4;

Statistics
---------------------------------------------------------
183         recursive calls
176         db block gets
714         consistent gets
1957        physical reads
0           redo size
3726521     bytes sent via SQL*Net to client
37429       bytes received via SQL*Net from client
3362        SQL*Net roundtrips to/from client
4           sorts (memory)
1           sorts (disk)
50402       rows processed

NAME                                          DIFF
--------------------------------------------  --------------
physical reads direct temporary tablespace    1957
physical writes direct temporary tablespace   1957

Im nächsten Testschritt vergrößern wir die sort_area_size ausreichend, jetzt findet die Sortierung komplett im Arbeitsspeicher statt:

SQL> alter session set sort_area_size=100000000;
SQL> select * from t order by 1,2,3,4;

Statistics
----------------------------------------------------------
575         recursive calls
0           db block gets
799         consistent gets
4           physical reads
0           redo size
3726521     bytes sent via SQL*Net to client
37429       bytes received via SQL*Net from client
3362        SQL*Net roundtrips to/from client
21          sorts (memory)
0           sorts (disk)
50402       rows processed

NAME                                          DIFF
--------------------------------------------  --------
physical reads direct temporary tablespace    0
physical writes direct temporary tablespace   0

Nun wird zusätzlich eine allerdings noch etwas zu kleine sort_area_retained_size festgelegt. Die Sortierung erfolgt nach wie vor komplett im Hauptspeicher, das Ergebnis muss jedoch temporär ausgelagert werden:

SQL> alter session set sort_area_retained_size=6125000;
SQL> select * from t order by 1,2,3,4;

Statistics
----------------------------------------------------------
7           recursive calls
2           db block gets
697         consistent gets
699         physical reads
0           redo size
3726521     bytes sent via SQL*Net to client
37429       bytes received via SQL*Net from client
3362        SQL*Net roundtrips to/from client
1           sorts (memory)
0           sorts (disk)
50402       rows processed

NAME                                          DIFF
--------------------------------------------  -------
physical reads direct temporary tablespace    699
physical writes direct temporary tablespace   699

Wir vergrößern die sort_area_retained_size, nun muss das Sortierergebnis nicht mehr ausgelagert werden:

SQL> alter session set sort_area_retained_size=6500000;
SQL> select * from t order by 1,2,3,4;

Statistics
----------------------------------------------------------
0           recursive calls
0           db block gets
697         consistent gets
0           physical reads
0           redo size
3726521     bytes sent via SQL*Net to client
37429       bytes received via SQL*Net from client
3362        SQL*Net roundtrips to/from client
1           sorts (memory)
0           sorts (disk)
50402       rows processed

NAME                                          DIFF
--------------------------------------------  -------
physical reads direct temporary tablespace    0
physical writes direct temporary tablespace   0

Fazit...

Die Werte der beschriebenen Parameter können erheblichen Einfluss auf die Performance Ihrer Datenbank nehmen. Die Optimierung ist also die Mühe wert. Ab Oracle 9i Release 1 können Sie über den Parameter workarea_size_policy eine automatische dynamische lastabhängige Anpassung dieser Parameter durch den Oracle-Server aktivieren. Ob das die richtige Lösung ist, hängt von der Art und Anzahl der Zugriffe auf Ihre konkrete Datenbank ab. Wenn Sie sich nicht ganz sicher sind oder Hilfe benötigen oder einfach noch Fragen haben, wenden Sie sich bitte wie immer vertrauensvoll an uns.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.