Partitionierung aus Designsicht – Eine praktische Anleitung
Es war Anfang der 90er Jahre und ich arbeitete für die Informatik eines internationalen Konzerns, als unerwartet unsere Anwender anriefen und fragten, ob wir denn einen Serverabsturz hätten. Eine kurze Analyse ergab, dass ein neu errichtetes Datawarehouse der Verursacher der Probleme war. Das Datawarehouse wurde von einer anderen Abteilung in einem Nachbarort betrieben. Wir ließen unseren Kollegen von der Datawarehouse-Entwicklung die folgende Nachricht zukommen: "Was immer ihr macht, bitte hört sofort damit auf."
Ich bekam den Auftrag, zu untersuchen, was schief gegangen war. Wie ich herausfand, hatten die Kollegen gerade eine Umsatzstatistik erzeugen wollen. Eines der Suchkriterien war das aktuelle Jahr. Unglücklicherweise gab es auf der Spalte Jahr einen Index. Der damals aktuelle Rule-Based Optimizer sah die Verwendung des Index als die beste Option an (eindeutiger Vergleich auf einspaltigen Index, Rang 9). Daher wurden über eine Million Sätze der Umsatzstatistik Stück für Stück gelesen. Viel besser wäre ein Full Table Scan gewesen. Dieser kam aber für den Rule-Based Optimizer gar nicht in Frage (Full Table Scan, Rang 15).
Mit dem Aufkommen der Datawarehouse-Anwendungen hatte sich der Charakter der Datenbankverwendung grundlegend geändert. Es stellten sich neue Fragen. Zum Beispiel: "Wie kann man große Datenmengen effizient verwalten?" oder "Wie kann man mit einem Full Table Scan gezielt Daten lesen?". Sehr bald wurde auch der Rule-Based Optimizer ersetzt, um den neuen Anforderungen gerecht zu werden. Neue Strategien der Datenverarbeitung mussten entwickelt werden.
Eine der wichtigsten Möglichkeiten, große Datenmengen effizient zu verarbeiten, ist die Partitionierung. Wir werden im Folgenden die wichtigsten Partitionierungsarten behandeln. Dabei ist es nicht das Ziel dieses Artikels, alle Möglichkeiten der Partitionierung im Detail zu demonstrieren, sondern anhand von praktischen Beispielen typische Lösungsmuster aufzuzeigen und so die Phantasie von Datenbank-Designern in Richtung einer eigenen Lösung anzuregen.
Partitionierung: eine Definition
In diesem Artikel geht es immer um horizontale Partitionierung [1]. Eine Partitionierung wird im Wesentlichen durch drei Elemente bestimmt:
1. Eine Aufteilung der Daten
Die Daten werden auf Partitionen verteilt. Im Wesentlichen ist eine Partition nichts weiter als eine Tabelle. Nur eben, dass sie von der Datenbank anders interpretiert wird.
2. Eine Regel, welche die Aufteilung organisiert
Es gibt die unterschiedlichsten Möglichkeiten, wie Daten in Partitionen verteilt werden. Es gibt immer einen Schlüsselwert (Partition Key), der mittels der Regel darüber entscheidet, in welcher Partition ein Datensatz gespeichert wird. Die Regel kann eine Einteilung in sortierte Bereiche sein (Range Partitioning) oder eine mathematisch gleichmäßig verteilende Funktion (Hash Partitioning), eine Zuordnung von Einzelwerten (List Partitioning) oder analog einer anderen Tabelle (Reference Partitioning).
Zusätzlich diesen grundlegenden Möglichkeiten gibt es dann noch Varianten wie Kombinationen (Composite Partitioning) oder Verwaltungsvereinfachungen (Interval Partitioning).
3. Ein Verhalten der Datenbank-Engine, welche die Regel reflektiert
Je nach Art der Partitionierung reagiert die Datenbank unterschiedlich auf die einzelnen Partitionierungsvarianten. Es gibt jedoch auch Verhaltensformen, die für alle Partitionierungsarten gleich sind. Ein Beispiel dafür ist das Partition Pruning, bei dem anhand von Suchbedingungen eine Abfrage errechnet wird, welche Partitionen die Abfrage absuchen muss. Eine weitere Art generellen Verhaltens im Bezug auf die Partitionierung ist beispielsweise, dass bei grundlegenden Manipulationen der Tabellendefinition alle Partitionen als Einheit betrachtet werden. Wenn man zum Beispiel eine partitionierte Tabelle löscht (drop), werden in Folge natürlich alle Partitionen ebenfalls gelöscht.
Gründe für Partitionierung (Teile und herrsche)
Grundsätzlich nennen die Unterlagen drei Gründe für Partitionierung [2]:
1. Beschleunigung von Zugriffen
Dies ist der ursprünglichste Grund für die Partitionierung. Über das Partition Pruning ist es möglich, gezielt nach Daten einer Partition zu suchen und gleichzeitig den effizienteren Full Table Scan (hier eigentlich ein Full Partition Scan) zu verwenden.
Beim Full Table Scan werden alle Blöcke einer Tabelle der Reihe nach gelesen, so wie sie gespeichert sind. Dadurch ist es möglich, mehrere Blöcke auf einmal zu lesen. In der Regel sind es 128 Blöcke, die auf einmal gelesen werden.
Wie groß ist jetzt der Unterschied? Das hängt von vielen Details ab und schwankt stark. Als Orientierung finden Sie unten ein konkretes Beispiel. Es wurden jeweils 100 Millionen Sätze gelesen.
Wie Sie sehen, ist der Multi Block Read ca. 40-mal schneller. Das Ergebnis variiert sehr stark je nach Aufgabenstellung, jedoch ist der Multi Block Read eindeutig effizienter, wenn große Datenmengen gelesen werden sollen. Eine Faustregel besagt, dass ein Indexzugriff sich nur lohnt, wenn 5 Prozent oder weniger des Datensätze einer Tabelle gelesen werden sollen.
Das nächste Listing zeigt dasselbe SQL mit über Hint erzwungenem Single Block Read (Ein Index Scan ist praktisch immer ein Single Block Read. Es gibt Variationen davon, ohne dass dies etwas Wesentliches ändert. Das Listing unten ist aber ein ganz klassischer Fall.)
2. Verteilen von Aktivität
Diese Art der Partitionierung zielt auf das Beseitigen von Engpässen ab. Ein einfaches Beispiel bietet das Einfügen von Datensätzen. Grundsätzlich wird eine Tabelle in Oracle beginnend mit dem ersten freien Datenblock beschrieben. Wenn nun zum Beispiel mit hoher Frequenz Datensätze geschrieben werden, kann sich die Aktivität auf einen Datenblock konzentrieren. Die Strukturen dieses Blocks (z. B. Transaction Slots, Block Dictionary) können dann überlastet werden. Es kann zu Verklemmungen und Warteereignissen kommen. Eine Hash-Partitionierung (s. entsprechendes Kapitel unten) kann das verhindern.
3. Organisation des Lebenszyklus von Daten
Daten können im Rahmen ihrer Existenz einen Lebenszyklus durchlaufen. Dieser kann je nach Geschäftsprozessen sehr unterschiedlich sein. Ein einfaches Beispiel wären Phasen eines Datensatzes wie "offen", "verarbeitet", "historisch" und "zu löschen".
Wenn man solche Phasen über jeweils eigene Partitionen abbildet, kann man beispielsweise historische Daten auf ein langsameres, aber billigeres Speichermedium auslagern und diese Daten besonders stark komprimieren. Dabei nutzt man die Fähigkeit der Datenbank aus, einzelne Partitionen unterschiedlich speichern zu können. Veraltete Partitionen könnte man einfach über ein "drop partition" entfernen: eine sehr effiziente Art, Daten zu löschen. Löschen ist grundsätzlich die aufwändigste Art, Daten zu ändern. Ein schönes Beispiel dazu findet man bei Dani Schnider [3].
Partitionierungsarten und typische Anwendungen
Range Partitioning
Range Partitioning ist die älteste Art der Partitionierung und die am leichtesten verständliche. Meist wird die Range Partitionierung mit einer Partitionierung nach Datumswerten in Verbindung gebracht. Grundsätzlich aber kann man alles Range partitionieren, was sortierbar ist. Dies können genauso gut Zahlen oder alphabetische Werte sein. Ich denke dann beispielsweise an die Türen in Ämtern, die mit den Anfangsbuchstaben der Nachnamen angeschrieben waren (z. B. A – F).
Dennoch, der Klassiker bleibt die Partitionierung nach Datum. Dies ist kein Wunder, denn in den ersten Datawarehouses waren Umsatzdaten und Statistiken die Tabellen mit den meisten Datensätzen und diese waren in der Regel datumsbezogen.
Will man beispielsweise überprüfen, ob eine Partitionierung nach Verkaufsmonat für eine Umsatztabelle sinnvoll ist, so sollte man sich die folgenden Fragen stellen:
- Welches ist der zeitliche Fokus, in dem sich die meisten meiner Abfragen bewegen?
- Für welchen zeitlichen Bereich gibt es nur noch geringes Interesse?
- Hat die überwiegende Anzahl meiner Abfragen einen zeitlichen Bezug?
- Gibt es Abfragen, die gar keinen zeitlichen Bezug haben und welche sind es?
- Wie lange muss ich meine Daten aufbewahren?
Hier ein paar beispielhafte Antworten um ein potenzielles Szenario zu entwerfen:
- Die letzten drei Monate
- Älter als 2 Jahre (Vorjahresvergleich)
- Ja
- Umsatzstatistik eines konkreten Kunden
- 10 Jahre
Anhand der obigen Antworten spräche nichts gegen eine mögliche Partitionierung nach Umsatzmonat. Die letzten drei Monate lassen sich gezielt finden, auch der Vorjahresvergleich würde gut funktionieren. Die Umsatzstatistik für einen bestimmten Kunden sucht man nicht mit einen Full Scan auf die Partition, sondern mit einem Index auf die Kundenummer. Daten, die älter als zwei Jahre sind, sollte ich so gut wie möglich komprimieren und in Erwägung ziehen, sie auf ein billigeres Speichermedium auszulagern. Partitionen die älter als 10 Jahre sind, könnte ich einfach droppen.
Natürlich ist die Entscheidungsfindung nicht immer so einfach und manchmal muss man Vor- und Nachteile gegeneinander abwägen. Jedoch gelingt es oft durch gezieltes Nachdenken, vernünftige Kompromisse zu finden.
Hash Partitioning
Die Hash-Partitionierung ist nichts weiter als eine möglichst gleichmäßige Verteilung der Datensätze auf verschiedene Töpfe (engl.: buckets). Dabei muss man sich im Grunde nur entscheiden, wie viele Töpfe man haben will und nach welcher Spalte verteilt werden soll. Der Partitionsschlüssel wird in der Regel der Primary Key oder ein Foreign Key sein. Den Rest übernimmt dann die Datenbank automatisch und verteilt die Datensätze anhand der gewählten Kriterien. Die Hash-Partitionierung hat normalerweise keinerlei Business-Bedeutung, sondern optimiert einen Ablauf. In der Regel soll durch das Verteilen von Aktivität eine Engpassbildung vermieden werden.
Hier eine unvollständige Liste von Warteereignissen, die durch Hash-Partitionierung abgemildert werden können: Global Cluster waits[4], enq: TX – Index contention, latch: cache buffers chains[5] und buffer busy waits[6].
Beispiel: Partition-Wise Join
Hash Joins können sehr groß werden und sehr speicherintensiv. Sollte das PGA Memory nicht ausreichen, muss auf den Temporary Tablespace geschrieben werden. Eine Möglichkeit, große Hash Joins kosteneffizienter durchzuführen, ist der Partition-Wise Join. Als Voraussetzung müssen die Tabellen, die verknüpft werden sollen, nach demselben Kriterium Hash partitioniert sein. Also beispielsweise die Tabellen AUFTRAG und AUFTRAGSPOSITION. In beiden Tabellen könnte sich die Spalte AUFTRAGS_NR befinden. In einem Fall als Primärschlüssel und im anderen Fall als Fremdschlüssel. Wenn man nun beide Tabellen auf der Spalte AUFTRAGS_NR mit jeweils derselben Anzahl von Partitions hash-partitioniert, würden sich je zwei Partitionen der beiden Tabellen entsprechen.
So würden sich beispielsweise in der ersten Partition der Auftragstabelle alle Datensätze befinden, die zur ersten Partition der Auftragspositionstabelle passen.
Statt die ganze Tabelle zu joinen, würde es genügen, die jeweils einander entsprechenden Partitionen zu verknüpfen.
Das spart Ressourcen, also PGA Memory und Temp Tablespace. Die Abbildungen unten dokumentieren den Verlauf. Zunächst wurde versucht, beide Tabellen vollständig miteinander zu joinen. In Abb. 5 sehen wir über den beiden Storage Full Scans (die Hardware war eine Exadata) jeweils einen Loop über alle Partitionen. Abb. 6 zeigt ein Ansteigen des Temp-Tablespace-Verbrauchs innerhalb von 40 Minuten. Danach sehen wir einen starken Abfall des reservierten Temp-Tablespace mit einer Spitze bei ca. 65 GB. An dieser Stelle ist die Verarbeitung abgestürzt, da kein weiterer Temp-Tablespace mehr reserviert werden konnte.
Abb. 7 zeigt den zweiten Versuch, diesmal mit einem Partition-Wise Join. Dabei befindet sich der Join in dem Loop über die Partitionen. Die Temp-Tablespace-Verwendung liegt in der Spitze bei vergleichsweise lächerlichen 67 Megabyte, das ist rund ein Tausendstel des vorigen Verbrauchs. Die Laufzeit beträgt 25 Minuten und der Befehl läuft diesmal zu Ende.
List Partitioning
Beim List Partitioning wird jeder Partition ein eindeutiger Wert oder eine Liste von eindeutigen Werten zugeordnet. Das List Partitioning kann als eine Spezialform des Range Partitioning verstanden werden, ist aber kürzer zu schreiben und klarer in seiner Intention. In der Praxis habe ich festgestellt, dass List Partitioning manchmal subtil bessere Pläne erzeugt als ein äquivalentes Range Partitioning.
Beispiel: Partitionieren nach Verarbeitungsstatus
Bei einem sehr großen Kunden sollen Dokumente nach verschiedenen Regeln in ein PDF-Format umgewandelt werden. Entscheidend ist dabei der Verarbeitungsstatus. Wenn neue Dokumente geladen werden, ist der Status zunächst einmal auf "nicht verarbeitet". Nachdem die Dokumente in verschiedene PDF-Formate umgewandelt worden sind, wird der Status auf "verarbeitet" gesetzt. Der Kunde hat an der aktuellen Situation zwei Kritikpunkte. Einerseits dauert das Auffinden von nicht verarbeiteten Datensätzen zu lange, andererseits leidet das Löschen von verarbeiteten Datensätzen nach dem Ende der Aufbewahrungsfrist an ständigen Locking-Problemen.
Mein Vorschlag war, die Daten in zwei Partitionen aufzuteilen. Und zwar jeweils in "verarbeitete" und "nicht verarbeitete" Datensätze. Der Fokus dieser Applikation war es, "nicht verarbeitete" Datensätze zu finden. Durch das Aufteilen in zwei Partitionen werden die Ressourcen stärker auf die Datensätze fokussiert, die bei der Verarbeitung von zentralem Interesse sind. Nehmen wir zum Beispiel den Buffer-Cache. Vor der Partitionierung waren in einem Block des Buffer-Caches verarbeitete und nicht verarbeitete Sätze enthalten. Wenn man also nach nicht verarbeiteten Sätzen sucht, so kann es sein, dass die Hälfte oder mehr der Datensätze eines Blockes im Sinne der Suche unbrauchbar sind. Werden hingegen alle verarbeiteten Datensätze sofort in eine andere Partition verschoben, so kann der freiwerdende Platz von neuen Datensätzen mit dem Status "nicht verarbeitet" beansprucht werden. Dadurch werden die 8 Kilobyte eines Datenblocks besser genutzt und die Suche wird effizienter. Ich nenne dies eine "Reinraumstrategie" in Anlehnung an den Begriff bei der Chipproduktion.
Zudem ist das Löschen von alten Datensätzen aus der verkehrsberuhigten "Verarbeitet"-Partition viel unproblematischer. Bevor man jedoch eine solche Designänderung einführen kann, muss man diese durch Tests absichern. Im Besonderen sind die folgenden Fragen zu beantworten:
1. Wird bei allen relevanten Abfragen der Status ebenfalls abgefragt?
Diese Frage muss man sich vor einer Partitionierung immer stellen. In der Tat mussten in diesem Fall einige Abfragen angepasst werden. Außerdem wird man oft einige Abfragen finden, in denen das Partitionskriterium nicht enthalten ist. Dann muss man sicherstellen, dass diese Abfragen im Sinne der Laufzeit unkritisch sind.
2. Kann das Verschieben der Datensätze in die "Verarbeitet"-Partition mit der Statusänderung gleichzeitig durchgeführt werden oder muss man den Prozess entkoppeln?
Auch die Frage nach potenziellen Nebenwirkungen gehört zu den Fragen, die man sich in Bezug auf physisches Datenbank-Design immer stellen muss. Eine Frage, die sich an dieser Stelle aufdrängt, ist, ob Datensätze, deren Statuswert geändert wird, sofort in die korrekte Partition verschoben werden sollen, oder ob dieses Verschieben entkoppelt werden soll. Technisch bedeutet es, ob bei dieser Tabelle der Wert "enable row movement" gesetzt werden soll [7]. Geschieht dies, so bedeutet das, dass der Datensatz an der alten Stelle gelöscht und an der neuen Partition wieder eingefügt wird – während der laufenden Transaktion. Die Datensätze können also immer in einer Partition genau gefunden werden. Das ist natürlich ein gewisser Overhead. Aus Erfahrung kann man sagen, dass dieser Overhead in Ordnung geht, solange ein Datensatz nach dem anderen verarbeitet wird. Haben wir es jedoch mit einem Massenupdate zu tun, in dem Sätze vielleicht auch noch parallel mit hoher Frequenz geändert werden, dann wird dieser Overhead sehr störend sein. Die Frage ist also, ob so ein Massenupdate im Zuge der regulären Verarbeitung regelmäßig vorkommt oder eine Ausnahme darstellt. Mit einer Ausnahme kann man leben und man kann das "row movement" kurzfristig disablen. Kommen Massenupdates regelmäßig vor, so muss man das row movement verbieten und verarbeitete Datensätze in einer dedizierten Aktion in die "Verarbeitet"-Partition umspeichern. Zwar wird auch dann der überwiegende Teil der Datensätze, welche verarbeitet worden sind, in der "Verarbeitet"-Partition zu finden sein, jedoch wird man die Vorteile der oben definierten Reinraumstrategie nur teilweise realisieren können.
Reference Partitioning
Wie schon im obigen Abschnitt über den Partition-Wise Join gezeigt, kann es von Vorteil sein, wenn zwei Tabellen nach dem gleichen Wert und der gleichen Methode partitioniert werden. Dazu war es jedoch früher erforderlich, dass derselbe Schlüssel in beiden Tabellen vorhanden ist. Das ist aber nicht immer der Fall und eine Tabellendefinitionsänderung ist bei einem Softwarehersteller oft schwierig zu erreichen.
Seit es Reference Partitioning gibt, können zwei Tabellen gleich partitioniert werden, auch wenn das Partitionskriterium sich in nur einer der beiden Tabellen befindet. Die Voraussetzung ist jedoch, dass sich der Zusammenhang zwischen beiden Tabellen über einen aktiven Foreign Key Constraint herstellen lässt.
Beispiel: Eingangskorb-Verarbeitung
In diesem Beispiel bespreche ich eine Art der Verarbeitung für die ich bis jetzt keine optimale Lösung kannte. Dies ist der einzige Fall in diesem Artikel, bei dem die gezeigte Lösung nicht in der Praxis ausprobiert worden ist. Es handelt sich um eine Simulation auf meiner eigenen Datenbank. Die grundsätzliche Idee ist, dass über eine Referenz-Partitionierung eine Reinraumstrategie von einer Tabelle auf eine andere Tabelle übertragen wird.
Zunächst noch einige Worte zum Thema des Eingangskorbes generell. Diese Aufgabenstellung kommt in der Praxis immer wieder vor und spiegelt ein grundsätzliches Problem der Arbeitsteilung wider. Aus einem Eingangskorb (Definition von mir) holen sich Bearbeiter ihre nächste zu bearbeitende Aufgabe. Solche Eingangskörbe können an unterschiedlichen Stellen in Applikationen vorkommen. Beispiele sind Call-Center (nächster Anruf), Kreditbeurteilung und Freigabe, Überprüfen von Versicherungsansprüchen, Auftragsfreigabe in der Produktion und etliches mehr.
Das Anspruchsvolle an der Themenstellung, die jeweils nächste zu bearbeitende Aufgabe zu finden, ist, dass mehrere Suchkriterien kombiniert werden müssen, die sich auf unterschiedlichen Tabellen befinden. Solche Suchkriterien können beispielsweise sein:
- Der Status des Auftrages.
- Die für die Abarbeitung des Auftrages benötigten Kenntnisse (z. B. durch die Zuteilung auf eine spezialisierte Bearbeitergruppe).
- Die Priorität des Auftrags.
Im Folgenden sehen Sie ein vereinfachtes Beispiel eines Eingangskorbs. Eine wichtige Vereinfachung besteht darin, dass direkt nach einer Benutzernummer gesucht wird und nicht nach einer Spezialistengruppe. Sie werden nun Schritt für Schritt durch die Lösung geführt. Beginnen wir mit der grundlegenden Datenstruktur. Im Beispiel soll es um ein Call-Center gehen. Da gibt es einerseits die Tabelle CALL, in der die Anrufe in der Reihenfolge des Eintreffens stehen. In der Tabelle ASSIGNMENT steht, welche Anrufe ein bestimmter Benutzer entgegennehmen könnte.
Listing: DDL für Eingangskorb-Beispiel
Create table call (call_id number(7) not null,
is_open char(1) not null,
priority number(2) not null,
data varchar2(600))
/
create Table Assignment (call_id number(7) not null,
user_id number(7) not null,
assignment_sequence number(1) not null,
data varchar2(200))
/
create Index assignment_idx1 on assignment(call_id); Gesucht sind die ersten elf unbeantworteten Anrufe für mich (user_id = 55), geordnet nach Priorität.
Listing: Zentrale Abfrage, die optimiert werden soll.
SELECT * FROM (
SELECT c.call_id, c.data c_data, a.data a_data
FROM call c,
assignment a
WHERE c.call_id = a.call_id
AND a.user_id = 55
AND c.is_open = 'Y'
ORDER BY priority
)
WHERE rownum <= 11
/
Die Laufzeitstatistiken zur Abfrage zeigen den tatsächlichen Aufwand, welche die Datenbank mit dieser Abfrage hatte. Bemerkenswert ist, dass aus der Tabelle ASSIGNMENT 17.220 Datensätze gelesen werden mussten, um schlussendlich elf Datensätze für das Ergebnis zu haben. Das ist doch nicht sehr effizient, oder?
Listing 5: Ergebnis des ersten Versuches, 47959 Buffer, Laufzeit 3,81 Sekunden
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:03.81 | 47959 | 43494 |
|* 1 | COUNT STOPKEY | | 1 | | 11 |00:00:03.81 | 47959 | 43494 |
| 2 | VIEW | | 1 | 17220 | 11 |00:00:03.81 | 47959 | 43494 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 17220 | 11 |00:00:03.81 | 47959 | 43494 |
| 4 | NESTED LOOPS | | 1 | 17220 | 1728 |00:00:03.80 | 47959 | 43494 |
| 5 | NESTED LOOPS | | 1 | 17220 | 1728 |00:00:03.47 | 46318 | 40417 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| ASSIGNMENT | 1 | 17220 | 17220 |00:00:03.24 | 20669 | 37907 |
|* 7 | INDEX SKIP SCAN | ASSIGNMENT_PK | 1 | 17220 | 17220 |00:00:00.47 | 4895 | 4895 |
|* 8 | INDEX RANGE SCAN | CALL_IDX1 | 17220 | 1 | 1728 |00:00:00.22 | 25649 | 2510 |
| 9 | TABLE ACCESS BY INDEX ROWID | CALL | 1728 | 1 | 1728 |00:00:00.33 | 1641 | 3077 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=11)
3 - filter(ROWNUM<=11)
7 - access("A"."USER_ID"=55)
filter("A"."USER_ID"=55)
8 - access("C"."CALL_ID"="A"."CALL_ID" AND "C"."IS_OPEN"='Y')
Im nächsten Schritt wird die uns schon bekannte Reinraumstrategie, die Partitionierung nach Status, angewandt.
Listing: Partitionierung der Tabelle CALL
Create table call_p (call_id number(7) not null,
is_open char(1) not null,
priority number(2) not null,
data varchar2(600))
PARTITION BY LIST (is_open)
(PARTITION OPEN VALUES('Y'),
PARTITION CLOSED VALUES('N')
)
/
Wie wir sehen, hat sich der Zugriff auf die Tabelle CALL in einen Full Table Scan gewandelt. Obwohl 100.000 Datensätze statt 1.728 gelesen werden, benötigt die Datenbank nur annähernd die halbe Zeit. Die Anzahl der Buffer-Zugriffe für die Abfrage sinkt von 47.959 auf 32.615.
Listing: Laufzeitstatistiken mit partitionierter Tabelle CALL
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.80 | 32615 | 32609
|* 1 | COUNT STOPKEY | | 1 | | 11 |00:00:00.80 | 32615 | 32609
| 2 | VIEW | | 1 | 17220 | 11 |00:00:00.80 | 32615 | 32609
|* 3 | SORT ORDER BY STOPKEY | | 1 | 17220 | 11 |00:00:00.80 | 32615 | 32609
|* 4 | HASH JOIN | | 1 | 17220 | 1728 |00:00:00.80 | 32615 | 32609
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| ASSIGNMENT | 1 | 17220 | 17220 |00:00:00.46 | 15859 | 15856
|* 6 | INDEX RANGE SCAN | ASSIGNMENT_IDX1 | 1 | 17220 | 17220 |00:00:00.04 | 82 | 82
| 7 | PARTITION LIST SINGLE | | 1 | 100K| 100K|00:00:00.32 | 16756 | 16753
| 8 | TABLE ACCESS FULL | CALL_P | 1 | 100K| 100K|00:00:00.32 | 16756 | 16753
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=11)
3 - filter(ROWNUM<=11)
4 - access("C"."CALL_ID"="A"."CALL_ID")
6 - access("A"."USER_ID"=55)
Im nächsten Schritt wird die Reinraumstrategie von der Tabelle CALL mittels Reference-Partitionierung übertragen.
Listing: Reference-Partitionieren von Tabelle ASSIGNMENT
create Table Assignment_p (call_id number(7) not null,
user_id number(7) not null,
assignment_sequence number(1) not null,
data varchar2(200),
CONSTRAINT pk_Assignment_p primary key
(call_id , user_id ,
assignment_sequence ),
CONSTRAINT callid_ref FOREIGN KEY (call_id)
REFERENCES call_p (call_id)
)
partition by reference (callid_ref )
pctfree 50
/
create Index assignment_p_idx1 on assignment_p(call_id) local;
Erst jetzt entfaltet die Partitionierungsstrategie ihre volle Wirkung. Die Laufzeit sinkt drastisch. Auf der positiven Seite kann man noch anmerken, dass das Laufzeitverhalten auch in Zukunft annähernd stabil bleiben sollte. Auch wenn die Anzahl der erledigten Calls anwächst, wird die Anzahl der offenen Calls in etwa konstant bleiben.
Listing: Laufzeitstatistiken mit beiden partitionierten Tabellen und Partition-Wise Join
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.05 | 26112 |
|* 1 | COUNT STOPKEY | | 1 | | 11 |00:00:00.05 | 26112 |
| 2 | VIEW | | 1 | 15272 | 11 |00:00:00.05 | 26112 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 15272 | 11 |00:00:00.05 | 26112 |
| 4 | PARTITION REFERENCE SINGLE| | 1 | 15272 | 1728 |00:00:00.05 | 26112 |
|* 5 | HASH JOIN | | 1 | 15272 | 1728 |00:00:00.05 | 26112 |
|* 6 | TABLE ACCESS FULL | ASSIGNMENT_P | 1 | 15272 | 1728 |00:00:00.02 | 9360 |
| 7 | TABLE ACCESS FULL | CALL_P | 1 | 100K| 100K|00:00:00.02 | 16751 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=11)
3 - filter(ROWNUM<=11)
5 - access("C"."CALL_ID"="A"."CALL_ID")
6 - filter("A"."USER_ID"=55)
Schlussbetrachtung
Wie man sieht, ist die Partitionierung ein sehr wirksames Mittel, um das physische Design effizienter zu gestalten.
Wie partitioniert werden soll, lässt sich nicht allgemein sagen. Deshalb finden sich in diesem Artikel vor allem praktische Beispiele, in der Hoffnung, den Blick des Lesers für die Möglichkeiten zu schärfen. Wichtig ist, dass der betriebliche Ablauf sich im Design wiederfindet. Bei vielen Designs gibt es Vor- und Nachteile. Diese gegeneinander abzuwägen ist die kreative Leistung hinter einem Datenbank-Design.
- Wikipedia: Partition (database)
- Oracle: Get the best out of Oracle Partitioning
- D. Schnider: Housekeeping in Oracle: How to Get Rid of Old Data
- R. Shamsudeen: gc buffer busy waits
- Oracle: latch: cache buffers chains
- U. Hesse: How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle
- H. K. Chitale: Enable Row Movement
