DOAG Datenbank Kolumne: Oracle-Partitioning-Option – Ein Heilmittel oder wie man es garantiert falsch macht!

In diesem Artikel für die DOAG Datenbank Kolumne habe ich Erfahrungen aus einem Kundenprojekt geschildert, die auf Oracle Partitioning gesetzt haben und dabei alles falsch gemacht haben.

Hier nochmals der Artikel zum Nachlesen.


Was ist Tabellen-Partitioning und wie funktioniert es?

Die kostenpflichtige Oracle-Partitioning-Option erweitert die Enterprise-Edition-Datenbank um die deklarative Partitionierung von Daten. Damit kann man Tabellen nach verschiedenen Kriterien in mehrere Segmente aufteilen, während die Applikation – zumindest theoretisch – unverändert auf die Tabellen zugreifen kann und trotzdem Performancevorteile erzielt.

Oracle unterstützt folgende Partitionierungsarten:

  • RANGE bzw. INTERVAL Partitioning – um Daten, basierend auf Wertebereiche, zusammen zu fassen. Beispielsweise die Verkäufe basierend auf dem Datum monatsweise zu gruppieren.
  • LIST Partitioning – um Daten basierend auf bestimmten Werten zu gruppieren, beispielsweise die Kunden pro (Bundes-)Land.
  • HASH Partitioning – die Daten werden auf Grund Ihres Wertes mittels HASH Algorithmus in Partitionen verteilt, um die parallele Verarbeitung mittels DMLs zu ermöglichen/erleichtern.
  • REFERENZ Partitioning – um Daten, basierend auf der Partition der übergeordneten Tabelle, in der untergeordneten Tabelle in die gleiche Partition abzulegen. Beispiel: Bestellkopf (Range Partitioned nach Bestelldatum) und Bestellpositionen Tabellen.

Beispiel: Bestellungen

Wenn man die Tabelle Bestellungen auf der Spalte Bestelldatum mit RANGE (oder INTERVAL) Partitionierung im monatliche Partitionen verteilt, werden vor allem jene Abfragen profitieren, die das Bestelldatum in der WHERE Clause haben. In diesem Fall erkennt der CBO (Cost Based Optimizer), dass die Daten nur in einer bestimmten Partition liegen können und greift nur noch auf diese Partition der Tabelle zu.

Ein Heilmittel, oder wie man es garantiert falsch macht!

Projektbeschreibung

In dem Kundenprojekt wird ein zentraler Data Lake in einer Oracle Datenbank aufgebaut. Das Ziel ist es, dass alle Mandanten und ihre Abteilungen einfach und schnell auf relevante Daten zugreifen können. Um den Kunden zu schützen und die Komplexität zu reduzieren wurden die Beispiele vereinfacht – im Kundenprojekt sind die Herausforderungen deutlich komplexer.

Der Data Lake wird von der zentralen IT für rund 500 Mandanten betrieben, wobei jeder Mandant nur seine eigenen Daten sehen darf. In jeder Tabelle gibt es eine Spalte MANDANT und diese muss in jedem Statement in der WHERE Clause angegeben werden. Die Daten bestehen aus verschiedenen Ereignissen, die immer einem Kunden des Mandanten zugeordnet sind. Je nach Ereignis gibt es weitere Detaildaten in entsprechenden Tabellen, die Ihrerseits wieder weitere Detail-Tabellen besitzen können. Beschränken wir uns auf folgende Tabellen:

EREIGNIS mit Spalten für:

  • MANDANT … Mandantennummer
  • GUID … Primary Key: Generierte Unique ID, damit jeder Datensatz garantiert eindeutig ist
  • EreignisDatum … Wann wurde das Ereignis erfasst (entspricht dem Erstellen des Datensatzes)
  • Wirkungsdatum … Ab wann ist das Ereignis wirksam (das kann sowohl vor als auch nach dem EreignisDatum liegen)
  • EreignisType … Art des Ereignisses
  • weiter Spalten mit Informationen zum EREIGNIS

Partitionierung der Tabelle mit LIST-Partitioning für JEDEN Werktag (Ein Werktag = Eine Partition) auf der Spalte EreignisDatum.

Das EreignisDatum kann das Bestelldatum sein und das Wirkungsdatum das Rechnungsdatum. Bei Gutschriften ist das EreignisDatum der Zeitpunkt der Gutschrift, diese kann auch rückwirkend erfolgen. Bei einem Gutschein ist das EreignisDatum der Tag des Ausstellens des Gutscheins und das Wirkungsdatum der Zeitpunkt des Einlösens.

KUNDE mit Spalten für:

  • MANDANT
  • GUID
  • Kundennummer
  • Kundentype
  • weiter Spalten mit Informationen zum KUNDEN

Partitionierung der Tabelle mit LIST-Partition auf Kundentype.

EREIGNISTYPEDETAIL mit Spalten für:

  • MANDANT
  • GUID
  • EreignisType
  • EreignisDatum
  • Kundennummer … Kundennummer (ebenfalls über alle Mandanten hinweg eindeutig)
  • weiter Spalten mit Informationen zum Ereignis

Partitionierung der Tabelle mit LIST-Partitioning auf der Spalte EreignisDatum.

Diese drei Tabellen (in der Praxis im Kundenprojekt waren es immer 4-7 Tabellen) werden in jeder Abfrage genutzt – und die Antwortzeiten sind über die letzten Monate explodiert, weil der Aufbau des Data Lakes mit dem aktuellen Kalenderjahr begonnen wurde und laufend ältere Daten nachgeladen werden (aktuell ca. 8 Jahre in die Vergangenheit).

Alle Abfragen haben folgende Spalten in der WHERE Bedingung:

  • EREIGNIS.MANDANT =
  • EREIGNIS.Wirkungsdatum between (einige Tage bis maximal 3 Monate)

Optionale Kriterien sind:

  • EREIGNIS.Kundennummer
  • EREIGNIS.EreignisType
  • KUNDE.irgendwelcheSpalten
  • EREIGNISTYPEDETAIL.irgendwelcheSpalten

Was bedeutet das für den Zugriff auf die Daten?

Keine einzige Tabelle ist nach dem MANDANTEN partitioniert, obwohl dieser in jedem Statement in der WHERE Clause vorkommt! Der Zugriff auf die Daten in der Tabelle EREIGNIS schränkt immer auf das Wirkungsdatum ein. Die Tabelle ist aber nach EreignisDatum partitioniert. Somit bleibt dem CBO nichts anderes übrig als alle Partitionen der Tabelle EREIGNIS zu lesen und auszuwerten.

Danach erfolgt ein OUTER Join auf die Tabelle EREIGNISTYPEDETAIL, weil es Ereignisse gibt, die keine Informationen in EREIGNISTYPEDETAIL gespeichert haben. Der Join erfolgt über MANDANT und Wirkungsdatum. Da die Tabelle aber nur nach EreignisDatum partitioniert ist, müssen wieder alle Partitionen – und somit alle Daten – gelesen werden.

Als letztes erfolgt wieder ein OUTER Join auf die KUNDEN, da man ja die Ereignisse ohne Kundenbeteiligung ebenfalls benötigt. Der Join nutzt die Spalten MANDANT und Kundennummer – und wieder müssen alle Partitionen gelesen werden, da die Kundentabelle nach Kundentype partitioniert ist.

Alle Abfragen brauchen inzwischen Stunden, weil alle Daten für alle Mandanten seit inzwischen über 8 Jahren ausgewertet werden. Nur weil ein Mandant wissen möchte, welche Ereignisse für seine Kunden beispielsweise in der letzten Woche geschehen sind.

Was ist die Ursache dieses Problems?

Die Ursache liegt darin, dass die Designer des Data Lakes sich zwar erkundigt haben, welche Entitäten / Informationen zu speichern sind und welche Attribute diese haben. In dem Projekt hat aber niemand nachgefragt / nachgedacht, welche Kriterien die Abfragen haben werden. Auch das Wissen über die Partitionierungsmöglichkeiten bei Oracle war anscheinend nicht vorhanden. Die Entwicklung selbst ist auch noch in mehrere Entwicklergruppen aufgeteilt. Es gibt eine Gruppe, die sich nur um das Laden der Daten in den Data Lake kümmert. Diese interessiert sich nur dafür, dass das Datenladen möglichst schnell erfolgen kann, daher sind Indizes verpönt. Mehrere andere Gruppen sind damit beschäftigt, Auswertungen entsprechend den Mandantenanforderungen zu entwickeln. Diese würden gerne Indizes anlegen, scheitern aber an der Priorisierung der „Datenladegruppe“. Die Designer des Data Lakes sind inzwischen in andere Projekte weitergewandert.

Lösungsansatz

Die aktuelle Partitionierung ist aus vielen Gründen suboptimal.

  • Es fehlt die Partitionierung auf Basis des Mandaten. Dadurch, dass es ca. 500 Mandaten sind, sind die Datenmengen pro Mandanten nur ca. 1/500 der Gesamtdatenmenge. Allein die Umstellung auf Partitionierung nach Mandant würde die Performance dramatisch verbessern.
  • Warum wird für Zeitbereiche LIST Partitionierung genutzt? RANGE bzw. noch besser INTERVAL Partitioning würde sich hier viel besser eignen. Im Fall von INTERVAL Partitioning braucht man in den Lade-Jobs auch nicht zu verifizieren, ob es schon passende Partitionen gibt. Diese würden automatisch erzeugt werden.
  • Warum die Partitionierung oft auf EreignisDatum und nicht auf WirkungsDatum erfolgt, ist ebenfalls nicht nachvollziehbar. Auf Grund der Verwendung der Daten ist lediglich das WirkungsDatum für die Abfragen relevant. Der einzige Grund könnte sein, dass die Datenladegruppe es einfacher hat, weil diese jeweils einen EreignisTag auf einmal laden.

Wie könnte eine sinnvolle Partitionierung für diese Aufgabenstellung aussehen?

Damit der Zugriff auf die Daten so effizient wie möglich erfolgen kann (und damit in vielen Fällen auch ohne Indizierung schnell sein kann), wäre folgende Partitionierung sinnvoll:

  • Die Tabelle EREIGNIS mit LIST Partitionierung auf Mandant und Sub-Partitioning mit INTERVAL (täglich) auf das Wirkungsdatum
  • Die Tabelle EREIGNISTYPEDETAIL mit LIST Partitionierung auf Mandant und Sub-Partitionierung mit INTERVAL (täglich) auf das Wirkungsdatum
  • Die Tabelle KUNDE mit LIST Partitionierung auf Mandant und optional Sub-Partitionierung mit LIST Partitionierung auf den Kundentype, weil dieser in ca. 20-30% der Abfragen eingeschränkt wird.

Wie würde die Auswirkung auf die Statements schätzungsweise sein?

Aktuell werden immer alle Daten aller Tabellen gelesen. Durch die Partitionierung auf MANDANT müsste im Schnitt nur 1/500 der Daten gelesen werden, die Statements würden sofort durchschnittlich 500 mal schneller sein! Im Moment sind 8 Jahre Daten im Data Lake, es sollen aber noch historische Daten ab 2002 (Einführung Euro als Bargeld in Österreich) nachgeladen werden. Die meisten Abfragen schränken auf weniger als ein Monat ein (selten wird das erlaubte Maximum von 3 Monaten genutzt). Wenn man nur davon ausgeht, dass auf ein Monat eingeschränkt wird und die Partitionierung der Tabelle der Spalte in der WHERE Clause entspricht, so würden die Statements durchschnittlich nur ca. 1/240 (20 Jahre in Monaten) der Daten innerhalb eines Mandanten benötigen. Ob die Tabelle KUNDEN nur nach Mandant (Faktor 500) oder zusätzlich noch nach Kundentype (es gibt ca. 10 Kundentypen, nicht alle kommen gleich häufig vor) partitioniert sind, macht für die meisten Abfragen kaum einen Unterschied. Es gibt aber einige Anfragen, die spezifisch auf seltene Kundentypen einschränken (Großkunden). Hier würde das Sub-Partitioning nochmals einen Performancevorteil um einen hohen zweistelligen Faktor bringen.

Zusammenfassung

Der richtige Einsatz von Oracle Partitioning kann ein perfektes Heilmittel für Performanceprobleme sein, allerdings muss man einige Kriterien erfüllen:

  • Es muss klar definiert werden, welche WHERE Bedingungen vorkommen werden.
  • Man muss sich im Vorfeld genau überlegen, welche Partitionierungkriteren man nutzt.
  • Das Wissen über die möglichen Partitionierungsoptionen ist essentiell, um ein optimales Ergebnis zu erzielen.

Dieses Projekt hat (leider) wieder einmal gezeigt, dass man ohne ausreichende Informationen keine sinnvolle Partitionierung umsetzen kann.

Comments

  1. Stefan Rudolph-Klindtwort

    Moin Moin,
    sehr interessanter Artikel, über effzientes Teamwork und alles was schiefgehen kann.
    Was mich jedoch noch einmal interessieren würde, um welche Datenvolumen geht es bei diesem Projekt. D.h. wie viele Zeilen werden da bei 500 Mandanten über 20 Jahre zusammenkommen? Die daraus abgeleitete Frage ist, ab wie viel Zeilen man überhaupt über Partitionierung nachdenken sollte.

    Viele Grüße
    Stefan

  2. Christian Pfundtner

    Hallo,

    Aktuell pro Jahr deutlich über ein TB (alle Mandanten gemeinsam, nur Daten ohne Indizes). Die ältesten Jahre haben knapp ein TB pro Jahr.

    Lg

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

This site uses Akismet to reduce spam. Learn how your comment data is processed.