Data Warehouse - DWH Community

<Insert Picture Here>
Oracle Developer Day Data Warehouse
– Von Konzept bis Betrieb
Christoph Blessing / Detlef Schroeder / Alfred Schlaucher
• ODD Data Warehouse
Themen
•
•
•
•
•
•
•
•
•
•
•
2
Konzept und Referenzarchitektur des Oracle Data Warehouse
Das Data Warehouse modellieren
Den ETL-Prozess entwerfen und generieren
Datenqualität optimieren / Metadaten
Wichtige Technologien in der Datenbank
ROLAP / MOLAP
Die richtige Hardware für das Data Warehouse
Exadata – Die Appliance für das Data Warehouse
Unstrukturierte Daten und Big Data
Advanced Analytics
Maintenance
Was macht das DWH-Konzept so
erfolgreich (auch nach 15 Jahren) ?
1
Daten sollten zentral
und leicht für alle
Benutzergruppen
gleichermaßen
zugänglich sein
3
Trendfähige
Informationen durch
Aufbewahrung und
Aufbereitung
historischer Daten
3
2
Zentrale
Bereitstellung
BusinessDaten
Semantik
Historisch
(-> Trends)
Entkopplung von
op. System
Daten sollten leicht
leicht verstehbar sein
- Informationen statt Daten
- Semantische Zusammenhänge
4
Flexibel und unabhängig
von operativen
Anwendungen
analysieren können
A
Evolution des Data Warehouse
¾ unserer Kunden nutzen ihr DWH auch zu operativen Zwecken
Hochvolumig / granular
Überschaubar / aggregiert
Operativ
überschaubar
Taktisch
DWH
Strategisch
Jahr/Quartal/Monat
Komplexe InformationsAusarbeitung und Analysen
4
Woche/Tag
Periodische Berichte
Stunde/Minute/Sekunde/Realtime
oft und schnell
wiederholbare Einzelinformationen
Prinzip Normalisieren / Denormalisieren
Granularisierung als Lösung
Operative
Daten
Normalisierte
Daten (DWH)
Produktsparten
PRODUKTDATEN
PD4711
AMKLB
9987865234
7769
0000000
KLABAUTER
IIO
???
EERWEERW
883466
888750000
888000
EU-Wert
735328567353654
i8886345
7746
5
Müll, Altlast,
unverständliche
Daten
Neu
sortierte
Daten
Spartenname
Spartennr
Produktdaten
Produktname
Produktgruppen
Produktenr
Gruppenname
Einzelpreis
Gruppennr
Gruppenname
Gruppennr
Produkte
Produktname
Spartenname
Spartennr
Produktenr
Einzelpreis
Granulare Daten
Im DWH
Verständliche
Information
(denormalisiert)
Das Neutralitätsprinzip des DWH
Enterprise Information Layer
(Kern DWH)
Process neutral / 3 NF
Prüfen
Integrieren
Harmonisieren
Standardisieren
Erweitern
Verbinden
In Beziehung setzen
Rohdaten
Neutral gegenüber
Vorsystemen,
Sprachen, OS
6
User View Layer
(Data Marts)
Anwenden
Aufbereiten
Aggregieren
Angebot
Anwendungsneutral,
granular,
Zeit-neutral
Any User Group
Data Integration Layer
(Stage)
Data Integration
Any Source/Target System
Redundanzen
Neutral gegenüber
Endbenutzern:
Alle User! Alle Tools!
Bedarf
Oracle Data Warehouse Architektur für
unternehmensweites Datenmanagement
Any
Source
BI Server
Interactive
Dashboards
Data Integration
Real Time & Batch
Information Layer Architecture Concept
Data Integration
Layer
User View Layer
Enterprise Information Layer
InDatabase
ROLAP
InDatabase
MOLAP
Reporting &
Publishing
BI Apps
Data Management Concept
Reference Data Models
InDatabase
Operational Data Layer
Oracle Database Management System
Cluster
Big Data Solution
DWH Security Utilities
DWH Backup / Recovery Concept
Big Data Exadata
Appliance
Scorecards
Financial
DWH System Monitoring Utilities
Optimized Network
Optimiertes Netzwerk
Exadata / Database Machine / Exalytics
Technical
Auditing
Lifecycle Management Concept
Storage
Hierarchy
Ad-hoc
Analysis
Sales
Metadata Business
Utilities Catalogue
Hadoop
Operating
System
HR
Marketing
DWH Logistic Utilities
Server
Server
Cluster
Controlling
Dynamic Data Marts
Data Quality Rules Checks&Monitoring
noSQL
R
InDatabase
Data Mining
Exalytics
Concept Framework
Office
Integration
Mobile
Regeln einer effizienten DWH-Architektur
• Orientierung an den Informationsbedürfnissen der Benutzer
• Granularisierte 3NF-DWH Schicht schafft
• Neutralität gegenüber Vorsystemen
• Flexibilität bei der Bereitstellung neuer Abfragemodelle
• Über Data Mart-Grenzen hinweg gemeinsam genutztze Berechnungen
Aggregationen usw. so früh wie möglich umsetzen
• Zusammenhängende Data Mart-Schicht
• Mehrfachnutzung von Dimensionen
• Geschikter Umgang mit sehr großen Faktentabellen
• Eher granulare Informationen auch in den Fakten-Tabellen
• Alle Schichten in einem DB-Raum
• Ein zusammenhängender DB-Server-Cluster zum Verhindern unnötiger Wege
Analysemodell – Prozess-Sicht
Bewegungsdaten
Stamm-Objekte
Was wissen wir über den Prozess?
Eigenschaften
Stammobjekte
Zeit
kauft
Artikel
Kunde
+
wird geliefert
9
StammdatenInformationen
AktivitätenInformationen:
“Was geschieht”,
Bewegungsdaten
StammdatenInformationen
Ort
Bewegungsdaten
Stamm-Objekte
Objektmodell (Datensicht)
Strukturierung und Beziehungen der Objekte
Bestellung
Kunde
Kontaktperson
KD#...
tätigt
Status
...
Gruppe
Firmenkd
...
...
Name
Gruppe
Sparte
...
• Kandidaten für Dimensions-Hierarchien finden
• Schlüsselpaare PK / FK für spätere Fakten-Joins
10
Jahr
Region
Monat
Land
Tag
Ort
...
Generalisierung
Artikel
Privatkd
Sparte
Externe
Personen
Status
Spezialisierung
Bestelldatum
Lieferdatum
Eigenschaften
Stammobjekte
Gesammelten Input zusammenfassen
Entwürfe für multidimensionale Sichten
•
•
•
•
11
Stammdaten
Bewegungsdaten
Generalisierung
Spezialisierung
 Kandidaten für Dimensionen
 Kandidaten für Fakten
 Kandidaten für Hierarchie-Level
 potentielle Ausprägung
Multidimensionales Modell (Star Schema)
Status
V1
V2
V3
V4
Einstiegspunkte
für Anwender-Abfragen
Artikel
A1
A2
A3
A4
Farbe
Art1
Art2
Blau
Gelb
Art3
Art4
Rot
Lila
Star Schema
• Flexibel
• Graphisch auch für
Business-User
verständlich
Maier
Müller
Schmid
Engel
A1
A2
A3
A4
R1
R2
R3
R4
Kunde
1
:
n
Verkäufe
1:n
P
F
P
F
Z1
Z2
Z3
Z4
V1
V2
V3
V4
Zeit
4
4
9
8
Z1
Z2
Z3
Z4
n
:
1
R1
R2
R3
R4
Nord
Sued
West
Ost
Schwach
Mittel
Hoch
Schwach
Wohndichte
12
n:1
Regionen
6.7.09
7.7.09
8.7.09
9.7.09
Q3
Q3
Q3
Q3
Dimensionen
Dim_Artikel
Artikelsparte_Langname
Levelschlüssel
Artikelsparte
Sparte
Parent
Aggregation
Parent
Aggregation
Artikelgruppe_Langtext
Levelschlüssel
Artikelgruppe
Artikel_Langtext
Levelschlüssel/
Objektname
Artikel
Business Key
Artikel_Schlüssel
Künstlicher
Dimension Key
Dim_Schlüssel
Fakten
13
Spielarten des Star Schemas
Lieferant
Produkte
Connect by
Parent
Intersection- Table
(Degenerated Fact)
Teil von
Verkäufer
Verkaufsanteil
Fakt
Gelieferte
Teile
Bestellkosten
Zentrale
FaktTabelle
Ort
14
Heterogenious
Fact
Kreis
Land
Degenerate Facts
Degenerate Dimensions
Conforming Dimensions
Factless Facts
Slowly changing dimensions
Verkäufer
Zeit
Fakt
Umsatz
Pro
Verk.
Hierarchie
Drill Down
Roll up
Drill Across
Slice / Dice
Pivot
Spielarten des Star Schemas
Teile
Parent
Teil von
Benutzte
Teile
Produkte
Sparte
Gruppe
Verkäufer
Verkaufsanteil
Produkt
Fakt
Gelieferte
Teile
Bestellkosten
Intersection- Table
(Degenerated Fact)
Verkauf
Fakten
Verkäufer
Fakt
Umsatz
Pro
Verkäufer.
Region
Ort
Kreis
Land
Heterogenious
Fact
Region
Lieferant
Zeit
Connect by
15
Degenerate Facts
Degenerate Dimensions
Conforming Dimensions
Factless Facts
Slowly changing dimensions
Drill Down
Roll up
Drill Across
Slice / Dice
Pivot
Degenerated Dimension
Produkte
ProdNr
Prodname
Summe
ProduktNr
BestellNr. Vorgangsnr. KD.Nr KD.Name
In der Regeln sind Attribute aus
Dimensionen in die Faktentabelle
aufgenommen worden, die eine 1:1Beziehung zu den Faktensätzen haben
oder die sehr häufig genutzt werden und
man dabei den Join umgehen will.
16
Attribute von degenerated
Dimensions
Zeit
Bestellungen
BestellNr
Vorgangnr
Kunden
KD_Nr
KD_Name
Factless Fact Table
ZeitNr
ProduktNr
VerkauferNr
17
Zeit
Produkt
Verkäufer
ZeitNr
ProduktNr
VerkauferNr
Dimensionen
Dim_Artikel
Artikelsparte_Langname
Levelschlüssel
Artikelsparte
Sparte
Parent
Artikelgruppe_Langtext
Levelschlüssel
Artikelgruppe
Artikel_Langtext
Levelschlüssel
Artikel
Artikel_Schlüssel
Fakten
18
Parent
Star vs. Snowflake Schema
19
Auslagern von Attributen
Häufig oder weniger häufig genutzt
Separate
Dimension
20
Enterprise Information Layer
User View Layer
Any User Group
Data Integration
Any Source/Target System
Data Integration Layer
Process neutral / 3 NF
Operational Data Layer
Die Schichten im Detail
Die (Kern-) Data Warehouse - Schicht
Oracle Data Warehouse
21
DWH-Kerndatenschicht
Aufgaben und Ziele
• Eindeutigkeit aller Objekte und Namen
• Redundanzfreiheit aller Informationen
• Langlebigkeit der Daten (Historisierung)
 Granulare Informationen als Bausteine für neue
Informationszusammenhänge
22
DWH-Kerndatenschicht
•
•
•
•
3 Normalform (3 NF)
Subjekt-bezogen
In Teilbereiche (Subject Areas) gegliedert
Anwendungs- und Geschäftsprozess-neutral
• Objekte werden in mehreren Geschäftsprozesse benötigt
• Daten müssen tauglich genug sein, um sie in allen Anwendungen zu
verwenden
• Datenarten
• Stammdaten (historisiert)
• Referenzdaten – externe / interne, allgemeine Sammlungen
• Bewegungsdaten (angesammelt)
23
ETL
Oracle Data Warehouse
24
Daten-nahe Transformation im DWH
Den richtigen Platz finden
Quellsystem
DWH-System
n-tier
n-tier
Application Server
ETL?
ETL?
25
Application Server
Der Sinn des 3-Schichten-Modells
Aus Sicht des Ladeprozesses
Data
Mart
ETL: Kosten
pro Kunde
CRM
Staging
Area
ERP
Stage
Warehouse
ETL: Kosten
pro Kunde
Data
Mart
ETL: Kosten
pro Kunde
Data
Mart
!
ETL: Kosten
pro Kunde
26
Aktivitäten in einem ETL-Prozess
•
•
•
•
•
•
•
•
•
•
•
•
•
•
27
Standardfunktionen Insert, Update, Delete, Merge (Insert / Update)
1:1-Transformationen (reines Kopieren, auch mit minimalen Änderungen)
Selektionen (z.B. Where-Klauseln, Bedingungen)
Gruppierende Transformationen (Aggregationen, Sortieren, Segmentieren)
Pivotierende Transformationen (Verändern der Kardinalität von Zeilen und
Spalten)
Berechnungen (einfache oder komplexe, Funktionen oder Programme)
Formatieren von Daten
Zusammenführende und spaltende Transformationen (Join / Split)
Anreichernde Transformationen (Referenzen auslesen, Lookups, Konstanten,
Fallunterscheidungen)
Aussortieren / Trennen von Datenbereichen
Prüflogik (logisch / fachliche und physisch / technische)
Protokollierende Maßnahmen (Log Files, Statistiken)
Steuerungen (Rules-Systeme)
Kommunizieren mit anderen Systemen (Messages senden / empfangen /
quittieren)
Generieren statt Programmieren
Vorteile
• Vermindern von Fehlern durch Handprogrammierung
• Tabellen- und Spaltennamen müssen nicht mehr
mühsam geschrieben werden
• Steuerung vieler Entwicklungsschritte durch Wizards
• Automatische Steuerung von Ziel- und Quellschemata
• Automatische Validierung (z.B. Typverträglichkeiten)
• Debugging der Laderoutinen
• Laufzeitumgebung steht bereit
• Dokumentation
28
Quellen und Ziele
Oracle (Remote)
DB2 OS390, UDB
Sybase, Informix,
SQL-Server...
Log
Golden Gate
Streams
DataPump
CDC
tcp
Access/Excel
Oracle
Database
Gateway
SAP
MessageBroker
Peoplesoft
Siebel
ODBC
UTL_FILE
Adapter
DB-Link
SQL Loader
Queue
PL/SQL
Ext. Table
Webservices
Tabellen
View
Sequenz
Index
Cube
MView
XML
Procedure
Function
eMail
Flat File
XML
JDBC
Agent
XML
DB-Link
Queue
Queue
OWB Architektur
Oracle 10g /11.1/11.2
OWBSYS-Schema
OWB11.2
Metadaten
WindowsXP,
Vista, 7 oder
Linux
Workspace
OWB112
Control
Center
Service
generiert
Oracle 10g /11.1/11.2
(Warehouse-Datenbank)
Remote DB
(Source DB)
OLTP-Quellschema
DWH-Zielschema
PL/SQL
Datentransfer
DB Link
Warehouse
Warehouse
Warehouse
tabellen
tabellen
Tabellen
Control
Center
Agent
Der Mapping-Editor
Graphik und SQL
Graphik und SQL
Call
Filter
External Table
Distinct
PL/SQL
Table Function
Lookup
Multiple
Insert
Multiple
Targets
Text
Files
XML
MINUS
Merge
UNION
Metadaten-Dependency-Manager
E-LT Architecture with ODI-EE
High Performance, Flexible, Lightweight Architecture
• Key Architecture Benefits: 100% Java, Open APIs, fast E-LT
Packaged
Application
Business Intelligence
& Data Warehouse
ODI
Agent
ODI Agent may be
deployed in any part
of the architecture
LKM
JKM A
IKM
D
B
C$_0
LKM
C$_1
I$
File
C
E$ (Errors)
IKM
CKM
RKM
Extract-Load
Transform
Check-Load
Wer glaubt schon bunten Charts?
Ohne Daten kein Business
Schlechte Daten sind wie Sand im Getriebe der Geschäftsprozesse
Information Chain
Marketing
Werbung
Adresse
KD-Daten
Bedarf
Adresse
Kreditdaten
Kunde
Angebot
Kredit OK
Kundenbetreuer
Bestelldaten
Bestand
Stammdaten
Order
Logistiksystem
Lager
Buchhaltung
Verkaufsdaten
Lieferschein
Spedition
Rechnung
Mahnung
Bezahlung
Reklamation
Kunde
Operative Prozesse
Wo sollten Korrekturen stattfinden
Correction
Data Load
Data
Warehouse
Operative Anwendung
Vorsysteme bzw. Fachabteilungen sind in der Pflicht!
Wo sollten Korrekturen stattfinden
?
Operative Anwendung
Correction
Data
Warehouse
Operative Anwendung
Operative Anwendung
Data Load
Warum wächst die Herausforderung
der Qualität der Daten
Gewachsene Bedeutung
des Faktors Information
für den Erfolg von
Unternehmen.
Fehlende Praxis in
Datenmanagement
Datenqualität
Immer häufigere
Prozessänderungen
Ausufernde
Datenmengen
Vermehrtes Inseltum
durch FertigAnwendungen
Was ist Datenqualität?
Aspekte (Dimensionen) der Datenqualität
Brauchbarkeit
der Daten!
1. Korrekt
2. Stimmig
3. Vollständig
4. Dokumentiert
5. Redundanzfrei
6. Aktuell
7. Verfügbar (Access)
8. Nützlich (TCO)
9. Handhabbar
10. Vertrauenswürdig
11. Harmonisch
Methoden und Hilfsmittel
•
•
•
•
•
•
•
•
•
Vorgehensmodell
Datenmodellierung
Datenqualitätsprüfmethoden
Data Profiling
Data Profiling Tool
Attribut-Klassifizierung (Namen)
Kategorisierung von Qualitätsregeln
ETL-Tool
Datenbank
Vorgehensmodell Datenqualitätsanalyse
Zieldefinition
Geschäftsregeln
Erwartungen
Bestandsaufnahme
Owner
User
Ressourcen
Kosten
Modelle
Problemkomplexe
Priorisieren
Strukturanalysen
Felder
Objekte
Beziehungen
Hierarchien
Regelanalysen
Daten
Werte
Fach
Umsetzung Ergebnisse
Abgleich-Alt
Neudefinition
Monitoring
6 Phasen, 95 Aktivitäten, 16 Ergebnis-Templates, 1 Metamodell, Klassifizierungen
Bottom Up
Top Down
Planen
Starten eines
Profiling-Laufs
GenerierungRule
Starten einer CorrectionMapping-Generierung
Auswahl und Ergebnisansicht Methoden
Die Tabellen,
die zu dem
Analysefukus
gehören
Tabellen-Darstellung
Chart-Darstellung
Feintuning
zu den
Analysemethoden
Drill-Werte
Operative Datensätze
AnalyseJobProtokolle
Aktivierbare Business Rules
Informations-Repository
• Aufstellung zu allen ermittelten und formulierten
Informationsanforderungen der Endbenutzer
• Informationskataloge zu den Tabellen und Spalten der zentralen
Warehouse-Schicht
• Ein zusätzliches Klassifizierungsverfahren zum Verhindern von
Synonymen und Homonymen
• Nachweis darüber, welcher Benutzer welche Daten nutzt
• Dokumentation der über Materialized Views aufgebauten KennzahlenHierarchien
• Dokumentation aller Dimensionen sowie ihrer Hierarchien und die
hierüber selektierbaren Felder
• Dokumentation der Kennzahlen in den Fakten-Tabellen
• Datenqualitätsregeln in dem Integration Layer
• Ein Schlagwortverzeichnis könnte noch hinzugefügt werden
Agenda
•
•
•
•
•
•
•
•
•
Oracle Partitioning
Compression
Indizes
Star Query Transformation
Parallelisierung
Result Cache
Materialized Views
Analytische Funktionen
Oracle OLAP/ Cube-organized Materialized Views
Oracle Partitioning
Oracle Data Warehouse
49
Partitioning unterstützt viele Aufgaben
Query Performance
Partition Pruning
Ladeprozess
Partitioning
Unterstützung ILM
(Information Lifecycle
Management)
Leichterer Umgang mit
Indizierung
Unterstützung im
Backup-Prozess
Unterstützung bei der
Komprimierung
Unterstützung bei
der Aktualisierung
von Materialized Views
(Partition Change Tracking)
50
PartitionierungsKriterium fachlich
anwendbar oder
nicht?
Partitioning Typ:
- Range
- List
- Hash
Hochverfügbarkeit auch
während des Ladens und
Maintenance
Wie wird partitioniert
• Partition Key
• Eine oder mehrere Spalten in der Tabelle bestimmen den
tatsächlichen Speicherort eines Datensatzes
• Separate Tablespaces
• Pro Partition einen eigenen
Tablespace
 Vereinfachte Wartung
Tablespace
Segment
Extent
Blocks
51
Partitioning ist transparent
• Gesamte Tabelle selektieren
SELECT * FROM orders;
Alle Partitionen werden selektiert
• Abfrage nur auf eine Partition
Jan 2007
Feb 2007
SELECT * FROM orders
WHERE order_dat between
Partition
Pruning: AND
to_date
('2007-01-01')
Automatische
Beschränkung
to_date
('2007-01-31');
auf betroffene
Partition
SQL-Abfrage
ist von Partitionierung
unabhängig
Mär 2007
Apr 2007
Mai 2007
Jun 2007
52
Verschiedene Varianten
• Partitioning-Typen
•
•
•
•
•
•
•
53
Range
List
Hash
Reference
Interval
System
Virtual Column
• Subpartitioning-Typen
• Range - Hash
• Range - List
• Range - Range
• List - Range
• List - Hash
• List - List
Range Partitioning
Partitionierung nach Wertebereichen
• Partitionierung nach Wertebereichen
• Für sortierte Wertebereiche
• LESS THAN:
Angabe eines maximalen Wertes pro Partition
CREATE TABLE orders (
order_no number,
part_no varchar2(40),
ord_date date
) PARTITION BY RANGE (ord_date) (
PARTITION M1 VALUES LESS THAN
(to_date('2007-02-01','YYYY-DD-MM')),
PARTITION M2 VALUES LESS THAN
(to_date('2007-03-01','YYYY-DD-MM')),
:
54
Jan 2007
Feb 2007
Mär 2007
Apr 2007
Mai 2007
Jun 2007
Range Partitioning
Neue Tabellenzeilen
• Partition für neue Tabellenzeile muss vorhanden sein
FEHLER in Zeile 1:
ORA-14400: Eingefügter Partitionsschlüssel kann keiner
Partition zugeordnet werden
• Lösung I: MAXVALUE-Partition
• Lösung II: Eigene Jobs
• Lösung III: Oracle11g
55
Range Partitioning
Alphabetische Sortierung in den Partitionen
CREATE TABLE kunde_part_alpha
(
kundennummer
NUMBER,
vorname
VARCHAR2(20),
kundenname
VARCHAR2(40)
)
PARTITION BY RANGE (kundenname)
(
PARTITION kunde_ae VALUES LESS THAN ('F%')
TABLESPACE part_range1,
PARTITION kunde_fl VALUES LESS THAN ('M%')
TABLESPACE part_range2,
PARTITION kunde_mr VALUES LESS THAN ('S%')
TABLESPACE part_range3,
PARTITION kunde_sz VALUES LESS THAN (MAXVALUE) TABLESPACE part_range4
);
56
Hash Partitioning
Gleichverteilung der Daten
• Partitionierung nach Hash-Funktion
• Schlüsseltypen:
Alle built-in Datentypen außer ROWID, LONG, LOB
• Ziel: Gleichverteilung der Daten
• Anzahl Partitionen: als Potenz von 2 empfohlen
CREATE TABLE orders (
order_no number,
part_no varchar2(40),
ord_date date
) PARTITION BY HASH (ord_date)
PARTITIONS 4
57
Hash Partitioning
CREATE TABLE bestellung_part_hash
(
bestellnr
NUMBER(10) NOT NULL,
kundencode
NUMBER(10),
bestelldatum
DATE,
lieferdatum
DATE,
bestell_total
NUMBER(12,2),
auftragsart
VARCHAR2(30),
vertriebskanal NUMBER
)
PARTITION BY HASH (bestellnr)
PARTITIONS 8;
58
List Partitioning
AMER
• Für diskrete, unsortierte Werte
• Angabe einer Werteliste pro Partition
• VALUES (DEFAULT) für “alles andere”
• Verhalten wie MAXVALUE in der Range-Partitionierung
CREATE TABLE bestellung (
bestellnr number,
auftragsart varchar2(40),
land varchar2(2)
)
PARTITION BY LIST (land) (
PARTITION EMEA VALUES ('DE','FR',[..]),
PARTITION AMER VALUES ('US','CA',[..]),
PARTITION APAC VALUES ('JP','CN',[..]),
PARTITION OTHERS VALUES (DEFAULT)
)
59
EMEA
APAC
Partition Pruning
Wie hoch waren die
Verkäufe für das
Wochenende vom
20-22 Mai 2008?
Sales Table
May 18th 2008
May 19th 2008
May 20th 2008
SELECT sum(sales_amount)
FROM sales
May 21st 2008
WHERE sales_date BETWEEN
to_date(‘05/20/2008’,’MM/DD/YYYY’)
May 22nd 2008
AND
to_date(‘05/23/2008’,’MM/DD/YYYY’);
60
Nur die 3
relevanten
Partitionen
werden betrachtet
May 23rd 2008
May 24th 2008
Partitionwise Join
• Sind beide am Join beteiligten Tabellen partitioniert, erfolgt
• Eine “Portionierung” der Abfragemenge
• Parallelisierung
• Full partitionwise Join
• Gleiche Partitionierungsmethode
• Gleicher Partitionierungsschlüssel
• Partial partitionwise Join
• Nur eine der beteiligten Tabellen ist partioniert
• Die andere Tabelle wird durch das System dynamisch partitioniert
• Der Cost Based Optimizer entscheidet über die Vorgehensweise
61
Interval Partitioning
• Erweiterung der Range-Partitionierung
• Volle Automatisierung für gleichgroße Range-Partitionen
• Partitionierung wird als Metadaten-Information
abgelegt
• Start-Partition ist dabei persistent
• Sobald neue Daten hinzukommen werden
Segmente allokiert
• Lokale Indizes werden automatisch mitgepflegt
62
Interval Partition automatisch angelegt
Mai
Maidaten
?
April
März
Märzdaten
März
Februar
Februardaten
Januar
Januardaten
Februar
Januar
Kunden
63
Produkte
Aprildaten
April
Interval Partitioning Syntax
CREATE TABLE "BESTELLUNG"
(
"BESTELLNR" NUMBER(10) NOT NULL,
"KUNDENCODE" NUMBER(10),
"BESTELLDATUM" DATE,
"LIEFERDATUM" DATE,
"BESTELL_TOTAL" NUMBER(12, 2),
"AUFTRAGSART" VARCHAR2(30),
"ORDER_ID" NUMBER
)
PARTITION BY RANGE ("BESTELLDATUM")
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION "Jan07"
VALUES LESS THAN (TO_DATE(' 2007-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "TS_PAR",
PARTITION "Feb07"
VALUES LESS THAN (TO_DATE(' 2007-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "TS_PAR"
)
;
64
Reference Partitioning
FK
FK
• Child Tables mussten bisher die Spalte mit dem
Partitioning Key mitführen, um so wie die Parent Table
partitioniert zu werden
• Jetzt erfolgt das Equi-Partitioning von Child Tables
über den Foreign Key
• Erspart Redundanz beim Speichern
• Vereinfacht die Administration
• Nicht für Interval Partitioning anwendbar
65
Reference Partitioning
PK
BestellNr
KundenNr
BestellDatum
PK
FK
April
BestellNr
ArtikelNr Menge PosNr
FK
März
April
BestellNr
LieferNr
PosNr
Februar
März
April
Januar
Februar
März
Bestellungen
Januar
Februar
Bestell_Positionen
Januar
Auslieferungen
66
Sub- und Composite Partitioning
Oracle Data Warehouse
67
Composite Partitioning
• Range ...
• Range - Range
[Oracle11g]
• Range - Hash
[Oracle8i]
• Range - List [Oracle9iR2]
1st Level:
Interval Partitioning möglich
• List ...
• List - Range [Oracle11g]
• List - Hash
[Oracle11g]
• List - List
[Oracle11g]
68
2nd Level:
Interval Partitioning nicht möglich
Composite Partitioning
• Kombinationen: Ein Beispiel für Range - List
JAN 07
Produkt
Service
Storno
69
FEB 07
MAR 07
...Und zu guter Letzt: Partition Advisor
• Erweiterung des SQL Access Advisors
• Partitionierung von nicht-partitionierten Materialized Views, Tabellen
und Indizes
• Generierung von ausführbaren Skripts
• Nutzung via EM
• Package DBMS_ADVISOR
70
Tuning
Pack
Maintenance und Reorganisation
• ADD PARTITION
• DROP PARTITION
• TRUNCATE PARTITION
• MOVE PARTITION
• SPLIT PARTITION
• MERGE PARTITION
• COALESCE PARTITION (Hash Partition / Index)
• EXCHANGE PARTITION
• Verändern der Default-/ realen Attribute
• Partition Exchange Loading
71
Partition Exchange Loading (PEL)
Financial
Temporäre
Tabelle
Production
Human Res.
Store
Supplier
Marketing
Service
Neuer Monat
P1
P2
P3
P4
Z1
Z2
Z3
Z4
4
4
9
8
Parallel Direct Path INSERT
(Set Based)
CREATE TABLE AS SELECT
(CTAS)
CREATE Indizes / Statistiken anlegen
EXCHANGE Tabelle
• Unvergleichbar schnell!
72
Zeit
Monat 13
Monat 12
Monat 11
Monat 10
DROP
PARTITION
Faktentabelle
Region
Information Lifecycle Management
Rolling Window Operationen
• Ganze Partitionen löschen
• ALTER TABLE DROP PARTITION
Jan 2008
• Partitionen verschieben
• ALTER TABLE MOVE PARTITION
Feb 2008
• Oder: DBMS_REDEFINITION
Mar 2008
• Tablespaces mit einzelnen Partitionen
• READ ONLY setzen
• Komprimieren
• Verschlüsseln (TDE)
Apr 2008
May 2008
Jun 2008
:
Jan 2010
73
Information Lifecycle Management (ILM)
Mit Partitionierung und Storage Layern
• Für die Anwendung sind die
Daten eine Tabelle
• Unterschiedliche Bereiche auf
unterschiedlichen Storages
• Bedarfsgerechte Performance
und Kosten
Disk Group (DG) "High Perf "
2009
High End Storage €€€
74
DG "Standard"
2006
2008
Midrange Storage €€
DG "Historic"
19902005
Entry Level Storage €
Einsatz von Compression
Oracle Data Warehouse
75
Das Datenwachstum beherrschen
Komprimieren: Verwaltung und Kosten reduzieren
Kompressions Typ:
Einsatz für:
Basic Compression
Read only Tabellen und Partitionen in Data
2-4
Warehouse Umgebungen oder “inaktive” DatenPartitionen in OLTP Umgebungen.
Aktive Tabellen und Partitionen in OLTP und Data
2-4
Warehouse Umgebungen.
Non-relational Daten in OLTP und Data Warehouse 2-4
Umgebungen.
OLTP Compression
SecureFiles
Compression
Indizes auf Tabellen in OLTP und Data Warehouse
Umgebungen.
Alle Umgebungen.
2
Hybrid Columnar
Compression –
Data Warehousing
Read only Tabellen und Partitionen in Data
Warehouse Umgebungen.
8-12
Hybrid Columnar
Compression –
Archival
“Inaktive” Daten Partitionen in OLTP und Data
Warehousing Umgebungen.
10-40
Index Compression
Backup Compression
76
Faktor
2
Anwendung für Komprimierung
• Nicht nur für
• Indizes
• Strukturierte Daten in Tabellen
(bzw. Partitionen) mit DIRECT Load
• Mit Advanced Compression auch für
•
•
•
•
Unstrukturierte Datentypen (SecureFiles)
Konventionelles DML (OLTP Compression)
DataPump Daten und RMAN
Redo Traffic mit Data Guard
Redo Logs
77
Standby
Backups
Advanced Compression in Oracle 11g
Overhead
Free Space
Uncompressed
Compressed
Inserts are
again
uncompressed
Block usage reaches
PCTFREE – triggers
Compression
Inserts are
uncompressed
78
Block usage reaches
PCTFREE – triggers
Compression
Tabellen-Komprimierung in 11g
• Komprimierungseinstellung durch
• CREATE TABLE beim Neuanlegen
• ALTER TABLE MOVE COMPRESS bei existierenden Daten
• ALTER TABLE MOVE PARTITION COMPRESS bei Partitionen
• Beispiel - Syntax:
CREATE TABLE sales_history(…) COMPRESS
FOR BASIC | OLTP
• Im Enterprise Manager:
79
Schlüssel im
DWH und Indizierung
Oracle Data Warehouse
80
Warum künstliche Schlüssel verwenden?
• Gründe für den zusätzlichen Aufwand künstlicher Schlüssel
sind:
• Integration
• In mehreren Vorsystemen gibt es unterschiedliche Schlüssel
• Stabilität
• Natürliche Schlüssel können sich ändern
• Geschäftsbereiche können sich ändern
 DWH langlebiger als operative Anwendungen
• Künstliche Schlüssel bedeuten Performance für das Star
Schema
81
Umschlüsselung
Anwendung 1
Verkaufsregion
Einkommensgruppe
Data Warehouse
Wohnart
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
82
Neuer Schlüssel
Indizes in Oracle
• Indexarten
• B*Tree Index
•
•
•
•
•
•
•
•
•
Index organisierte Tabellen
Cluster Index
Reverse Key Index
Descending Index
Bitmap Index
Bitmap Join Index
Function based Index
Textindex
Hash Index
• Ausprägungen
• Invisible Indizes
• Lokale / Globale Indizes
B*Tree Index – 4 Zugriffe bis zum Wert
1
2
Clustering
Factor
Zugriff über die
RowID
4
84
3
Bitmap – Zugriff auf Werte per Bit Stream
Rowid
Name
Abschluss
Rating
AAAHfVAAJAAAKOKAAA
Meier
Klasse_10
5
AAAHfVAAJAAAKOKAAB
Schubert
Abitur
5
AAAHfVAAJAAAKOKAAC
Klaus-Gustav
Abitur
5
AAAHfVAAJAAAKOKAAD
Schmidt
Diplom
5
AAAHfVAAJAAAKOKAAE
Langbein
Doktor
5
AAAHfVAAJAAAKOKAAF
Hund
Klasse_10
5
AAAHfVAAJAAAKOKAAG
Vogel
Abitur
5
AAAHfVAAJAAAKOKAAH
Messner
Abitur
5
SELECT Name
FROM KD_Table
WHERE Abschluss=‘Diplom‘;
85
Abschluss=
Klasse_10
Abschluss=
Abitur
Abschluss=
Diplom
Abschluss=
Doktor
AAAHfVAAJAAAKOKAAA
1
0
0
0
AAAHfVAAJAAAKOKAAB
0
1
0
0
AAAHfVAAJAAAKOKAAC
0
1
0
0
AAAHfVAAJAAAKOKAAD
0
0
1
0
AAAHfVAAJAAAKOKAAE
0
0
0
1
AAAHfVAAJAAAKOKAAF
1
0
0
0
AAAHfVAAJAAAKOKAAG
0
1
0
0
AAAHfVAAJAAAKOKAAH
0
1
0
0
Bitmap Index
• Zusammengesetzte Schlüssel sind ungünstiger als einzelne
Bitmap-Schlüssel
• Langsamer zu verarbeiten
• Können nicht komprimiert werden
• Bei Änderungsoperationen an der Tabelle kann es zu
Overflow-Operationen im Bitmap Index kommen
• Änderungen werden z.T. an anderer Stelle der Platte gespeichert
86
Platzverbrauch im Vergleich
Tests mit unterschiedlicher Kardinalität
CREATE TABLE I_Kunde
(KD_NR
Name
Geb_Dat
Bildungsgruppe
KR_Rating_1_bis_Variabel
number,
varchar2(30),
date,
varchar2(30),
number);
SELECT index_name,index_type blevel, leaf_blocks, distinct_keys
FROM user_indexes;
Leaf_
Anzahl Distinct
Blocks
Sätze
Werte Prozent BTree
Bildungsgruppe
Bildungsgruppe
Geb_Dat
KR_Rating_1_bis_Variabe
KD_NR
87
100000
100000
100000
100000
100000
5
100
14575
43211
100000
0.005
0.1
14.575
43.211
100
271
192
265
220
222
Leaf_
Blocks
bitmap
11
34
97
179
348
Rebuild Index Operation
ALTER INDEX index_name REBUILD [ NOLOGGING ];
• Schneller als DROP / CREATE
• NOLOGGING-Klausel
• Fragmentierung wird beseitigt
• Nach Änderungsaktivitäten
• Freien Platz “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
• Usage-Monitor zeigt, ob ein Index wirklich genutzt wurde
alter index PK_BESTELLNR_PART_RANGE_HASH monitoring usage
SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED FROM SYS.V$OBJECT_USAGE
88
Wo und wie wird im DWH indiziert
Lade-Aktivitäten
Lese-Aktivitäten
Data Integration Layer
Enterprise Information Layer
User View Layer
Process neutral / 3 NF
Keine Indexe
B*tree für Eindeutigkeit
und als Primary Key
Bitmaps
Bitmap Join
Bitmaps
B*tree für Primary Keys
In den Dimensionen
Tabellen
Physische Strukturen im Star Schema
Data Mart-Schicht
Dimensionsobjekt
Dimensionsobjekt
Reg
Zeit
Primary Key Constraint
PK Constraint
Komprimiert
Partitioniert
Foreign Key (NOT NULL)
Lokale Indizes
Security
Verschlüsselung
Bitmap-Index
Dimensionsobjekt
Dimensionsobjekt
Org.
Linie
Prod
PK Constraint
90
PK Constraint
Schlüsselverteilung im Star
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
PK
FKs
PK
D_REGION
PK REGION_ID
ORT_ID
ORT_NAME
KREIS_ID
KREIS_NUMMER
KREIS_NAME
LAND_NAME
LAND_ID
LAND_NUMMER
REGION_NAME
REGION_NUMMER
91
F_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
Potentiell
B*Tree
Potentiell
Bitmap
B*Tree
Bitmap
D_KUNDE
KUNDEN_ID
VORNAME
NACHNAME
GEBDAT
BRANCHE
WOHNART
KUNDENART
BILDUNG
EINKOMMENSGRUPPE
ORTNR
BERUFSGRUPPE
STATUS
BERUFSGRUPPEN_NR
BILDUNGS_NR
EINKOMMENS_NR
WOHNART_NR
PLZ
ORT
PK
Star Query Transformation
Oracle Data Warehouse
92
Star Query Transformation
Optimierung für Joins mit großen Faktentabellen
SELECT sum(summe) FROM
1.000.000
65
12.834
F_Bestellungen B,
D_Artikel A,
D_Region R,
D_Zeit Z,
3.074
1.029
D_Kunde K
WHERE
B.FK_Kunden_ID
= K.Kunden_ID
AND B.FK_Datum_ID
= Z.Datum_ID
AND B.FK_Ort_ID
= R.Ort_ID
AND B.FK_Artikel_Nummer
= A.Nummer
AND Z.JAHR_NUMMER
= 2008
AND A.GRUPPE_NR
= 3
AND K.KUNDENART
= 8
AND R.REGION_Name IN ('MITTE','SUED','NORD');
93
STAR_TRANSFORMATION_ENABLED=FALSE;
Abgelaufen: 00:00:03.48
Ausführungsplan
---------------------------------------------------------Plan hash value: 876979892
-------------------------------------------------------------------------------------| Id
| Operation
| Name
Rows |Bytes |Cost (%CPU)| Time
|
---------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
1 |
50 |1057
(2)| 00:00:13
|
1 |
SORT AGGREGATE
|
1 |
50 |
|
|
2 |
NESTED LOOPS
|
|
|
|
|
3 |
|*
4 |
|*
5 |
|*
6 |
|*
7 |
|*
8 |
TABLE ACCESS FULL
| D_KUNDE
|
9 |
TABLE ACCESS FULL
|* 10 |
|* 11 |
|* 12 |
NESTED LOOPS
|
12 |
600 |1057
(2)| 00:00:13
|
31 | 1209 |1026
(2)| 00:00:13
|
121 | 3993 |1022
(2)| 00:00:13
TABLE ACCESS FULL
| D_ZEIT
152 | 1216 |
7
(0)| 00:00:01
HASH JOIN
|
2459 |61475 |1015
(2)| 00:00:13
HASH JOIN
HASH JOIN
TABLE ACCESS FULL
INDEX UNIQUE SCAN
9
(0)| 00:00:01
| F_BESTELLUNGEN 1010K|
18M|1001
(2)| 00:00:13
| D_ARTIKEL
16 |
96 |
3
(0)| 00:00:01
| PK_REGION
1 |
|
0
(0)| 00:00:01
1 |
11 |
1
(0)| 00:00:01
TABLE ACCESS BY INDEX ROWID| D_REGION
3 |
18 |
----------------------------------------------------------------------------------------
94
STAR_TRANSFORMATION_ENABLED=TRUE;
Abgelaufen: 00:00:00.76
Ausführungsplan
---------------------------------------------------------Plan hash value: 4213778833
-----------------------------------------------------------------------------------------------| Id
| Operation
| Name
|Rows | Bytes| Cost (%CPU)| Time|
------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|*
7 |
|*
8 |
|
9 |
|
10 |
|* 11 |
|* 12 |
|
13 |
|
14 |
|* 15 |
|* 16 |
|
17 |
|
18 |
|* 19 |
95
|* 20 |
SORT AGGREGATE
|
|
|
TABLE ACCESS BY INDEX ROWID | F_BESTELLUNGEN
|
|
BITMAP CONVERSION TO ROWIDS|
BITMAP AND
BITMAP MERGE
BITMAP KEY ITERATION
TABLE ACCESS FULL
BITMAP KEY ITERATION
TABLE ACCESS FULL
BITMAP KEY ITERATION
TABLE ACCESS FULL
BITMAP KEY ITERATION
TABLE ACCESS FULL
19 |
|
|
268 |199(2)| 00:00:03 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| D_KUNDE
|
3 |
18 |
9(0)| 00:00:01 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| D_ARTIKEL
|
16 |
96 |
3(0)| 00:00:01 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| D_ZEIT
BITMAP INDEX RANGE SCAN| IDX_FK_DATUM_ID_BM
BITMAP MERGE
1 |
14 |
BITMAP INDEX RANGE SCAN| IDX_FK_ARTIKEL_NUMMER_BM |
BITMAP MERGE
19 |199(2)| 00:00:03 |
|
BITMAP INDEX RANGE SCAN| IDX_FK_KUNDEN_ID_BM
BITMAP MERGE
1 |
| 152 | 1216 |
7(0)| 00:00:01 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| D_REGION
BITMAP INDEX RANGE SCAN| IDX_FK_ORT_ID_BM
|5069 |55759 | 69(0)| 00:00:01 |
|
|
|
|
|
Star Query Transformation
1. Zugriff auf die Faktentabelle und Lookup mit den
Filterkriterien auf Dimension 1 zur Erzeugung eines Bitmap
entsprechend der Primary Keys
2. Wiederholen für alle Dimensionen
3. AND-Verknüpfung der Bitmaps und Suchen nach den
Faktentabellen-Row IDs
4. Zugriff mit gefundenen Row IDs auf die Faktentabelle
5. Evtl. Join-back auf die Dimensionen für die restlichen
Spalten, die benötigt werden.
 Es findet zu keinem Zeitpunkt ein Full Table Scan auf der
Faktentabelle statt
101
Bedingungen für die Star-Transformation
• STAR_TRANSFORMATION_ENABLED=TRUE
• Keine Bind Variable im SELECT Statement, kein CONNECT BY
und kein START WITH verwenden
• Die Faktentabelle
•
•
•
•
Muss mehr als 15000 Sätze haben (Stand 10g)
Kann keine View sein
Kann keine Remote-Tabelle sein
Muss mehr als 2 Bitmap Indizes haben
• Die Foreign Key Felder müssen als Bitmap Index definiert
sein (Faktentabelle)
• Ein Foreign Key Constraint als solches muss nicht definiert
sein
102
Parallelisierung
Oracle Data Warehouse
103
Parallelisierung und Skalierung
• Abfragen
•
•
•
•
seriell
SELECT
JOIN-Operationen
SORT-Operationen
GROUP BY
• DDL
• CREATE TABLE / MV
• CREATE INDEX
SQL
100%
CPU
I/O
CPU
I/O
50%
parallel
• Online Index Rebuild
100%
SQL
50%
• DML
• INSERT
• UPDATE / DELETE
• MOVE / SPLIT PARTITION
104
Ein SQL Statement wird vom
Optimizer in kleinere Arbeitsschritte
aufgeteilt und läuft skalierbar ab.
Voraussetzungen für Parallelisierung
• Hardware-Architektur
• Symmetric Multiprocessors (SMP)
• Clusters (RAC, Grid Computing)
• Massively Parallel Processing (MPP)
• Ausreichend I/O-Bandbreite
• Geringe oder mittlere CPU-Auslastung
• Systeme mit CPU-Auslastungen von weniger als 30%
• Genügend Hauptspeicher für speicherintensive Prozesse
• Sortierung
• Hashing
• I/O-Puffer
105
Degree of Parallelism (DOP)
• Automatic Degree of Parallelism
• PARALLEL_DEGREE_POLICY = AUTO
• Degree of Parallelism manuell festlegen
• ALTER TABLE sales PARALLEL 8;
• ALTER TABLE customers PARALLEL 4;
• Default Parallelism
• ALTER TABLE sales PARALLEL;
• Parallelisieren von Abfragen
• SELECT /*+ PARALLEL(b)n PARALLEL(a)n */
bestellung b, artikel a;
SI : DOP =
RAC: DOP =
106
a,b,c FROM
PARALLEL_THREADS_PER_CPU x CPU_COUNT
PARALLEL_THREADS_PER_CPU x CPU_COUNT x
INSTANCE_COUNT
Kontrolle über Parallelisierung behalten
• Parameter PARALLEL_DEGREE_POLICY
• Manual
•
•
•
•
Verhalten wie vor 11gR2, der DBA konfiguriert alles manuell
Kein Automated DOP
Kein Statement Queuing
Keine In-Memory Parallel Execution
• Limited
• Eingeschränkter Automated DOP für Abfragen auf Tabellen mit
Default Parallelisierung
• Kein Statement Queuing
• Keine In-Memory Parallel Execution
• Auto
• Alle in Frage kommenden Statements werden parallel ausgeführt
• Statement Queuing
• In-Memory Parallel Execution
107
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
108
Statement wird
parallel ausgeführt
Parallel Statement Queuing
SQL Monitoring im Enterprise Manager
Klicken auf
SQL ID für
weitere
Informationen
Uhrsymbol
zeigt ein
wartendes
Statement
an
109
Awaiting screen shot
from EM
Query Result Cache
Oracle Data Warehouse
110
Situation im Datawarehouse
• Lang andauernde, teure Abfragen
• Sich wiederholende Abfragen
• Rechenintensive PL/SQL Funktionen
• Randbedingungen
• Abfragen mit kleinen Ergebnismengen
• Zusätzliches Memory steht zur Verfügung
• Tabellen sind relativ statisch
• Ziel: SQL Performance mit möglichst einfachen
Mitteln erhöhen
Konzept und Einsatz des Result Cache
•
•
•
•
Eigener Cache im Shared Pool
Keine Installation notwendig
Automatischer Refresh bei Datenänderungen
Einfaches Setup und Monitoring der Cache-Nutzung
• Der Query Result Cache ist anwendbar für
• SQL-Abfragen
• PL/SQL-Funktionen
112
Implementierung und Nutzung
• Anwendung steuerbar über Initialisierungsparameter
RESULT_CACHE_MODE
• Falls RESULT_CACHE_MODE=MANUAL gesetzt ist,
dann einen Hint im Statement einfügen wie z.B.
SELECT /*+ result_cache */ count(*) FROM sales
• Falls RESULT_CACHE_MODE=FORCE gesetzt ist,
dann erfolgt ein automatisches Einfügen des Hints im
Root-SELECT
SELECT count(*) FROM sales
113
...
Parameter zum Result Cache
RESULT_CACHE_MAX_RESULT
RESULT_CACHE_MAX_SIZE
RESULT_CACHE_MODE
RESULT_CACHE_REMOTE_EXPIRATION
5 (%)
abhängig vom OS
MANUAL/FORCE
0 (min)
• RESULT_CACHE_MAX_SIZE: Gesamtgröße des reservierten Bereichs für den Result
Cache im Shared Pool
• RESULT_CACHE_MAX_RESULT: Prozentualer Anteil am gesamten Result Cache für die
einzelnen Ergebnisse
• RESULT_CACHE_REMOTE_EXPIRATION: Zeitdauer bei Remote Objekt-Nutzung, wie
lange das Resultat in Minuten im Cache verbleibt
114
Tipps zum Einsatz von Result Cache
• Sinnvoll bei
• Zumeist statischem SQL
• Häufiger „Read Only“-Nutzung (nur wenige Invalidierungen)
• Rechenintensiven (teuren) Operationen
• FORCE-Einstellung
• Wirkt auf alle SQL-Statements: wird genutzt, wenn keine Änderung
am SQL möglich ist
• Ausnahmen mit Hint /*+ NO_RESULT_CACHE */
• Detailliertes Monitoring: V$RESULT_CACHE_OBJECTS
• Speicher anpassen mit RESULT_CACHE_MAX_SIZE
• Siehe auch DBA Community Tipp:
http://www.oracle.com/global/de/community/dbadmin/tipps/result_cache/index.html
Materialized Views
Oracle Data Warehouse
116
Aufgaben der Materialized Views (MAVs)
• Erleichtern das Management von Summentabellen
• Wegfall von Erstellungsprozeduren
• Einfache Steuerung des Zeitpunktes zur Aktualisierung
• Eventuell Beschleunigung der Aktualisierung
(inkrementelles Refresh)
• Abfrage-Performance optimieren
• Variable Kennzahlensysteme aufbauen
• Mehrstufige MAVs
• Abfragegruppen zusammenfassen (Kategorisierung)
• Geschäftsobjekt-bezogene MAVs
117
Ähnlichkeit von Result Cache und MAVs
• Result Cache
• Nutzung des Result Cache für Ergebnisse aus SQL-Abfragen und
PL/SQL-Funktionen
• Automatisches Refresh nach Datenänderungen
• Eigener Speicherbereich im Shared Pool
 Wirkt wie eine “just-in-time” Materialized View
• Materialized Views
• Nutzung für häufig erfragte Ergebnisse (Summen, Joins etc.)
• Mehrere Refresh-Methoden bei Datenänderungen
• Speicherung auf Disk
 “Caching” von speziellen Ergebnissen (transparentes Rewrite)
118
Beispiel einer Materialized View
CREATE MATERIALIZED VIEW MV_Standard
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer
Jahr,
z.monat_desc
Monat,
sum(u.umsatz)
Summe,
a.artikel_id
ID,
count(u.umsatz)
FROM
f_umsatz u,
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;
119
Data Dictionary Views für MAVs
• Weitreichende Informationen über Zustand der MAVs und
ihrer dazugehörigen Basistabellen
•
•
•
•
•
ALL_MVIEWS
DBA_MVIEWS
USER_MVIEWS
USER_MVIEW_DETAIL_RELATIONS
USER_MVIEW_DETAIL_SUBPARTITION
• Mit 11g wurde der Detailgrad in diesen Views erhöht, vor
allem bei partitionierten Tabellen (Staleness etc.)
120
Skalierung bei der Auswertung
Architekturbasierte Anwendergruppen-Unterstützung
..
Reg
Detaillevel 0
Zeit
Aggregationslevel 1
Aggregationslevel 2
.. ..
MAV
MAV
MAV
MAV
MAV
Org.
Linie
121
Prod
Top Management
(wenige hochverdichtete
Kennzahlen)
Aggregat
Summentabelle
Summentabelle (Meier)
Summentabelle (Müller)
Sachmitarbeiter
Planung / Marketing
(verdichtete Daten)
.. .. .. ..
Mitarbeiter
operative Ebene
(Detaildaten auf dem
Level von operativen
Transaktionen)
Nested Materialized Views
CREATE MATERIALIZED VIEW MV_Umsatz_Monat
ENABLE QUERY REWRITE
AS SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz u,
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;
122
CREATE MATERIALIZED VIEW MV_Umsatz_Jahr
ENABLE QUERY REWRITE
AS SELECT
Jahr,
sum(summe) Summe,
ID artikel_id,
count(summe)
FROM
MV_Umsatz_Monat
GROUP BY
jahr,ID;
Materialized Views
Query Rewrite
Oracle Data Warehouse
123
Text Match – Abarbeitungsreihenfolge
• Textvergleich der SELECT-Liste
• Reihenfolge spielt dabei keine Rolle
• Auflösung von möglichen Berechnungen
• Vergleich der Join-Bedingung
• Vergleich der GROUP BY-Klausel
124
Exaktes Text-Matching
SELECT
z.jahr_nummer Jahr,
sum(u.umsatz)Summe,
a.artikel_id ID,
z.monat_desc Monat
FROM
f_umsatz u,
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;
SELECT
z.jahr_nummer Jahr,
avg(u.umsatz) Schnitt,
a.artikel_id ID,
z.monat_desc Monat
FROM
f_umsatz u,
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;
• Umstellen der Spalten und avg() anstelle von sum()
125
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
F_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
Ableitung von Aggregaten
Wenn gebraucht
COUNT(expr)
Voraussetzung
Optional
-
-
COUNT(expr)
-
MIN(expr)
MAX(expr)
SUM(expr)
SUM(col),
col has NOT NULL constraint
126
-
AVG(expr)
COUNT(expr)
SUM(expr)
STDDEV(expr)
COUNT(expr)
SUM(expr)
SUM(expr * expr)
VARIANCE(expr)
COUNT(expr)
SUM(expr)
SUM(expr * expr)
Prinzip Aggregate Rollup
SELECT
z.jahr_nummer Jahr, --> Bezugsgröße in MAV ist Monat
sum(u.umsatz) Summe,
a.artikel_id ID,
count(u.umsatz)
FROM
f_umsatz u,
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, a.artikel_id;
• Abfragen lässt sich alles, was in der GROUP BY-Klausel
der MAV zu finden ist
127
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
F_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
Join Back-Methode
SELECT
z.jahr_nummer Jahr,
z.monat_desc Monat,
a.artikel_name Artikel,
sum(u.umsatz) Summe
FROM
Ist nicht in der
MAV-Definition
enthalten
f_umsatz u,
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_Name;
• Join Back-Tabelle muss einen Primary Key nutzen
128
D_ZEIT
DATUM_DESC
TAG_DES_MONATS
WOCHE_DES_JAHRES
JAHR_NUMMER
QUARTALS_NUMMER
MONATS_NUMMER
MONAT_DESC
DATUM_ID
sum / count
F_UMSATZ
ARTIKEL_ID
ZEIT_ID
KUNDE_ID
REGION_ID
UMSATZ
MENGE
BESTELL_DATUM
F_ARTIKEL
SPARTE_NAME
SPARTE_NR
GRUPPE_NAME
GRUPPE_NR
ARTIKEL_NAME
ARTIKEL_ID
Materialized Views im Detail
Verwaltung
Oracle Data Warehouse
129
EXPLAIN_MVIEW – Auswertung
start D:\O11\db11\RDBMS\ADMIN\utlxmv.sql
SQL> desc MV_CAPABILITIES_TABLE;
Name
Null?
----------------------------------------- -------STATEMENT_ID
MVOWNER
MVNAME
CAPABILITY_NAME
POSSIBLE
RELATED_TEXT
RELATED_NUM
MSGNO
MSGTXT
SEQ
130
Typ
--------------------VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
CHAR(1)
VARCHAR2(2000)
NUMBER
NUMBER(38)
VARCHAR2(2000)
NUMBER
EXPLAIN_MVIEW-Routine
• Zeigt auf, welche Funktionen für eine jeweilige MAV genutzt
werden kann
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');
131
EXPLAIN_MVIEW-Routine
SELECT capability_name, possible p, substr(related_text,1,20) obj,
substr(msgtxt,1,100) erklaerung FROM mv_capabilities_table;
CAPABILITY_NAME
-----------------------------PCT
REFRESH_COMPLETE
REFRESH_FAST
REWRITE
PCT_TABLE
PCT_TABLE
REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ONETAB_DML
P
N
Y
N
Y
N
N
N
N
N
N
N
CAPABILITY_NAME
-----------------------------REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ANY_DML
REFRESH_FAST_PCT
P OBJ
ERKLAERUNG
- --------------------------------------------------------------------N
SUM(expr) ohne COUNT(expr)
N
Siehe Grund, warum REFRESH_FAST_AFTER_ONETAB_DML deaktiviert ist
N
PCT bei keiner der Detail-Tabellen in der Materialized
View m÷glich
Y
Y
Y
N
Allgemeines Neuschreiben nicht m÷glich oder PCT bei keiner
der Detail-Tabellen m÷glich
N F_UMSATZ
Relation ist keine partitionierte Tabelle
N D_ARTIKEL
Relation ist keine partitionierte Tabelle
REWRITE_FULL_TEXT_MATCH
REWRITE_PARTIAL_TEXT_MATCH
REWRITE_GENERAL
REWRITE_PCT
PCT_TABLE_REWRITE
PCT_TABLE_REWRITE
132
OBJ
ERKLAERUNG
------------------------------------------------------------------------
F_UMSATZ
D_ARTIKEL
MAV.F_UMSATZ
MAV.D_ARTIKEL
SUM(U.UMSATZ)
Relation ist keine partitionierte Tabelle
Relation ist keine partitionierte Tabelle
Detail-Tabelle enthΣlt kein Materialized View-Log
Detail-Tabelle enthΣlt kein Materialized View-Log
SUM(expr) ohne COUNT(expr)
Siehe Grund, warum REFRESH_FAST_AFTER_INSERT deaktiviert ist
COUNT(*) ist in SELECT-Liste nicht vorhanden
EXPLAIN_REWRITE-Routine
Angabe der Bedingungen für Query Rewrite
@D:\app\aths\product\11.1.0\db_1\RDBMS\ADMIN\utlxrw.sql
SQL> desc rewrite_table
Name
Null?
----------------------------------------- -------STATEMENT_ID
MV_OWNER
MV_NAME
SEQUENCE
QUERY
QUERY_BLOCK_NO
REWRITTEN_TXT
MESSAGE
PASS
MV_IN_MSG
MEASURE_IN_MSG
JOIN_BACK_TBL
JOIN_BACK_COL
ORIGINAL_COST
REWRITTEN_COST
FLAGS
RESERVED1
RESERVED2
133
Typ
--------------VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
NUMBER(38)
VARCHAR2(4000)
NUMBER(38)
VARCHAR2(4000)
VARCHAR2(512)
VARCHAR2(3)
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(4000)
VARCHAR2(4000)
NUMBER(38)
NUMBER(38)
NUMBER(38)
NUMBER(38)
VARCHAR2(10)
EXPLAIN_REWRITE-Routine
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
f_umsatz u,
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;
MV_UMS_ART_ZEIT_JOIN
QSM-01082: Materialized View, MV_UMS_ART_ZEIT_JOIN, kann nicht mit Tabelle, F_UMSATZ,
verknpft werden
MV_UMS_ART_ZEIT_JOIN
QSM-01102: Materialized View, MV_UMS_ART_ZEIT_JOIN, erfordert Join zurck zu Tabelle, F_UMSATZ,
in Spalte, UMSATZ
134
Analytische Funktionen im SQL
Oracle Data Warehouse
135
Analytische Funktionen
• Seit Oracle 8.1.6 dabei
• Speziell für analytische Aufgaben wie:
• BI-Applikationen, Berichte, Ad-Hoc-Abfragen
• Sind ANSI Standard SQL-konform (SQL-99)
• Bieten erweiterte Abfrage-Performance
• Verarbeitung ist effizient und skalierbar
• Kann Verarbeitungslast der Anwendungen entlasten
136
Welche Antworten man von analytischen
Funktionen erwarten kann
• Welches sind die Top 10 Kundenberater in jeder
Region?
• Welches sind die 90-Tage-Durchschnitte der
Produktbestände?
• Welchen Prozentsatz vom Jahres-Total machen die
Dezember-Verkäufe aus?
• Welchen Rang hat ein Produkt, das für 100000
verkauft worden ist, verglichen mit anderen in seiner
Kategorie?
• Welches ist der Median von den Verkäufen pro
Produkt und Region?
137
Arbeitsweise analytischer Funktionen
• Analytische Funktionen berechnen einen aggregierten
Wert basierend auf einer Gruppe (Partition / Window)
von Zeilen
• Syntax:
FUNCTION_NAME (<arg>,<arg>....)OVER
(<partition clause><order by clause><windowing clause>)
• Ausführungsablauf:
JOIN, WHERE,
GROUP BY,
HAVING
138
Partitionen erzeugen,
Funktionen
auf Partionen anwenden
ORDER BY
Favoriten: Beliebte Funktionen
•
•
•
•
•
•
•
•
139
LAG / LEAD: Vorgänger und Nachfolger ohne SELF-JOIN
FIRST / LAST oder MIN / MAX
ROW_NUMBER: vergibt eindeutige Zahl, z.B. für TOP-N
RANK, DENSE_RANK: Rangfolge
RATIO_TO_REPORT: Verhältnis zur Summe
Aggregatfunktionen wie AVG, SUM
NTILE: Aufteilung in sog. Buckets
PERCENTILE_CONT/_DISC: zur Berechnung des Median
Beispiel für analytische Funktionen
• Finde das höchste Gehalt in jeder Abteilung und gib die
Liste der Gehälter der zugehörigen Angestellten aus
• 1.Schritt:
SELECT department_id, MAX(salary) FROM employees
GROUP BY department_id
• 2.Schritt: und für die Angestellten noch eine Subquery
(korreliert ?) ..
140
Einfache Anwendung...
SELECT department_id dept, first_name || ' ' || last_name
name, salary
FROM
(SELECT department_id, first_name, last_name,
MAX(salary) OVER (PARTITION BY department_id) dept_max_sal,
salary FROM employees e)
WHERE salary = dept_max_sal;
DEPT
NAME
SALARY
---------- -------------------- -----------------10 Jennifer Whalen
20 Michael Hartstein
13000
30 Den Raphaely
11000
......
141
4400
...Und hohe Flexibilität
• Doch eigentlich interessiert das höchste Gehalt je Job...
SQL> SELECT job_id job, first_name || ' ' || last_name name, salary
FROM
(SELECT job_id, first_name, last_name,
MAX(salary) OVER (PARTITION BY job_id) job_max_sal,
salary FROM employees e)
WHERE salary = job_max_sal;
JOB
NAME
SALARY
---------- --------------------- ---------AC_ACCOUNT William Gietz
AC_MGR
Shelley Higgins
12000
AD_ASST
Jennifer Whalen
4400
AD_PRES
Steven King
....
142
8300
24000
Zusätzliche Funktionalitäten
• Sortierung innerhalb einer Partition
• Ohne Angabe der Partition-Klausel wird auf der
gesamten Menge gearbeitet
• Jede analytische Funktion kann ihre eigene PartitionKlausel haben
• Windowing-Klausel für Teiloperationen in einer Partition
• Physikalische Rows: Anzahl, von Vorgänger bis Nachfolger etc.
• Logische Wertebereiche (Ranges)
143
Abfrage der Top 10 Artikel
ArtikelSparte
Zeit
Artikelgruppe
Dimension
Artikel
Artikel
Umsatz
Top 10 Artikel
SELECT * FROM
(SELECT substr(A.Artikel_Name, 1, 15), SUM(U.umsatz) AS Wert,
Region
Kunde
RANK() OVER (ORDER BY SUM(U.umsatz)
DESC ) AS Rangfolge
FROM f_umsatz U, d_artikel A
WHERE U.artikel_ID = A.artikel_ID GROUP BY A.Artikel_Name)
WHERE rownum < 11;
144
Abfrage Top 3 Artikel pro Artikelgruppe
ArtikelSparte
Zeit
Artikelgruppe
Dimension
Artikel
Artikel
Umsatz
Top 3 Artikel pro Gruppe
SELECT substr(Artikel,1,25) AS Artikel, substr(Artikelgruppe,1,25)
AS Artikelgruppe, Wert, Rangfolge
FROM
(SELECT Artikel, Artikelgruppe, SUM(U.umsatz) AS Wert, RANK() OVER
(PARTITION
BY A.Artikelgruppe
Region
Kunde
ORDER BY SUM(U.umsatz) DESC) AS Rangfolge
FROM f_umsatz U, dim_artikel A WHERE U.artikel_ID = A.artikel_ID
GROUP BY A.Artikelgruppe, A.Artikel
ORDER BY A.Artikelgruppe)
WHERE Rangfolge < 4;
145
Über Quartale kumulierte Umsätze
Pro Kunde
Q1_2003
Q4_2002
Q3_2002
Q2_2002
Q1_2002
Zeit
Name
Dimension
Kunde
Jahr
Quartal
Umsatz
Region
146
SELECT substr(k.kunden_Name, 1, 25) AS kunde,
Z.jahr, Z.quartal_des_jahr AS Quartal,
SUM(U.umsatz) AS Umsatz, SUM(SUM(U.umsatz)) OVER
(PARTITION BY K.kunden_Name
ORDER BY K.kunden_Name, Z.jahr, Z.quartal_des_jahr
ROWS UNBOUNDED PRECEDING)
AS Umsatz_Summe
FROM dim_kunde K, f_Umsatz U, dim_zeit Z
WHERE K.kunde_id = U.kunde_id AND
to_char(Z.Datum) = to_char(U.Datum)
GROUP BY K.kunden_Name, Z.jahr, Z.quartal_des_jahr;
Kunde
¼ der Kunden tragen zu wieviel
Prozent des Umsatzes bei?
1
Zeit
Buckets
2
Name
Dimension
Kunde
3
4
Umsatz
1
Region
2
147
SELECT SUM(umsatz), anteil,
(SUM(umsatz) * 100 / Gesamt_umsatz) AS Prozent
FROM
(SELECT substr(K.nachname, 1, 25)
AS kunde, SUM(U.umsatz) AS Umsatz,
NTILE(4) OVER (ORDER BY sum(U.umsatz))
AS Anteil
FROM Kunde
d_kunde K, f_Umsatz U
WHERE K.kunden_id = U.kunde_id
GROUP BY K.nachname),
(SELECT SUM(U.umsatz) AS Gesamt_Umsatz
FROM f_Umsatz U)
GROUP BY anteil, Gesamt_umsatz;
Durchschnittliche Bestellquote eines
Kunden über 3 Monate
M5_2002
M4_2002
M3_2002
M2_2002
M1_2002
Zeit
Name
Dimension
Kunde
Jahr
Monat
Umsatz
Region
148
SELECT substr(K.kunden_Name,1,25) AS kunde,
Z.jahr AS Jahr, Monat_des_jahres AS Mon,
SUM(u.umsatz) AS Umsatz, AVG(SUM(u.umsatz))
OVER (ORDER BY K.kunden_Name, Z.jahr,
Z.Monat_des_jahres ROWS 2 PRECEDING) AS
Mov_3M_AVG
FROM dim_kunde K, f_Umsatz U, dim_zeit Z
WHERE K.kunde_id = U.kunde_id
AND to_char(Z.Datum) = to_char(U.Datum)
AND K.kunden_name
Kunde= 'Bauer'
GROUP BY K.kunden_Name, Z.jahr, Z.Monat_des_jahres
ORDER BY Z.jahr, Z.Monat_des_jahres;
Das Bundesland mit dem stärksten
Umsatz für jede Artikelgruppe
Region
Bundesland
Artikelgruppe
Dimension
Artikel
Artikel
Ort
Region
Kreis
Region
149
Umsatz
SELECT ArtGr, Land, Umsatz
FROM
(SELECT Artikelgruppe AS ArtGr,
Bundesland AS Land,
SUM(umsatz)AS Umsatz,
MAX(SUM(umsatz)) OVER
(PARTITION BY Artikelgruppe) AS Max_Ums_Land
FROM dim_region R, dim_artikel A, f_umsatz U
WHERE R.ort_ID = U.ort_ID
AND A.Artikel_ID = U.artikel_ID
GROUP BY Artikelgruppe, Bundesland
ORDER BY Artikelgruppe, Bundesland)
WHERE Umsatz = Max_Ums_Land ;
Vergleiche Umsätze mit Vorjahreszeitraum
M2_2003
M1_2003
M12_2002
M11_2002
M10_2002
M9_2002
M8_2002
M7_2002
M6_2002
M5_2002
M4_2002
M3_2002
M2_2002
M1_2002
Zeit
Jahr
Monat
Region
150
Umsatz
Name
Dimension
Kunde
SELECT substr(K.kunden_Name,1,25) AS
kunde, z.jahr AS Jahr, Monat_des_jahres
AS Mon, SUM(U.umsatz) AS Umsatz,
LAG(SUM(U.umsatz),12) OVER (ORDER BY
Z.jahr, Z.Monat_des_jahres) AS vorjahr
FROM dim_kunde
Kunde K, f_Umsatz U, dim_zeit Z
WHERE K.kunde_id = U.kunde_id
AND to_char(Z.Datum) = to_char(U.Datum)
AND K.kunden_name = 'Bauer'
GROUP BY K.kunden_Name, Z.jahr,
z.Monat_des_jahres
ORDER BY z.jahr, z.Monat_des_jahres;
Eine der SQL-Neuigkeiten in 11.2
Die LISTAGG-Funktion aggregiert VARCHAR2
• Neue Aggregatsfunktion für Zeichenketten
• Beispiel (Tabelle EMP):
select deptno,
listagg(ename, ':') within group (order by ename) ename_list
from emp
group by deptno
DEPTNO ENAME_LIST
---------- ---------------------------------------10 CLARK:KING:MILLER
20 ADAMS:FORD:JONES:SCOTT:SMITH
30 ALLEN:BLAKE:JAMES:MARTIN:TURNER:WARD
151
Aggregate und Materialized Views
• Bereitstellung von Aggregaten auch in Form von
Materialized Views
•
•
•
•
Spart separaten ETL-Lauf
Ist flexibler, weil nur ein SQL-Statement ausgeführt wird
Weitreichende Funktionalitäten für SQL-Query-Rewrite
Aufbau eines mehrstufigen Kennzahlensystems möglich
 Optimierung durch OLAP-basierte Materialized Views
152
Grundlagen von
Oracle OLAP
8 Gründe für OLAP
• Performance
• Multidimensionale Technologie
ermöglicht maximale Performance
für Ad Hoc-Analysen
• Managebility
• Einfache Administration in / mit
der Datenbank
• MAV
• Lösung für viele gleichartige
MAVs
• Nähe zum ODS
• Drill -Through und Recalculation
leicht gemacht
• DB Calculation Power
• Datenbank-Engine berechnet
komplexeste Formeln,
Verhältniskennzahlen u.v.m.
• Mehr als Reporting
• OLAP-Analysen gehen über
traditionelles Reporting hinaus bis
hin zu hypothetischen und
kausalen Fragestellungen
• Benutzer-Logik
• Keine Einarbeitung und DesktopEinbindung notwendig
• Offenheit
• Support aller BI-Applikationen
durch die Offenheit der Datenbank
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
Komplexe Fragestellungen
Rückblick / IST-Zustand
•
Was sind die Top10-Märkte?
•
Abweichung zum Forecast?
•
Welche Kunden, Produkte, Dienste sind
profitabel?
•
Umsatz pro Produkt im Vergleich zum
letzten Jahr?
•
Verkaufszahlen pro Produkt, pro Filiale
und pro Quartal?
•
Entwicklung Personalkosten?
•
Personalbestand vs. offene Stellen?
Vorhersage / SOLL-Zustand
•
Wie können Promotions unsere Verkäufe um
10% steigern?
•
Wie ändert sich das Ergebnis, wenn der USDollar um 5% fällt?
•
Wenn Rohstoffpreise um 20% steigen, was heißt
das für den Gewinn?
•
Mit welchen Kunden erzielen wir 80% unseres
Gewinns?
•
Wie ändern sich die Verkäufe einer Filiale bei
einem um 5% höheren Warenumschlag?
OLAP: Daten als Cube organisiert
Umsätze
Kosten
Deckungsbeitrag in %
Zeit
Zelle
Slicing und Dicing mit OLAP
Zeit
Wann?
Sicht des
Vertriebs
Sicht der
Produktmanager
Region
Wo?
Indikatoren
Produkt
Was?
Sicht des
Controllings
Ad HocAnalysen
Vorteile der Integration in die Datenbank
Report
Dashboard Web
Service
Analysis
• Business Rules liegen im
Data Dictionary
• Rules werden einmalig definiert
zur Erstellung des OLAP Cubes
• Zugriff mit allen Client Tools und
Applikationen
• Komplexität der Berechnung
wird in die Datenbank verlegt
• Vereinfacht die Implementation
(Daten sind bereits im DWH)
• Effiziente Verarbeitung
Bestandteile von Oracle OLAP
• OLAP Catalog
• Speichert das logische Modell der Cubes (Metadaten)
• Analytic Workspace
• Enthält die multidimensionalen Daten in der Datenbank
(gespeichert als LOB-Daten)
• OLAP Calculation Engine
• Ehemals eigenes Produkt (Oracle Express Server)
• Seit Oracle 9iR2 integriert in der Datenbank
• Schnittstellen mit Applikationen
• OLAP DML
• SQL
OLAP Cubes anlegen
• Metadaten der Cubes liegen im OLAP Catalog
• Messgrößen, Cubes und Dimensionen
• Level, Hierarchien und Attribute
• Definition der Metadaten erfolgt hauptsächlich über
zwei Werkzeuge
• Analytic Workspace Manager
• Wird zusammen mit dem Oracle DB Client installiert
• Speziell auf den Aufbau von OLAP Cubes zugeschnitten
• Oracle Warehouse Builder
• Fast gleicher Arbeitsgang wie beim Anlegen eines Star oder
Snowflake Schemas
Kennzahlen und Dimensionen
• Die Kennzahlen stellen die Fakten dar, die Zellen des
Cubes bilden die einzelnen Werte
• Kennzahlen setzen sich aus zwei oder mehr
Dimensionen zusammen
• Es gibt nicht nur das numerische Format, sondern
auch Text, Boolean, etc.
• Die Kanten des Cubes werden mit den Dimensionen
definiert
• Die Dimensionen weisen durch Pointer auf die
angeforderten Zellen im Cube
Ein großer vs. zwei kleine Cubes
Dimension
Members
D1
100
D2
Data Points
Dimension
Members
100
D1
100
100
100
10,000
D2
100
10,000
D3
100
1,000,000
D3
100
1,000,000
D4
100
100,000,000
D5
100
10,000,000,000
Dimension
Members
D6
100
1,000,000,000,000
D1
100
100
D2
100
10,000
D3
100
1,000,000
D4
100
100,000,000
Ein großer Cube mit 1
Trillion Zellen
Data Points
Data Points
Zwei kleinere Cubes
mit insgesamt 101
Millionen Zellen
OLAP und Materialized Views
in Oracle 11g
Wachsende Anzahl an Materialized Views
• Bei zunehmender Menge von MAVs
• Können zahlreiche Ergebnisse für verschiedene Nutzer
vorgehalten werden
• Wird die Administration erschwert (Indexes, Storage,
Anlegen und Löschen welcher MAVs, etc.)
Month, City, Category
Qtr, State, Item
Month, State
SALES_MCC
SALES_QSI
SALES_MS
month_id
category_id
city_id
quantiy
sales
qtr_id
item_id
state_id
quantiy
sales
month
state
quantiy
revenue
Year, City, Category
Year, Region
SALES
SALES_YC
year_id
category_id
city_id
quantiy
sales
day_id
prod_id
cust_id
chan_id
quantity
sales
year_id
region_id
quantity
revenue
Year, Region, Category
Year, District
SALES_YCC
SALES_YCC
SALES_YCT
year_id
category_id
region_id
quantiy
sales
year_id
type_id
region_id
quantiy
sales
Cust, Time, Prod, Chan Lvls
SALES_XXX
XXX_id
SALES_XXX
XXX_id
SALES_XXX
XXX_id
expense_amount
SALES_XXX
XXX_id
XXX_id
potential_fraud_cost
XXX_id
XXX_id
XXX_id
XXX_id
XXX_id
XXX_id
expense_amount
expense_amount
XXX_id
potential_fraud_cost
potential_fraud_cost
quantiy
revenue
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
Vorteile von Cube-organized MAVs
• Ein Cube ersetzt viele Summierungs-Kombinationen
(implementiert als Materialized Views)
• Der Query Optimizer in Oracle 11g behandelt OLAP
Cubes als Materialized Views und leitet SQLAbfragen transparent auf den Cube um
• Der Cube wird mit den für Materialized Views
verfügbaren Mechnismen aktualisiert
Hardware Komponenten
169
Balanced Konfigurationen
• Anzahl CPU‘s
• ~200 MB Datendurchsatz pro CPU
• Anzahl CPU = Max. Durchsatz in MB/s / 200
• Größe des
Hauptspeichers
• Anzahl Platten
• Größe des Speichers in GB = 2 * Anz. CPUs
• Trennung von Storage für
OLTP und DWH-Systeme !!
• Schnelle Platten nutzen (15000 U/min)
• Eher mehr, kleine Platten nutzen,
als wenige große Platten nutzen
• Flash-Speicher in Betracht ziehen
• ASM in Betracht ziehen
•
• Anzahl Disk Controller
Einfaches und DB-optimiertes Verwalten
Anzahl Disk Controller =
Controllerdurchsatz in MB =
170
Max. Durchsatz in MB/s
Controllerdurchsatz in MB
70% * Herstellerangaben in Gbit/s
8
Messung von IO-Durchsatz
• Einfache Schätzmethode
• Calibrate_IO
• Read-only Test
• Wenige Test-Optionen -> leicht anwendbar
• > 11g
• Orion (ORacle IO Numbers)
•
•
•
•
•
171
Read / Write – Tests (Achtung schreibt auf Platten)
Viele Test-Optionen
OLTP + DWH Workload
Ab 11.2 im BIN-Verzeichnis der DB
www.oracle.com/technology/software/tech/orion/index.html
ASM
• Verwalten ganzer Gruppen von Platten
• Keine Einzelaktionen
• DBA übernimmt die Storage-Verwaltung
• Gewohnte Kommandos… SQL Create…
• SAME in der DB
• Verlagern des Striping and Mirroring Everything in die Verantwortung der
Datenbank
• Automatische Verteilung der Daten über alle Platten
• Verhindert von Hotspots
• Messung von IO-Zugriffen über DB-Statistiken (ist klassischen RAIDVerfahren überlegen)
• Bequemes Hinzufügen /Wegnehmen von Platten
• Verhindert Fragmentierung der Platten
• Einführung von ASM kann bis 25% verbessertes IOVerhalten liefern
• Performance kommt an Raw Devices heran
172
ASM Architektur
173
Options: RAC
Der physische Aufbau einer
RAC-Umgebung
Öffentliches Netzwerk
Privates Netzwerk
(Interconnect)
CPU
CPU
CPU
Knoten 1
CPU
CPU
Knoten 2
CPU
CPU
Instanz 1
CPU
Instanz 2
Speichernetzwerk
Daten
174
‘Typische’ Cluster Konfiguration – 2005
1 Gigabit ethernet
4 nodes, each with
4 x 2 Ghz CPUs
5 PCI slots
16-port switch
16-port switch
16 Storage arrays, each with
10-20 disks
175
Performance und Systemzustand
überwachen / Hilfsmittel
•
•
•
•
•
•
2) Perfstat
1) Alerts
3) AWR (EE, Diagnostic Pack)
ADDM (EE, Diagnostic Pack)
SQL Tuning
ASH
analog
Polling
Beginn-Zeitpunkt
Ende-Zeitpunkt
Tracing Permanente
Betrachtung
Protokolldatei
176
Automatic Database Diagnostic Monitor
(ADDM) und AWR
DBMS_ADVISOR Package
Statistics_level
OEM
Addmrpt.sql
TYPICAL -> ON
BASIC -> OFF
1
AWR-Report
ADDM
Findings
1……nn%
2……nn%
3……nn%
…….
use
stündlich
AWR
2
MMONProcess
Recommendations
3
User 1
User 2
sysaux
Action
8 Tage lang
4
Undo Advisor
177
SQL Tuning Advisor
Segement Advisor
Rationale
- Hardware
- Init-Parameter
- Space Konfig.
- Performance
Advisor
AWR (Analytic Workload Repository)
• Regelmäßiges Sammeln von einer Vielzahl von System-generierten Statistiken
• Mit Hintergrundprozessen (MMON)
• Gespeicherte Statistiken des MMON in SYSAUX Tablespace
• Vorkonfiguriert generiert AWR alle 60 Minuten Snapshots
• Parameter STATISTICS_LEVEL (Basic/Typical/All)
• Basic schaltet das Sammeln aus
• Retention-Time (Default 8 Tage)
• DBA_HIST_* - Views zur Auswertung
• Manuell starten
• execute dbms_workload_repository.create_snapshot(‘ALL‘);
• Auswerten
• Awrrpt.sql
• OEM
178
ADDM
(Automatic Database Diagnostic Monitor)
• Automatic Database Diagnostic Monitor (ADDM)
• Gezielte Auswertung von AWR Daten
• Liefert Informationen zu
•
•
•
•
•
Besonders teuere SQL-Statements
I/O – Performance
Locking-Situationen
Ressourcen-Engpässe (Speicher, CPU bottlenecks)
Exzessive Logon/Logoff-Aktivitäten
• Manuelle Berichtserstellung: ADDMRPT.SQL
179
Art der Information
• „Intelligente“ selbständige Analyse von Zuständen und
Vorkommnissen in der DB
• „Findings“
• Basierend auf Erfahrungswerte und Best-Practises
• Sortiert nach der Schwere und dem Grad der Beeinflussung
• „Recommendations“
• Allgemeine Empfehlung mit einer Abschätzung über die prozentuale
Gewichtung der Verbesserung der Situation
(nn% benefits)
• Konkreter „Action“-Vorschlag
• „Rationale“ Vorschlag: Sonstige, damit in Verbindung stehende
Massnahmen.
180
ADDM-Screen
Snapshot mit „Findings“
Weiterführende Aktivitäten
181
182
Oracle Database Machine (Exadata)
Traditionelle
Umgebung
Extreme Performance
Einführungsdauer
0,5 – 5 GB/sec
Monate
Database
Machine
25/50 GB/sec
Tage
Personaleinsatz
Tuning
Platten-, Server-,
Netz-Integration
Investionen
notwendig
minimal
(vorkonfiguriert)
sorgfältige
Planung
erforderlich
Ausgewogenes
System
Oracle Database Machine X2-2
Oracle Database Server Grid
• 8 compute servers
• 96 Intel Cores (gesamt)
(Six-Core Intel X5670, 2,93 GHz)
• 768 GB DRAM (gesamt)
• Jeder Server
• 2x10Gb Ethernet Port
• 4x1Gb Ethernet Port
• 4x300 GB SAS Disks
InfiniBand Network
• 40 Gb/sec unified server and
storage network
• Fault Tolerant
Enterprise Linux
Exadata Storage Server Grid
• 14 storage servers
168 Platten / 112 Intel Cores
• 100 TB raw SAS disk storage
or
336 TB raw SATA disk storage
• 5,3TB flash storage!
25 GB / Sec IO – Datendurchsatz
50 GB / IO für Flash-Speicher
Query Processing:
Das Problem mit klassischem Storage
What Were
Yesterday’s
Sales?
Oracle Database Server
Grid
Select sum(sales)
where salesdate=
‘22-Dec-2009’ …
SUM
Storage Array
Retrieve
Entire Sales
Table
Query Processing:
Bei Exadata fliessen weniger Daten durch das Netz
What Were
Yesterday’s
Sales?
Oracle Database Server
Grid
Select sum(sales)
where salesdate=
22-Dec-2009’ …
SUM
Oracle Exadata Storage
Grid
Retrieve Sales
for Dec 22 2009
Hybrid Columnar Compression
Storage und IO sparen (Beispiele)
Anzahl
Sätze
GB
Vor K.
EHCC
Query Low
T4
22.241.978
587.794.948
17.952.967
43
550
29
11
11.3
8.6
28.5
17.3
16.6
28.5
18.5
17.3
40.6
24
24.9
T5
34.341.563
63
4.8
9.1
10.2
12.4
T6
354.985.310
360
9.9
10.9
26.6
39.1
T7
60.703.833
84
9.4
19.5
19.5
23.6
Table
T1
T2
EHCC Query EHCC Archive
EHCC
High
Low
Archive High
DATA WAREHOUSE
BigData – neue Optionen für das
Data Warehouse
Alfred Schlaucher, Oracle
Klassische Daten
• Messbare Größen und
Einheiten
• Transaktionsbezogene
Daten
• Misst OLTP-Systeme
• Definierte Kennzahlen
• 1 – 100 TB
• Tabellen und Spalten
• Täglich neue Daten
• Jeder Satz ist relevant
• „Buchungs“relevant
• Zeitrelevant
• Wachstum messbar
Der größte Teil der entstehenden Daten
wird noch nicht der Analyse zugeführt
• Verkehrsströme
• Kontaktinformationsdaten / CRM
• Briefwechsel
• Vertragsunterlagen
• Mailverkehr
• EnergieVerbrauchsdaten
• Treuepunkt-Daten
• Mobile-Banking
• Verbrechensprofile
• Auto-Mobilitätsdaten
• FahrzeugInformationssysteme
• Maschinen-Messdaten
Big Data: Potentielle Anwendungsfälle
Aufgabenstellung
„Neue“ Daten
Lösungen
Healthcare
Teures Gesundheitssystem
Remote Erfassung
von Patientendaten,
Krankenverläufe etc.
Genauere+günstigere
Medikation
Weniger Krankenhausaufenthalte
Produktion
Personeller Support
Location Based Services
Öffentlicher Dienst
Bürger-Angebote
Retail
Marketing
Sensoren an Maschinen
und Anlagen
Realtime
Bewegungsdaten
potentieller Kunden
Bevölkerungsstatistiken
Verbrauchsdaten
Soziale Netzwerke /
Medien
Remote – Support
Ausfallvorhersagen
Geo-bezogenes Marketing,
Besucherstrom-Analyse
Verkehrsanalysen
Individualisierte Dienste
Kostensenkung
Stimmungsanalysen
Genauere Segmentierungen
BigData bedeutet nicht nur „Viele Daten“
sondern erweiterte Analysen mit anderen Daten
• Messbare Größen und
Einheiten
• Transaktionsbezogene
Daten
• Misst OLTP-Systeme
• Definierte Kennzahlen
• 1 – 100 TB
• Tabellen und Spalten
• Täglich neue Daten
• Jeder Satz ist relevant
• „Buchungs“relevant
• Zeitrelevant
• Wachstum messbar
+
• Keine klassischen
Masseinheiten
• Daten entstehen durch
zufällige Begebenheiten
• „Abfallprodukt“
• Die Relevanz ist zunächst
noch unbestimmt
• Ansammlung von
unterschiedlichen Objekten
• Mengen und Anhäufungen
sind interessant
• Einzelnes Objekt ist unwichtig
• Wachstum indifferent
Big Data: Infrastruktur Anforderungen
Acquire
Organize
• Unvorhersehbares Auftreten
• Hohe Datenmengen
• Flexible Daten-Strukturen
• Arbeiten mit vielen Servereinheiten
• Abfragen mit extrem
hohen Daten-Durchsatz
• Bearbeitung am
Speicherplatz
•Hohe Parallelisierung
Analyze
• Exporative Analyse
•Komplexe statistische
Analysen
• Agile
Berichtsentwicklung
• Massive Skalierung
• Real Time Ergebnisse
Heutige Lösungen sind isoliert und
“handgemacht”
Data
Variety
Unstructured
Distributed
HDFS
File Systems
(z. B. HDFS)
Schema-less
Schema
Information
Density
Transaction (KeyOracle
Value)Stores
NoSQL
DB
(Cassandra)
DBMS
RDBMS
(OLTP)
(OLTP)
Acquire
NoSQL
Hadoop
MapReduce
Solutions
(Hadoop MapReduce)
Oracle Loader for
“R”
Hadoop
Home
Grown
ETL
ETL
ETL
Home
Advanced
DBMS
RDBMS Analytics
Grown
Advanced
(DW)
(DW)
BI
Analytics
Organize
Analyze
Flexible
Specialized
Developer
Centric
SQL
Trusted
Secure
Administered
Oracle’s integrierte Software Lösung
Data
Variety
Unstructured
Cloudera
HDFS
Schema-less
Schema
Information
Density
Oracle
NoSQL DB
Hadoop
Oracle
MapReduce
Oracle
Hadoop
Loader
Acquire
Mining
R
Spatial
Graph
Oracle
Data Integrator
Oracle
(OLTP)
Oracle
(DW)
Organize
Oracle
Analytics
OBI EE
Analyze
Oracle Engineered Systems
Data
Variety
Unstructured
Exalytics
Schema-less
Schema
Information
Density
Big Data
Appliance
Exadata Database Machine
Acquire
Organize
Analyze
Big Data Appliance
Hardware:
• 216 CPU cores with 864 GB RAM
• 648 TB of raw disk storage
• 40 Gb/s InfiniBand
Integrated Software:
•
•
•
•
•
•
Oracle Linux
Oracle Java VM
Cloudera Distribution of Apache Hadoop (CDH)
Cloudera Manager
Open-source distribution of R
NoSQL Database Community Edition
All integrated software (except NoSQL DB CE) is supported as part of
Premier Support for Systems and Premier Support for Operating
Systems
Oracle Loader for Hadoop
ORACLE LOADER FOR HADOOP
• Leverage Hadoop
Cluster to pre-process
data for loading
MAP
REDUCE
MAP
MAP
SHUFFLE
/SORT
MAP
REDUCE
MAP
MAP
REDUCE
REDUCE
SHUFFLE
/SORT
Last stage in
MapReduce
workflow
Partitioned and
non-partitioned
tables
REDUCE
Online and offline
loads
Oracle Direct Connector for HDFS
• Direct Access from
Oracle Database
HDFS
Oracle Database
SQL Query
SQL access to HDFS
External
Table
External table view
Data query or import
Infini
Band
DCH
DCH
DCH
HDFS
Client
Oracle NoSQL Database
A distributed, scalable key-value database
• Simple Programming and Operational Model
• Simple Major + Sub key and Value data structure
• ACID transactions
• Configurable consistency & durability
• Scalable throughput, bounded latency
• Commercial Grade Software and Support
Application
Application
NoSQLDB Driver
NoSQLDB Driver
• General-purpose
• Reliable – Based on proven Berkeley DB JE HA
• Easy to install and configure
• Easy Management
• Web-based console, API accessible
• Manages and Monitors: Topology; Load;
Performance; Events; Alerts
Storage Nodes Storage Nodes
Data Center A
Data Center B
R Statistische Programmiersprache
Open source Sprache und
Entwicklungsumgebung
Geeignet für statistische
Berechnungen und
graphische Darstellung
der Ergebnisse
Endbenutzertaugliche
Graphiken
Erweiterbar
Oracle R Enterprise Lösung
Vorher
Kleine Modelle oft nur auf
Benutzer Laptops
Oracle R
Modelle laufen in der
skalierbaren Datenbank
Große Datenmengen
können verarbeitet werden
Nutzt die Performance der
Oracle DB und von Exadata
Gleicher Code nur schneller
Data Mining Provides
Better Information, Valuable Insights and Predictions
Cell Phone Churners
vs. Loyal Customers
Customer Months
Source: Inspired from Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management by Michael J. A. Berry, Gordon S. Linoff
Data Mining Provides
Better Information, Valuable Insights and Predictions
Cell Phone Churners
vs. Loyal Customers
Customer Months
Source: Inspired from Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management by Michael J. A. Berry, Gordon S. Linoff
Data Mining Provides
Better Information, Valuable Insights and Predictions
Cell Phone Churners
vs. Loyal Customers
Segment #3:
IF CUST_MO > 7 AND
INCOME < $175K, THEN
Prediction = Cell Phone
Churner, Confidence =
83%, Support = 6/39
Segment #1:
IF CUST_MO > 14 AND
INCOME < $90K, THEN
Prediction = Cell Phone
Churner, Confidence =
100%, Support = 8/39
Customer Months
Source: Inspired from Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management by Michael J. A. Berry, Gordon S. Linoff
Data Mining Provides
Better Information, Valuable Insights and Predictions
Cell Phone Churners
vs. Loyal Customers
Segment #3:
IF CUST_MO > 7 AND
INCOME < $175K, THEN
Prediction = Cell Phone
Churner, Confidence =
83%, Support = 6/39
Insight &
Prediction
Segment #1:
IF CUST_MO > 14 AND
INCOME < $90K, THEN
Prediction = Cell Phone
Churner, Confidence =
100%, Support = 8/39
Customer Months
Source: Inspired from Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management by Michael J. A. Berry, Gordon S. Linoff
Data Mining Provides
Better Information, Valuable Insights and Predictions
Cell Phone Fraud
vs. Loyal Customers
?
Customer Months
Source: Inspired from Data Mining Techniques: For Marketing, Sales, and Customer Relationship Management by Michael J. A. Berry, Gordon S. Linoff
Oracle Data Mining Algorithms
Problem
Algorithm
Classification
Logistic Regression (GLM)
Decision Trees
Naïve Bayes
Support Vector Machine
Multiple Regression (GLM)
Support Vector Machine
Regression
Anomaly
Detection
Attribute
Importance
Association
Rules
Clustering
Feature
Extraction
One Class SVM
Minimum Description
Length (MDL)
A1 A2 A3 A4 A5 A6 A7
Apriori
Hierarchical K-Means
Hierarchical O-Cluster
Nonnegative Matrix
Factorization
F1 F2 F3 F4
Applicability
Classical statistical technique
Popular / Rules / transparency
Embedded app
Wide / narrow data / text
Classical statistical technique
Wide / narrow data / text
Lack examples of target field
Attribute reduction
Identify useful data
Reduce data noise
Market basket analysis
Link analysis
Product grouping
Text mining
Gene and protein analysis
Text analysis
Feature reduction
In-Database Data Mining
Traditional Analytics
Oracle Data Mining
Results
Data Import
Data Mining
Model “Scoring”
Savings
Data Preparation
and Transformation
Data Mining
Model Building
Data Prep &
Transformation
Model “Scoring”
Data remains in the Database
Embedded data preparation
Data Extraction
Cutting edge machine learning
algorithms inside the SQL kernel of
Database
Model “Scoring”
Embedded Data Prep
Model Building
Data Preparation
Hours, Days or Weeks
Source
Data
• Faster time for
“Data” to “Insights”
• Lower TCO—Eliminates
• Data Movement
• Data Duplication
• Maintains Security
Dataset
s/ Work
Area
Analytic
al
Process
ing
Process
Output
Target
Secs, Mins or Hours
SQL—Most powerful language for data
preparation and transformation
Data remains in the Database
Oracle Data Miner 11g Release 2 GUI
Churn Demo—Simple Conceptual Workflow
Oracle Data Miner 11g Release 2 GUI
Churn Demo—Simple Conceptual Workflow
Churn models to product
and “profile” likely
churners
Oracle Data Miner 11g Release 2 GUI
Churn Demo—Simple Conceptual Workflow
Market Basket Analysis to
identify potential product
bundless
Oracle Data Mining and Unstructured Data
• Oracle Data
Mining mines
unstructured i.e.
“text” data
• Include free text
and comments in
ODM models
• Cluster and
Classify
documents
• Oracle Text
used to
preprocess
unstructured text
Oracle Communications Industry Data Model Example
Better Information for OBIEE Dashboards
ODM’s predictions & probabilities
are available in the Database for
reporting using Oracle BI EE and
other tools
DWH-bezogenes Monitoring
Oracle Data Warehouse
217
DWH-bezogene Monitoring-Aktivitäten
•
•
•
•
•
•
•
218
ASH-Report
SQL-Monitoring (OEM)
Informationsbedarf Endanwender
Messung Platzverbrauch
Lesestatistiken über tatsächlich genutzte Daten
Ressourcen-Manager
ETL-Monitoring
ASH Reports
Active Session History
• Auflisten der wichtigsten Aktivitäten in den letzten 30
Minuten
•
•
•
•
•
Langläufer
Waits
Top SQL-Statements
Aktive Session
Blocking Sessions
• Report erzeugen mit Ashrpt.sql
• HTML-Report
220
Beispiel-Session
SQL> SELECT sid, serial# FROM gv$session WHERE username = 'MON';
Sessiondaten
abfragen
SID
SERIAL#
---------- ---------134
63
SELECT sample_time, event, wait_time FROM gv$active_session_history
WHERE session_id = 134 AND session_serial# = 63
Sample-Time
Mit Sessiondaten
abfragen
Aktives SQL
abfragen
221
SAMPLE_TIME
EVENT
WAIT_TIME
---------------------------------- ---------------- 05-SEP-11 08.47.44.282 PM
1
05-SEP-11 08.46.42.283 PM
1
SELECT sql_text, application_wait_time FROM gv$sql
WHERE sql_id IN (
SELECT sql_id
FROM gv$active_session_history
WHERE TO_CHAR(sample_time) = '05-SEP-11 08.44.53.283 PM'
AND session_id = 134
AND session_serial# = 63)
/
Beobachtung des
Informationsbedarfs
222
Beobachten des Informationsbedarfs
• Regelmäßige Teilnahme an Gremien
• Abstimmung / Feedback / Planung mit Fachabteilungen und DWHNutzern
• Statistiken über DWH-Nutzung
• Benutzerzahlen / Session-Statistik
• Datenmengen / Platzverbrauch
• Segment-Reads
223
Messung tatsächlich belegter Plattenplatz
• Häufig gibt es nur Zahlen über den allokierten Speicher
• Oft genannt von der Storage-Abteilung, die nicht in die Dateien
hineinschauen kann
• Manchmal werden Zahlen genannt, bei den auch den
Spiegel oder auch Backup-Platz beinhalten
• Plattenplatz im DWH wird oft ähnlich organisiert wie
Plattenplatz im OLTP-Umfeld
• Zu große Free-Space-Bereiche, obwohl die Zugänge zeitlich und
mengenmäßig gut kalkulierbar sind
Gibt kein realisitisches Bild über den tatsächlichen Bedarf und Kosten
224
Messung belegter Plattenplatz pro Tablespace
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY
OFF
COLUMN tablespace FORMAT a18
COLUMN filename
FORMAT a50
COLUMN filesize
FORMAT 999.999,999,999,999
COLUMN used
FORMAT 999.999,999,999,999
COLUMN pct_used
FORMAT 999
BREAK ON report
COMPUTE SUM OF filesize ON report
COMPUTE SUM OF used
ON report
COMPUTE AVG OF pct_used ON report
225
HEADING
HEADING
HEADING
HEADING
HEADING
'Tablespace Name'
'Filename'
'File Size'
'Used (in bytes)'
'Pct. Used‚
SELECT /*+ ordered */
d.tablespace_name
tablespace
, d.file_name
filename
, d.file_id
file_id
, d.bytes
filesize
, NVL((d.bytes - s.bytes), d.bytes)
used
, TRUNC(((NVL((d.bytes - s.bytes) , d.bytes)) / d.bytes) * 100)
FROM
sys.dba_data_files d
, v$datafile v
, ( select file_id, SUM(bytes) bytes
from sys.dba_free_space
GROUP BY file_id) s
WHERE
(s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
UNION
SELECT
d.tablespace_name
tablespace
, d.file_name
filename
, d.file_id
file_id
, d.bytes
filesize
, NVL(t.bytes_cached, 0)
used
, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d
, v$temp_extent_pool t
, v$tempfile v
WHERE
(t.file_id (+)= d.file_id)
AND (d.file_id = v.file#)
/
pct_used
Tablespace Name
-----------------DWH1
DWH1
DWH1
EXAMPLE
PERFSTAT
PERFSTAT
SYSAUX
SYSTEM
TEMP
TEST
TEST_ALERT
TEST_ALERT
UNDOTBS1
USERS
avg
sum
226
Filename
FILE_ID
FILESIZE
USED Pct. Used
--------------------------------------------- ---------- ---------- --------D:\ORA\ORADATA\ORCL\DWH1.DBF
7
52428800
25100288
47
D:\ORA\ORADATA\ORCL\DWH1_1
8 209715200
32178176
15
D:\ORA\ORADATA\ORCL\DWH1_2
9 2726297600
23068672
0
D:\ORA\ORADATA\ORCL\EXAMPLE01.DBF
5 104857600
82247680
78
D:\ORA\ORADATA\ORCL\PERFSTAT01.DBF
6 104857600 102498304
97
D:\ORA\ORADATA\ORCL\PERFSTAT2
12 209715200
1048576
0
D:\ORA\ORADATA\ORCL\SYSAUX01.DBF
2 723517440 679608320
93
D:\ORA\ORADATA\ORCL\SYSTEM01.DBF
1 734003200 729874432
99
D:\ORA\ORADATA\ORCL\TEMP01.DBF
1
20971520
18874368
90
D:\ORA\ORADATA\ORCL\TEST.DBF
10
3145728
1048576
33
D:\ORA\ORADATA\ORCL\TEST_ALERT.DBF
11
3145728
2097152
66
D:\ORA\ORADATA\ORCL\TEST_ALERT2
13
3145728
3145728
100
D:\ORA\ORADATA\ORCL\UNDOTBS01.DBF
3
52428800
33816576
64
D:\ORA\ORADATA\ORCL\USERS01.DBF
4
5242880
4325376
82
---------- ---------- --------62
4953473024 1738932224
Lesestatistiken für die wichtigsten Tabellen
anlegen
• dba_hist…. - Views zum Sammel der Lese-Zugriffe
• dba_hist_seg_stat
• dba_hist_seg_stat_obj
• dba_hist_snapshot
• dba_hist_sqlstat
• dba_hist_sqltext
• Achtung:
• Views werden nur aktualisiert wenn
• Auch tatsächlich gelesen wurde
• Ein AWR-Snapshot gezogen wurde
• Zähler fällt auf 0, wenn die DB durchgestartet wird
• Aufbau einer eigenen Statistik-Tabelle mit
• Tab-Name, Snap-ID, Datum/Uhrzeit, Physical Reads
• Aktualisieren immer nachdem ein AWR-Snapshot gezogen wurde
227
Lesestatistiken für die wichtigsten Tabellen
anlegen
Select distinct * from
(select
to_char(begin_interval_time,'dd.mm.yyyy:hh24:MI') Zeit,
logical_reads_total log_rd,
logical_reads_delta log_rd_delta,
physical_reads_total phy_rd,
physical_reads_delta phy_rd_delta
from
dba_hist_seg_stat
s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot
sn
where
o.owner = 'DWH1'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id and
object_name = 'UMSATZ')
order by zeit;
ZEIT
LOG_RD LOG_RD_DELTA
PHY_RD PHY_RD_DELTA
---------------- ---------- ------------ ---------- -----------06.09.2010:22:00
3357520
3357520
3355361
3355361
06.09.2010:23:00
4030816
673296
4028177
672816
07.09.2010:12:32
8060160
4029344
8054609
4026432
07.09.2010:15:50
688
688
1
1
228
Zugriffsdaten auf Tabellen über SQL sammeln
• SQL-Statements pro User analysieren
• From-Klausel parsen
• Zugriffe auf Tabellen
• System-Zugriffe ausschließen
• Wegen der Menge
• Historien-Tabelle aufbauen
• Mit aus der FROM-Klausel herausgefilterten Tabellennamen
• Zuordnung zu USER, Zeit und SQL-Statement
229
Beispielabfrage
col
col
col
col
col
col
col
col
col
c1
c2
c3
c4
c5
c6
c7
c8
c9
heading
heading
heading
heading
heading
heading
heading
heading
heading
select
to_char(s.begin_interval_time,'mm-dd hh24') c1,
break on c1
sql.sql_id
c2,
t.SQL_TEXT
C9,
sql.executions_delta
c3,
sql.buffer_gets_delta
c4,
sql.disk_reads_delta
c5,
sql.iowait_delta
c6,
sql.apwait_delta
c7,
sql.ccwait_delta
c8
from
dba_hist_sqlstat
sql,
dba_hist_snapshot
s,
dba_hist_SQLTEXT
t
where
s.snap_id = sql.snap_id
and
sql.PARSING_SCHEMA_NAME = 'DWH1' and
t.SQL_ID = sql.SQL_ID
and
sql.sql_id = '01978kjxb5yd2' and
to_char(s.begin_interval_time,'mm-dd hh24') = '09-12 13'
order by c1, c2;
230
‘Begin|Interval|time’
‘SQL|ID’
‘Exec|Delta’
‘Buffer|Gets|Delta’
‘Disk|Reads|Delta’
‘IO Wait|Delta’
‘Application|Wait|Delta’
‘Concurrency|Wait|Delta’
'SQL-Text'
format
format
format
format
format
format
format
format
format
a8
a13
9,999
9,999
9,999
9,999
9,999
9,999
a50
`Begin
Interval
time'
-------09-12 13
`Buffer `Disk
`Application `Concurrency
`SQL
`Exec
Gets Reads
Wait
Wait
ID'
SQL-Text
Delta' Delta' Delta'
C6 Delta'
Delta'
------------- ---------------------------------------------- ------ ------- ------ ---------- ------------ -----------01978kjxb5yd2 Select * from
1
8,573 8,390
7448344
0
0
(select
Produkt, sum(U.summe) AS Wert,
RANK()
-------- ------------- ---------------------------------------------- ------ ------- ------ ---------- ------------ -----------01978kjxb5yd2 Select * from
1
8,573 8,390
7494081
0
0
(select
Produkt, sum(U.summe) AS Wert,
RANK()
-------- ------------- ---------------------------------------------- ------ ------- ------ ---------- ------------ -----------01978kjxb5yd2 Select * from
1
8,576 8,390
6601478
0
0
(select
Produkt, sum(U.summe) AS Wert,
RANK()
231
Verwendungsinformationen speichern
User
Tabelle DWH-Zugriffshistorie
Tabname
232
Gelesen_Von_User
Anzahl_Read_IO
Lese_Datum
Verbrauchsdaten sammeln
• Mess-Aufruf in der
aktuellen
ETL-Job-Session
als letzten
Aufruf einbauen
• Ergebnis-Daten in
Historien-Tabelle
eintragen
233
SELECT
/*+ use_nl (e s) ordered */
s.sql_id, s.plan_hash_value,
to_char(s.hash_value),
rawtohex(s.address),
s.sql_text,
s.disk_reads,
s.buffer_gets,
s.executions,
s.sharable_mem,
s.parsing_user_id,
s.sorts,
s.parse_calls,
s.command_type,
s.child_number,
s.parsing_schema_id,
s.rows_processed,
e.username dbuser,
u.name parsing_user,
e.sid,
s.module,
s.action,
s.open_versions,
1 current_set
FROM
v$session e, v$sql s, sys.user$ u
WHERE s.address = e.sql_address AND
s.hash_value = e.sql_hash_value AND
s.child_number = e.sql_child_number AND
u.type# != 2 AND
s.parsing_user_id = u.user#