WORKAREA_SIZE_POLICY - Teil 5

Nach dem wilden Experimentieren und freien Assoziieren jetzt zurück zu den Autoritäten: was sagt Jonathan Lewis in seinem Cost-Based Oracle Fundamentals-Buch zum Thema der Sortierungen? Das passende Kapitel ist Nr 13: Sorting and Merge Joins. Hier eine kurze Liste dort erläuterter Details (ohne Anspruch auf Vollständigkeit):

• beim Sortieren gibt es drei unterschiedlich effektive Varianten:
• optimal: erfolgt vollständig im Arbeitsspeicher. Dabei erfolgt die Allocation des Speichers nach Bedarf (es wird also nicht initial der komplette Speicher verwendet, der duch die SORT_AREA_SIZE definiert ist). Für 6 MB Daten benötigt Lewis in seinem Test einen Speicher von 25,5 MB, um die Sortierung optimal zu halten; ob dieses Verhältnis der Normalfall ist, wäre zu testen.
• onepass: erfolgt, wenn die Daten nicht komplett im Speicher sortiert werden können, aber es möglich ist, kleinere Teilmengen zu sortieren, die Ergebnisse auf der Platte abzulegen, und anschließend Stücke von allen vorsortierten Mengen im Speicher zusammenzuführen
• multipass: verhält sich wie die onepass-Variante, aber es gibt zu viele vorsortierte Mengen, so dass sie nicht in einem Schritt zusammengeführt werden können. Stattdessen werden größere Zwischenergebnisse zusammengeführt wieder auf der Platte abgelegt und diese dann wieder kombiniert.
• zur Analyse der Effekte nutzt JL die gleichen Werkzeuge, die ich auch bei meinen Tests eingesetzt hatte (wobei er aber kompetenter wirkt...): v$sesstat (oder auch v$mystat für die eigene Session), sowie die Trace Events 10032 und 10033 (außerdem schaut er sich auch noch Block Dumps der temp files an).
• Zum Sortieralgorithmus entwickelt Lewis die Theorie, dass intern ein (balancierter) binary insertion tree im Spiel sein könnte, und liefert Indizien, die für diese Annahme sprechen; seit 10.2 könnte aber auch ein neuer Mechanismus im Spiel sein, so dass ich mir die Details spare.
• Sortierungen mit größerer Speichernutzung benötigen auch größere CPU-Ressourcen, so dass für Systeme, in denen I/O ein kleineres Problem als die CPU-Nutzung ist, auch eine Verkleinerung der Speichernutzung zu einer Beschleunigung führen kann.
• Kapitel 13 ist ziemlich umfangreich, so dass ich zu den Erläuterungen zur WORKAREA_SIZE_POLICY ein andermal kommen werde.

Noch ein abschließender Hinweis. Das CBO-Buch stammt von 2006 und behandelt 10.1 (und in wenigen Fällen 10.2) - manche Beobachtungen sind wahrscheinlich für 11.2 nicht mehr zutreffend. Die grundlegenden Prinzipien dürften sich aber nicht verändert haben.