Data Warehousing in a Nutshell

Data Warehousing in a Nutshell
Bianca Stolz
Systemberaterin
ORACLE Deutschland B.V. & Co. KG
Berlin, 08. Dezember 2010
Agenda
• Die Motivation hinter einem Data Warehouse
<Insert Picture Here>
• Konzept und Design
• Drei in Eins: Das Schichtenmodell
• Über die Rolle der Fachanwender
• Laden der Daten
• Eine Auswahl an geeigneten Methoden
• Parallelisierung nutzen
• Betrieb des Warehouse
• Effiziente Datenhaltung über die Zeit
• Optimizer Statistiken
• Backup
Agenda
• Die Motivation hinter einem Data Warehouse
<Insert Picture Here>
• Konzept und Design
• Drei in Eins: Das Schichtenmodell
• Über die Rolle der Fachanwender
• Laden der Daten
• Eine Auswahl an geeigneten Methoden
• Parallelisierung nutzen
• Betrieb des Warehouse
• Effiziente Datenhaltung über die Zeit
• Optimizer Statistiken
• Backup
Wozu überhaut ein Data Warehouse?
• Auf Daten zentral zugreifen
Einfache Verwaltung, (fast) alle Unternehmensdaten liegen
einheitlich und integriert vor
• Nach Themen sortieren
Geschäftsobjekte in einem für alle verständlichen Kontext
bereitstellen
• Historische Daten sammeln
Änderungen über die Zeit verfolgen, Trends berechnen
• Neue Daten, Referenzdaten und „weiche“ Daten
Entlastung operativer Systeme, wenn Reports und Analysen in ein
eigenes System verlegt werden
Unternehmensweit gültige Daten
• Als zentraler Knotenpunkt liefert das Data
Warehouse geprüfte Daten für jegliche
Kennzahlberechungen
• Flexibilität sowohl für die Fachbereiche als auch für
die IT wird durch eine unternehmensweit gültige
Architektur möglich
Schnittstellen und Performance
• Das Data Warehouse ist die definierte Schnittstelle zu
sämtlichen Daten für alle Report-Anfragen
• Gleichzeitig sind durch das Warehouse alle Schnittstellen
zu sämtlichen Quellsystemen dokumentiert
• Die Performance der Quellsysteme bleibt vom
Reporting unbeeinträchtigt
• Eine gute Performance des Data Warehouse sichert die
Performance im Bereich der Auswertungen (BI, Statistik)
Eine Zugriffs- und Bewegungssprache
Data
Mart
CRM
SQL
SQL
Staging
Area
SQL
Stage
Warehouse
SQL
SQL
SQL
XML
Data
Mart
ERP
SQL
XML
SQL
SQL / XML
SQL
XML
SQL
XML
Data
Mart
SQL
XML
Vorteil: Alles mit einer Sprache – mit SQL
• SQL ist ein weit verbreiteter Standard
• Breite Palette an Werkzeugen
• Breites Wissen am Markt
• Während Design und Betrieb gibt es keine
Reibungsverluste durch „Brüche“ mit anderen Systemen
• SQL ist eine mächtige mengenbasierte Sprache
• Kennzahlen direkt in der Datenbank berechnen, deren Engine für
solche Rechnungen genau ausgelegt ist
• Große Bandbreite an fertigen, integrierten SQL-Funktionen
Agenda
• Die Motivation hinter einem Data Warehouse
<Insert Picture Here>
• Konzept und Design
• Drei in Eins: Das Schichtenmodell
• Über die Rolle der Fachanwender
• Laden der Daten
• Eine Auswahl an geeigneten Methoden
• Parallelisierung nutzen
• Betrieb des Warehouse
• Effiziente Datenhaltung über die Zeit
• Optimizer Statistiken
• Backup
3-Schichten-Modell auf einen Blick
Neutral gegenüber
Vorsystemen,
Sprachen, OS
Anwendungsneutral,
granular,
Zeit-neutral
Neutral gegenüber
Endbenutzern:
Alle Nutzer / Werkzeuge
Aufgaben der Staging Area
• Konsolidierung der verschiedenen Quellen
(Schnittstellen-Thematik)
• Fehler abfangen während des Ladevorgangs
• Datenanalyse
• Datenbereinigung
• Kann bereits für Abfragen verwendet werden
• Dient als temporäre Datenablage, bis die Daten im
übergeordneten Datenmodell korrekt geladen sind
• Daten aus Quellsystemen laden
• Batch-Betrieb oder laufend als „Mini-Batch“
• Parallelisierung
• Constraints
Varianten von Prüfungen
Attributbezogen
• NOT NULL / Pflichtfelder
• Formatangaben
• Check Constraint
• Wertbereiche
•
•
Ober- / Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Rekursive Zusammenhänge
•
•
•
•
Ober- / Untergrenzen / Wertelisten
Satzbezogen (Tupel)
• Abhängigkeiten von Werten in
anderen Attributen desselben Satzes
Satzübergreifend (Relationen)
• Primary Key / Eindeutigkeit
• Aggregat-Bedingungen
•
•
Tabellenübergreifend (interrelational)
• Foreign Key
• Aggregat-Bedingungen
Verweise auf andere Sätze derselben
Tabelle (Relation)
Ober- / Untergrenzen von Summen
Anzahl Sätze pro Intervall usw.
Rekursive Zusammenhänge
•
Verweise auf Sätze einer anderen
Tabelle (Relation)
Zeitbezogen (Tupel)
• Zeitinvariante Inhalte
•
•
•
Anz. Bundesländer
Zeitabhängige Veränderungen
Über die Zeit mit anderen Daten
korrelierende Feldinhalte
Verteilungsbezogen
• Arithmetische Mittel
• Varianz / Standardabweichungen
• Qualitätsmerkmale und Mengen
Der Foundation Layer
• Aufbau eines geschäftsneutralen, übergreifenden
Datenmodells
• Üblicherweise in der 3. Normalform
• Dient der Flexibilität bei Anfragen der Fachbereiche
• Enthält nur endgültig korrekte Daten, die von der
Staging Area geladen werden
• Archivierung von Daten (Historie pflegen)
• Wenn Änderungen des Datenmodells nötig werden,
lassen sich diese im Foundation Layer leichter
nachvollziehen
Datenmodelle aufbauen
Normalisierung
Denormalisierung
• Abstrakte, informationstechnische Sicht
• Viele kleine Tabellen
Schneller Zugriff für viele
Benutzer
• Geschäftsobjekt-orientiert
• Fachlich verständlich
• Auf mehrere Tabellen verteile
Daten
Gut für Online-Masken
• Einfach und intuitiv verständlich
(auch für den Fachbereich)
• Oft viele Joins
• Tendenz zu aufwendigem SQL
• Wenige Joins
• Oft einfaches SQL
Prinzip Normalisieren / Denormalisieren
Quelldaten
Normalisierte
Daten
Neue migrierte
Daten
Produktsparten
PRODUKTDATEN
PD4711
AMKLB
9987865234
7769
0000000
KLAUBAUTER
IIO
???
EERWEERW
883466
888750000
888000
EU-Wert
735328567353654
i8886345
7746
Müll und Altlast
Spartenname
Spartennr
Produktdaten
Produktename
Produktgruppen
Produktenr
Gruppenname
Einzelpreis
Gruppennr
Gruppenname
Gruppennr
Produkte
Produktename
Produktenr
Einzelpreis
Spartenname
Spartennr
Regeln für künstliche Schlüssel
• Schlüssel sind einfach zu benutzen und kurz, um
• Speicherplatz zu sparen
• Fehler zu vermeiden
• Nach Möglichkeit keine zusammengesetzten Schüssel
• Erfordert beim Zugriff unnötig viel Vorwissen zu den einzelnen
Schlüsselbestandteilen
• Schlüsselbestandteile können leicht NULL-Wert annehmen,
die Eindeutigkeit ist gefährdet
• Keine Felder wählen, die NULL werden können
• Spaltenwerte sollten stabil sein und sich nicht mehr
ändern
Bilden von künstlichen Schlüsseln
Anwendung 1
Verkaufsregion
Einkommensgruppe
Wohnart
Data Warehouse
Berufsgruppe
Verkaufsregion
Anzahl Kinder
Einkommensgruppe
Alter
Wohnart
Name
...
Kunden_NR
PLZ
Ort
Anwendung 2
Kunden_NR
Tel
Partnernummer
PLZ
Dim_Kd_NR
Ort
Strasse
Partnernummer
Sequence
Neuer Schlüssel
Der Access und Performance Layer
• Geschäftsrelevanter Kontext für die Fachbereiche
• Unterschiedliche Data Marts für unterschiedliche
Anforderungen (Bereichs-spezifisch)
• Daten liegen in denormalisierter Form und
entsprechend transformiert vor
• Aggregierte Daten, andere Kalkulationen usw.
• Relational oder „Würfel“
• Zugriffstrukturen sind für effiziente Verarbeitung
komplexer Statements optimiert
• Partitionierte Objekte
• Vorberechnete Daten
• Joins
Multidimensionales Modell: Star Schema
V1
V2
V3
V4
Einstiegspunkte
für Abfragen
Produkttabelle
P1
P2
P3
P4
Prod1
Prod3
Prod5
Prod6
Lief1
Lief4
Lief5
Lief9
Maier
Müller
Schmid
Engel
1
:
n
Verkäufe
1:n
P1
P2
P3
P4
R1
R2
R3
R4
Verkäufer
Z1
Z2
Z3
Z4
V1
V2
V3
V4
Zeit
4
4
9
8
n:1
Z1
Z2
Z3
Z4
n
:
1
R1
R2
R3
R4
München
Berlin
Hamburg
Frankfurt
Regionen
6.7.09
7.7.09
8.7.09
9.7.09
Q3
Q3
Q3
Q3
Beispiel einer Star Query Transformation
Tabellen und Daten
Faktentabelle Umsatz
Betrag
ID
Reg_ID
Kd_ID
Prod_ID
Zeit_ID
<Sequence>
<NUMBER>
<NUMBER>
<NUMBER>
<NUMBER>
<NUMBER>
0001
42.50
02
02
201004
0002
0003
9.99
51.31
03
02
010908
500030
071269
04
01
201004
201004
Dim_Produkt
ID
01
02
03
04
Prd_Kat
Buch
CD
Video
DVD
4 Ausprägungen
für Prd_Kat
Dim_Region
ID
01
02
03
04
Region
Nord
Sued
West
Ost
4 Ausprägungen
für Region
Dim_Zeit
Zeit
ID
201001 Jan_2010
201002 Feb_2010
Dim_Kunde
KdName
ID
000001 Müller
000002 Schulz
201003 Mär_2010
201004 Apr_2010
000003 Fritsch
4 Ausprägungen für
Zeit (fiktives Bsp.!)
000005 Ahrens
000004 Heinze
Beispiel einer Star Query Transformation
Verwendete Bitmap-Indizes
Faktentabelle Umsatz
Betrag
ID
Reg_ID
Kd_ID
Prod_ID
Zeit_ID
<Sequence>
<NUMBER>
<NUMBER>
<NUMBER>
<NUMBER>
<NUMBER>
0001
42.50
02
02
201004
0002
0003
9.99
51.31
03
02
010908
500030
071269
04
01
201004
201004
Bitmap-Indizes auf den FK-Spalten
der Faktentabelle (hier nur für die
WHERE-Prädikate)
01 02 03 04
01 02 03 04
0 1 0 0
0 0 1 0
0 1 0 0
0 1 0 0
0 0 0 1
1 0 0 0
Bitmap_Idx Reg_ID
Bitmap_Idx Prod_ID
4 Ausprägungen
201001 201002 201003 201004
0
0
0
0
0
0
0
0
0
Bitmap_Idx Zeit_ID
1
1
1
Beispiel einer Star Query Transformation
Schritt 1 und 2
01 02 03 04
01 02 03 04
Bedingungen der
WHERE Clause filtern,
wiederholte Lookups
auf den Dimensionstabellen
0 1 0 0
0 0 1 0
0 1 0 0
0 1 0 0
0 0 0 1
1 0 0 0
201001 201002 201003 201004
Bitmap_Idx Reg_ID Bitmap_Idx Prod_ID
Aus dem SELECT Statement:
...WHERE P.Prd_Kat='CD'‚
AND R.Region='SUED'‚
AND Z.Zeit='APR_2010'; *
0
0
0
Bitmap_Idx Zeit_ID
Passender Bitmap Stream
Passender Bitmap Stream
ID
01
02
03
04
Region
Nord
Sued
West
Ost
Dim_Produkt
ID
01
02
03
04
Prd_Kat
Buch
CD
Video
DVD
Dim_Zeit
Zeit
ID
201001 Jan_2010
201002 Feb_2010
201003 Mär_2010
201004 Apr_2010
* Transformation: .. WHERE (SELECT Prd_Kat FROM Dim_Produkt WHERE Prd_Kat IN('CD')) AND ..
( Bildung von Sub-SELECTs)
1
1
1
Passender Bitmap Stream
Dim_Region
Lookups
0
0
0
0
0
0
Beispiel einer Star Query Transformation
Schritt 3 und 4
01 02 03 04
01 02 03 04
201001 201002 201003 201004
Bitmap Merge der
0 1 0 0 AND 0 1 0 0 AND 0
1
0
0
herausgefilterten
Bitmap Streams,
0 0 1 0 AND 0 0 0 1 AND 0
1
0
0
Zugriff auf die Fakten0 1 0 0 AND 1 0 0 0 AND 0
1
0
0
tabelle, Anwendung
von Funktionen
Bitmap_Idx Reg_ID Bitmap_Idx Prod_ID
Bitmap_Idx Zeit_ID
(hier sum(..))
TRUE
Für den Ausschnitt an Daten in diesem
FALSE
Beispiel wird nur der erste Datensatz
FALSE
als Ergebnis zurückgeliefert
Faktentabelle Umsatz
Betrag
ID
Reg_ID
Kd_ID
Prod_ID
Zeit_ID
<Sequence>
<NUMBER>
<NUMBER>
<NUMBER>
<NUMBER>
<NUMBER>
0001
42.50
02
02
201004
0002
0003
9.99
51.31
03
02
010908
500030
071269
04
01
201004
201004
[Schritt 5]
Join Back auf Dimensionstabellen für evtl. weitere angeforderte Spalten im SELECT Statement
Materialized Views
Verkäufe Faktentabelle
R1
R2
R3
R4
Z1
Z2
Z3
Z4
Partition
Woche 2
P1
P2
P3
P4
R1
R2
R3
R4
Z1
Z2
Z3
Z4
V1
V2
V3
V4
4
4
9
8
V1
V2
V3
V4
4
4
9
8
Materialized View (MAV)
Summe aller Verkäufe für
alle Produkte
der letzten 8 Wochen
aller Verkäufer
in Region 1 bis 8
..
Partition
Woche 1
P1
P2
P3
P4
..
Refresh
Partition
-NEUWoche 8
P1
P2
P3
P4
R1
R2
R3
R4
Z1
Z2
Z3
Z4
V1
V2
V3
V4
4
4
9
8
P1
P2
P3
P4
R1
R2
R3
R4
Z1
Z2
Z3
Z4
V1
V2
V3
V4
P5
P6
P7
P8
R5
R6
R7
R8
Z5
Z6
Z7
Z8
V5
V6
V7
V8
8
8
18
16
8
8
18
16
Veränderte
Werte
..
Kennzahlenbäume mit Nested MAVs
Anstelle von Lade-Operationen
Umsatz Prod. Gr B relativ zum Gesamtjahresumsatz
Summierung/Jahr
Umsatz Prod.Gr A
Umsatz Prod.Gr B
Summierung/Monat
Aufwendige
Join-Operation
DIM_Zeit
FAKT_Umsatz
Materialized View
Level 4
Materialized View
Level 3
Materialized View
Level 2
Materialized View
Level 1
DIM_Produkte
Basistabellen
Funktion EXPLAIN_REWRITE
MAV-Definition
CREATE MATERIALIZED VIEW MV_UMS_ART_Zeit_Join
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
a.artikel_id ID,
FROM
f_umsatz u,
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id;
SELECT mv_name, message FROM rewrite_table;
MV_UMS_ART_ZEIT_JOIN
QSM-01150: Abfrage wurde nicht umgeschrieben
DBMS_MVIEW.EXPLAIN_REWRITE
begin
dbms_mview.explain_rewrite('
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID
FROM
zu analysierende
f_umsatz u,
Abfrage
d_artikel a,
d_zeit z
WHERE
a.artikel_id = u.artikel_id AND
u.zeit_id = z.datum_id
GROUP BY
z.jahr_nummer, z.monat_desc,
a.artikel_id', 'MV_UMS_ART_Zeit_Join');
end;
MAV
MV_UMS_ART_ZEIT_JOIN
QSM-01082: Materialized View, MV_UMS_ART_ZEIT_JOIN, kann nicht mit Tabelle, F_UMSATZ,
verknüpft werden
MV_UMS_ART_ZEIT_JOIN
QSM-01102: Materialized View, MV_UMS_ART_ZEIT_JOIN, erfordert Join zurück zu Tabelle, F_UMSATZ,
in Spalte, UMSATZ
Funktion EXPLAIN_MVIEW
• Zeigt auf, welche Funktionen für eine jeweilige MAV
genutzt werden kann, z.B. Refresh-Varianten
EXECUTE dbms_mview.explain_mview(_
'SELECT sum(u.umsatz),a.artikel_name _
FROM f_umsatz u, d_artikel a _
WHERE a.artikel_id = u.artikel_id _
GROUP BY a.artikel_name');
MAV-Definition
Multidimensionales Modell: MOLAP
st
ca
re
Fo
et
dg
Bu
Szenario
T
IS
Umsätze
Kennzahlen
Zelle
Kosten
Mar
Zeit
Feb
Jan
Deckungsbeitrag in %
Integrierte SQL-Funktionen
• LAG / LEAD: Vorgänger und Nachfolger
• FIRST / LAST und MIN / MAX
• ROW_NUMBER: eindeutige Zahl z.B. für TOP-n
• RANK: Rangfolge
• RATIO_TO_REPORT: Verhältnis zur Summe
• AVG, SUM: Aggregierungs-Funktionen
• NTILE: Aufteilung in n Teile
• PERCENTILE_CONT/_DISC: Median-Berechnung
• STDDEV: Standardabweichung
(Komplette Liste aller SQL-Funktionen: SQL Language Reference Guide 11gR2, Kapitel 5, “Aggregate Functions” und “Analytic Functions”)
OLAP als Erweiterung zum Star Schema
• Für manche Kennzahlen ist die Berechnung auf reiner
SQL-Ebene nicht mehr performant genug
• OLAP speichert die Daten in einer Form, welche die
Verarbeitung besonders von rechenintensiven
Kennzahlen und Prognosen beschleunigt
• Vorberechnung des gesamten Würfels oder Teilen davon
• Die Datenbank berechnet zuerst diejenigen Werte, welche die
längste Verarbeitungszeit benötigen
• Obwohl in der Datenbank als eigenes Format definiert,
lässt sich OLAP trotzdem mit SQL bedienen
• Auch MAVs können mit OLAP arbeiten (ab 11g)
Sinn und Zweck der OLAP-Technologie
• Das multidimensionale OLAP-Speicherformat ist
speziell für Analyse-Funktionen optimiert
Mit “OLAP” ist im Folgenden die Oracle OLAP-Option gemeint
im Unterschied zum “relationalen OLAP”, welches durch ein
Star oder Snowflake Schema repräsentiert wird
• Komplexe Analysen lassen sich relational nicht effizient
abbilden
• Mehrfaches Verarbeiten derselben Daten mehrerer
Dimensionen während der Berechnungen
• Daten werden z.T. temporär in Struktur und Inhalt geändert, um
Analyse-Kriterien zu genügen
• Temporäre Änderungen sollen nicht in der Datenbank sichtbar
werden
BI-Funktionalität in der Datenbank
OLTP /
3NF DWH
Star Schema
Standard
Reporting
Ad-Hoc
Reporting
Ad-Hoc
Analysis
“Analyse-freie
Zone”
Ad Hoc-Abfragen
Ad Hoc-Analysen
Zeitreihen
Shares/Indizes
“Analyse in
Denkgeschwindigkeit”
OLAP
Planning
Vorhersagbare
Analysen
Statistisches Forecasting
Budgetzuordnungen
Advanced Aggregations
Modellberechnung
Wirtschaftsbezogene und
selbstdefinierte Funktionen
OLAP TransaktionsModell
Oracle 11g: Cube-Organized MAVs
PRODUCT
SQL
item_id
subcategory
category
type
CUSTOMER
cust_id
city
state
country
TIME
SALES
day_id
prod_id
cust_id
chan_id
quantity
sales
Automatisches
Query Rewrite
day_id
month
quarter
year
CHANNEL
chan_id
class
Materialized View
Refresh
OLAP relational: Dimension Views
• Für jede Dimension gibt es eine Dimension View und
eine Hierarchy View
• Eine Zeile für jedes
DimensionsElement
• Keine Hierarchiespezifischen
Spalten
channel_view
channel_sales_channel_view
dim_key
level_name
long_desc
short_desc
dim_key
parent
level_name
long_desc
short_desc
all_channels_long_desc
class_long_desc
channel_long_desc
channel_all_channels_id
channel_class_id
channel_channel_id
all_channels
all_channels_long_desc
channel_all_channels_id
class
class_long_desc
channel_class_id
channel
channel_long_desc
channel_channel_id
• Eine Zeile für
Dimensions-Elemente
innerhalb der Hierarchie
• Hierarchie-spezifische
“Parent”-Spalte
Channel
OLAP relational: Cube Views
• Jeder Cube ist eine einzelne View
• Dimensionen entsprechen den Schlüsseln, Kennzahlen den
Datenspalten
sales_cube_view
time
product
geography
channel
sales
quantity
sales_ytd
sales_ytd_pr_year
sales_pr_year
sales_pr_period
sales_share_tot_chan
sales_share_prnt_chan
sales_rank_by_prod_lv
sales_3_per_mov_avg
sales_3_per_mov_tot
…
sales_cube
forecast_view
keys
time
product
geography
measures
best_fit
linear_regression
measures
forecast_cube
Cube Views für SQL-Abfragen nutzen
Summe aller Verkäufe
Fact Table Query:
SELECT SUM(sales)
FROM sales_fact;
Cube View Query:
SELECT sales
FROM sales_cube_view
WHERE product = 'ALL_PRODUCTS'
AND geography = 'ALL_REGIONS'
AND channel = 'ALL_CHANNELS'
AND time = 'ALL_YEARS'
• Der Cube hat bereits den summierten Wert
Kein Full Table Scan nötig
• “ALL”-Bedingungen für jede Dimensions-Spalte
bringen den größten Mehrwert der bereits summierten
Werte im Cube
Der Fachbereich wirkt beim Design mit
• Der Fachbereich legt fest, welche Kennzahlen
berechnet werden sollen
• Einfluss auf das Design des Daten-Modells:
•
•
•
•
Welche Daten gehören logisch zusammen (Geschäftsprozess)
Welche Daten sind Stammdaten, welche Bewegungsdaten
Welche Daten können weggelassen werden
Welche mathematischen/analytischen Funktionen kommen zum
Einsatz
• Fachanwender sind für die Datenqualität
mitverantwortlich
• Definieren Qualitätskriterien
• Liefern valide Daten
Eigenschaften und Objekte trennen
Wohnsituation
Artikel
Privat- zu Firmenkunden
regionale Schwerpunkte
Berufsgruppe
Kundensegmente
Umsatz
• Was ist ein Attribut? / Was ist eine Entity?
• Lassen sich Objekte spezifizieren / generalisieren?
• Über die Prozess-Sicht Bewegungsdaten und
Stammdaten ermitteln
Analysemodell – komplex
Was wissen wir über den Prozess?
Produkte
Handwerker
bietet an
beauftragt
Dienstleistungen
Kunden
Firmen
bietet an
verkauft
Lieferanten
Service GmbH
holt ab
Privat
Kundenkarte
bestellt
storniert
beauftragt
liefert aus
Spediteur
beliefert
Lager
holt
stornierte
Ware ab
liefert ab
Geschäftsprozess: Bestellungen
Bewegungs- und Stammdaten finden
Status
Bestellprozess
Beschaffung
offene
Posten
Kundendaten
prüfen
MAX/MIN
Menge
Kreditlimit
prüfen
Kundenstamm
Verfügbarkeit
prüfen
Kundenstamm
Spediteur
beauftragen
Bestellung
anlegen
Produktestamm
Bestellung
Best_Pos
Lieferschein
Dienstleistung
beauftragen
Vertrag
Bestellsatz
updaten
Liefersatz
anlegen
Bestellung
Best_Pos
Lieferung
Objektmodell
Generalisierungen und Spezialisierungen
Bestellung
Kunde
tätigt
Kontaktperson
KD#...
Bestelldatum
Lieferdatum
Sparte
...
Externe
Personen
Status
Generalisierung
Spezialisierung
Status
Artikel
Privatkd
...
Gruppe
Firmenkd
...
...
Name
Gruppe
Sparte
...
• Kandidaten für Dimensions-Hierarchien finden
• Schlüsselpaare PK / FK für spätere Fakten-Joins
Agenda
• Die Motivation hinter einem Data Warehouse
<Insert Picture Here>
• Konzept und Design
• Drei in Eins: Das Schichtenmodell
• Über die Rolle der Fachanwender
• Laden der Daten
• Eine Auswahl an geeigneten Methoden
• Parallelisierung nutzen
• Betrieb des Warehouse
• Effiziente Datenhaltung über die Zeit
• Optimizer Statistiken
• Backup
Kopiertechniken beim Laden
•
•
•
•
•
•
•
•
•
•
Trigger in Quelltabelle
SQL*Loader und Data Pump
External Tables
Transportable Tablespaces
Change Data Capture
Golden Gate
Multi-Table Insert
MERGE
Table Functions
Partition Exchange Load
SQL*Loader – Empfehlungen
• Direct Path Load nutzen
• Alle Integrity Constraints ausschalten
• NOT NULL, Unique und Primary Key Constraints
• Verhindern von Index-Aktualisierungen
• UNRECOVERABLE Option wählen
• Partitionen nach und nach laden
• Andere Partitionen bleiben für andere Benutzer im Zugriff
• Parallel laden, wenn es möglich ist
• Nutzung paralleler Schreib-Threads
• Alternativ parallele Jobs starten
Beispiel: Control File für SQL*Loader
OPTIONS (SKIP=1, BINDSIZE=50000, ERRORS=50, ROWS=200, DIRECT=TRUE,
PARALLEL=TRUE, READSIZE=65536, RESUMABLE=TRUE,
RESUMABLE_TIMEOUT=7200)
UNRECOVERABLE LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'C:\orte.csv'
BADFILE 'orte.bad'
DISCARDFILE 'orte.dis‚
INTO TABLE dwh.tb_orte WHEN ort_id != BLANKS
APPEND
REENABLE DISABLED_CONSTRAINTS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
(orte_nr POSITION(1) INTEGER EXTERNAL ,
ort CHAR ,
plz CHAR ,
bundesland CHAR ,
region CHAR ,
nummernfeld INTEGER EXTERNAL )
External Tables
• Eine External Table ist eine Tabelle, die eine Datei
referenziert
• Datei wird als normale Tabelle behandelt
• Nur lesend zugreifbar
• RMAN sichert nicht die Daten
• Bulk Loads wie INSERT..SELECT möglich
• Mehr Transformationsoptionen
als im SQL* Loader
• Parallelisierbares Lesen
• Alternative zum SQL*Loader
External Tables mit Data Pump
• Erstellen einer External Table im Quellsystem
• Verwendung von CREATE AS SELECT * FROM
<source_table>
• Das Ausführen des CREATE startet den Data Pump Export
• Kopieren der Dump-Datei auf die Zielumgebung
• In der Zielumgebung neue External Table Definition
erstellen und aktivieren
• Durch Zugriff mit SELECT auf die External Table die
Daten lesen
Ablauf des Exports bei Data Pump
expdp dwh/dwh@o11 parfile=dp_export_param.txt
impdp dwh2/dwh2@o11 DIRECTORY=DP_OUT
DUMPFILE=EXP1.DMP
LOGFILE=DP_OUT:imp_log
REMAP_SCHEMA=DWH:DWH2
• Export über Parameter-Datei
• Export auch mit Remote-Zugriff
• Einschränkung der Datenmenge
durch QUERY
• Bei dem Import: REMAP auf das
Schema
Preprocessing für External Tables in 11g
CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER
...)
ORGANIZATION external (TYPE oracle_loader
DEFAULT DIRECTORY data_file_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
PREPROCESSOR exec_file_dir:'gunzip' OPTIONS '-C'
BADFILE log_file_dir:'sh_sales.bad_xt'
LOGFILE log_file_dir:'sh_sales.log_xt'
FIELDS TERMINATED BY "|" LDRTRIM
( PROD_ID, CUST_ID, TIME_ID DATE(10) "YYYY-MM-DD",
CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
UNIT_COST, UNIT_PRICE))
LOCATION ('sh_sales.dat.gz'))
REJECT LIMIT UNLIMITED;
Multi-Table Insert
INSERT ALL
WHEN STATUS = 'P'‚
THEN INTO WH_TRANS_PRIVAT
(BESTELLMENGE, KUNDENCODE, BESTELL_TOTAL, STATUS)
VALUES
(BESTELLMENGE$1, KUNDENCODE$1, BESTELL_TOTAL$1, STATUS)
WHEN STATUS = 'F'‚
THEN INTO WH_TRANS_FIRMA
(BESTELLMENGE, KUNDENCODE, BESTELL_TOTAL, STATUS)
VALUES
(BESTELLMENGE$1, KUNDENCODE$1, BESTELL_TOTAL$1, STATUS)
SELECT WH_TRANSAKTIONEN.BESTELLMENGE BESTELLMENGE$1,
WH_TRANSAKTIONEN.KUNDENCODE KUNDENCODE$1,
WH_TRANSAKTIONEN.BESTELL_TOTAL BESTELL_TOTAL$1,
WH_TRANSAKTIONEN.STATUS STATUS
FROM
WH_TRANSAKTIONEN WH_TRANSAKTIONEN
WHERE
(WH_TRANSAKTIONEN.STATUS = 'P' /*SPLITTER.PRIVATKUNDEN*/)
OR
(WH_TRANSAKTIONEN.STATUS = 'F' /*SPLITTER.FIRMENKUNDEN*/);
MERGE
• Funktion MERGE dient
dem gleichzeitigen
INSERT und UPDATE
• Basierend auf dem
Matching des
definierten Schlüssels
(ON-Klausel)
• Auch DELETEOperationen möglich
MERGE INTO kunde_tgt
USING
(SELECT
kunden_stamm.kundennr kundennr,
kunden_stamm.vorname vorname,
kunden_stamm.nachname nachname,
kunden_stamm.status status,
kunden_stamm.strasse strasse,
kunden_stamm.telefon telefon,
kunden_stamm.telefax telefax
FROM
kunden_stamm kunden_stamm) merge_subquery
ON (kunde_tgt.kundennr = merge_subquery.kundennr)
WHEN NOT MATCHED THEN
INSERT
(kunde_tgt.kundennr,
kunde_tgt.vorname,
kunde_tgt.nachname,
kunde_tgt.status,
kunde_tgt.strasse,
kunde_tgt.telefon,
kunde_tgt.telefax)
VALUES
(merge_subquery.kundennr,
merge_subquery.vorname,
merge_subquery.nachname,
merge_subquery.status,
merge_subquery.strasse,
merge_subquery.telefon,
merge_subquery.telefax)
WHEN MATCHED THEN
UPDATE SET
vorname = merge_subquery.vorname,
nachname = merge_subquery.nachname,
status = merge_subquery.status,
strasse = merge_subquery.strasse,
telefon = merge_subquery.telefon,
telefax = merge_subquery.telefax
DELETE WHERE merge_subquery.status=-1;
Table Functions: Pipelined Transformation
Merge
Multiple
Inserts
External Table
Table Function
Validate
Tabellen
Flat Files
Transform
Lookup
Aggregate
Analytische
Funktionen
Ein Schritt, parallelisierbar, mengenbasiert
Mengenbasiert trotz Programmierung
Table Functions
INSERT INTO Table
SELECT Feld1, Feld2
FROM Table_Function(
Funktion
Cursor
Fetch
Loop
Variante 1
Variante 2
If a = b...
Update...
Case...
pipe
row(record Type)
Return Table
)
• Schnelle Verarbeitung
(Pipelined)
• Objekttechnik
• Parallelisierung
• Mehrere
Rückgabewerte
und Einzelrückgaben
• Cursor als Input
• Schachtelbar
Beispiel einer Table Function
CREATE OR REPLACE FUNCTION f_bestellung_x(cur cursor_pkg.refcur_t) Übernahme von Ausgangssätzen
als Cursor
RETURN bestellung_x_t_table IS
bestellnr
NUMBER(10);
kundencode
NUMBER(10);
bestelldatum
DATE;
lieferdatum
DATE;
bestell_total
NUMBER(12,2);
fehler_datum,
DATE;
order_id
NUMBER(10);
Definieren einer Nested-Table-Struktur
objset bestellung_x_t_table := bestellung_x_t_table();
für die spätere Rückgabe.
i number := 0;
BEGIN
LOOP
Lesen aus Cursor
-- read from cursor variable
FETCH cur INTO bestellnr, kundencode, bestelldatum, lieferdatum, bestell_total, order_id;
-- ext when last row
EXIT WHEN cur%NOTFOUND;
i := i+1;
IF substr(to_char(lieferdatum,'YYYY.MM.YY'),1,4) > 2002 THEN
fehler_datum := to_date('9999.12.12','YYYY.MM.DD');
Erweitern Nested-Table um einen
ELSE fehler_datum := lieferdatum;
Satz und Überführen eines Satzes in
END IF;
die Nested-Table
objset.extend;
objset(i) :=
bestellung_x_t(bestellnr,kundencode,bestelldatum,lieferdatum,bestell_total,fehler_datum);
END LOOP;
CLOSE cur;
Rückgabe der kompletten Tabelle an
RETURN objset;
das aufrufende Statement
END;
(Alternative zu PIPE).
Aufruf der Table Function
INSERT INTO bestellung_x
SELECT * FROM
TABLE(f_bestellung_x(CURSOR(SELECT * FROM bestellung)))
SELECT * FROM
TABLE(f_bestellung(CURSOR(SELECT * FROM bestellung)))
SELECT count(*) FROM
TABLE(f_bestellung(CURSOR(SELECT * FROM bestellung))
Partition Exchange Load Verfahren
Buchhaltung
Produktion
Personal
Lager
Lieferanten
Temp Tabelle
P1
P2
P3
P4
Z1
Z2
Z3
Z4
4
4
9
8
Time
Monat 13
Marketing
Service
Neuer Monat
Kein Index / Constraint
Parallel Direct Path Insert (Set Based)
CREATE TABLE AS SELECT (CTAS)
Monat 12
Monat 11
Monat 10
Region
Faktentabelle
Indizes und DDL
• Bei DDL-Operationen auf Partitionsebene können
globale Indizes UNUSABLE werden
• Mit MERGE, SPLIT, MOVE, DROP, TRUNCATE
• Lösung I: Globalen Index mitpflegen
Tabellen Tabellen
Partition Partition
• ALTER TABLE T1 DROP PARTITION P1
UPDATE GLOBAL INDEXES
• Operation dauert länger
• Lösung II: Index-“Reparatur” (sämtliche Index-Partitionen)
• Kommando ALTER INDEX ... REBUILD [ONLINE]
• Parameter SKIP_UNUSABLE_INDEXES beachten (seit 10g)
• Für lokale Indizes existiert eine optionale Klausel, um
die Speicherung zu beeinflussen
ALTER TABLE part1
MOVE PARTITION p1 TABLESPACE new_tbsp UPDATE INDEXES
(my_parts_idx (PARTITION p1 TABLESPACE my_tbsp));
Rebuild Index Operation
ALTER INDEX index_name REBUILD [ NOLOGGING ];
• Schneller als DROP / CREATE, besonders bei nicht-partitionierten Indizes
• NOLOGGING-Klausel einsetzen (weniger Redo-Informationen)
• Fragmentierung wird beseitigt
• Nach Änderungsaktivitäten
• Freier Platz wird “richtig” freigegeben
• Im DWH werden Änderungen aber oft als Batch-Lauf durchgeführt
Zunächst DROP INDEX (beschleunigt den Batch-Lauf)
Dann Neuerstellen des Index
Ab Oracle 11.2: UNUSABLE Indizes geben automatisch ihre Segmente frei
und können mit REBUILD wieder aufgebaut werden
Parallelisierungsgrad manuell bestimmen
Hoch
• SELECT /*+ PARALLEL(b)n PARALLEL(a)n */
a,b,c FROM bestellung b, artikel a;
• Setzen von Hints
• Eher vor 10g... (Optimizer)
• ALTER SESSION FORCE PARALLEL QUERY;
• Als Sessionparameter
• ALTER TABLE <table_name> PARALLEL n;
• Als Tabelleneigenschaft
Priorität des definierten DOP
• Festlegung im Resource Manager
Niedrig
Funktionsweise von Automated DOP
SQL
Statement
Statement wird geparsed
Optimizer ermittelt
Geschätzte Ausführung ist
Execution Plan
größer als Schwellwert
Geschätzte Ausführung ist
kleiner als Schwellwert
Optimizer bestimmt
idealen DOP
Tatsächlicher DOP
= MIN(Default DOP, idealer DOP)
PARALLEL_MIN_TIME_THRESHOLD
Statement wird
seriell ausgeführt
Statement wird
parallel ausgeführt
Arbeitsweise von Parallel Stmt. Queuing
SQL
Statements
Statement wird geparsed
Oracle ermittelt
automatisch den DOP
Wenn zu wenig Parallel Server vorhanden
sind, landet das Statement in der Queue
64
32
64
16
32
128
16
FIFO Queue
Sind genügend Parallel
Server vorhanden, wird
erstes Statement aus der
Queue ausgeführt
Wenn genügend Parallel
Server vorhanden sind, wird
das Statement sofort
ausgeführt
8
128
Parallel Statement Queuing
SQL Monitoring im Enterprise Manager
Klicken auf
SQL ID für
weitere
Informationen
Uhrsymbol
zeigt ein
wartendes
Statement
an
Awaiting screen
shot from EM
Parallel Statement Queuing
SQL Monitoring im Enterprise Manager
Wait Event
zeigt an,
dass das
Statement
am Anfang
der Queue
steht
Parameter für Parallel Query in 11.2
Neue Parameter
•
•
•
•
•
parallel_degree_limit = CPU
parallel_degree_policy = MANUAL
parallel_force_local = FALSE
parallel_min_time_threshold = AUTO
parallel_servers_target = 8
(CPU|IO|integer)
(MANUAL|LIMITED|AUTO)
(FALSE|TRUE)
(AUTO|integer)
(0 - max_servers)
Parameter
•
•
•
•
•
•
•
parallel_adaptive_multi_user = TRUE
parallel_execution_message_size = 16384
parallel_instance_group = ''
parallel_max_servers = 20
parallel_min_percent = 0
parallel_min_servers = 0
parallel_threads_per_cpu = 2
(TRUE|FALSE)
(2148 – 32768)
()
(0 - 3600) pro Instanz
(1 - 100) %
(0 - max_servers)
(1 - 4|8) pro core
Veraltete Parameter
• parallel_automatic_tuning = FALSE
• parallel_io_cap_enabled = FALSE
(FALSE|TRUE)
(FALSE|TRUE)
Level der Systemauslastung
Anzahl ServerProzesse
100% Auslastung
Seriell und im Hintergrund
256
PARALLEL_MAX_SERVERS
Spielraum für
parallele Statements
PARALLEL_SERVERS_TARGET
128
ConcurrencyLevel
PARALLEL_DEGREE_LIMIT
0
Annahme: PARALLEL_DEGREE_LIMIT = 16 (mit 32 Prozessen)
Parameter-Hierarchie
PX Features:
• NONE
1.
PARALLEL_DEGREE_POLICY = MANUAL
a) Keiner der neuen Parameter wird genutzt
PX Features:
2.
3.
PARALLEL_DEGREE_POLICY = LIMITED
a)
PARALLEL_MIN_TIME_THRESHOLD = 10s
b)
PARALLEL_DEGREE_LIMT = CPU
• Auto DOP
Restricted
PARALLEL_DEGREE_POLICY = AUTO
a) PARALLEL_MIN_TIME_THRESHOLD = 10s
PX Features:
b)
PARALLEL_DEGREE_LIMT = CPU
• In-Memory
c)
PARALLEL_SERVERS_TARGET = 4 * CPU_COUNT *
PARALLEL_THREADS_PER_CPU (* ACTIVE_INSTANCES)
• Auto DOP
• Queuing
Full Partitionwise Join
Beispiel Range-Hash
SELECT sum(sales_amount)
FROM sales s, customer c
WHERE
Sales
s.cust_id = c.cust_id;
Customer
Range
partition May
18th 2008
Range
partition May
18th 2008
Sub part 1
Sub part 1
Sub part 1
Sub part 1
Sub part 2
Sub part 2
Sub part 2
Sub part 2
Sub part 3
Sub part 3
Sub part 3
Sub part 3
Sub part 4
Sub part 4
Sub part 4
Sub part 4
Beide Tabellen haben den
gleichen Parallelisierungsgrad
und sind nach der Join Column
partitioniert
Ein großer Join wird in
mehrere kleine Joins
unterteilt, wobei
parallelisiert über die
Partitions gearbeitet wird
Agenda
• Die Motivation hinter einem Data Warehouse
<Insert Picture Here>
• Konzept und Design
• Drei in Eins: Das Schichtenmodell
• Über die Rolle der Fachanwender
• Laden der Daten
• Eine Auswahl an geeigneten Methoden
• Parallelisierung nutzen
• Betrieb des Warehouse
• Effiziente Datenhaltung über die Zeit
• Optimizer Statistiken
• Backup
Über die richtige Anzahl an Platten
Performance optimieren
OLTP
DWH
• Typisch sind
5 IOPs pro Transaktion
• Erreichbar sind
• SAS: 80-100 IOPs
• SATA: 50
• Formel
• 200 MB Daten pro Core
• Erreichbar sind
20-30 MB/Sec pro Platte
• Anzahl Platten =
Anzahl Transaktionen pro Sek. * 5
--------------------------------------------Erreichbare IOPs pro Platte
• Formel
• Anzahl Platten =
Anzahl Cores * 200 / 20
Messung von I/O-Durchsatz
• Orion (ORacle IO Numbers)
•
•
•
•
•
Read/Write-Tests (Achtung: schreibt Daten auf Platte!)
Command Line Tool mit vielen Testoptionen
OLTP- und DWH-Workload werden nachgestellt
Ab 11.2 im /bin-Verzeichnis der DB
www.oracle.com/technology/software/tech/orion/index.html
• Calibrate_IO
• Read-Only Test
• Wenige Testoptionen
• Ab Version 11g verfügbar
• Beide Werkzeuge erfordern die Aktivierung von
asynchronous I/O
Calibrate_IO
• Nutzt Oracle Libraries statt beliebige Last-Generatoren
• Bestandteil des Ressourcen Managers
• Wird aus der DB heraus aufgerufen
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>,
<MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
END; /
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_resmgr.htm#CJGHGFEA
Performance und Systemzustand
Hilfsmittel beim Monitoring
•
•
•
•
•
•
•
•
•
PERFSTAT
Diverse Alerts
AWR Reports (Automatic Workload Repository)
ADDM (Automatic Database Diagnostic Monitor)
SQL Tuning
Statistiken
Histogramme
Proaktive Healthchecks
Support Workbench
Inkrementelle globale Statistiken
Sales
Table
May
18 2008
S1
May 19th 2008
S2
May 20th 2008
S3
May 21st 2008
S4
May 22nd 2008
S5
May 23rd 2008
S6
th
1. Partitions-Statistiken
werden gesammelt &
zusammengefasst
Global
Statistic
2. Globale Statistiken
werden durch die
Aggregation von
Partitionsinformationen
erzeugt
Sysaux Tablespace
Inkrementelle globale Statistiken
Sales
Table
May
18 2008
S1
May 19th 2008
S2
May 20th 2008
S3
May 21st 2008
S4
May 22nd 2008
S5
May 23rd 2008
S6
th
May 24th 2008
3. Eine neue Partition
wird hinzugefügt &
Daten fließen ein
6. Generierung der globalen
Statistiken durch Aggregation
der neuen PartitionsStatistiken
Global
Statistic
S7
4. Statistiken für neue
Partition sammeln
Sysaux Tablespace
5. PartitionsStatistikdaten aus
SYSAUX anfordern
Weitere Neuerungen für Statistiken
• Pending Statistics
• OPTIMIZER_USE_PENDING_STATISTICS=FALSE (Default)
• Verifizieren und publizieren (mittels DBMS_STATS Pack)
• Erweiterte Optimizer-Statistiken
• Verhinderung von falschen Kardinalitäts-Schätzungen für
Spalten, für die funktionale Abhängigkeiten bestehen
• Spalten, die oftmals gemeinsam in der WHERE-Klausel als Filter genutzt
werden
• Betrachtung von Spalten-Gruppen und deren Wechselwirkung:
Spalten-Abhängigkeiten wie Automarke und Typ, saisonale
Verkäufe etc.
• Bekanntgabe von Spalten, auf die in der WHERE-Klausel mit
einer Funktion gebraucht werden, z.B. to_upper()
http://st-curriculum.oracle.com/obe/db/11g/r1/prod/perform/multistats/multicolstats.htm
Konzept der Table Compression
Unkomprimierter Block
Lokale
Symboltabelle
Komprimierter Block
Mehr Daten pro
Block
Advanced Compression in Oracle 11g
Overhead
Free Space
Unkomprimiert
Komprimiert
Weitere Inserts sind
wieder nicht
komprimiert
Inserts sind nicht
komprimiert
Block Usage erreicht
PCTFREE
– Komprimierung wird
ausgelöst
Block Usage erreicht
PCTFREE
– Komprimierung wird
ausgelöst
Verwendung OLTP Table Compression
• Compression für existierende Tabelle einschalten
ALTER TABLE t2 COMPRESS FOR OLTP;
Keine Komprimierung für existierende Datensätze
• Existierende Tabelle komprimieren
ALTER TABLE … MOVE COMPRESS;
ALTER TABLE … MOVE COMPRESS FOR OLTP;
ALTER TABLE … MOVE PARTITION … COMPRESS;
Partitionen: Lokale Bitmap Indizes werden UNUSABLE
“Normale” Komprimierung mit PCTFREE=0,
Advanced Compression mit PCTFREE=10
Best Practices OLTP Table Compression
• Komprimieren der zehn größten Tabellen
• 20% der Tabellen verbrauchen 80% des Speicherplatzes
• Bessere Kompressionsraten mit größeren Blöcken
• Höhere Wahrscheinlichkeit mehrfacher gleicher Werte
• Sortiertes Laden nach nicht selektiven Spalten
• B-Tree Index Kompression
• Index validieren und INDEX_STATS analysieren
index_stats.opt_compr_count
liefert Prefix Länge N
index_stats.opt_compr_pctsave liefert Einsparung in %
CREATE INDEX idx_comp ON ... COMPRESS N;
• Bitmap Indexes sind an sich hoch komprimiert
• Geeignet für niedrige bis mittlere Kardinalität
Abschätzen der Compression Ratio
• Compression Advisor
(für Oracle 9i und höher, ab 11.2 im DB Control enthalten)
http://www.oracle.com/technology/products/database/compression/compression-advisor.html
Exadata: Hybrid Columnar Compression
• Compression Unit
• Eine logische Struktur, welche mehrere Datenblöcke umspannt
• Die Spaltenorganisation wird während des Ladens der Daten
durchgeführt
• Jede Spalte wird separat komprimiert
• Alle Spaltendaten für ein Datenset werden in einer Compression
Unit gespeichert
• Typische Größe von 32k (4 Blöcke x 8k Blockgröße)
Logical Compression Unit
BLOCK HEADER
CU HEADER
C1
C2
BLOCK HEADER
C3
BLOCK HEADER
C7
C4
C5
C6
BLOCK HEADER
C8
C8
Vergleich der Kompressionstypen
1000
500
Unkomprimiert
OLTP Compress
1000
500
Hybrid & Pure
Column
0
Unkomprimiert
OLTP
Pure
Column
50
Hybrid
Pure Column
0
Tabellengröße
100
0
Zeit eines Scans
Zeit zum Auffinden einer Zeile
• HCC ist eine spaltenorientierte Technologie der zweiten Generation, welche
die Vorteile der spalten- und zeilenorientierten Speicherung kombiniert
• Höchste Komprimierung – HCC reicht an rein spaltenorientierte Systeme heran
• Sehr gute Zeiten beim Scan – 93% so gut wie bei rein spaltenorientiert
• Gute Zeiten beim Auffinden einer einzelnen Zeile – eliminiert die Schwäche rein
spaltenorientierter Systeme
• Das Zeilenformat ist immer noch am geeignetsten für Workloads mit Updates
oder Trickle Feeds
Information Lifecycle Management (ILM)
• Archivierung der Daten in einem
rollierenden Verfahren
Jan 2009
• Backup der ältesten Partition
• Älteste Partition löschen
• Aktuellste Partition hinzufügen
Feb 2009
Mar 2009
• Einzelne Partitionen bzw. Tablespaces mit
Partitionen
• Auf READ ONLY setzen
• Tablespaces komprimieren und verschlüsseln
• Partitionen auf günstigen Storage verschieben
Apr 2009
May 2009
Jun 2009
:
Jan 2010
Eine eigene Backup/Recovery-Strategie
Warum für das Data Warehouse wichtig?
• Datenmengen sind viel größer
• Kosten für OLTP-analoge Verfahren zu hoch
• Geringerer Anspruch an Hochverfügbarkeit
• System kann auch mal 1 Tag oder länger nicht zur Verfügung
stehen
• Änderungsvorgänge finden kontrolliert statt
• Keine Online-Updates durch Benutzer
• Zeitpunkt-bezogene Batch-Läufe sind wiederholbar
• Fehlerhafte Daten können gezielt wieder entladen werden
• Ein Warehouse enthält oft historische Daten
• Statische Daten, die nicht mehr geändert werden
• Sind bereits gesichert und müssen nicht mehr gesichert werden
Wo liegen die Unterschiede
Zeitlich
definierter
Bulk-Ladestrom
Viele Updates
Viele Inserts
OLTP
DWH
OLTP
OLTP
OLTP
2-200
GB
2-200
TB
Read Only
OLTP
Incremental Backup
• RMAN> BACKUP INCREMENTAL LEVEL n DATABASE;
Incremental
Differential
Backup
Incremental
Cumulative
Backup
Sonntag
Incremental Level 0
Incremental Level 0
Montag
Incremental Level 1
Incremental Level 1
Dienstag
Incremental Level 2
Incremental Level 1
Mittwoch
Incremental Level 3
Incremental Level 1
DWH-spezifische Aspekte
• RMAN> REPORT UNRECOVERABLE;
• Liste aller Files, die aufgrund von z.B. einer NOLOGGINGOperation nicht in sicherem Zustand vorliegen
• Frühzeitig Warnung vor dem Volllaufen von
Tablespaces absetzen
Read-only Tabellen und Tablespaces
• Read-only Tabellen und Tablespaces einsetzen
• Erweiterte Performance, Skalierung und Sicherheit
• Reduzierung der zu sichernden Datenmenge
• RMAN muss Read-only Tabellen nicht immer wieder
sichern
• Bessere Abfrage-Performance für Read-only Tabellen
• Dient zusätzlich auch der Absicherung von
Compliance-Vorgaben (keine Datenveränderung)
• Die Daten können auf Read-only Medien gespeichert
werden
Rollback von Ladeläufen
• Betrachten des kompletten Ladelaufs als eine
zusammenhängende Transaktion
• Entweder werden alle Sätze geladen oder keine
• Wie können abgebrochene Ladeläufe wieder
rückgängig gemacht werden?
Vorteile des RMAN
• Incremental Backup
• Die einzige Lösung, die dies erlaubt
• Block Media Recovery
• Einzelne fehlerhafte Blöcke können korrigiert werden
• Minimiert „menschliche“ Fehler
• Einfach handhabbar
• Komplettsicherung mit Kommandos wie BACKUP DATABASE
• Backup-Vorgänge sind automatisierbar
• Unused Block Compression
Vorteile des RMAN
• Es entstehen keine Redo-Informationen während des
Online Backup
• Minimierter Aufwand
• Binary Compression
• Scripting
• Stored Scripts und Test Scripts
• Verwalten von Backup-Scripts im RMAN Katalog
•
•
•
•
Simulieren von Backups und Restores
Integrierbar mit 3rd Party Media-Management-Produkten
Lässt sich auch über Enterprise Manager ansteuern
Erstellen von Clones und Standby Datenbanken
Verwaltungsdaten des RMAN
• Gespeichert werden die Verwaltungsdaten entweder
• Im Control file
• Einfacher zu handhaben
• Nicht alle Funktionen verfügbar
• Oder im Recovery Catalog
• Empfohlen in separater Datenbank
• Hält alle historischen Informationen
• Mehrere DBs können gleichzeitig verwaltet werden
• Das Backup-Thema kann zentralisiert durchgeführt werden
• Es können Scripte gespeichert werden
Ausblick: Workshop-Reihe DWH im Fokus
•
•
•
•
•
•
Teil 1: Architektur und Konzepte
Teil 2: Tabellen als Basis
Teil 3: Star Schema / Optimierung
Teil 4: Materialized Views und OLAP
Teil 5: Laden und Aktualisieren
Teil 6: Betrieb und Management
Ankündigung in der Oracle DWH Community:
http://www.oracle.com/global/de/community/dwh/index.html
[email protected]