Datenhistorisierung II

Im vorherigen Post habe ich die Möglichkeit gezeigt, wie man eine Kombination aus UNION ALL und GROUP BY nutzen kann, um die Daten als Slowly Changing Dimension Type 2 zu historisieren. Seitdem habe ich einige Performance-Tests durchgeführt, um diesen Ansatz mit herkömmlichen Vorgehensweisen in verschiedenen Situationen zu vergleichen.

Testfall 1

Wir betrachten das Laden einer versionierten Tabelle im Datawarehouse Core-Bereich aus einer Staging-Tabelle. Schauen wir uns erst mal mögliche Konstellationen an, welche Datenmengen wir zu verarbeiten haben und wo die Performance verloren gehen kann. Grundsätzlich gibt es zwei Implementierungvarienten für diesen ETL-Prozess: Voll- und Delta-Laden.

Full loading

Vollladen

Beim Vollladen haben wir den vollen Datenabzug aus dem Quellsystem im Staging-Bereich. Der Join von diesem Vollabzug zu allen gültigen Datensätzen in der versionierten Tabelle kann unter Umständen langsam sein. Und dieser Join muss dazu auch noch zwei mal ausgeführt werden, es sei denn wie würden die Zwischenergebnisse des Joins im ETL-Prozess materialisieren.

Auf der anderen Seite müssen wir mit dem „neuen“ Ansatz über die Datenmenge gruppieren, die zwei mal so groß ist wie der Vollabzug der Daten aus dem Quellsystem. Und das kann auch langsam sein!

Delta loading

Delta-Laden

Beim Delta-Laden haben wir im Staging-Bereich in der Regel nur einen kleinen Teil der Daten – die neuen und die aktualisierten Datensätze. Somit kann der Join zu gültigen Datensätzen im Core ziemlich effizient sein, wobei wir mit dem „neuen“ Ansatz immer noch über beträchtliche Datenmenge gruppieren müssen: alle aktuellen Datensätze plus „Delta“. Wie können wir das verbessern?

Delta loading with pre-filter

Delta-Laden mit einem Vorfilter

Offensichtlich müssen wir nur die Datensätze vergliechen (auf die Änderungen untersuchen), die es bereits im Core-Bereich gab. Wir können eine Art Vorfilter einbauen, zum Beispiel als IN- oder EXISTS-Unterabfrage über den Business Key. Somit muss am Ende nur über eine Datenmenge gruppiert werden, die ungefähr zwei Mal so groß ist, wie der Delta-Extrakt.

Einige Rahmenbedingungen und Zahlen zum meinem Testfall:

  • ziemlich breite Tabelle mit 120 Spalten
  • Vergleich des herkömmlichen Ansatzes mit GROUP BY bzw. analytischen FUnktion
  • Vollextrakt in der Staging-Tabelle als Quelle vs. Deltaextrakt (mit und ohne Vorfilter)
  • ca. 6 Mio Datensätze in der Zieltabelle
  • ca. 3 Mio Datensätze im Vollextrakt
  • ca. 3000 Datensätze im Deltaextrakt
VorgehensweiseDelta-Laden, minVollladen, min
Outer Join (herkömmliche Ansatz)0:090:41
GROUP BY1:101:04
GROUP BY mit Vorfilter0:04N/A
Analytische Function2:124:52
Analytische Function mit Vorfilter0:12N/A

Beim Laden von Deltaextrakt sah der herkömmliche Ansatz aus der Performance-Sicht ziemlich gut aus. Vor allem seine Komplexität, mit dem aufwendigen Vergleich aller Spalten oder Generieren, Speichern und Vergleichen eines Hash-Diffs für alle Datensätze, war der Grund für die Suche nach einem alternativen Verfahren. Auf der anderen Seite, der „reine“ GROUP BY Ansatz ist einfacher aber aber deutlich langsamer. Der Gewinner ist hier der GROUP BY mit Vorfilter. Aber der Preis ist wieder eine etwas höhere Komplexität.

Beim Vollladen kann der „neue“ Ansatz mit dem Join nicht mithalten – er ist um ca. 50% langsamer.

Um den Unterschied zwischen GROUP BY und analytischen Funktion zu verstehen, müssen wir uns die Ausführungspläne anschauen. Das Gruppieren wurde bis Oracle 10g Release 2 mithilfe der Operation SORT GROUP BY durchgeführt. Und diese ist vergleichbar mit WINDOW SORT, die wir in dem Plan für die analytische Funktion COUNT() sehen. Mit 10g Release 2 wurde hash aggregation eingeführt. Diese können wir durch die Schritte HASH GROUP BY im Ausführungsplan erkennen (Listing 1, Zeile 21). Aber die analytischen Funktionen profitieren nicht von dem neuen Feature, die Zeit geht an den Zeilen 14 and 15 (Listing 2) – WINDOW SORT – verloren.

                                                                                                                                                                                                                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                   
| Id  | Operation                                | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                   
|   0 | MERGE STATEMENT                          |                   |      1 |        |       | 10843 (100)|          |        |      |            |      0 |00:00:04.33 |    2447 |      0 |       |       |          |                                                                                   
|   1 |  MERGE                                   | CO_S_ORDER_TEST   |      1 |        |       |            |          |        |      |            |      0 |00:00:04.33 |    2447 |      0 |       |       |          |                                                                                   
|   2 |   VIEW                                   |                   |      1 |        |       |            |          |        |      |            |   3799 |00:00:04.29 |     796 |      0 |       |       |          |                                                                                   
|   3 |    SEQUENCE                              | SEQ_CO_S_ORDER    |      1 |        |       |            |          |        |      |            |   3799 |00:00:04.29 |     796 |      0 |       |       |          |                                                                                   
|   4 |     PX COORDINATOR                       |                   |      1 |        |       |            |          |        |      |            |   3799 |00:00:04.28 |      36 |      0 |       |       |          |                                                                                   
|   5 |      PX SEND QC (RANDOM)                 | :TQ10005          |      0 |    241 |  2353K| 10843  (44)| 00:00:02 |  Q1,05 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|*  6 |       HASH JOIN OUTER BUFFERED           |                   |      4 |    241 |  2353K| 10843  (44)| 00:00:02 |  Q1,05 | PCWP |            |   3799 |00:00:11.51 |       0 |      0 |  3418K|  1207K|     4/0/0|                                                                                   
|   7 |        PX RECEIVE                        |                   |      4 |    241 |  2248K|  6095  (50)| 00:00:01 |  Q1,05 | PCWP |            |   3799 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|   8 |         PX SEND HASH                     | :TQ10003          |      0 |    241 |  2248K|  6095  (50)| 00:00:01 |  Q1,03 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|*  9 |          VIEW                            |                   |      4 |    241 |  2248K|  6095  (50)| 00:00:01 |  Q1,03 | PCWP |            |   3799 |00:00:00.04 |       0 |      0 |       |       |          |                                                                                   
|  10 |           WINDOW SORT                    |                   |      4 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,03 | PCWP |            |   3799 |00:00:00.04 |       0 |      0 |  3046K|   544K|     4/0/0|                                                                                   
|  11 |            PX RECEIVE                    |                   |      4 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,03 | PCWP |            |   3799 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|  12 |             PX SEND HASH                 | :TQ10002          |      0 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,02 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|* 13 |              FILTER                      |                   |      4 |        |       |            |          |  Q1,02 | PCWC |            |   3799 |00:00:00.07 |       0 |      0 |       |       |          |                                                                                   
|  14 |               HASH GROUP BY              |                   |      4 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,02 | PCWP |            |   4300 |00:00:00.07 |       0 |      0 |    22M|  2073K|     4/0/0|                                                                                   
|  15 |                PX RECEIVE                |                   |      4 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,02 | PCWP |            |   4654 |00:00:00.04 |       0 |      0 |       |       |          |                                                                                   
|  16 |                 PX SEND HASH             | :TQ10001          |      0 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,01 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|  17 |                  HASH GROUP BY           |                   |      4 |    241 |   727K|  6095  (50)| 00:00:01 |  Q1,01 | PCWP |            |   4654 |00:00:03.41 |     100K|  99050 |    35M|  4145K|     4/0/0|                                                                                   
|  18 |                   VIEW                   |                   |      4 |   4801 |    14M|  6093  (50)| 00:00:01 |  Q1,01 | PCWP |            |   4801 |00:00:00.77 |     100K|  99050 |       |       |          |                                                                                   
|  19 |                    UNION-ALL             |                   |      4 |        |       |            |          |  Q1,01 | PCWP |            |   4801 |00:00:00.77 |     100K|  99050 |       |       |          |                                                                                   
|  20 |                     PX BLOCK ITERATOR    |                   |      4 |   3120 |  1523K|     4   (0)| 00:00:01 |  Q1,01 | PCWC |            |   3120 |00:00:00.01 |     290 |      0 |       |       |          |                                                                                   
|* 21 |                      TABLE ACCESS FULL   | STG_S_ORDER_DELTA |     58 |   3120 |  1523K|     4   (0)| 00:00:01 |  Q1,01 | PCWP |            |   3120 |00:00:00.01 |     290 |      0 |       |       |          |                                                                                   
|* 22 |                     HASH JOIN RIGHT SEMI |                   |      4 |   4152 |  1857K|  6088  (50)| 00:00:01 |  Q1,01 | PCWP |            |   1681 |00:00:04.41 |   99890 |  99050 |  1295K|  1295K|     4/0/0|                                                                                   
|  23 |                      PX RECEIVE          |                   |      4 |   3120 | 31200 |     4   (0)| 00:00:01 |  Q1,01 | PCWP |            |  12480 |00:00:00.02 |       0 |      0 |       |       |          |                                                                                   
|  24 |                       PX SEND BROADCAST  | :TQ10000          |      0 |   3120 | 31200 |     4   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |      0 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|  25 |                        PX BLOCK ITERATOR |                   |      4 |   3120 | 31200 |     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |   3120 |00:00:00.01 |     290 |      0 |       |       |          |                                                                                   
|* 26 |                         TABLE ACCESS FULL| STG_S_ORDER_DELTA |     58 |   3120 | 31200 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |   3120 |00:00:00.01 |     290 |      0 |       |       |          |                                                                                   
|  27 |                      PX BLOCK ITERATOR   |                   |      4 |   4135K|  1766M|  6048  (50)| 00:00:01 |  Q1,01 | PCWC |            |   3710K|00:00:03.26 |   99890 |  99050 |       |       |          |                                                                                   
|* 28 |                       TABLE ACCESS FULL  | CO_S_ORDER_TEST   |     84 |   4135K|  1766M|  6048  (50)| 00:00:01 |  Q1,01 | PCWP |            |   3710K|00:00:02.92 |   99890 |  99050 |       |       |          |                                                                                   
|  29 |        PX RECEIVE                        |                   |      4 |   6107K|  2609M|  4694  (35)| 00:00:01 |  Q1,05 | PCWP |            |   6107K|00:00:11.11 |       0 |      0 |       |       |          |                                                                                   
|  30 |         PX SEND HASH                     | :TQ10004          |      0 |   6107K|  2609M|  4694  (35)| 00:00:01 |  Q1,04 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |       |       |          |                                                                                   
|  31 |          PX BLOCK ITERATOR               |                   |      4 |   6107K|  2609M|  4694  (35)| 00:00:01 |  Q1,04 | PCWC |            |   6107K|00:00:05.37 |   99890 |  99050 |       |       |          |                                                                                   
|* 32 |           TABLE ACCESS FULL              | CO_S_ORDER_TEST   |     84 |   6107K|  2609M|  4694  (35)| 00:00:01 |  Q1,04 | PCWP |            |   6107K|00:00:04.69 |   99890 |  99050 |       |       |          |                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                   

Listing 1 Ausführungsplan für GROUP BY mit Vorfilter


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                              
| Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem |  O/1/M   | Max-Tmp |                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                              
|   0 | MERGE STATEMENT                     |                  |      1 |        |       |       |   221K(100)|          |        |      |            |      0 |00:05:46.11 |    2447 |      0 |      0 |       |       |          |         |                                                              
|   1 |  MERGE                              | CO_S_ORDER_TEST  |      1 |        |       |       |            |          |        |      |            |      0 |00:05:46.11 |    2447 |      0 |      0 |       |       |          |         |                                                              
|   2 |   VIEW                              |                  |      1 |        |       |       |            |          |        |      |            |   3799 |00:05:45.89 |     797 |      0 |      0 |       |       |          |         |                                                              
|   3 |    SEQUENCE                         | SEQ_CO_S_ORDER   |      1 |        |       |       |            |          |        |      |            |   3799 |00:05:45.89 |     797 |      0 |      0 |       |       |          |         |                                                              
|   4 |     PX COORDINATOR                  |                  |      1 |        |       |       |            |          |        |      |            |   3799 |00:05:45.88 |      37 |      0 |      0 |       |       |          |         |                                                              
|   5 |      PX SEND QC (RANDOM)            | :TQ10003         |      0 |   7422K|    69G|       |   221K  (3)| 00:00:35 |  Q1,03 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |                                                              
|*  6 |       HASH JOIN RIGHT OUTER BUFFERED|                  |     16 |   7422K|    69G|       |   221K  (3)| 00:00:35 |  Q1,03 | PCWP |            |   3799 |00:15:42.85 |       0 |     71 |     71 |  2047M|    33M|    16/0/0|    1024 |                                                              
|   7 |        PX RECEIVE                   |                  |     16 |   6107K|  2609M|       |  1174  (35)| 00:00:01 |  Q1,03 | PCWP |            |   6107K|00:01:42.93 |       0 |      0 |      0 |       |       |          |         |                                                              
|   8 |         PX SEND HASH                | :TQ10001         |      0 |   6107K|  2609M|       |  1174  (35)| 00:00:01 |  Q1,01 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |                                                              
|   9 |          PX BLOCK ITERATOR          |                  |     16 |   6107K|  2609M|       |  1174  (35)| 00:00:01 |  Q1,01 | PCWC |            |   6107K|00:00:05.42 |     101K|  99050 |      0 |       |       |          |         |                                                              
|* 10 |           TABLE ACCESS FULL         | CO_S_ORDER_TEST  |    251 |   6107K|  2609M|       |  1174  (35)| 00:00:01 |  Q1,01 | PCWP |            |   6107K|00:00:04.86 |     101K|  99050 |      0 |       |       |          |         |                                                              
|  11 |        PX RECEIVE                   |                  |     16 |   7422K|    66G|       |   219K  (3)| 00:00:35 |  Q1,03 | PCWP |            |   3799 |00:08:25.26 |       0 |      0 |      0 |       |       |          |         |                                                              
|  12 |         PX SEND HASH                | :TQ10002         |      0 |   7422K|    66G|       |   219K  (3)| 00:00:35 |  Q1,02 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |                                                              
|* 13 |          VIEW                       |                  |     16 |   7422K|    66G|       |   219K  (3)| 00:00:35 |  Q1,02 | PCWP |            |   3799 |01:08:17.83 |     823 |   1127K|   1127K|       |       |          |         |                                                              
|  14 |           WINDOW SORT               |                  |     16 |   7422K|    21G|    25G|   219K  (3)| 00:00:35 |  Q1,02 | PCWP |            |   7422K|01:23:52.70 |     823 |   1127K|   1127K|    10G|    24M|    4/12/0|     591K|                                                              
|  15 |            WINDOW SORT              |                  |     16 |   7422K|    21G|    25G|   219K  (3)| 00:00:35 |  Q1,02 | PCWP |            |   7422K|00:52:43.97 |     754 |    601K|    601K|    10G|    24M|          |     615K|                                                              
|  16 |             PX RECEIVE              |                  |     16 |   7422K|    21G|       |  2188  (45)| 00:00:01 |  Q1,02 | PCWP |            |   7422K|00:11:15.00 |       0 |      0 |      0 |       |       |          |         |                                                              
|  17 |              PX SEND HASH           | :TQ10000         |      0 |   7422K|    21G|       |  2188  (45)| 00:00:01 |  Q1,00 | P->P | HASH       |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         |                                                              
|  18 |               VIEW                  |                  |     16 |   7422K|    21G|       |  2188  (45)| 00:00:01 |  Q1,00 | PCWP |            |   7422K|00:01:03.91 |     161K|    156K|      0 |       |       |          |         |                                                              
|  19 |                UNION-ALL            |                  |     16 |        |       |       |            |          |  Q1,00 | PCWP |            |   7422K|00:00:52.98 |     161K|    156K|      0 |       |       |          |         |                                                              
|  20 |                 PX BLOCK ITERATOR   |                  |     16 |   3710K|  1525M|       |   676  (36)| 00:00:01 |  Q1,00 | PCWC |            |   3712K|00:00:24.22 |   59203 |  57097 |      0 |       |       |          |         |                                                              
|* 21 |                  TABLE ACCESS FULL  | STG_S_ORDER_FULL |    234 |   3710K|  1525M|       |   676  (36)| 00:00:01 |  Q1,00 | PCWP |            |   3712K|00:00:24.75 |   59203 |  57097 |      0 |       |       |          |         |                                                              
|  22 |                 PX BLOCK ITERATOR   |                  |     16 |   4135K|  1766M|       |  1512  (50)| 00:00:01 |  Q1,00 | PCWC |            |   3710K|00:00:22.72 |     101K|  99050 |      0 |       |       |          |         |                                                              
|* 23 |                  TABLE ACCESS FULL  | CO_S_ORDER_TEST  |    251 |   4135K|  1766M|       |  1512  (50)| 00:00:01 |  Q1,00 | PCWP |            |   3710K|00:00:22.19 |     101K|  99050 |      0 |       |       |          |         |                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                              
 

Listing 2 Ausführungsplan für analytische Funktion mit Vorfilter

Testfall 2

Die Quelle für den ETL-Prozess im ersten Testfall war eine Staging-Tabelle. Aber was wäre, wenn wir eine Dimensionstabelle aus einer riesengroßen versionereten Core-Tabelle oder über eine View laden würden? Der Vorteil, dass wir die große Tabelle nicht zwei mal lesen müssen oder die komplizierte View-Logik nicht zwei mal ausführen müssen kann den Performnaceverlust beim Gruppieren durchaus überwiegen, sogar ohne Vorfilter. In meinem Testfall werden einige „große“ Tabellen (50 Gb, 40+ Mio Datensätze) in einer View gejoint. Daraus werden täglich etwa 500 DImensionsdatensätze produziert. Die Ladezeit konnte um 45% reduziert werden (3 min 50 sec → 2 min).

Fazit

Ich finde, der vorgeschlagene Ansatz ist eine einfache Alternative, die durchaus weiteres Testen verdient hat. Nicht in jedem Anwendungsfall. Nach meinem Verständnis sollte die Entwicklung eines Data-Warehouse-Systems hochautomatisiert ablaufen, mit Einsatz entsprechender Generator- /DWH-Automation Tools, wie z.B. biGENiUS, die die ganze Komplexität der Deltaerkennung verstecken. Sollte das nicht der Fall sein und die ganzen ETL-Prozesse mit Versionierung der Daten im Wesentlichem manuell entwickelt werden, kann man durchaus diesen „neuen“ Ansatz in Betracht ziehen und von seiner Einfachheit in der Entwicklung und Pflege profitieren.

Schreibe einen Kommentar

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