wie können konstante laufzeiten für etl-prozesse bei ... · granularity = 'approx_global and...
TRANSCRIPT
Wie können konstante Laufzeiten
für ETL-Prozesse bei wachsendem Volumeneines DWH erreicht werden?
Reinhard MenseGeschäftsführer ARETO Consulting
PraxisBeispiel aus der
80
100
120
140
160
80
100
120
140
Einsatz verbesserter
Hardware
Minuten Mio. Datensätze
0
20
40
60
80
0
20
40
60
01 04 07 10 13 16 19 22
Laufzeit des ETL-Prozesses max. Zeitfenster
neue Datenmenge historische Datenmenge
Testszenario
Datum
Kunde
Ein überschaubares
ProduktSCD Typ 110.000 Produkte
10 Änderungen/Tag10 neue Produkte/Tag
KundeSCD Typ 21.000.000 Kunden
100.000 Änderungen/Tag 1.000 neue Kunden/Tag
Umsatz1.000.000 Umsätze/Tag
AggregationUmsatz
Aggregation pro Tag und Produkt
� 10.000 Datensätze/Tag
25
30
35
40
1000
1200
1400
1600
1800
Laufzeiten bei schlechtem DWH-Design
Sekunden Mio. Datensätze
Total
+ 303 %
0
5
10
15
20
0
200
400
600
800
1000
1 4 7 10 13 16 19 22 25 28 31
Gesamtlaufzeit historische Datenmenge aktuelle Datenmenge
25
30
35
40
1000
1200
1400
1600
1800Sekunden Mio. Datensätze
+ 319 %
+ 814 %
Laufzeiten bei schlechtem DWH-Design
0
5
10
15
20
0
200
400
600
800
1000
1 4 7 10 13 16 19 22 25 28 31
Tabellen Indizes MatView Statistiken historische Datenmenge aktuelle Datenmenge
+ 814 %
+ 1837 %
+ 81 %
25
30
35
40
1000
1200
1400
1600
1800Sekunden Mio. Datensätze
+ 770 %
Laufzeiten bei schlechtem DWH-Design
0
5
10
15
20
0
200
400
600
800
1000
1 4 7 10 13 16 19 22 25 28 31
Kunde Produkt Umsatz Aggregation historische Datenmenge aktuelle Datenmenge
+ 792 %
+ 132 %
200
300
400
500
600
700
800
Schlechter ETL-Prozess für die Fakttabelle
Lookup auf Dimensionstabellen + Stage-Tabelle füllen mit
INSERT APPEND
Leeren der Stage-Tabelle mit TRUNCATE TABLE
Aktualisieren der globalen Statistiken der Stage-Tabelle mit
DBMS_STATS.GATHER_TABLE_STATS
Sekunden
����
+ 231 %
����
0
100
1 4 7 10 13 16 19 22 25 28 31
Truncate Stage Lookup + Stage Insert Stage Statistiken Delete Fakten
Insert Fakten Rebuild Indizes Fakten Statistiken
Einfügen der aktuellen Datensätze in die Fakttabelle mit
INSERT
Aufbau der globalen Bitmap Indizes mit ALTER INDEX … REBUILD
Aktualisieren der globalen Statistiken der Fakttabelle mit
DBMS_STATS.GATHER_TABLE_STATS+ 293 %
����
+ 3318 %
Löschen der ggf. vorhandenen Datensätze aus der Fakttabelle mit
DELETE+3600 %
100
150
200
250
300
350
Lookup auf Dimensionstabellen + INSERT APPEND in Stage-Tabelle
Leeren der Stage-Tabelle mit TRUNCATE TABLE
Aufbau der globalen Bitmap Indizes der Stage-Tabelle mit
ALTER INDEX … REBUILD
Sekunden
Guter ETL-Prozess für die Fakttabelle
����
+ 231 %
����
0
50
1 4 7 10 13 16 19 22 25 28 31
Stage Truncate Lookup + Stage Insert Stage Rebuild Indexes
Stage Statistiken Partition Exchange Fakten Statistiken
Tausch der Staging-Tabellegegen die aktuelle Partition der Fakttabelle
ALTER TABLE … EXCHANGE PARTITION … WITH TABLE … INCLUDING INDEXES
WITHOUT VALIDATION
Aktualisieren der globalen Statistiken der Fakttabelle mit
DBMS_STATS.GATHER_TABLE_STATS
Aktualisieren der globalen Statistiken der Stage-Tabelle mit
DBMS_STATS.GATHER_TABLE_STATS
+ 1093 %
����
����
100
200
300
400
500
600
700
Einfügen der aktuellen Kundendaten in die Stage-Tabelle mit INSERT APPEND
Leeren der Stage-Tabelle mit TRUNCATE TABLE
Aktualisieren der globalen Statistiken der Stage-Tabelle mit
DBMS_STATS.GATHER_TABLE_STATS
Sekunden
Schlechter ETL-Prozess für die Kundendimension
����
����
����
0
100
1 4 7 10 13 16 19 22 25 28 31
Stage Truncate Stage Insert Stage Statistiken Dim. Update
Dim. Insert Rebuild Indexes Statistiken
Veraltete Datensätze in der Kundendimension abschließen mit
UPDATE
Neue und geänderte Kundendatensätze in die Kundendimension einfügen mit
INSERT
Aufbau der globalen Indizes mit REBUILD
globalen Statistiken für Dimension DBMS_STATS.GATHER_TABLE_STATS
+ 40%
+ 278 %
����
+ 793 %
Partition Wert des
Partitions-
schlüssels
Inhalt
01.01.2010
Inhalt
02.01.2010
Inhalt
03.01.2010
… Inhalt
31.01.2010
P20100101 < 02.01.2010 0 100.000 100.000 … 100.000
P20100102 < 03.01.2010 0 0 100.000 … 100.000
P20100103 < 04.01.2010 0 0 0 … 100.000
… … … … … … …
3.000.000
Partitionierung der Kundendimension
… … … … … … …
P20100131 < 01.02.2010 0 0 0 … 100.000
PDEFAULT < MAXVALUE 1.000.000 1.000.000 1.000.000 … 1.000.000
Geänderte Datensätze
1.000.000(alle neu)
100.000 100.000 … 100.000
Leeren der Stage-Tabelle mit TRUNCATE TABLE
Einfügen der aktuellen Kundendaten in die Stage-Tabelle mit INSERT APPEND
200
400
600
800
1000
1200Sekunden
Veraltete Datensätze in der Kundendimension abschließen mit
Aktualisieren der globalen Statistiken der Stage-Tabelle mit
DBMS_STATS.GATHER_TABLE_STATS
Guter ETL-Prozess für die Kundendimension
����
����
����
����
Neue und geänderte Kundendatensätze in die Kundendimension einfügen mit
INSERT
Aufbau der lokalen Indizes mit ALTER INDEX … REBUILD PARTITION
Aktualisieren der lokalen Statistiken mit DBMS_STATS.GATHER_TABLE_STATS
Aktualisieren der globalen Statistiken mit DBMS_STATS.GATHER_TABLE_STATS
0
200
1 4 7 10 13 16 19 22 25 28 31
Stage Truncate Stage Insert Stage Statistiken Dim. Update
Dim. Insert Rebuild Indexes lokale Statistiken globale Statistiken
abschließen mit UPDATE … PARTITION (…)
+ 1666 %
����
����
����
����
Partitionierung verbessert Lookup für Fakttabelle
40
50
60
+ 231 %
Sekunden
0
10
20
30
1 4 7 10 13 16 19 22 25 28 31
nicht partitioniert partitioniert
+ 231 %
����
100
150
200
250
300
350Aktualisierung der Materialized View mit
COMPLETE REFRESH
Aufbau der globalen Bitmap Indizes mitALTER INDEX … REBUILD
Sekunden
Schlechter ETL-Prozess für die Aggregation
+ 814 %
n. m.
0
50
100
1 4 7 10 13 16 19 22 25 28 31
Refresh Indizes Statistiken
ALTER INDEX … REBUILD
Aktualisieren der globalen Statistiken mit DBMS_STATS.GATHER_TABLE_STATS
n. m.
n. m.
Aktualisierung der Materialized View mitFAST PCT REFRESH
Aufbau der loaklen Bitmap Indizes mitALTER INDEX … REBUILD PARTITION
100
150
200
250
300
350Sekunden
Guter ETL-Prozess für die Aggregation
n. m.
����
Aktualisieren der lokalen Statistiken mit DBMS_STATS.GATHER_TABLE_STATS
Aktualisieren der globalen Statistiken mit DBMS_STATS.GATHER_TABLE_STATS
0
50
100
1 4 7 10 13 16 19 22 25 28 31
Refresh Indizes Statistikenn. m.
n. m.
Join in Materialized View Beispiel Optimierungs-
möglichkeiten
Grenzen
Fakten � SCD Typ 1 Aggregation auf Ebene der Produktgruppen
Materialized View Log auf Dimension + Fast Refresh
Keine Konstanz, da eine Änderung alle Partitionen der Materialized View betrifft
Fakten � SCD Typ 2 Aggregation auf Ebene der Kundenkategorie
Materialized View Log auf Dimension + Fast Refresh
Keine Konstanz, da eine Änderung alle Partitionen der Materialized View
Grenzen des Fast Refresh
bei komplexen Materialized Views
der Materialized View betrifft
Aufnahme eines Partition-Markers der Dimension in die Materialized View + Subpartitionierung
Keine Konstanz, da eineÄnderung alle Partitionen der Materialized View betrifft (aber immer nur jeweils eine Subpartition)
Manueller Refresh Konstanz möglich, da nur für die aktuelle Partition der manuelle Refresh durchgeführt werden muss
800
1000
1200
1400
Sekunden
Laufzeiten bei gutem DWH-Design
+ 559 %
0
200
400
600
800
1 4 7 10 13 16 19 22 25 28 31
Tabellen Indizes MatView Statistiken
+ 559 %
����
Datenbankversion Inkrementelle Statistiken Aktivierung
<= 10.2.0.3 Nicht möglich Nicht möglich
10.2.0.4Einfaches Verfahren,nicht immer erfolgreich.
DBMS_STATS.GATHER_TABLE_STATSmit Parameter GRANULARITY = 'APPROX_GLOBAL AND PARTITION'
Inkrementelle globale Statistiken
nicht immer erfolgreich.GRANULARITY = 'APPROX_GLOBAL AND PARTITION'
ab 11g Release 1Verbessertes Verfahren
(synopsis pro Partition)
DBMS_STATS.SET_TABLE_PREFS
('SH, 'SALES‚ 'INCREMENTAL','TRUE')
DBMS_STATS.GATHER_TABLE_STATS
('SH','SALES');
600
700
800
900
1000
+ 559 %
Laufzeiten für Statistiken
Sekunden
0
100
200
300
400
500
1 4 7 10 13 16 19 22 25 28 31
nicht inkrementell inkrementell
+ 559 %
+ 81 %
Zusammenfassung und Ausblick
KonstanzKonstanz
0
200
400
600
800
1000
1200
1400
1600
1800
1 4 7 10 13 16 19 22 25 28 31
0
200
400
600
800
1000
1200
1400
1600
1800
1 4 7 10 13 16 19 22 25 28 31
COMPRESS
PARALLEL
NOLOGGING
Atomic Refresh FALSE
Direct Path Load
PCTFREE 0
Kaskadierende MatViews
Constraints NOVALIDATE
Constraints RELY
OptimierungOptimierung
0
200
400
600
800
1000
1200
1400
1600
1800
1 4 7 10 13 16 19 22 25 28 31
0
20
40
60
80
100
120
140
160
0
20
40
60
80
100
120
140
01 04 07 10 13 16 19 22
200
250
120
140
ARETO Consulting GmbH
51429 Bergisch Gladbach
www.areto-consulting.de
Reinhard Mense
Geschäftsführer ARETO Consulting
0
50
100
150
200
0
20
40
60
80
100
01 04 07 10 13 16 19 22