Zeit sparen im Data Warehouse Projekt

Performanceverbesserung durch Parallel DML Operationen im ODI

Dieser erste Blogartikel der Artikelserie „Maßgeschneidertes & performantes ETL durch ODI Knowledge Modul Modifikationen (Achtung: Entwurfslinks)“ setzt sich mit dem Einsatz von Parallel DML Operationen im ODI zur Performanceverbesserung auseinander. Hierbei ist die geschilderte Anpassung am ODI Knowledge Modul eher geringfügig, kann aber je nach Konstellation einen sehr großen positiven Impact auf die Laufzeit bieten. So wird im Laufe dieses Artikels, exemplarisch anhand eines Beispiels, gezeigt, wie die Laufzeit einer Insert-Into-Select Operation mit über  135 Millionen Datensätzen, von ursprünglichen 20 Minuten auf 3 Minuten reduziert werden konnte.

Parallelisierung

Die Nutzung von parallelen Operationen, stellt gerade bei großen Datenmengen, wie sie in Data Warehäusern häufig anzutreffen sind, eine sehr gute Möglichkeit zur Performanceverbesserung dar. Bei der parallelen Abarbeitung von Operation werden dabei mehrere Unterprozesse gestartet (PX (Parallel Execution) Server), die jeweils für sich eine Teilmenge der Daten bearbeiten und das Zwischenresultat anschließend zur Ermittlung des Endergebnisses an den zentralen Benutzerprozess (QC (Query Coordinator) Server oder PX Coordinator) zurückmelden. Hierdurch werden die zur Verfügung stehenden Rechnerressourcen besser genutzt und es kann bei großen Tabellen eine erhebliche Verkürzung der Antwortzeiten erzielt werden.

Parallele Ausführung im ODI

Während bei ETL-Tools, wie z.B. IBM DataStage, die Transformationen von einem ETL-Server durchgeführt werden, und die Parallelisierung von Operationen damit diesem Server überlassen sind,  verfolgen ELT-Tools, wie der Oracle Data Integrator, einen anderen Ansatz. Im ODI werden über ein Template-basiertes Verfahren SQL-Statements generiert und zur Ausführung an die Datenbank gesendet. Ob und zu welchem Grad diese Operationen parallelisiert ausgeführt werden, hängt dabei in erster Linie von der Datenbankkonfiguration, den betroffenen Datenbankobjekten und dem generierten SQL-Statement ab.

Die Templates zur SQL-Generierung liegen in Form von anpassbaren sogenannten “Knowledge Modulen” (KM) vor. Der in den KM enthaltene Code entspricht zu großen Teilen dem SQL, welcher anschließend generiert und auf der Datenbank ausgeführt wird. Nachfolgend ist ein solcher Vorlagencode eines relativ simplen Insert-Select KM-Schritts abgebildet:
Code 1: Insert flow into I$ table – IKM Oracle Control Append
Code 1: Insert flow into i$ table – IKM Oracle Control Append

Im Code befinden sich ODI-Substitutionsmethoden für Variablen (z.B. odiRef.getOption), mit denen die SQL-Statements über deklarative Regeln basierend auf Metadaten, generisch erzeugt werden. Hier können neben den obligatorischen Tabellen- und Feldnamen  z.B. auch optionale Optimizer Hints in das SQL eingefügt werden. Diese Hints werden über KM-Optionen im ODI gesetzt („HINT_INSERT_FLOW“, „HINT_INSERT_TARGET“, „HINT_SELECT“):
Abbildung 1: Optimizer Hints in den KM-Optionen
Abbildung 1: Optimizer Hints in den KM-Optionen

In dieser Abbildung wurde die Option HINT_SELECT auf PARALLEL(8) und die beiden HINT_INSERT_X Optionen auf APPEND PARALLEL(8) NOLOGGING gesetzt. Es folgt ein beispielhaftes SQL, das auf Basis des Code 1 und den definierten Optimizer Hints aus Abbildung 1 entstehen könnte:
Code 2: Erzeugtes SQL im KM-Schritt
Code 2: Erzeugtes SQL im KM-Schritt

Bei Ausführung eines solchen SQLs auf einer Oracle 12C Datenbank (Version 12.0.2; weitestgehend Standard-Konfiguration) zeichnet sich bei Betrachtung der Sessions folgendes Bild ab:

Abbildung 3: Blick in die Sessions (SQLDeveloper) des ausgeführten Befehls
Abbildung 2: Blick in die Sessions (SQLDeveloper) des ausgeführten Befehls

Es findet eine Parallelisierung statt, weil für das Statement mehrere PX-Server aktiv sind. Auch ein oberflächlicher Blick in den Ausführungsplan dieses Statements, scheint zu bestätigen, dass dieser Insert-Select Befehl parallelisiert ausgeführt wird:
Abbildung 3: Ausführungsplan Parallel Query Befehl
Abbildung 3: Ausführungsplan Parallel Query Befehl

Die Einträge PX COORDINATOR, PX SEND QC, PX RECEIVE, PX SEND HASH, PX SELECTOR zeigen eine parallele Bearbeitung auf. Zur Veranschaulichung der Laufzeit wurde im ODI der folgende Job ausgeführt:

In einem einfachen 1:1 Mapping, bestehend aus einer Quellview und einer Zieltabelle, wurden über 135 Millionen Datensätze von der Quelle in das Ziel übertragen. Das vom ODI generierte SQL entspricht dabei nahezu 1:1 dem betrachteten Insert-Select Statement, nebst den definierten Optimizer Hints aus Code 2. Unter Verwendung einer Oracle 12C Datenbank auf einem DB-Server mit 12 Cores, 192 GB Ram und einem Shared SAN-Filesystem, hatte diese Operation eine Laufzeit von 20:36 Minuten:

Abbildung 4: Laufzeit Mapping 135 Millionen Datensätze
Abbildung 4: Laufzeit Mapping 135 Millionen Datensätze

Kann diese Laufzeit von 20:36 Minuten für die Übertragung der besagten 135 Millionen Datensätze noch verbessert werden, oder findet hier bereits eine vollständige & optimale Parallelisierung statt?

Hinweis: Diese Frage richtet sich ausdrücklich nicht an den verwendeten Grad der Parallelisierung, was natürlich eine wichtige Rolle spielt. In diesem Beispiel wurde exemplarisch PARALLEL(8) verwendet. Oracle empfiehlt die Datenbank den Parallelitätsgrad bestimmen zu lassen (Auto DOP).

Parallel Query != Parallel DML

Nicht ganz, denn in dem geschilderten Beispiel ist Parallel DML nicht aktiviert. Parallel DML steht für die parallele Ausführung von DML-Operationen wie Inserts, Updates und Deletes. Diese Option ist standardmäßig in Oracle 12C Datenbanken deaktiviert, wohingegen Parallel QUERY und Parallel DDL aktiviert sind. Auf welchem Wege lässt sich denn nun herausfinden ob Parallel DML aktiv ist oder nicht? Auf den ersten Blick war es zumindest in der SQLDeveloper Session Übersicht (Abbildung 2) nicht ersichtlich.

Ermitteln lässt sich diese Information aus der Spalte PDML_STATUS der View V$SESSION. Für das ausgeführte SQL aus unserem Beispiel erhalten wir unter Abfrage der View für die betroffene SID folgendes Ergebnis:

Code 3: SQL zur Abfrage von Parallel DML
Code 3: SQL zur Abfrage von Parallel DML

Abbildung 5: Parallelisierung für den Befehl aktiv oder nicht
Abbildung 5: Parallelisierung für den Befehl aktiv oder nicht?

Hier wird dann auch ersichtlich, dass zwar Parallel Query (PQ_STATUS) und Parallel DDL (PDDL_STATUS) aktiviert sind, hingegen Parallel DML (PDML_STATUS) deaktiviert ist.

Impact von Parallel DML

Welchen konkreten Impact hat eine Aktivierung von Parallel DML auf das betrachtete Insert-Select Statement? Dies soll nachfolgend verdeutlicht werden.

Dazu wird im ersten Schritt Parallel DML für die Session aktiviert:

Code 4: Parallel DML aktivieren
Code 4: Parallel DML aktivieren

Eine Überprüfung mit dem Befehl aus Code 3 zeigt, dass PDML_STATUS hiernach nun aktiviert ist:

Abbildung 6: Abfrage Parallel DML Status
Abbildung 6: Abfrage Parallel DML Status

Jetzt wird das ODI-Mapping gestartet und damit das SQL auf der Datenbank ausgeführt. Ein Blick in die Sessions zeigt, dass weiterhin, wie erwartet, mehrere PX-Server für den Befehl aktiv sind – die SQL_ID ist im Vergleich zum obigen Lauf gleich geblieben (d.h. das Statement selbst ist absolut identisch):

Abbildung 7: Sessions für Parallel DML aktivierte Operation
Abbildung 7: Sessions für Parallel DML aktivierte Operation

Bei Betrachtung des Ausführungsplans stellt man fest, dass dieser sich, im Gegensatz zu dem Lauf mit deaktivierten Parallel DML geringfügig verändert hat:

Abbildung 8: Ausfuehrungsplaene ohne & mit Parallel DML
Abbildung 8: Ausfuehrungsplaene ohne & mit Parallel DML

Der LOAD AS SELECT (=Direct Load Insert) Eintrag ist durch die Aktivierung von PARALLEL DML aus dem Ausführungsplan weggefallen.

Es folgt der Ausführungslog der Mapping-Ausführung aus dem ODI:

Abbildung 9: Laufzeit mit Parallel DML
Abbildung 9: Laufzeit mit Parallel DML

Durch den rot markierten Schritt mit dem Namen Enable Parallel DML wurde Parallel DML für die Datenbankoperation aktiviert. Im nachfolgenden Schritt sieht man in der Spalte Duration, dass die Operation mit aktiviertem Parallel DML lediglich 3:45 Minuten, statt den vorherigen 20:36 Minuten ohne Parallel DML gelaufen ist. Das entspricht also einer Laufzeitersparnis von annähernd 17 Minuten, die lediglich durch eine Aktivierung von Parallel DML zustande gekommen ist.

Wie Parallel DML generell und im ODI aktiviert wird, findet nachfolgend Betrachtung.

Aktivierung von Parallel DML im ODI

Für Oracle 12C Datenbanken kann Parallel DML für die jeweilige Session einfach über den Hint ENABLE_PARALLEL_DML aktiviert und mit DISABLE_PARALLEL_DML wieder deaktiviert werden. In diesem Fall geht die Aktivierung im ODI erfreulich einfach von statten. Hierzu reicht es, in den Knowledge Modul Optionen, die Hint-Variable entsprechend zu setzen:

Abbildung 10: Aktivierung von Parallel DML über Hints im ODI
Abbildung 10: Aktivierung von Parallel DML über Hints im ODI

Das vom ODI erzeugte SQL enthält den benötigten Hint zur Aktivierung von Parallel DML:

Code 5: Erzeugtes SQL mit dem Hint
Code 5: Erzeugtes SQL mit dem Hint

Leider funktioniert dieser Hint nicht auf Oracle 11g Datenbanken. Stattdessen wird das Absetzen des ALTER SESSION Befehls notwendig. Dies kann durch eine geringfügige Modifikation an einer Kopie des jeweils verwendeten Integration Knowledge Moduls erzielt werden (z.B. Control Append, Slowly Changing Dimension 2, Incremental Update, etc.):

Hierzu sollte im aktuellen Projekt eine Projektkopie des gewünschten IKM’s eingefügt werden – in diesem Beispiel handelt es sich um das IKM Oracle Control Append:

Abbildung 11: Lokale Projektkopie des IKM
Abbildung 11: Lokale Projektkopie des IKM

Es empfiehlt sich, im Namen kenntlich zu machen, dass es sich hierbei um ein modifiziertes Knowledge Modul handelt (z.B. durch ein „CUSTOM“). Anschließend sollte das Knowledge Modul geöffnet und ein neuer Task hinzugefügt werden. Der Task sollte aussagekräftig benannt werden (z.B. Enable Parallel DML, ebenfalls mit einer Kennzeichnung wie „CUSTOM“ im Namen):

Abbildung 12: Hinzugefügte Parallel DML Schritte im IKM
Abbildung 12: Hinzugefügte Parallel DML Schritte im IKM

Zu beachten gilt hierbei, dass Parallel DML pro Session aktiviert wird. D.h. wenn mehrere Transaktionen im Knowledge Modul verwendet werden und man für alle diese Transaktionen Parallel DML aktivieren möchte, so sollte ein entsprechender Aktivierungsschritt pro Transaktion eingefügt werden. So gibt es in dem obigen Beispiel die beiden Transaktionen Autocommit und Transaction 1 (Target Transaction). Die beiden kenntlich gemachten Aktivierungsschritte müssen mit dem bereits bekannten ALTER SESSION Befehl erweitert werden:

Abbildung 13: Aktivierung von Parallel DML im ODI
Abbildung 13: Aktivierung von Parallel DML im ODI

Wird im ODI dieses KM in einem Mapping ausgewählt und das entsprechende Mapping hiernach ausgeführt, so sollte die Operationen mit Parallel DML stattfinden. Zu beachten gilt hierbei, dass nach jeder Parallel DML Operation pro Session ein explizites Commit notwendig wird. Zur Überprüfung, ob Parallel DML für die DML-Operationen vom ODI tatsächlich aktiviert ist, sollte sicherheitshalber eine Prüfung in der V$SESSION-View durchgeführt werden (Befehl siehe Code 3). Analog zu dieser Schilderung kann über einen gesonderten KM-Task Parallel DML mit dem Befehl „ALTER SESSION DISABLE PARALLEL DML“ wieder deaktiviert werden.

Zu beachten gilt, dass bei Parallel DML Operationen ein Overhead auftritt, das Transaktionsverhalten verändert wird und außerdem diverse Restriktionen vorhanden sind. Der Einsatz von Parallel DML ist damit also nicht pauschal sinnvoll und ergibt z.B. für OLTP-Anwendungen meistens wenig Sinn. Mehr Informationen finden sich unter: Database VLDB and Partitioning Guide – Restrictions on Parallel DML

Zusammenfassung

In diesem Artikel wurde die parallele Ausführung von Operationen im ODI behandelt. In einem ersten Schritt wurde lediglich der Parallelitätsgrad per Optimizer Hint in den KM-Optionen gesetzt und eine exemplarische Insert-Select Operation im ODI gestartet. Die Übertragung von 135 Millionen Datensätzen hat eine Laufzeit von über 20 Minuten aufgewiesen. Dabei sah es laut Sessions und dem Ausführungsplan so aus, als ob diese DML-Operation von der Datenbank parallelisiert ausgeführt worden ist.

Danach wurde aber herausgestellt, dass bei diesem Befehl lediglich Parallel Query, nicht jedoch Parallel DML aktiviert war (Parallel DML ist in der Oracle Datenbank-Standardkonfiguration deaktiviert). Zur Verdeutlichung des Parallel DML Impacts, wurde die gleiche Operation anschließend mit einem um Parallel DML modifizierten KM ausgeführt und wies eine Laufzeit von nur noch etwas über 3 Minuten auf. Durch eine geringfügige Knowledge Modul Anpassung konnte also ein Laufzeitgewinn von über 17 Minuten erzielt werden. Danach wurden zwei konkrete Möglichkeiten aufgezeigt, wie Parallel DML im ODI zur Performanceverbesserung, über die Knowledge Module, aktiviert werden kann.

Abschließend gilt zu sagen, dass DML-Operation gerade im DWH-Kontext durch den Einsatz von Parallel DML stark beschleunigt werden können.

Quellen/Weiterführende Informationen

Ähnliche Beiträge

Schreibe einen Kommentar

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

Bitte füllen Sie dieses Feld aus
Bitte füllen Sie dieses Feld aus
Bitte gib eine gültige E-Mail-Adresse ein.

Menü