Zu dieser Skriptesammlung - Oracle Data Warehouse ... · Web viewZugriff auf...

Data Warehouse Technik im Fokus - Skripte in Kurzform 1 /44

Oracle Data Warehouse Kurzreferenz

Skripte, Systemabfragen und Empfehlungen

Stand Juni 2013 V4 (03.10.2013)

/tt/file_convert/5b26604f7f8b9a2b498b5862/document.docx

Data Warehouse Technik im Fokus - Skripte in Kurzform 2 /44Zu dieser Skriptesammlung 5Allgemeine Hilfen 6Einrichten und Einstellen einer Testumgebung 6Mess-Einstellungen + allgemeine Settings in SQLPlus 6Gelöschte Tabellen endgültig löschen 6Ausschalten der Recyclebin-Funktion 6Speicher leeren 6SELECT-Ausgabeformatierung 6Systemparameter abfragen 6Dictionary-Views auslesen 6Ändern der Berücksichtigung Groß-/Kleinschreibweise bei Logon 6

SQL Plus 6Metadaten-DDL für Objekte aus dem Dictionary generieren lassen6

Enterprise Manager 6Aufruf im Browser z. B. mit 6Starten der Console 6Wenn es mal klemmt 6

Datenbank, Listener und BS-Variablen 6Starten der Datenbank 6Listener starten und Status abfragen 6Erreichbarkeit einer Datenbank über SID-Abfrage 6

Beispielumgebung 7Beispiel-OLTP Schema 7Skript –Tabellen OLTP-System 7

Beispiel-Star Schema 7Vergleichs-Demo-Umgebung Steckbrief 7Skript Fakten-Tabelle 7Skript Kunden-Dimension 7Skript Artikel-Dimension 8Skript Regionen-Dimension 8Skript Zeit-Dimension 8Skript Vertriebskanal-Dimension 8Skript Unique Keys für die Dimensionstabellen [nicht nötig] 8Skript Bitmap-Indexe auf FK-Felderder Fakten-Tabelle F_UMSATZ8Skript Drop Bitmap Indexe 8Skript Primary-Key-Felder auf Dimensions-Tabellen [nicht nötig] 8Skript Dimensional Table D_ARTIKEL 8Skript Dimensional Table D_Region 8Skript Dimensional Table D_Zeit 8Skript Dimensional Table D_Kunde 8

Beispielabfragen auf das Beispiel-Star Schema 9Beispielabfrage 1a Generische Abfrage auf vorgenanntes Star-Schema 9Beispielabfrage 1b Einfache Abfrage Summe Umsatz an einem Tag 9Beispielabfrage 1c Summe Umsatz an einem Tag und Vertriebskanal 9Beispielabfrage 2 Einschränkungen über alle Dimensionen 9Beispielabfrage3 (Ein-/Ausschalten Where-Argumente /Test Star Query 9Beispielabfrage 4: Top 3 Länder bezogen auf Einzelverkäufe 9Beispielabfrage 5: Umsatz nach Quartalen 9Beispielabfrage 6: Nach Umsatzstärke sortierte Quartals-Rangfolge 9Beispielabfrage 7:: Das umsatzstärkste Quartal pro Jahr 9Beispielabfrage 8: Stärkster Umsatzmonat pro Bundesland 10Beispielabfrage9: Top 10 Artikel bezogen auf Menge / Rank /Subquery 10Beispielabfrage9a: Top 10 Artikel bezogen auf Menge / Rank /Subquery 10Beispielabfrage10: -- Top 10 Artikel bezogen auf Umsatz in einem Jahr 10Beispielabfrage 11: - Top 3 Produkte je Produktgruppe sortiert nach Rangfolge 10Beispielabfrage 12: Die Top-Produktgruppe pro Bundesland 10Beispielabfrage 13: Land mit dem stärksten Umsatz je Produktgruppe 11Beispielabfrage 14: Die Top und Flop Produktgruppe pro Bundesland 11Beispielabfrage 15: Die Top 2 Produktgruppen pro Bundesland 11Beispielabfrage 16: Top/Bottom 2 Produktgruppen pro Bundesland11Beispielabfrage 17: Über Quartale kumulierte Umsätze pro Kunde11Beispielabfrage 18: Über Quartale kumulierte Umsätze pro Kunde (Jahr + Kreis) 11Beispielabfrage 19: Wieviel Prozent des Gesamtumsatzes pro Land machen die 3 Top Produkte aus 12Beispielabfrage 20: ¼ der Kunden tragen zu ? % des Umsatzes bei? 12Beispielabfrage21: Durchschnittliche Bestellquote eines Kunden über 3 Monate? 12Beispielabfrage 22: - CUBE 12Beispielabfrage23 Vergleiche Umsätze mit Vorjahreszeitraum 12Beispielabfrage 24 Year-To-Date. Kumulierung der Monatsumsätze von Jahresbeginn an 12Beispielabfrage 26 Verwendung von Grouping-Sets 12

Tabellen 14Allgemeiner Umgang mit Tabellen 14Logging/Nologging-Modus für eine einzelne Tabelle ein-/ausschalten 14Allgemeine Informationen 14Tabellengrößen und Platzverbrauch 14Blick in die Column-Struktur einer Tabellen / Distinct Values Nulls etc 14Anlegen einer leeren Tabelle mit bestimmter Struktur 14Anlegen von temporären Tabellen 14Spaltennamen nchträglich ändern 14Ungenutzten Platz freigeben und komprimieren 14Blocknummern auslesen 14Tablespace mit Datafile anlegen 14Größe von Tabellen, Segmenten, Extents auslesen 14

Umgang mit Constraints 14Abfragen auf bestehende Constraints im aktuellen Schema 14Ausschalte / Einschalten von Constraints 14

Compression 14Anlegen einer komprimierten Tabelle 14Komprimieren eines Tablespace 14Prüfen, ob Tabellen komprimierte sind 14


Data Warehouse Technik im Fokus - Skripte in Kurzform 3 /44Tabellen und Partitionen komprimieren 14Komprimierte Tabelle aus seiner unkomprimierten erstellen 14Komprimierungsgrad für bestimmte Tabellen auslesen 14

Partitioning 15Wo und warum wird in dem Data Warehouse Partitioniert 15Was wird partitioniert 15

Partitioning-Varianten 15Skript Beispiel Range Partitioning Beispielskript F_UMSATZ_RANGE 15Skript zum schnellen Erstellen der Testtabelle aus F_UMSATZ 15Skript Beispiel Range Partitioning Beispielskript F_BESTELLUNG_RANGE 15Beispielabfrage mit Einschränkung auf Zeit 15MAXVALUE zum Aufnehmen von Werten ohne Zuordnung 15Skript Beispiel Range Partitioning nach Alphabet 15Erstellen Hash Partitioned Table au seiner anderen 15Allgemeines Hash-Partitioning-Beispiel 15Skript Beispiel Hash Partitioning und Tablespace-/Datafile-Zuordnung 15Bitmap-Indexe auf Hash-partitionierte Tabelle 15Drop Index 16Skript Beispiel List Partitioning 16Skript Beispiel für Sub-Partition (Range-List) 16Beispielabfrage auf Range/List 16Zweites Range/List – Beispiel 16Skript Beispiel Reference Partitioning 16Skript Beispiel Interval-Partition mit Monatspartitionen 16Skript Beispiel Interval-Partition mit numerischer Bereichsangabe 16Ändern bestehender Range-Partition-Tables auf Interval Partitioning 16Skript Beispiel Virtuel Column Partition 16Zuweisen unterschiedlicher Tablespaces 17

Abfragen auf Partitionen 17Partitiondaten direkt abfragen 17Grössen, Mengen und Namen 17Verteilung von Partitionen auf Tablespaces 17Tabellen,Partition,Rows 17Tabellen,Partition,Rows,Blocks,MegaBytes 17Partitionsgrenzen abfragen 17Auslesen von Sub Partitions 17Sich refenzierende Tabellen abfragen / Referen Partitioning 17

Verwaltung von Partitionen 17Partionen hinzufügen 17Partitionen auf anderen Tablespace verlagern 17Merge von zwei Sub-Partitions 17Ändern Defaults-Tablespace 17Ändern der Werte bei List-Partitioning 17Umwandeln einer Partion in eine Tabelle 17Umbenennen einer Partition 17Truncate einer Partition 17Aufspalten einer Partition 17Partition Exchange (Hinzufügen einer Tabelle als weitere Partition) 17

Indexe 18Wie und wo wird indiziert 18Suche nachbestehenden Indexen 18Suche nach Indexen bezogen auf eine bestimmte Tabelle 18Status-Abfragen 18Auflisten aller Indexe eines Schemas inkl. Größe 18Auflisten aller lokalen Index 18Anzeigen des Platzverbrauchs der Indexe 18Beispiele für Definitionen (Bitmap / B*tree) 18Behandlung von Indexen beim Laden 18Rebuild Partitioned Index 18Rebuild Sub-Partitioned Index 18Verschieben eines Index auf einen anderen Tablespace 18Aktualisierung der Index zusammen mit Tabellen-Updates 18Einschalten Usage - Monitoring für einen Index 18Welcher Index wurde tatsächlich genutzt: Usage - Abfrage 18Clustering Faktor und Anzahl Leaf-Blöcke abfragen 18

Partitionierung von Indexen 18Skript Local Partion Index 18Partitionierung der Indexe entsprechend der zugehörigen Table-Partionen (Local Partiton Index) und zweisen unterschiedlicher Tablespaces 18Partitionierung des Index unabhängig von den Table-Partitionen (Global Partition Index) 18Partitionierte Indexe suchen 18Rebuild lokaler Index 18Rebuild Sub Partition Index 18Verschieben auf anderen Tablespace 18

Star Schema 19Allgemeine Regeln 19Konsistenz, Mengen und Indexe im Star-Schema 19Prüfen ob FK der Fakten-Tab in PK der Dimensionen 19Größe der Indexe ausgeben 19Index-Typen anzeigen 19Größe aller Bitmap-Indexe 19Star Transformation aktivieren 19Menge der WHERE-Bedingungen überprüfen 19

Materialized Views 20Hilfen/Konzepte für Materialized Views 20Allgemeine Hinweise zur Definition von Materialized Views 20Informationszugriffen planen und dokumentieren 20Relevante Parameter 20MVIEW suchen 20


Data Warehouse Technik im Fokus - Skripte in Kurzform 4 /44Größe und Anzahl Zeilen von Mviews abfragen 20Beispieldefinition MAV_Zeit_Umsatz 20Beispieldefinition MAV_Artikel_Umsatz 20Beispieldefinition MAV_Region_Umsatz 20Beispieldefinition MAV_Kunde_Umsatz 20Beispieldefinition MAV_Region_Artikel_Umsatz 20Beispieldefinition MAV_Region_Zeit_Umsatz 21Beispieldefinition MAV_Kunde_Zeit_Umsatz 21Beispieldefinition MAV_Region_Zeit_Artikel_Umsatz 21Beispiel-Definition 21Beispiel für eine Partitionierte MAV 21Definition Materialized View Log 21Löschen Materilized View Log 21Build Funktionen 21Refresh Funktionen 21Feststellen, ob PCT Tracking funktioniert 21Refresh auf eine MAV 21PMARKER Funktion 21Komprimieren von Materialized Views 22Refresh auf alle von einer Tabelle abhängigen MAVs 22Aktualisieren aller Materialized Views 22Anzeigen Materialized Views mit Zustand und View Logs 22Anzeigen Materialized Views mit Zustand und PCT Regions 22Stimmigkeit von Dimensionen prüfen 22DBMS_MVIEW.Explain_mview 22Auflisten von Dimension-Tables 22Anzeigen der Struktur einer Dimensional Table 22

Optimierung für schnelles Lesen 23Memory-Cache 23Tabellen in Cache legen / aus Cache entfernen 23Welche Tabellen liegen im Cache 23

Query Result Cache 23Aktivieren mit 23Ausnutzen Result Cache durch Hint in der Abfrage 23Abfragen auf im Cache befindliche Statements 23Result-Cache-Memory-Report 23Result Cache leeren 23

Parallelisierung 23Parallelisierung aktivieren 23Automatisches Steuern mit 23Eine einzelne Tabelle auf parallel” setzen 23Parallelisierter Select-Zugriff 23Prüfen welche Art der Parallelisierung eingestellt ist 23Aktuelle SQL-Statements und deren Parallelisierung abfragen 23

Optimizer - Statistiken sammeln 23Statistiken für eine Tabelle 23Statistiken für einen Index 23Statistiken für eine Materialized View Definition 23Statistiken für ein Schema 23Automatische Aktualisierung für ein Schema einrichten 23Automatisiertes Sampling 23Abfrage ob automatisiertes Sammel aktiviert ist 23Zustand/Aktualität der Statistiken abfragen 23Zustand von Index-Statistiken abfragen 23Histogramme abfragen 23Histogramme sammeln 23Abfragen der Grenzwerte der Histogram-Buckets 23Löschen von Statistiken 23Löschen von Histogrammen für einzelne Spalten 23Markieren von Tabellen um inkrementelles Aktualisieren zu ermöglichen 23

Umgebungsinformationen auslesen 24Datenbank-Informationen, Version, Patchstände 24Database-ID abfragen 24Installierte Komponenten 24Patchstand abfragen 24Patch-Historie 24Datenbankversion abfragen 24

User-Informationen / Einstellungen /Security 24Welche USER gibt es in einer Umgebung und welchen Zustand haben sie 24Benutzer anlegen 24Eigene Tabellen zum Lesen für alle freigeben 24Allgemeine Benutzerinformationen abfragen 24Ausschalten Expiration Date in Entwicklungs- und Test-Umgebung24Ändern des Lock-Zustads eines Users 24Welche Rechte wurden einem User vergeben 24Plattenplatz der Segment-Objekte eines Users 24Logins pro User 24User Platzverbrauch Tablespace 24Maximale Parallelität von Benutzeraktivitäten 24

Tablespaces, Auflistungen, Mengen und Größen 24Anlegen eines Tablespace mit Zuweisung eines Datafiles 24Größe aller Data Files 24Liste aller Tablespace mit Größenangaben 24Temp-Space 24Größe Redo Logs 24Finden der größten Tabellen 24Top 10 größten Tabellen eines Users 24Data Files + Redo Logs + Temp 24Freier Datenbank-Platz 24Benutzter Datenbank-Platz 24Belegter und benutzter Temp-Tablespace 25Blockgrößen auslesen 25


Data Warehouse Technik im Fokus - Skripte in Kurzform 5 /44Liste Tabellen und Berechnung der Größe in Byte, MB, GB 25Tabellengrößen und Platzverbrauch 25Größen und Mengen mit zusätzlichem Tablespace-Namen 25Welche Objekte gehören zu einem Tablespace 25Größenangaben bezogen auf Partitionen 25Auflisten des Wachstums einzelner Tablespaces 25Segment-Informationen 25Gesamtauswertung belegter Plattenplatz und Freespace pro Tablespace und Datafiles 25

Database Files 26Auflistung aller Dateien 26In welchen Datafiles liegen bestimmte Tabellen 26Welche Tabellen liegen in einem bestimmten Data File 26Single Block / Multi Block Reads 26Database Files mit asynchronem Lesen und Schreiben 26

Lesestatistiken / Benutzungsverhalten 26Gelesene und geschriebene Blöcke 26

Security 26Benutzer anlagen 26Ein Profil anlegen 26Eine Rolle anlegen 26Rollenrechte weitergeben 26Default-Rolle einem Benutzer zuweisen 26Rollen für einen Bnutzer aktivieren / deaktivieren 26Was machen die Benutzer gerade 26

Laden des DWH 27Schnelles Laden / Mengenbasiertes Laden 27Grunsätzliches Schema für mengenbasiertes Laden 27Direct Path Load 27Insert ohne Log-Datei 27Sequence-Objekt anlegen 27Schnelles Schreiben CTAS (Create Table As Select) 27Schnelles Löschen von Daten 27

Databas Link 27Zugriff auf Remote-Oracle-Datenbank (Database Link) 27Verwendung 27

Sequence für Zähl-Felder / Schlüssel aufbauen 27Defintion eines Sequence-Objektes 27Zugriff um den nächsten Zählerwert abzugreifen 27Abfragen des aktuellen Stands 27

Trigger 27Beispiel für einen Trigger (Insert,Delete,Update) 27

Text-Dateien Importieren 27SQL Loader 27Beispiel für Loader Control – File 27External Table Beispiel 27

Directory-Objekte 27Directory anlegen 27Auflisten bestehender Directory-Definitionen 27Logischen Directory-Name ändern 27Name der Daten-(CSV-)Datei ändern 27

Datapump 27Datapump - Beispiel mit Steuerdatei 27Allgemeines Beispiel 27Umändern des Default-Output-Directories 27Datapump-Schätzung benötigter Plattenplatz 27Datapump Data only Import 28Datapump Schema Mode 28Datapump Network Mode 28Interaktiver Modus mit CTRL C aktivierbar 28Datapump und External Tables 28

Transportable Tablespace 28Transportable Tablespace-Verfahren 28

Error Logging 28Error Logging 28Eindeutigkeitsprüfung ohne DML_Error_Logging 28

Regular Expressions 28Regular Expression in Constraints verwenden 28Umgang mit einzelnen Zeichen: 28Bestehende Zeichengruppen 28

Arbeiten ohne Constraints und mengenbasiertes SQL / Prüfen 29Aus- und Einschalten aller Constraints 29CASE in SQL-Statements / Manuelles Prüfen 29IsNumeric-Prüfung 29IsDate-Prüfung 29Abhängigkeiten von anderen Feldern im selben Satz 29Satzübergfreifendes Zusammenzählen von Feldwerten (analytische Funktion) 29Eindeutigkeitsprüfungen 29Aggregatbildung und Bewerten von satzübergreifenden Summen 29Foreign Key Prüfung 30

SQL-Mittel beim Laden 30Pivot/Unpivot Beispiele 30Multiple Inserts /Manuelles Aufspalten von korrekten und nicht korrekten Sätzen 30Merge-Beispiel 30

Flashback 30Log-Modus / Archiv-Modus prüfen 30Aktuelle SCN abfragen 30Retention-Zeit abfragen 30Ändern Retention-Zeit 30


Data Warehouse Technik im Fokus - Skripte in Kurzform 6 /44Abfragen der letzten Logs 30Zurücksetzen einer Tabelle auf ältere SCN mit Flashback 30Beispiel Flasback 30

Table Function 31Record / Object - Definition 31Definition einer Tabellen-Struktur 31Table-Function-Definition 31

SQL / PL/SQL - Funktionen 31Leeres PL/SQL-Function-Template 31Numerische Funktionen 31Stringfunktionen 31Datum-Funktionen 31Bedingte Abfragen (Decode / CASE) 32Sonstige Funktionen 32Konvertierungsfunktionen 32Konvertierung von Datum in Zeichenkette (to_char) und umgekehrt (to_date) 32Zeitformat-Umwandlung 32Konvertierung von Zahlen in Zeichenketten (to_char) und umgekehrt (to_number) 32

Verwalten des Systems / Systembeobachtung 33Anzeigen der gesetzten Schwellwerte für Alerts 33Alerts abfragen 33Alerts abfragen (historisch) 33Alert-Datei-Ablage 33Wait Classes abfragen 33Wait Events 33Menge der Undos 33

Informationen über die Session 33Sortvorgänge auf Platte oder im Speicher 33Session-Informationen 33

AWR (Analytic Workload Repository) 33Einstellungen 33Eingestellte Intervalle 33Platzverbrauch AWR messen 33Auflistung bestehender Snapshots 34AWR-Snapshot manuell anlegen 34Snapshot-Nummern ausfindig machen 34AWR-Bericht erstellen 34

ADDM Informationen abfragen 34Welche Informationen liegen vor 34ADDM-Analyse starten 34ADDM-Bericht anzeigen 34Relevante Dictionary Views für Alerts und Session 34

Tracing 34Trace-Output-Verzeichnis 34Identifizierung einer zu prüfenden Session 34Aktivieren des SQL-Trace 34Deaktivieren 34Beispiel-Trace-Session 34Unleserlichen Trace-Output mit TKPROF formatieren 34

Session-bezogene Informationen 34Session Daten abfragen 34Größe SGA und entsprechende Speicherbereiche 34Abfragen der idealen Memory-Ausnutzung 34Abfragen der SGA / Welche Objekte sind im Speicher 34Sessions schnell ‘killen’ 35Schnelle Übersicht über aktuell laufende Sessions / wer / wo / was 35Verhindern von Memory Paging 35Herausfinden Memory und Session 35

ASH (Active Session History), Session + User Informationen 35Größe ASH - Buffer 35Session Daten abfragen 35Die aktivsten SQLs in der letzten Stunde 35Die aktivsten IO-Operationen 35Sample-Time abfragen 35Aktives SQL Abfragen 35Übersicht über SQL-Statements in der Vergangenheit 35Session-Informationen 35

SQL-Monitoring 35SQL Cache 35Kurzabfrage auf ein bestimmtes Select-Statement mit einer bestimmten Tabelle 36SQL-Statistik abfragen / Wurde ein Ergebniss aus dem Cache oder von der Platte gelesen? 36Abfrage auf v$sql_Monitor 36Feststellen welche Benutzer mit welchem SQL und welcher Parallelität zugegriffen haben 36SQLTun-Report über SQL ID aufrufen 36Lese-Statistiken auf einzelne Tabellen gezielt abfragen 36

Planmanagement 36Automatisiertes Sammeln von Plänen einschalten 36Parameter zur Behandlung von Plänen 36Nutzen der Pläne einschalten 36Anzeigen von Plänen in Plan_Baselines - Eingeschränkt auf einen User 36Betrachten eines gespeicherten Plans 37Evolve eines neuen Plans 37

Lese-Performance messen 37Abschätzen Lesegeschwindigkeit (IO-Performancen) bei dem Lesen einzelner Tabellen 37

IO Messung / calibrate IO 37Calibrate-Status abfragen 37Calibrate IO abfragen 37

Perfstat 37Orion 37


Data Warehouse Technik im Fokus - Skripte in Kurzform 7 /44Lesestatistiken: Werden Daten genutzt? 38Backup im Data Warehouse 39Allgemeine Überlegungen 39Argumente für ein DWH spezifisches Backup-Konzept 39Was muss gesichert werden. 39Wie wird gesichert 39

Archive Log 39Festeststellen des Log-Modus der Datenbank 39Ein-(Aus-)schalten des Archivelog-Modus 39Archive-Zustand anzeigen lassen 39Wohin wird das Archive-Log geschrieben und wie groß ist die Recovery Area 39Wie voll ist die Recovery Area aktuell 39Recovery Area vergrößern 39

RMAN 39RMAN starten 39An Zieldatenbank einwählen 39Welche Backups sind überflüssig 39Welche Files benötigen ein Backup 39Leeren Recover Area 39Welche Files können nicht wiederhergestellt werden 39Welche Sicherungen liegen vor 39Alle Einstellungen von RMAN 39Definieren eines Backup-Kanals 39Flashback aktivieren 39Verwendung der Recovery Area 39Prüfen, ob Flashback aktiviert ist 39Prüfung des benötigten Platzes 39Retention-Zeit Flashback Area einstellen 39Abfragen einer Tabelle von einem bestimmten SCN-Zustand 39Abfragen einer Tabelle zu einer bestimmten Zeit 39Tabellen mit Flashback zurückholen 39Datenbank mit Flashback zurücksetzen (Kommandobeispiele) 39Ältest mögliche Rückhol-Position feststellen 39

Anhang 40(DWH-Administrations-Checkliste - wird aktualisiert) 40Testdaten erstellen 40SQL-Trigger-Tabelle für Dummy-FROM-Klausel erstellen 40Tabelle mit laufender Nummer erstellen 40

Vorgehensweise bei der Erstellung der Demo-Umgebung 40Hilfsprozeduren zu den Beispielmodellen 40Zeitdimension 40Daten in der Faktentabelle F_UMSATZ 41



Zu dieser SkriptesammlungWem ist das nicht schon einmal passiert: Man entwickelt eine PL/SQL-Prozedur zum Beschreiben einer Tabelle und hat die Parameter für eine bestimmte Funktion vergessen. Nachschlagen in der Doku: zu mühsam weil zu dick.Nachschlagen im Handbuch: gerade verlegt.Google’n: schon eher .... und und und...Das ist Alltag von vielen Data Warehouse-Entwicklern: die Dinge, die man nicht permanent anwendet, vergisst man. Diese Sammlung von Skripten soll in dieser Situation etwas helfen: Sie ist keine hochspezialisierte Sammlung von Experten-

Skripten, sondern sie fast einfache, alltägliche Lösungen an einer Stelle zusammen, um sie schnell zur Hand zu haben. Spezialwissen oder eine vollständige Beschreibung von Kommando-Syntax: bitte in der Dokumentation nachsehen. Diese Unterlage ersetzt nicht die Dokumentation.

Die Skriptesammlung legt ihren Fokus auf Data Warehouse –Fragestellungen. Hierfür gibt es in der Tat einen Mangel in der Lituratur aber auch bei „Google“, denn in der Regel findet man eine Beschreibung von Funktionen und Features quer über alle Anwendungsgebiete (OLTP, DWH) hinweg.

Die Sammlung enthält die wichtigsten Skripte und Kommandos der Seminar-Reihe Data Warehouse Technik im Fokus, die bei Oracle Deutschland schon seit 2006 regelmäßig angeboten wird. Das in dieser Seminarreihe vermittelte Wissen ist durchaus geeignet, um einen Mitarbeiter erfolgreich für ein Data Warehouse Projekt vorzubereiten.

Die Skriptesammlung ist stellenweise kommentiert, um eine Bewertung bzw. Orientierung für deren Anwendung im Data warehouse – Umfeld mitzugeben.

Die der Skripte-Sammlung zu Grunde liegende Seminarreihe Data Warehouse Technik im Fokus stellt Datenbank-Technologie nicht beliebig vor, sondern sie bespricht zunächst eine idealisierte Data Warehouse Architektur und ordnet die benötigten Datenbank-Features den Erfordernissen in dieser Architektur unter. Dadurch entsteht automatisch eine durch Data Warehouse-Anforderungen motivierte Fokussierung bei der Datenbank-Technologie.

Die Sammlung orientiert sich an einem einheitlichen Beispiel. Zu Beginn steht der Entwurf eines Star-Schemas.(Damit soll nicht gesagt werden, dass ein Data Warehouse nur aus einem Star-Schema besteht). Dieses Beispiel kann man durch die mitgelieferten Sourcen bzw. abgedruckten Skripte auch in einer eigenen Umgebung realisieren. Damit erhält man sofort eine Übungsumgebung, um die Wirkungsweise der jeweiligen Datenbank-Features zu testen. Zu dem Starschema gehören auch passende Abfragen, die möglichst viele potentiellen Problemstellungen bei der Abfrage des Schemas abdecken. Die Abfragen sind einfach und damit überschaubar gehalten. Die Beispielperformance-Werte liefern eine erste Orientierung, welcher Optimierungserfolg bei den jeweiligen Features erreichbar sein sollte. Die Werte stammen nur von einem schlichten Desktop-Rechner (also keine Hochleistungsmaschine). Das reicht, um die prinzielle Vorgehensweise für Optimierungen aufzuzeigen. Aber bereits in dieser Beispiel-Umgebung fällt es bei einigen Abfragen schwer, Performance-Steigerungen durch einzelne Features aufzuzeigen, da man sich auch bei einem Datenvolumen von mehr als 50 Millionen Sätzen in Anwortzeitbereichen von weniger als einer Sekunde bewegt. Und hier sind oftmals noch Netzübertragungs- und Bildschirmaufbauzeiten enthalten.Für das Starschema wird exemplarisch die passende (Bitmap-) Indizierung und Partitionierung vorgestellt. Dann folgen die nötigen Materialized Views. Die Summe aller Techniken führt in dem ausgeführten Beispiel zu fast nicht mehr spürbaren Antwortzeiten bei allen Beispielabfragen. Um eine leichtere Orientierung zu geben, sind an einigen Stellen farbige Blöcke eingebaut. Diese bedeuten:Performance-HinweiseFür diesen Punkt relevante FragestellungenFür diesen Punkt passende Dictionary-View

Da diese Sammlung nicht komplett und nicht fehlerfrei ist und wahrscheilich ständig korrigiert und ergänzt werden muss, bitten wir jeden, der damit arbeitet, um Feedback, Korrekturen und Verbesserungsvorschläge (-> [email protected])Die Beispieldaten zu dieser Skriptesammlung sind zu finden unter.http://www.oracledwh.de/downloads/AutoIndex-2.2.4/index.php?dir=downloads/Kurs_Materialien_und_DWH_TIF_und_Angebote/&file=Beispieldaten_Star_Skriptesammlung.zipEine Vorgehensweise für die Installation finden Sie am Ende von dieser Skriptesammlung.


http://www.oracledwh.de/downloads/AutoIndex-2.2.4/index.php?dir=downloads/Kurs_Materialien_und_DWH_TIF_und_Angebote/&file=Beispieldaten_Star_Skriptesammlung.zip



mailto:[email protected]


Allgemeine HilfenEinrichten und Einstellen einer Testumgebung Hier wird davon ausgegangen, dass man mit SQL Plus arbeitet. Alternativen sind z. B. der SQL Developer

Mess-Einstellungen + allgemeine Settings in SQLPlus-- SET TIMING ON-- SET AUTOTRACE ON / set autotrace traceonly-- SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]-- set pagesize 99-- set heading off-- set line 400 -- set long nnnn (vergößern des Ausgabe-Puffer)

Gelöschte Tabellen endgültig löschenPURGE RECYCLEBINOder gleich arbeiten mit [DROP TABLE tab_name PURGE;]

Ausschalten der Recyclebin-Funktion alter session set recyclebin=off;

Speicher leerenalter system flush shared_pool;alter system flush BUFFER_CACHE;alter system flush GLOBAL

SELECT-Ausgabeformatierungcolumn feld format a25column feld format a25

Systemparameter abfragenEntweder

SHOW PARAMETER …oder die View V$PARAMETER abfragen.

select name,value, description from v$parameter;SQL> desc v$parameter Name------------------------- NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED ISBASIC DESCRIPTION UPDATE_COMMENT HASH

Dictionary-Views auslesencolumn comments format a50select * from dict where TABLE_NAME like 'V$SESSION%';

TABLE_NAME COMMENTS-----------------------------------------------------------V$SESSION Synonym for V_$SESSIONV$SESSION_BLOCKERS Synonym for V_$SESSION_BLOCKERSV$SESSION_CONNECT_INFO Synonym for V_$SESSION_CONNECT_INFOV$SESSION_CURSOR_CACHE Synonym for V_$SESSION_CURSOR_CACHE

Ändern der Berücksichtigung Groß-/Kleinschreibweise bei Logonalter system set sec_case_sensitive_logon=FALSE;

SQL Plussqlplus user/passwort@ORACLE_SID -> geht über Listenersqlplus user/passwort -> ohne Listenersqlplus sys/passwort@ORACLE_SID as sysdba sqlplus sys/passwort as sysdba sqlplus “/ as sysdba”

Help Index -- listet alle SQLPLUS-Befehle auf

Weitere Hilfe mit Help [commando]

Beispiel SQL> help get@ @@ / ACCEPT APPEND ARCHIVE LOG ATTRIBUTE BREAK BTITLE CHANGE CLEAR COLUMN COMPUTE CONNECT COPY DEFINE DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT GET HELP HOST INPUT LIST PASSWORD PAUSE PRINT PROMPT QUIT RECOVER REMARK REPFOOTER REPHEADER RESERVED WORDS (SQL)

RESERVED WORDS (PL/SQL)

RUN

SAVE SET SHOWSHUTDOWN SPOOL SQLPLUSSTART STARTUP STORETIMING TTITLE UNDEFINEVARIABLE WHENEVER OSERROR WHENEVER SQLERRORXQUERY

Metadaten-DDL für Objekte aus dem Dictionary generieren lassenSELECT dbms_metadata.get_ddl('TABLE','BESTELLUNG_PART_RANGE_HASH') FROM dual;

select dbms_metadata.get_ddl('INDEX','IDX_BILDUNGSGRUPPE_BM') FROM dual

Enterprise ManagerAufruf im Browser z. B. mithttps://192.168.1.14:1158/emhttps://hostname:1158/em

Starten der Consoleemctl start dbconsole

Wenn es mal klemmtBei Fehlern wie:The OracleDBConsole[ServiceName] service could not be started.A service specific error occurred: 2Hilft oft nur das Neukonfigurieren

emca -deconfig dbcontrol dbemca -config dbcontrol db

Datenbank, Listener und BS-VariablenStarten der Datenbank-LinuxDbstart -Windows set ORACLE_SID=orclset ORACLE_HOME=D:\ora%oracle_home%\BIN\oradim -STARTUP -SID orcl -SYSPWD sys

Listener starten und Status abfragenLsnrctl startLsnrctl status

Erreichbarkeit einer Datenbank über SID-AbfrageTnsping sid-name


https://192.168.1.14:1158/em


BeispielumgebungIn diesewr Unterlage wird ein durchgägngiges Beispiel genutzt, so dass immer wieder dieselben Tabellennamen und Struturen zu finden sind.

Beispiel-OLTP Schema Einfaches OLTP-Schema. Geeignet zur Diskussion der ER-Modellierung. Aus dieser Informations-Grundmenge lässt sich das weiter unten dargestllte Star-Schema ableiten.

Skript –Tabellen OLTP-SystemCREATE TABLE BESTELLUNG ( BESTELLNR NUMBER, KUNDENNR NUMBER, ORTNR NUMBER, BESTELLDATUM DATE);

CREATE TABLE kunde ( KUNDENNR NUMBER(10) , KUNDENNAME VARCHAR2(20) , BERUFSGRUPPE VARCHAR2(20) , SEGMENT NUMBER(10));

CREATE TABLE BESTELLPOSITION (BESTELLNR number(4) ,POSITIONSNR number(4) ,MENGE NUMBER(4) ,ARTIKELNR NUMBER(10));

CREATE TABLE ARTIKEL (ARTIKEL_ID NUMBER(3),ARTIKEL_NAME VARCHAR2(50),GRUPPE_NR NUMBER(3);

CREATE TABLE ARTIKEL_GRUPPEN (GRUPPE_NR NUMBER(3),GRUPPE_NAME VARCHAR2(50),SPARTE_NR NUMBER(3);

CREATE TABLE ARTIKEL_SPARTEN (SPARTE_NR NUMBER(3),SPARTE_NAME VARCHAR2(50);

Beispiel-Star Schema Das folgende Star-Schema wird bei den anschließenden Beispielabfragen verwendet. Indizierung: Alle Dimensionstabellen (D-...) verfügen über einen Unique-Index auf ihren Prmary-Key-Feldern. Die Fakten-Tabelle (F_UMSATZ) verfügt über jeweils einen Bitmap-Index auf jedem Foreign-Key-Feld. Ein Foreign-Key-Constraint muss nicht definiert sein.

Vergleichs-Demo-Umgebung Steckbrief(In dieser Unterlage werden Beispielabfragen und Abfragezeiten angegeben, die in der hier beschriebenen Umgebung getestet wurden. Die Daten können entsprechend der Anleitung am Ende dieser Skriptesammlung geladen werden)TABLE_NAME BLOCKS GB NUM_ROWS PCT_FREE------------------------- ------- ---------- --------D_ARTIKEL 5 .00004 129 10D_KUNDE 28 .000224 1029 10D_REGION 73 .000584 7202 10D_ZEIT 43 .000344 5844 10D_VERTRIEBSKANAL 5 .00004 7 10F_UMSATZ 276890 2.21512 51200000 10

Eigenschaft des DemosystemsCompression Nein(Ja)Bitmap auf Fakten-FK-Felder JaUnique Key PK-Felder der Dimensionen JaPartitioning Nein(Ja)Results Cache NeinNull-Werte KeineServer-CPU-Kerne 4Server-RAM 16 GBServer-BS Linux OEL 6Oracle-DB 11.2.0.3 (EE)Storage Direkt Durchsatz ~180 MB/sec

Skript Fakten-TabelleCREATE TABLE F_UMSATZ( ARTIKEL_ID NUMBER(10), KUNDEN_ID NUMBER(10), ZEIT_ID DATE, REGION_ID NUMBER(10), KANAL_ID NUMBER(10), UMSATZ NUMBER(10), MENGE NUMBER(10), UMSATZ_GESAMT NUMBER(10));

Skript Kunden-DimensionCREATE TABLE D_KUNDE ( KUNDENNR NUMBER, GESCHLECHT VARCHAR2(10), VORNAME VARCHAR2(50), NACHNAME VARCHAR2(50), TITEL VARCHAR2(20), ANREDE VARCHAR2(10), GEBDAT DATE, BRANCHE VARCHAR2(30), WOHNART VARCHAR2(30), KUNDENART NUMBER, BILDUNG VARCHAR2(30), ANZ_KINDER VARCHAR2(5), EINKOMMENSGRUPPE VARCHAR2(30), ORTNR NUMBER, BERUFSGRUPPE VARCHAR2(30), STATUS VARCHAR2(1), STRASSE VARCHAR2(100), TELEFON VARCHAR2(30), TELEFAX VARCHAR2(30), KONTAKTPERSON NUMBER, FIRMENRABATT VARCHAR2(10), BERUFSGRUPPEN_NR VARCHAR2(1), BILDUNGS_NR VARCHAR2(1), EINKOMMENS_NR VARCHAR2(1), WOHNART_NR VARCHAR2(1), HAUSNUMMER NUMBER(10,0), PLZ VARCHAR2(10), ORT VARCHAR2(100), KUNDENKARTE NUMBER, ZAHLUNGSZIEL_TAGE VARCHAR2(4000),


Data Warehouse Technik im Fokus - Skripte in Kurzform 11 /44 KUNDEN_ID NUMBER TOTAL VARCHAR2(20), TOTAL_NR NUMBER);

Skript Artikel-DimensionCREATE TABLE D_ARTIKEL ( ARTIKEL_NAME VARCHAR2(50), ARTIKEL_ID NUMBER(3,0), GRUPPE_NR NUMBER(3,0), GRUPPE_NAME VARCHAR2(50), SPARTE_NAME VARCHAR2(50), SPARTE_NR NUMBER(3,0), ) ;

Skript Regionen-DimensionCREATE TABLE D_REGION ( ORTNR NUMBER(8,0), ORT VARCHAR2(50), KREISNR NUMBER(8,0), KREIS VARCHAR2(50), LAND VARCHAR2(50), LANDNR NUMBER(8,0), REGION VARCHAR2(50), REGIONNR NUMBER(8,0), REGION_ID NUMBER(22,0) ) ;

Skript Zeit-DimensionCREATE TABLE D_ZEIT ( ZEIT_ID DATE , DATUM_ID NUMBER(4), TAG_DES_MONATS NUMBER(2,0), TAG_DES_JAHRES NUMBER(3,0) , WOCHE_DES_JAHRES NUMBER(2,0) , MONATS_NUMMER NUMBER(2,0) , MONAT_DESC VARCHAR2(9) , QUARTALS_NUMMER NUMBER(1,0) , JAHR_NUMMER NUMBER(4,0) ) ;

Skript Vertriebskanal-DimensionCREATE TABLE D_VERTRIEBSKANAL (KANAL_ID NUMBER, VERTRIEBSKANAL VARCHAR2(20 BYTE), KANALBESCHREIBUNG VARCHAR2(20 BYTE), VERANTWORTLICH VARCHAR2(20 BYTE), KLASSE NUMBER ) ;

Skript Unique Keys für die Dimensionstabellen [nicht nötig]CREATE UNIQUE INDEX "D_KUNDE_PK" ON "D_KUNDE" ("KUNDEN_ID");CREATE UNIQUE INDEX "D_ARTIKEL_PK" ON "D_ARTIKEL" ("ARTIKEL_ID");CREATE UNIQUE INDEX "D_REGION_PK" ON "D_REGION" ("REGION_ID");CREATE UNIQUE INDEX "D_ZEIT_PK" ON "D_ZEIT" ("ZEIT_ID");CREATE UNIQUE INDEX "D_KANAL_PK" ON "D_VERTRIEBSKANAL" ("KANAL_ID");

Skript Bitmap-Indexe auf FK-Felderder Fakten-Tabelle F_UMSATZCREATE bitmap index idx_ARTIKEL_ID_BM on F_UMSATZ(ARTIKEL_ID);CREATE bitmap index idx_ZEIT_ID_BM on F_UMSATZ(ZEIT_ID);CREATE bitmap index idx_KUNDEN_ID_BM on F_UMSATZ(KUNDEN_ID);CREATE bitmap index idx_REGION_ID_BM on F_UMSATZ(REGION_ID);CREATE bitmap index idx_KANAL_ID_BM on F_UMSATZ(KANAL_ID);

Skript Drop Bitmap IndexeDROP INDEX idx_ARTIKEL_ID_BM;DROP INDEX idx_ZEIT_ID_BM;DROP INDEX idx_KUNDEN_ID_BM;DROP INDEX idx_REGION_ID_BM; DROP INDEX idx_KANAL_ID_BM;

Skript Primary-Key-Felder auf Dimensions-Tabellen [nicht nötig]ALTER TABLE D_Region ADD CONSTRAINT pk_Region PRIMARY KEY (Region_id);ALTER TABLE D_Zeit ADD CONSTRAINT pk_Zeit PRIMARY KEY (Zeit_id);ALTER TABLE D_Artikel ADD CONSTRAINT pk_Artikel PRIMARY KEY (artikel_id);ALTER TABLE D_Kunde ADD CONSTRAINT pk_Kunde PRIMARY KEY (kunden_id);ALTER TABLE D_VERTRIEBSKANAL ADD CONSTRAINT pk_VERTRIEBSKANAL PRIMARY KEY (KANAL_ID);

Skript Dimensional Table D_ARTIKELDROP DIMENSION D_ARTIKEL;CREATE DIMENSION d_artikel LEVEL artikel IS d_artikel.artikel_id LEVEL gruppe IS d_artikel.gruppe_nr LEVEL sparte IS d_artikel.sparte_nrHIERARCHY h_art ( artikel CHILD OF gruppe CHILD OF sparte)ATTRIBUTE artikel DETERMINES (d_artikel.artikel_name)ATTRIBUTE gruppe DETERMINES (d_artikel.gruppe_name)ATTRIBUTE sparte DETERMINES (d_artikel.sparte_name);

Skript Dimensional Table D_RegionDROP DIMENSION D_REGION;CREATE DIMENSION D_REGION LEVEL ort IS d_region.REGION_id LEVEL kreis IS d_region.kreisnr LEVEL land IS d_region.landnr

LEVEL region IS d_region.regionnrHIERARCHY h_region ( Ort CHILD OF Kreis CHILD OF Land CHILD OF Region )ATTRIBUTE Ort DETERMINES (d_region.ort, d_region.ortnr)ATTRIBUTE Kreis DETERMINES (d_region.kreis)ATTRIBUTE Land DETERMINES (d_region.land)ATTRIBUTE Region DETERMINES (d_region.region);

Skript Dimensional Table D_ZeitDROP DIMENSION D_Zeit;CREATE DIMENSION D_Zeit LEVEL Tag IS D_Zeit.Zeit_id LEVEL Monat IS D_Zeit.Monats_nummer LEVEL Quartal IS D_Zeit.Quartals_Nummer LEVEL Jahr IS D_Zeit.Jahr_NummerHIERARCHY h_region ( Tag CHILD OF Monat CHILD OF Quartal CHILD OF Jahr )ATTRIBUTE Tag DETERMINES (D_Zeit.Datum_ID, D_Zeit.Tag_des_Monats, D_Zeit.Tag_des_Jahres, D_Zeit.Woche_Des_Jahres)ATTRIBUTE Monat DETERMINES (D_Zeit.Monat_Desc)

Skript Dimensional Table D_KundeDROP DIMENSION D_Kunde;CREATE DIMENSION D_Kunde LEVEL Kunde IS D_Kunde.Kunden_id LEVEL Beruf IS D_Kunde.Berufsgruppen_nr LEVEL Einkommen IS D_Kunde.Einkommens_nr LEVEL Wohnart IS D_Kunde.Wohnart_nr LEVEL Bildung IS D_Kunde.Bildungs_nr LEVEL Ort IS D_Kunde.OrtnrHIERARCHY h_Beruf (Kunde CHILD OF Beruf)HIERARCHY h_Einkommen (Kunde CHILD OF Einkommen)HIERARCHY h_Wohnart (Kunde CHILD OF Wohnart)HIERARCHY h_Bildung (Kunde CHILD OF Bildung)HIERARCHY h_Ort (Kunde CHILD OF Ort)ATTRIBUTE Kunde DETERMINES (D_Kunde.KUNDENNR,D_Kunde.Geschlecht ,D_Kunde.Vorname ,D_Kunde.Nachname ,D_Kunde.Titel ,D_Kunde.Anrede,D_Kunde.Gebdat,D_Kunde.Branche,D_Kunde.Kundenart ,D_Kunde. Anz_kinder,D_Kunde.Status,D_Kunde.Strasse,D_Kunde.Telefon ,D_Kunde.Telefax ,D_Kunde.Kontaktperson,D_Kunde.Firmenrabatt,D_Kunde.Hausnummer,D_Kunde.Kundenkarte ,D_Kunde.Zahlungsziel_tage,D_Kunde.Total,D_Kunde.Total_NR)ATTRIBUTE Beruf DETERMINES (D_Kunde.Berufsgruppe) ATTRIBUTE Einkommen DETERMINES (D_Kunde.Einkommensgruppe)ATTRIBUTE Wohnart DETERMINES (D_Kunde.Wohnart) ATTRIBUTE Bildung DETERMINES (D_Kunde.Bildung)ATTRIBUTE Ort DETERMINES (D_Kunde.Ort, D_Kunde.PLZ)



Beispielabfragen auf das Beispiel-Star Schema Beispielabfrage 1a Generische Abfrage auf vorgenanntes Star-SchemaSELECT a.sparte_name, z.Jahr_Nummer, r.land,v.vertriebskanal, sum(U.umsatz), sum(U.menge)FROM f_Umsatz U,

d_region r,d_zeit z,D_artikel a,D_Vertriebskanal V

WHERE U.zeit_id = z.zeit_id AND U.REGION_ID = R.REGION_ID AND U.artikel_id = a.artikel_idAND U.Kanal_ID = V.Kanal_ID

GROUP by a.sparte_name, z.Jahr_Nummer, r.land, v.vertriebskanal ;Parallel SekundenP0 39,1P2 22,5P4 13,4P8 16,8P16 20

Beispielabfrage 1b Einfache Abfrage Summe Umsatz an einem TagSELECT /*+ no cache */ sum(UMSATZ) Umsatz FROM F_UMSATZ WHEREzeit_ID = to_date('10.03.2011','DD.MM.YYYY')

UMSATZ---------- 13883392

Parallel SekundenP0 2,6

Beispielabfrage 1c Summe Umsatz an einem Tag und Vertriebskanal

SELECT /*+ no cache */ sum(UMSATZ) Umsatz FROM F_UMSATZ U, D_vertriebskanal VWHERE u.kanal_id = v.kanal_id and zeit_ID = to_date('10.03.2011','DD.MM.YYYY') and V.vertriebskanal = 'Shop';

Parallel SekundenP0 2,5

Beispielabfrage 2 Einschränkungen über alle DimensionenSELECT sum(u.UMSATZ) Umsatz, R.REGION, Z.Quartals_nummer Quartal FROM

F_UMSATZ U,D_Artikel A,D_Region R,D_Zeit Z,D_Kunde K,D_Vertriebskanal V

WHERE U.Kunden_ID = K.Kunden_IDAND U.Zeit_ID = Z.Zeit_IDAND U.REGION_ID = R.Region_IDAND U.Artikel_ID = A.Artikel_IDAND U.Kanal_ID = V.Kanal_IDAND Z.JAHR_NUMMER = 2011AND A.GRUPPE_NAME = 'Bad_Sanitaer'AND K.BERUFSGRUPPE = 'Arbeiter'AND R.REGION IN ('Mitte','Sued','Nord')AND V.Vertriebskanal = 'Shop'

Group by R.Region,Z.Quartals_nummerOrder by Z.Quartals_nummer;Parallel SekundenP0 2,6P2 2,6P4 4,2P8 4,6P16 4,2

Beispielabfrage3 (Ein-/Ausschalten Where-Argumente /Test Star QuerySELECT a.sparte_name, z.Jahr_Nummer, r.land, sum(U.umsatz), sum(U.menge)FROM f_Umsatz U,

d_region r,d_zeit z,D_artikel a

WHERE U.zeit_id = z.zeit_id ANDU.REGION_ID = R.REGION_ID ANDU.artikel_id = a.artikel_id and

z.jahr_nummer = 2010 and R.Land = 'Bayern'GROUP by a.sparte_name, z.Jahr_Nummer, r.land;Parallel Sekunden

P0 11,3P2 6,4P4 3,2P8 5,8P16 5,3

Beispielabfrage 4: Top 3 Länder bezogen auf Einzelverkäufeselect * from ( select count(*) Anzahl_Verkaeufe, r.land Bundesland from f_umsatz u, d_region r WHERE

U.REGION_ID = R.REGION_ID group by r.land order by anzahl_verkaeufe desc )where rownum < 4;

[count(*) wegen der leichteren Rewrit-Fähigkeit]

BUNDESLAND ANZAHL------------------------------ ----------Rheinland Pfalz 13674496Bayern 13261312Schleswig Holstein 8107008

Parallel SekundenP0 12P2 10,5P4 3,7P8 3,8P16 4,1

Beispielabfrage 5: Umsatz nach Quartalenselect sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr, z.Quartals_nummer Quartalfrom f_umsatz u, d_zeit zwhere z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011group by z.Jahr_nummer,z.Quartals_nummerorder by z.Jahr_nummer,z.Quartals_nummer

UMSATZ JAHR QUARTAL---------- ---------- ----------1579442176 2006 11558222336 2006 21522254336 2006 31646631936 2006 41533571072 2007 11580020224 2007 2

~~~Parallel SekundenP0 10,1P2 7,1P4 2,6P8 4,7P16 2,5

Beispielabfrage 6: Nach Umsatzstärke sortierte Quartals-Rangfolgeselect sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr ,z.Quartals_nummer Quartal, RANK() OVER (PARTITION by z.Jahr_nummer ORDER BY sum(U.umsatz) ASC ) AS Rangfolgefrom f_umsatz u, d_zeit zwhere z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011group by z.Jahr_nummer,z.Quartals_nummerorder by z.Jahr_nummer,Rangfolge;

UMSATZ JAHR QUARTAL RANGFOLGE---------- ---------- ---------- ----------1522254336 2006 3 11558222336 2006 2 21579442176 2006 1 31646631936 2006 4 41533571072 2007 1 11563792384 2007 3 21566097920 2007 4 31580020224 2007 2 41521408512 2008 2 11551963136 2008 1 21610153472 2008 3 3


Beispielabfrage 7:: Das umsatzstärkste Quartal pro Jahrselect * from


Data Warehouse Technik im Fokus - Skripte in Kurzform 13 /44(select sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr ,z.Quartals_nummer "Top-Quartal", RANK() OVER (PARTITION by z.Jahr_nummer ORDER BY

sum(U.umsatz) ASC ) AS Rangfolgefrom f_umsatz u, d_zeit zwhere z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011group by z.Jahr_nummer,z.Quartals_nummer)where Rangfolge = 1order by Jahr;

UMSATZ JAHR Top-Quartal RANGFOLGE---------- ---------- ----------- ----------1522254336 2006 3 11533571072 2007 1 11521408512 2008 2 11488578048 2009 1 11467155968 2010 4 11528411648 2011 1 1

Parallel SekundenP0 9,6P2 5,1P4 2,2P8 2,8P16 1,8

Beispielabfrage 8: Stärkster Umsatzmonat pro BundeslandSELECT *FROM

(SELECTland Bundesland, monat_desc Monat,jahr_nummer Jahr,sum(umsatz) as Umsatz,RANK() OVER(PARTITION BY r.land ORDER BY

sum(u.umsatz) DESC) AS rangfolgeFROM

d_zeit z,d_region r,f_umsatz u

WHEREz.zeit_id = u.zeit_idAND r.region_id = u.region_idAND z.jahr_nummer = '2010'

GROUP BY r.land, z.monat_desc, jahr_nummerORDER BY jahr_nummer)

WHERErangfolge < 2

order by Bundesland;

BUNDESLAND MONAT JAHR UMSATZ RANGFOLGE-------------------- -------------- ---------- ----------Baden Wuerttemberg October 2010 59450368 1Bayern July 2010 163219968 1Berlin December 2010 807424 1Bremen June 2010 75264 1Hamburg June 2010 1023488 1Hessen November 2010 36823040 1Niedersachsen December 2010 85943808 1Nordrhein Westfalen February 2010 26347520 1Rheinland Pfalz January 2010 157769728 1Saarland August 2010 5460992 1Schleswig Holstein May 2010 87066624 1


Beispielabfrage9: Top 10 Artikel bezogen auf Menge / Rank /SubquerySELECT * FROM (SELECT Artikel_Name as Artikel, sum(U.menge) AS Gesamtmenge,

RANK() OVER (ORDER BY sum(U.menge) DESC ) ASRangfolge

from F_umsatz U, D_Artikel A WHERE U.artikel_id = a.artikel_id group by a.artikel_name)

WHERE rownum < 11;

ARTIKEL GESAMTMENGE RANGFOLGE----------------------------- ----------- ----------Wasserhahn 23144960 1Muffe 18mm 22593536 2Aussenkabel 4 Adern 2 22431744 3Luesterklemmen 2 22123008 4Topf_Guss_Klein 21908480 5Schubkarre 21797888 6Gluebirne Halogen 200 Watt 21556224 7Aussenkabel 3 Adern 1,5 21442048 8

Aussenkabel 5 Adern 2 21420032 9Innenkabel 3 Adern 2 21405184 10


Beispielabfrage9a: Top 10 Artikel bezogen auf Menge / Rank /SubquerySELECT * FROM (SELECT Gruppe_Name , sum(U.menge) AS Gesamtmenge,

RANK() OVER (ORDER BY sum(U.menge) DESC ) ASRangfolge

from F_umsatz U, D_Artikel A WHERE U.artikel_id = a.artikel_id group by a.gruppe_name)

Beispielabfrage10: -- Top 10 Artikel bezogen auf Umsatz in einem Jahr

SELECT * FROM (SELECT a.Artikel_Name as Artikel, r.Land Bundesland, z.Jahr_nummer Jahr, sum(U.umsatz) AS Wert, sum(U.Menge) Menge, round(sum(U.umsatz) / sum(U.Menge),2) Ums_pro_Art,

RANK() OVER (ORDER BY sum(U.umsatz) DESC ) AS Rangfolge from F_umsatz U, D_Artikel A ,D_Zeit z, d_region r WHERE U.artikel_id = a.artikel_id and U.REGION_ID = R.REGION_ID AND U.zeit_id = z.zeit_id AND z.jahr_nummer = 2010 group by a.artikel_name,r.Land,z.Jahr_nummer) WHERE rownum < 11;

ARTIKEL BUNDESLAND JAHR WERT MENGE UMS._PRO_ART. RANGFO.------------------------ ---------------------- -------- ------- ------------ ----Wandspiegel 40x60 Rheinland Pfalz 2010 25700352 570368 45.05 1Handtuchhalter Bayern 2010 25004544 756736 33.04 2Wandspiegel 50x80 Rheinland Pfalz 2010 24927744 534016 46.67 3Fahrradhalter Rheinland Pfalz 2010 24101376 713216 33.75 4Bindeseil Bayern 2010 23774208 559104 42.52 5Gluebirne Kerze 40 Watt Rheinland Pfalz 2010 23672832 470016 50.36 6Zusatzlicht_Front Bayern 2010 22036480 500224 44.05 7Innenkabel 3 Adern 1,5 Bayern 2010 21995520 480768 45.75 8Aussenkabel 5 Adern 1,5 Bayern 2010 21216768 419840 50.53 9Holzschauben_2Kg Rheinland Pfalz 2010 21105152 573952 36.77 10Parallel SekundenP0 9,8P2 3,3P4 1,7P8 1,9P16 1,8

Beispielabfrage 11: - Top 3 Produkte je Produktgruppe sortiert nach RangfolgeSELECT * FROM

(SELECT artikel_name Artikel, gruppe_name Prod_Grp, sum(U.Umsatz) AS Umsatz, RANK() OVER (PARTITION by a.gruppe_name 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.gruppe_name,a.artikel_name

ORDER by a.gruppe_name)WHERE Rangfolge < 4;

ARTIKEL PROD_GRP UMSATZ RANGFOLGE------------------------ ------------------ ---------- --Wasserhahn Bad_Sanitaer 881696768 1Wandspiegel 50x60 Bad_Sanitaer 853362688 2Waschbecken 50x80 Bad_Sanitaer 837804032 3Elektrohobel Elektro-Werkzeuge 813489152 1Schleifmaschine 600 Watt Elektro-Werkzeuge 793825792 2Bohrmaschine 600 Watt Elektro-Werkzeuge 782154752 3Aussenkabel 4 Adern 2 Elektroartikel 880760320 1Gluebirne Kerze 25 Watt Elektroartikel 858101760 2Luesterklemmen 2 Elektroartikel 848047616 3Hacke_5Kg Gartenbedarf 888975872 1Schubkarre Gartenbedarf 856668160 2Wasservorratsbehaelter Gartenbedarf 844909056 3

~~~Parallel SekundenP0 19P2 10,2P4 5,3P8 6,9P16 8,2

Beispielabfrage 12: Die Top-Produktgruppe pro BundeslandSELECT * FROM (SELECT R.Land Bundesland,


Data Warehouse Technik im Fokus - Skripte in Kurzform 14 /44 gruppe_name Prod_Grp, sum(U.Umsatz) AS Umsatz, Rank() OVER (PARTITION by R.Land ORDER BY sum(U.umsatz) DESC ) AS Rangfolge FROM f_umsatz U, d_artikel A , d_region R WHERE U.artikel_id = a.artikel_id and U.Region_ID = r.Region_ID GROUP by R.Land, gruppe_name order by R.land)where Rangfolge = 1 ;

LAND PROD_GRP UMSATZ RANGFOLGE-------------------- --------------- ---------- ---Baden Wuerttemberg Bad_Sanitaer 2517655040 1Bayern Bad_Sanitaer 6928505856 1Berlin KFZ-Zubehoer 5216256 1Bremen Haushaltswaren 7249920 1Hamburg Elektroartikel 11321344 1Hessen Bad_Sanitaer 1473691648 1Niedersachsen Bad_Sanitaer 3481312768 1Nordrhein Westfalen Bad_Sanitaer 1065768960 1Rheinland Pfalz Bad_Sanitaer 7339374592 1Saarland Bad_Sanitaer 97358848 1Schleswig Holstein Bad_Sanitaer 4261867520 1Parallel SekundenP0 25,3P2 12,2P4 6,3P8 6,7P16 7,2

Beispielabfrage 13: Land mit dem stärksten Umsatz je ProduktgruppeSELECT *FROM (SELECT gruppe_name Prod_grp, land Bundesland, sum(menge) as Umsatz, max(sum(menge)) over (partition by gruppe_name) as Max_Ums_Land FROM D_region R, d_artikel a, f_umsatz U WHERE R.region_id = U.region_id AND a.artikel_id = U.artikel_id GROUP by gruppe_name, land ORDER by gruppe_name , land ) WHERE Umsatz = Max_Ums_Land;

PROD_GRP BUNDESLAND UMSATZ MAX_UMS_LAND------------------ -------------------------- ------------Bad_Sanitaer Rheinland Pfalz 183886336 183886336Elektro-Werkzeuge Rheinland Pfalz 34775552 34775552Elektroartikel Rheinland Pfalz 123813376 123813376Gartenbedarf Rheinland Pfalz 87101952 87101952Haushaltswaren Rheinland Pfalz 92366336 92366336Heimwerker Rheinland Pfalz 85024256 85024256KFZ-Zubehoer Bayern 83915776 83915776Parallel SekundenP0 33.1P2 13.0P4 6,5P8 6,8P16 8,7

Beispielabfrage 14: Die Top und Flop Produktgruppe pro BundeslandSELECT * FROM (SELECT R.Land Bundesland, gruppe_name Prod_Grp, sum(U.Umsatz) AS Umsatz, Rank() OVER (PARTITION by R.Land ORDER BY sum(U.umsatz) DESC ) AS Rangfolge FROM f_umsatz U, d_artikel A , d_region R WHERE U.artikel_id = a.artikel_id and U.Region_ID = r.Region_ID GROUP by R.Land, gruppe_name order by R.land) where Rangfolge in (1,7);

BUNDESLAND PROD_GRP UMSATZ RANGFOLGE------------------- -------------------- ---------- -----Baden Wuerttemberg Bad_Sanitaer 2517655040 1Baden Wuerttemberg Elektro-Werkzeuge 452903936 7Bayern Bad_Sanitaer 6928505856 1Bayern Elektro-Werkzeuge 1380406784 7Berlin KFZ-Zubehoer 5216256 1Berlin Heimwerker 2012160 7Bremen Haushaltswaren 7249920 1

~~~Parallel SekundenP0 24,5P2 12.8P4 6,2P8 9,6P16 6,2

Beispielabfrage 15: Die Top 2 Produktgruppen pro BundeslandSELECT * FROM (SELECT r.land Bundesland, a.gruppe_name Prod_Grp,

sum(U.Umsatz) AS Top_Umsatz, RANK() OVER (PARTITION by r.land ORDER BY

sum(U.umsatz) DESC ) AS Top_folgeFROM f_umsatz U, d_artikel A , d_region rWHERE U.artikel_id = a.artikel_id and

U.region_id =r.region_idGROUP by r.land,a.gruppe_name

ORDER by r.land)WHERE Top_folge < 3;

LAND PROD_GRP TOP_UMSATZ TOP_FOLGE-------------------- ---------------- ---------- ---Baden Wuerttemberg Bad_Sanitaer 2517655040 1Baden Wuerttemberg Elektroartikel 1634190848 2Bayern Bad_Sanitaer 6928505856 1Bayern Elektroartikel 4916009472 2Berlin KFZ-Zubehoer 5216256 1Berlin Bad_Sanitaer 5194240 2Bremen Haushaltswaren 7249920 1Bremen Gartenbedarf 7211008 2Hamburg Elektroartikel 11321344 1


Beispielabfrage 16: Top/Bottom 2 Produktgruppen pro Bundesland select Top.Bundesland, top.Prod_grp,Top.top_umsatz,top.Top_folge, Bottom.Prod_grp,bottom.Bottom_Umsatz, bottom.Bottom_folge From (SELECT * FROM (SELECT r.land Bundesland, a.gruppe_name Prod_Grp, sum(U.Umsatz) AS Top_Umsatz, RANK() OVER (PARTITION by r.land ORDER BY

sum(U.umsatz) DESC ) AS Top_folgeFROM f_umsatz U, d_artikel A , d_region rWHERE

U.artikel_id = a.artikel_id and U.region_id =r.region_id

GROUP by r.land,a.gruppe_name ORDER by r.land) WHERE Top_folge < 3) Top, (SELECT * FROM (SELECT r.land Bundesland, a.gruppe_name Prod_Grp, sum(U.Umsatz) AS Bottom_Umsatz, RANK() OVER (PARTITION by r.land ORDER BY

sum(U.umsatz) ASC ) AS Bottom_folgeFROM f_umsatz U, d_artikel A , d_region rWHERE

U.artikel_id = a.artikel_id and U.region_id =r.region_id

GROUP by r.land,a.gruppe_name ORDER by r.land) WHERE Bottom_folge < 3) Bottomwhere top.Bundesland = bottom.Bundesland and top.Top_folge = bottom.Bottom_folgeorder by Top.Bundesland;

BUNDESLAND PROD_GRP TOP TOP PROD_GRP BOTTOM BOTTOM UMSATZ Folge UMSATZ FOLGE

--------------------- ---------------- ---------- -- -------------------------------- ---

Baden Wuerttemberg Bad_Sanitaer 2517655040 1 Elektro-Werkzeuge 452903936 1Baden Wuerttemberg Elektroartikel 1634190848 2 Heimwerker 1180500480 2Bayern Bad_Sanitaer 6928505856 1 Elektro-Werkzeuge 1380406784 1Bayern Elektroartikel 4916009472 2 Heimwerker 3230448640 2Berlin Bad_Sanitaer 5194240 2 Elektroartikel 3089408 2Berlin KFZ-Zubehoer 5216256 1 Heimwerker 2012160 1Bremen Haushaltswaren 7249920 1 Bad_Sanitaer 1293824 1Bremen Gartenbedarf 7211008 2 Elektro-Werkzeuge 1307136 2Hamburg Heimwerker 7856640 2 Gartenbedarf 1458688 2Hamburg Elektroartikel 11321344 1 Bad_Sanitaer 1054720 1


Beispielabfrage 17: Über Quartale kumulierte Umsätze pro KundeSELECT k.nachName, z.jahr_nummer, z.quartals_nummer, sum(u.menge) as Umsatz,

sum(sum(u.menge)) over (Partition By k.nachName ORDER BY k.nachName, z.jahr_nummer, z.quartals_nummer ROWS UNBOUNDED PRECEDING) as Umsatz_SummeFROM d_kunde K, f_Umsatz U, d_zeit ZWHERE

k.kunden_id = u.kunden_id ANDto_char(Z.zeit_id) = to_char(u.zeit_id)

GROUP by K.NachName, z.jahr_nummer, z.quartals_nummer;Parallel SekundenP0 75,2P2 31,7P4 15,3P8 23,7P16 13,8


Data Warehouse Technik im Fokus - Skripte in Kurzform 15 /44Beispielabfrage 18: Über Quartale kumulierte Umsätze pro Kunde (Jahr + Kreis)SELECT k.nachName,r.kreis, z.jahr_nummer, z.quartals_nummer, sum(u.Umsatz) as Umsatz, sum(sum(u.Umsatz)) over (Partition By k.nachName ORDER BY k.nachName, z.jahr_nummer, z.quartals_nummer ROWS UNBOUNDED PRECEDING) as Umsatz_SummeFROM d_kunde K, f_Umsatz U, d_zeit Z, d_region rWHERE

k.kunden_id = u.kunden_id ANDU.zeit_id = z.zeit_id ANDR.region_id = U.region_id AND

z.Jahr_nummer = 2010 and r.kreisnr in (151,51,170,566)GROUP by K.NachName,r.kreis, z.jahr_nummer, z.quartals_nummer;


Beispielabfrage 19: Wieviel Prozent des Gesamtumsatzes pro Land machen die 3 Top Produkte ausselect a.land, round(a.top_3_umsatz/1000000,2) Top_3_In_Mill, round(b.Gesamt_pro_land/1000000,2) Gesamt_pro_Land_in_Mill, round(a.top_3_umsatz/b.Gesamt_pro_land*100,2) Anteil_Prozentfrom(SELECT land, sum(umsatz) Top_3_Umsatz FROM

(SELECT r.land, artikel_name Artikel, sum(U.Umsatz) AS Umsatz, RANK() OVER (PARTITION by r.land ORDER BY sum(U.umsatz) DESC ) AS Rangfolge

FROM f_umsatz U, d_artikel A, d_region rWHERE U.artikel_id = a.artikel_id and

U.region_id = r.region_idGROUP by r.land, a.artikel_name

ORDER by r.land)WHERE Rangfolge < 4group by land) a,(SELECT land, sum(umsatz) Gesamt_pro_Land from f_umsatz U, d_region rwhere U.region_id = r.region_idgroup by land) bwhere a.land = b.land;

LAND TOP_3_IN_MILL GESAMT_PRO_LAND_IN_MILL ANTEIL_PROZENT------------------- ------------- ----------------------- --------------Hamburg 11.45 39.26 29.18Niedersachsen 362.68 12827.92 2.83Schleswig Holstein 491.98 16262.01 3.03Nordrhein Westfalen 152.71 4155.6 3.67Berlin 5.68 28.19 20.13Bremen 5.53 28.58 19.35Baden Wuerttemberg 280.01 9413.63 2.97Rheinland Pfalz 800.82 27477.07 2.91Saarland 26.04 389.81 6.68Bayern 739.02 26510.6 2.79Hessen 180.83 5314.5 3.4Parallel SekundenP0 38,2P2 19,0P4 9,8P8 19,1P16 11,4

Beispielabfrage 20: ¼ der Kunden tragen zu ? % des Umsatzes bei?SELECT sum(umsatz), anteil,

(sum(umsatz)*100/Gesamt_umsatz) as Prozent FROM

(SELECT k.nachName as kunde, sum(u.umsatz) as Umsatz, ntile(4) over (order by sum(u.umsatz)) as Anteil

FROM d_kunde K, f_Umsatz UWHERE k.kunden_id = u.kunden_idGROUP by K.nachName),(SELECT sum(u.umsatz) as Gesamt_Umsatz FROM

f_Umsatz U) GROUP by anteil,Gesamt_umsatz;Parallel SekundenP0 19,3P2 10,6P4 6,9P8 7,2P16 6,2

Beispielabfrage21: Durchschnittliche Bestellquote eines Kunden über 3 Monate?SELECT k.nachName,

z.Jahr_Nummer as Jahr,z.Monats_Nummer as Mon,sum(u.umsatz) as Umsatz, sum(avg(u.umsatz)) over

(ORDER by K.nachName, z.Jahr_Nummer, z.Jahr_Nummer Rows 0 Preceding) as Mov_3M_AVGFROM d_kunde K, F_Umsatz U, D_zeit ZWHERE

k.kunden_id = u.kunden_id ANDZ.zeit_id = u.zeit_id AND

Z.Jahr_Nummer in (2011,2010) ANDk.nachname = 'Bauer'

GROUP by K.nachName, z.Jahr_Nummer, z.Monats_NummerORDER by z.Jahr_Nummer, z.Monats_Nummer;Parallel SekundenP0 1,2P2 1,0P4 0,5P8 2,8P16 2,1

Beispielabfrage 22: - CUBE(Analytische Funktion: Ausgabe zusätzlicher Ergebniszeilen für die jeweiligen Ergebnislevel)SELECT

a.gruppe_name, Region, Land, sum(u.menge),grouping(gruppe_name) as AG,grouping(Region) as RE

FROMd_artikel a, f_umsatz U, d_region R

WHEREU.artikel_id = a.artikel_id ANDR.Region_id = U.Region_id ANDLand = 'Schleswig Holstein'

GROUP by cube (gruppe_name,Region,land);Parallel SekundenP0 8,6P2 4,4P4 2,1P8 2,3P16 2,1

Beispielabfrage23 Vergleiche Umsätze mit VorjahreszeitraumSELECT k.nachName,

z.Jahr_Nummer as Jahr,z.Monats_Nummer as Mon,sum(u.umsatz) as Umsatz,lag(sum(u.umsatz),12) over (ORDER by z.Jahr_Nummer,

z.Monats_Nummer) as vorjahr FROM D_kunde k, F_Umsatz U, D_zeit ZWHERE

k.kunden_id = u.kunden_id ANDZ.zeit_id = u.zeit_id ANDz.Jahr_nummer in (2010,2011) ANDk.nachname = 'Bauer'

group by K.nachName, z.Jahr_Nummer, z.Monats_Nummerorder by z.Jahr_Nummer, z.Monats_Nummer;


Beispielabfrage 24 Year-To-Date. Kumulierung der Monatsumsätze von Jahresbeginn anselect z.Jahr_nummer Jahr, z.MONAT_DESC Monat, sum(u.umsatz) Umsatz, sum(sum(u.umsatz)) over (PARTITION by z.Jahr_nummer order by z.MONATS_NUMMER) year_to_date from f_umsatz u, d_zeit z where z.zeit_id = u.zeit_id group by z.Jahr_nummer, z.MONATS_NUMMER, z.MONAT_DESC ;Beispielabfrage 25 Ausgabe von Summenzeilen einer Aggregation (GROUP BY ROLLUP)SELECT artikel_name Artikel, gruppe_name Prod_Grp, sum(U.Umsatz) AS Umsatz FROM f_umsatz U, d_artikel AWHERE U.artikel_id = a.artikel_id GROUP by ROLLUP (a.gruppe_name,a.artikel_name)ORDER by a.gruppe_name


Data Warehouse Technik im Fokus - Skripte in Kurzform 16 /44Beispielabfrage 26 Verwendung von Grouping-Sets[Analytische Funktion: Ausgeben zusätzlicher „Steuerinformation“ für „programmiertes“ Auslesen des Ergebnisses]SELECT artikel_name AS Artikel, gruppe_name AS Gruppe , sparte_name AS Sparte , sum(U.Umsatz) AS Umsatz , GROUPING(Artikel_name) AS Art_g , GROUPING(gruppe_name) AS Grp_g , GROUPING(Sparte_name) AS Spr_g , GROUPING_ID(artikel_name ,gruppe_name,sparte_name ) AS grouping_id FROM f_umsatz U, d_artikel AWHERE U.artikel_id = a.artikel_id GROUP by CUBE (a.sparte_name, a.gruppe_name,a.artikel_name)HAVING

GROUPING(Artikel_name) = 1 OR GROUPING(gruppe_name) = 1 ORGROUPING(Sparte_name) = 1

ORDER by GROUPING(Artikel_name),GROUPING(gruppe_name),GROUPING(Sparte_name)



TabellenAllgemeiner Umgang mit Tabellen

Relevante FragestellungenLogging / Nologging -> möglichst umgehen und ausschaltenGrössen und MengenStorage-Verhalten von Tabellen (PCTFREE) -> möglichst 0-> möglichst viel in einem Block speichernTemporäre Tabellen im Rahmen des ETL-Prozesses (CTAS)

Logging/Nologging-Modus für eine einzelne Tabelle ein-/ausschaltenalter table wh_transaktionen nologging;

Allgemeine Informationenselect table_name,blocks,num_rows,logging from user_tables;

Tabellengrößen und Platzverbrauchselect table_name, pct_free,blocks,blocks/1000000*8 GB,num_rows, compression,cache,empty_blocks, TABLESPACE_NAME from user_tables;(Achtung: Die Inhalte sind nur bei aktualisierten Statistiken gefüllt und richtig.)

Blick in die Column-Struktur einer Tabellen / Distinct Values Nulls etcselect TABLE_NAME,COLUMN_NAME,substr(DATA_TYPE,1,20) Type,NUM_NULLS,NUM_DISTINCT,USER_STATS from user_tab_columns where table_name = 'F_UMSATZ';

(auf aktuelle Statistiken achten)

TABLE_NAME COLUMN_NAME TYPE NUM_NULLS NUM_DISTINCT USE----------- -------------- ------------- ---------- ---------F_UMSATZ ARTIKEL_ID NUMBER 0 129 NOF_UMSATZ KUNDEN_ID NUMBER 0 1031 NOF_UMSATZ ZEIT_ID DATE 0 6001 NOF_UMSATZ REGION_ID NUMBER 0 7020 NOF_UMSATZ KANAL_ID NUMBER 0 7 NOF_UMSATZ UMSATZ NUMBER 0 4000 NOF_UMSATZ MENGE NUMBER 0 100 NOF_UMSATZ UMSATZ_GESAMT NUMBER 0 61832 NO

Anlegen einer leeren Tabelle mit bestimmter Strukturcreate table abc_0 as select * from abc where rownum < 1;

Anlegen von temporären Tabellen create global temporary table ABC_T on commit preserve rows as select * from ABC;

Spaltennamen nchträglich ändernAlter Table F_UMSATZ rename column ALTER_NAME to NEUER_NAME;

Ungenutzten Platz freigeben und komprimierenalter table F_UMSATZ enable row movement;alter table F_UMSATZ shrink space cascade;Parallel SekundenP2 480 (8 Minuten)

create table f_umsatz_tmp pctfree 0 as select * from f_Umsatz;Parallel SekundenP2 33

create table F_UMSATZ_comp compress as select * from F_UMSATZ;Parallel SekundenP2 99,7

Ergebnisse (nach Aktualisierung der Statistiken):TABLE_NAME PCT_FREE BLOCKS GB NUM_ROWS COMPRESS EMPTY_BLOCKS(1)F_UMSATZ 10 276890 2.21512 51200000 DISABLED 0(2)F_UMSATZ 10 276045 2.20836 51200000 DISABLED 0(3)F_UMSATZ_TMP 0 249137 1.993096 51200000 DISABLED 0(4)F_UMSATZ_COMP 0 235724 1.885792 51200000 ENABLED 0

Zu (1) F_umsatz vor alter … shrink Zu (2) F_umsatz nach alter shrink -> 0,3% ErsparnisZu (3) Kopie von F_Umsatz mit PCT_FREE = 0 -> 9,9 % ErsparnisZu (4) Standard-Komprimierung -> ~15% gegenü. (1) und ~6% gegenü. (3) (Der geringe Komprimierungs-Faktor erklärt sich aus der starken Selektivität der Zufalls-generierten Testdaten mit fehlenden NULL-Werten und sehr großer Verteilung der Daten)

Blocknummern auslesenselect region_id, dbms_rowid.rowid_block_number(rowid) blk_num from d_region order by blk_num;

Tablespace mit Datafile anlegenCREATE SMALLFILE TABLESPACE "TIF" DATAFILE 'D:\ORA\ORADATA\ORCL\TIF' SIZE 3072M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

Größe von Tabellen, Segmenten, Extents auslesenselect segment_name, bytes,blocks,extents from user_segments where segment_type = 'TABLE';

select SEGMENT_NAME,EXTENT_ID,BYTES,BLOCKS from user_extents where SEGMENT_NAME like '%T';

select t.TABLE_NAME,

t.blocks,t.EMPTY_BLOCKS,

t.AVG_SPACE,t.AVG_ROW_LEN,t.NUM_ROWS,t.pct_free,t.compression,s.EXTENTS,s.bytes seg_bytes,e.blocks ext_blks,e.bytes ext_bytesfrom user_segments s, user_tables t, user_extents ewhere t.TABLE_NAME = s.segment_nameand e.SEGMENT_NAME = s.SEGMENT_NAMEand t.TABLE_NAME = 'F_UMSATZ';

Relevante Dictionary Views für TabellenUSER_TAB_STATISTICS USER_TABLESUSER_TAB_PARTITIONS SYS.V$OBJECT_USAGEuser_tab_columns User_extentsuser_tablespaces User_segments

Umgang mit ConstraintsAbfragen auf bestehende Constraints im aktuellen Schemaselect constraint_name,CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints;

Status-Werte in der Abfrage können seinC (check constraint on a table)P (primary key)U (unique key)R (referential integrity)V (with check option, on a view)O (with read only, on a view)

Ausschalte / Einschalten von Constraintsalter table table_name DISABLE constraint constraint_name;alter Table D_Zeit disable constraint PK_ZEIT;alter Table D_Zeit enable constraint PK_ZEIT;

Relevante Dictionary Viewsuser_constraints

Compression

Relevante FragestellungenUnterschied Advanced Compression / Standard CompressionErzeugung einer komprimierten TabelleUrsachen der unterschiedlich hohen KomprimierungsrateSelektivität der Columns abfragen(Siehe auch Hinweise und ausgeführtes Beispiel unter dem Abschnitt Tabellen)

Anlegen einer komprimierten TabelleCREATE TABLE tab_name(…) COMPRESS create table F_UMSATZ_comp compress as select * from F_UMSATZ;

Komprimieren eines TablespaceCREATE TABLESPACE ... COMPRESS;

Prüfen, ob Tabellen komprimierte sindselect table_name, compression from user_tables order by table_name;

Tabellen und Partitionen komprimierenALTER TABLE MOVE COMPRESSALTER TABLE MOVE PARTITION COMPRESS

Komprimierte Tabelle aus seiner unkomprimierten erstellencreate table bestellung_comp compress as select * from bestellung;

Komprimierungsgrad für bestimmte Tabellen auslesenselect table_name, pct_free,blocks,blocks/1000000*8 GB,num_rows, compression,cache,empty_blocks from user_tables where table_name like 'BEST%' or table_name like 'LIEFE%';

Relevante Dictionary Views für Compressionuser_tables



PartitioningRelevante FragestellungenEinsatzgründe für Partitioning (Verwaltung + Performance)Partition KeyPartitioning-VariantenMaxvalue-FunktionSub-PartitioningVerteilung von Partitions auf DatafilesPartition-Struktur-AbfragenVerwaltung von Partitionen

Wo und warum wird in dem Data Warehouse Partitioniert Bei Lese-Aktivitäten / Abfrage-Sutuation hilft das

Partition Pruning. D. h. es müssen nur diejenigen Partitionen gelesen werden, für die Einschränkungen in der WHERE-Klausel gelten

Der ETL-Prozesse wird durch das “Partition Exchange Load”-Verfahren (PEL) unterstützt.

Ältere Daten in einer Tabelle können auf günstigere (billigere) Datenträger kopiert werden (ILM, Information Life Cycle Managemen).PARTITION MOVE.

Durch READ-ONLY Tablespaces kann man verhindern, dass Teile von Tabellen durch RMAN regelmäßig gesichert warden. Das spart Kosten. Die Partitionen werden auf Tabellen ausgelagert (PARTITION EXCHANGE) und dann z. B. über DATA PUMP einmal als Sicherung weggeschrieben.

Will man das Standard-Compression – Feature nutzen, dann kann man aktuelle Partitionen unkomprimiert lassen, während man die Masse der daten (die übrigen Partitionen) durch einen MOVE komprimiert.

Will mann eine möglichst hohe ONLINE-Verfügbarkeit auch während des ETL-Laufes haben.

Was wird partitioniert Die großen Bewegungsdatentabellen Die großen Fakten-Tabellen Tabellen, die man auch während des ETL-Laufes online

lassen will Tabellen mit mehr als ~ 5 Millionen Sätzen (Dieses Aussage

ist vage, da die Abfrage-Performance von den Maschinengrößen abhängt.

Partitioning-VariantenSkript Beispiel Range Partitioning Beispielskript F_UMSATZ_RANGECREATE TABLE f_umsatz_range

(ARTIKEL_ID NUMBER(10),KUNDEN_ID NUMBER(10),ZEIT_ID DATE,REGION_ID NUMBER(10),KANAL_ID NUMBER(10),UMSATZ NUMBER(10),MENGE NUMBER(10),UMSATZ_GESAMT NUMBER(10)

)PARTITION BY RANGE (ZEIT_ID) (

PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01','SYYYY-MM-DD')),

PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03-01','SYYYY-MM-DD')),

PARTITION mar10 VALUES LESS THAN (TO_DATE('2010-04-01','SYYYY-MM-DD')),PARTITION apr10 VALUES LESS THAN (TO_DATE('2010-05-01','SYYYY-MM-DD')),PARTITION mai10 VALUES LESS THAN (TO_DATE('2010-06-01','SYYYY-MM-DD')),PARTITION jun10 VALUES LESS THAN (TO_DATE('2010-07-01','SYYYY-MM-DD')),PARTITION jul10 VALUES LESS THAN (TO_DATE('2010-08-01','SYYYY-MM-DD')),PARTITION aug10 VALUES LESS THAN (TO_DATE('2010-09-01','SYYYY-MM-DD')),PARTITION sep10 VALUES LESS THAN (TO_DATE('2010-10-01','SYYYY-MM-DD')),PARTITION oct10 VALUES LESS THAN (TO_DATE('2010-11-01','SYYYY-MM-DD')),PARTITION nov10 VALUES LESS THAN (TO_DATE('2010-12-01','SYYYY-MM-DD')),PARTITION dec10 VALUES LESS THAN (TO_DATE('2011-01-01','SYYYY-MM-DD')),PARTITION jan11 VALUES LESS THAN (TO_DATE('2011-02-01','SYYYY-MM-DD')),PARTITION feb11 VALUES LESS THAN (TO_DATE('2011-03-01','SYYYY-MM-DD')),PARTITION mar11 VALUES LESS THAN (TO_DATE('2011-04-01','SYYYY-MM-DD')),PARTITION apr11 VALUES LESS THAN (TO_DATE('2011-05-01','SYYYY-MM-DD')),PARTITION mai11 VALUES LESS THAN (TO_DATE('2011-06-01','SYYYY-MM-DD')),PARTITION jun11 VALUES LESS THAN (TO_DATE('2011-07-01','SYYYY-MM-DD')),PARTITION jul11 VALUES LESS THAN (TO_DATE('2011-08-01','SYYYY-MM-DD')),PARTITION aug11 VALUES LESS THAN (TO_DATE('2011-09-01','SYYYY-MM-DD')),PARTITION sep11 VALUES LESS THAN (TO_DATE('2011-10-01','SYYYY-MM-DD')),PARTITION oct11 VALUES LESS THAN (TO_DATE('2011-11-01','SYYYY-MM-DD')),PARTITION nov11 VALUES LESS THAN (TO_DATE('2011-12-01','SYYYY-MM-DD')),

PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')),

PARTITION next_month VALUES LESS THAN (MAXVALUE));

Skript zum schnellen Erstellen der Testtabelle aus F_UMSATZEinschränkung auf 1 Jahr.insert into f_umsatz_Range select * from f_umsatz where zeit_ID > to_date('28-FEB-10', 'DD-MON-YY') and zeit_ID < to_date('01-JAN-12', 'DD-MON-YY')

Skript Beispiel Range Partitioning Beispielskript F_BESTELLUNG_RANGE[Beispiel analog zu vorherigem Beispiel, daher verkleinert]CREATE TABLE F_bestellung_part_range ( SUMME NUMBER(14,0), MENGE NUMBER(14,0), BESTELLDATUM DATE, FK_ARTIKEL_ID NUMBER, FK_KUNDEN_ID NUMBER, FK_ORT_ID NUMBER, FK_DATUM_ID NUMBER, auftragsart VARCHAR2(30) )PARTITION BY RANGE (bestelldatum) ( PARTITION jan11 VALUES LESS THAN (TO_DATE('2011-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION feb11 VALUES LESS THAN (TO_DATE('2011-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION mar11 VALUES LESS THAN (TO_DATE('2011-04-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION apr11 VALUES LESS THAN (TO_DATE('2011-05-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION mai11 VALUES LESS THAN (TO_DATE('2011-06-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jun11 VALUES LESS THAN (TO_DATE('2011-07-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jul11 VALUES LESS THAN (TO_DATE('2011-08-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION aug11 VALUES LESS THAN (TO_DATE('2011-09-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION sep11 VALUES LESS THAN (TO_DATE('2011-10-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION oct11 VALUES LESS THAN (TO_DATE('2011-11-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION nov11 VALUES LESS THAN (TO_DATE('2011-12-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL,

PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jan12 VALUES LESS THAN (TO_DATE('2012-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION feb12 VALUES LESS THAN (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION next_month VALUES LESS THAN (MAXVALUE) TABLESPACE DWH_SPINDEL);

Beispielabfrage mit Einschränkung auf ZeitSELECT sum(UMSATZ) Umsatz FROM F_UMSATZ_rangeWHEREzeit_ID = to_date('10.03.2011','DD.MM.YYYY') UMSATZ---------- 13883392Parallel SekundenP0 ohne Range-Partitioning 3,38P0 mit Range-Partitioning 0,1

MAXVALUE zum Aufnehmen von Werten ohne ZuordnungCREATE TABLE table orders ( ... )) PARTITION BY RANGE (ord_date) ( PARTITION p_first VALUES LESS THAN (to_date('2007-01-01')), [...], PARTITION p_last VALUES LESS THAN (maxvalue))

Skript Beispiel Range Partitioning nach AlphabetCREATE TABLE kunde_part_range_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 );

Erstellen Hash Partitioned Table au seiner anderenCreate table F_Umsatz_Hash PARTITION BY HASH (KUNDEN_ID) PARTITIONS 64 pctfree 0As select * from f_Umsatz;

Allgemeines Hash-Partitioning-BeispielCREATE TABLE F_BESTELLUNGEN_HASH ( SUMME NUMBER(14,0), MENGE NUMBER(14,0), BESTELLDATUM DATE, FK_ARTIKEL_ID NUMBER, FK_KUNDEN_ID NUMBER, FK_ORT_ID NUMBER, FK_DATUM_ID NUMBER)PARTITION BY HASH (BESTELLDATUM) PARTITIONS 64 pctfree 0;

Skript Beispiel Hash Partitioning und Tablespace-/Datafile-ZuordnungCREATE TABLESPACE T1 DATAFILE 'F:\ORA_DATA\T1.dbf' SIZE 10M;CREATE TABLESPACE T2 DATAFILE 'F:\ORA_DATA\T2.dbf' SIZE 10M;CREATE TABLESPACE T3 DATAFILE 'F:\ORA_DATA\T3.dbf' SIZE 10M;CREATE TABLESPACE T4 DATAFILE 'F:\ORA_DATA\T4.dbf' SIZE 10M;

CREATE TABLE F_BESTELLUNGEN_HASH_E ( SUMME NUMBER(14,0), MENGE NUMBER(14,0), BESTELLDATUM DATE, FK_ARTIKEL_ID NUMBER, FK_KUNDEN_ID NUMBER, FK_ORT_ID NUMBER, FK_DATUM_ID NUMBER)PARTITION BY HASH (BESTELLDATUM) PARTITIONS 4 pctfree 0STORE IN (T1, T2, T3, T4);

Bitmap-Indexe auf Hash-partitionierte TabelleCreate bitmap index idx_ARTIKEL_ID_BM_H on F_BESTELLUNGEN_HASH(FK_ARTIKEL_ID) local; Create bitmap index idx_ZEIT_ID_BM_H on F_BESTELLUNGEN_HASH (FK_DATUM_ID) local;


Data Warehouse Technik im Fokus - Skripte in Kurzform 19 /44Create bitmap index idx_KUNDE_ID_BM_H on F_BESTELLUNGEN_HASH (FK_KUNDEN_ID) local; Create bitmap index idx_REGION_ID_BM_H on F_BESTELLUNGEN_HASH (FK_ORT_ID) local;

Drop IndexDROP index idx_ARTIKEL_ID_BM_H; DROP index idx_ZEIT_ID_BM_H ;DROP index idx_KUNDE_ID_BM_H; DROP index idx_REGION_ID_BM_H;

Skript Beispiel List PartitioningCREATE TABLE bestellung_part_list ( bestellnr NUMBER(10) NOT NULL, kundencode NUMBER(10), bestelldatum DATE, lieferdatum DATE, bestell_total NUMBER(12,2), auftragsart VARCHAR2(30), vertriebskanal NUMBER )PARTITION BY LIST (auftragsart) ( PARTITION produkt VALUES ('ARTIKEL','TAUSCHWARE'), PARTITION service VALUES ('SERVICE','REISE'), PARTITION storno VALUES ('RETOURE','KOMMISSION'), PARTITION andere VALUES (DEFAULT) );

Skript Beispiel für Sub-Partition (Range-List)

CREATE TABLE f_umsatz_range_list(ARTIKEL_ID NUMBER(10),KUNDEN_ID NUMBER(10),ZEIT_ID DATE,REGION_ID NUMBER(10),KANAL_ID NUMBER(10),UMSATZ NUMBER(10),MENGE NUMBER(10),UMSATZ_GESAMT NUMBER(10))PARTITION BY RANGE (ZEIT_ID)

SUBPARTITION BY LIST (KANAL_ID) SUBPARTITIONTEMPLATE

(SUBPARTITION kanal1 VALUES (1),SUBPARTITION kanal2 VALUES (2),SUBPARTITION kanal3 VALUES (3),SUBPARTITION kanal4 VALUES (4),SUBPARTITION kanal5 VALUES (5),SUBPARTITION kanal6 VALUES (6),SUBPARTITION kanal7 VALUES (7))

(PARTITION jan10 VALUES LESS THAN

(TO_DATE('2010-02-01','SYYYY-MM-DD')),PARTITION feb10 VALUES LESS THAN

(TO_DATE('2010-03-01','SYYYY-MM-DD')),PARTITION mar10 VALUES LESS THAN (TO_DATE('2010-04-01','SYYYY-MM-DD')),PARTITION apr10 VALUES LESS THAN (TO_DATE('2010-05-01','SYYYY-MM-DD')),PARTITION mai10 VALUES LESS THAN (TO_DATE('2010-06-01','SYYYY-MM-DD')),PARTITION jun10 VALUES LESS THAN (TO_DATE('2010-07-01','SYYYY-MM-DD')),PARTITION jul10 VALUES LESS THAN (TO_DATE('2010-08-01','SYYYY-MM-DD')),PARTITION aug10 VALUES LESS THAN (TO_DATE('2010-09-01','SYYYY-MM-DD')),PARTITION sep10 VALUES LESS THAN (TO_DATE('2010-10-01','SYYYY-MM-DD')),PARTITION oct10 VALUES LESS THAN (TO_DATE('2010-11-01','SYYYY-MM-DD')),PARTITION nov10 VALUES LESS THAN (TO_DATE('2010-12-01','SYYYY-MM-DD')),PARTITION dec10 VALUES LESS THAN (TO_DATE('2011-01-01','SYYYY-MM-DD')),PARTITION jan11 VALUES LESS THAN (TO_DATE('2011-02-01','SYYYY-MM-DD')),PARTITION feb11 VALUES LESS THAN (TO_DATE('2011-03-01','SYYYY-MM-DD')),PARTITION mar11 VALUES LESS THAN (TO_DATE('2011-04-01','SYYYY-MM-DD')),PARTITION apr11 VALUES LESS THAN (TO_DATE('2011-05-01','SYYYY-MM-DD')),PARTITION mai11 VALUES LESS THAN (TO_DATE('2011-06-01','SYYYY-MM-DD')),PARTITION jun11 VALUES LESS THAN (TO_DATE('2011-07-01','SYYYY-MM-DD')),PARTITION jul11 VALUES LESS THAN (TO_DATE('2011-08-01','SYYYY-MM-DD')),PARTITION aug11 VALUES LESS THAN (TO_DATE('2011-09-01','SYYYY-MM-DD')),PARTITION sep11 VALUES LESS THAN (TO_DATE('2011-10-01','SYYYY-MM-DD')),

PARTITION oct11 VALUES LESS THAN (TO_DATE('2011-11-01','SYYYY-MM-DD')),

PARTITION nov11 VALUES LESS THAN (TO_DATE('2011-12-01','SYYYY-MM-DD')),

PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')),

PARTITION next_month VALUES LESS THAN (MAXVALUE));

Beispielabfrage auf Range/ListSELECT /*+ no cache, no parallel */ sum(UMSATZ) Umsatz FROM F_UMSATZ[_RANGE[_LIST]] U, D_vertriebskanal VWHERE u.kanal_id = v.kanal_id and zeit_ID = to_date('10.03.2011','DD.MM.YYYY') and V.vertriebskanal = 'Shop';

Parallel Sekunden Physical ReadsP0 ohne Partitioning 3,38 276045P0 mit Range-Partitioning 0,09 782P0 mit Range/List-Partitioning

0,08 248

(Diese Ergebnisse lassen nur schwer Unterschiede erkennen, obwohl das System eine Tabelle mit über 52 Millionen Sätzen liest. Daher ist das Auslesen der „Physical Reads“ hilfreich.-> set autotrace on)

Zweites Range/List – Beispiel[Analog dem ersten Beispiel, daher verkleinert]CREATE TABLE F_bestellung_part_range_list ( SUMME NUMBER(14,0), MENGE NUMBER(14,0), BESTELLDATUM DATE, FK_ARTIKEL_ID NUMBER, FK_KUNDEN_ID NUMBER, FK_ORT_ID NUMBER, FK_DATUM_ID NUMBER, auftragsart VARCHAR2(30) )PARTITION BY RANGE (bestelldatum) SUBPARTITION BY LIST (auftragsart) SUBPARTITION TEMPLATE

( SUBPARTITION produkt VALUES ('ARTIKEL','TAUSCHWARE','PRODUKT'),

SUBPARTITION service VALUES ('SERVICE','REISE'), SUBPARTITION storno VALUES ('RETOURE','KOMMISSION'), SUBPARTITION andere VALUES (DEFAULT) )

( PARTITION jan11 VALUES LESS THAN (TO_DATE('2011-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION feb11 VALUES LESS THAN (TO_DATE('2011-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION mar11 VALUES LESS THAN (TO_DATE('2011-04-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION apr11 VALUES LESS THAN (TO_DATE('2011-05-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION mai11 VALUES LESS THAN (TO_DATE('2011-06-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jun11 VALUES LESS THAN (TO_DATE('2011-07-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jul11 VALUES LESS THAN (TO_DATE('2011-08-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION aug11 VALUES LESS THAN (TO_DATE('2011-09-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION sep11 VALUES LESS THAN (TO_DATE('2011-10-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION oct11 VALUES LESS THAN (TO_DATE('2011-11-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION nov11 VALUES LESS THAN (TO_DATE('2011-12-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jan12 VALUES LESS THAN (TO_DATE('2012-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION feb12 VALUES LESS THAN (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION next_month VALUES LESS THAN (MAXVALUE) TABLESPACE DWH_SPINDEL );

Skript Beispiel Reference PartitioningCREATE TABLE bestellung_part_range_ref ( bestellnr NUMBER(10) NOT NULL, kundencode NUMBER(10), bestelldatum DATE, lieferdatum DATE, bestell_total NUMBER(12,2), auftragsart VARCHAR2(30), vertriebskanal NUMBER )PARTITION BY RANGE (bestelldatum) PARTITION dez09 VALUES LESS THAN (TO_DATE('2010-01-01', 'SYYYY-MM-DD')) TABLESPACE part, PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01', 'SYYYY-MM-DD')) TABLESPACE part, PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03-01', 'SYYYY-MM-DD')) TABLESPACE part, PARTITION mar10 VALUES LESS THAN (TO_DATE('2010-04-01', 'SYYYY-MM-DD')) TABLESPACE part, PARTITION apr10 VALUES LESS THAN (TO_DATE('2010-05-01', 'SYYYY-MM-DD')) TABLESPACE part, PARTITION mai10 VALUES LESS THAN (TO_DATE('2010-06-01', 'SYYYY-MM-DD')) TABLESPACE part ); ALTER TABLE bestellung_part_range_ref ADD CONSTRAINT pk_bestellnr_part_range_ref PRIMARY KEY (bestellnr);

CREATE TABLE best_position_part_range_ref ( posnummer NUMBER(10) NOT NULL, artikelnr NUMBER(10) NOT NULL, bestellnr NUMBER(10) NOT NULL, bestellmenge NUMBER(10), CONSTRAINT fk_bestellnr_part_range_ref FOREIGN KEY (bestellnr) REFERENCES bestellung_part_range_ref (bestellnr) )PARTITION BY REFERENCE (fk_bestellnr_part_range_ref);

Skript Beispiel Interval-Partition mit MonatspartitionenCREATE 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 );[Wenn ENABLE ROW MOVEMENT* verwendet wird, migrieren Datensätze bei Änderung der Werte automatisch in die jeweiligen Partitionen]

Skript Beispiel Interval-Partition mit numerischer Bereichsangabecreate table test (MA_NR number(6), NACHNAME varchar2(30), GEHALT number(6)) partition by range(GEHALT) Interval (5000)( partition p1 values less than (5000), partition p2 values less than (10000),partition p3 values less than (15000),partition p4 values less than (20000));

Ändern bestehender Range-Partition-Tables auf Interval PartitioningALTER TABLE table_name SET INTERVAL (interval value);

Skript Beispiel Virtuel Column PartitionCREATE TABLE "BESTELLUNG_VIRTUELL" ("BESTELLNR" NUMBER(10) NOT NULL,"KUNDENCODE" NUMBER(10),"BESTELLDATUM" DATE,"LIEFERDATUM" DATE,"BESTELL_TOTAL" NUMBER(12, 2),"AUFTRAGSART" VARCHAR2(30),


Data Warehouse Technik im Fokus - Skripte in Kurzform 20 /44"VERTRIEBSKANAL" NUMBER,Bonusgewicht as (Bestell_Total*VertriebsKanal/10))PARTITION BY RANGE (Bonusgewicht)(PARTITION Gruppe_20 VALUES LESS THAN (20),PARTITION Gruppe_50 VALUES LESS THAN (50),PARTITION Gruppe_100 VALUES LESS THAN (100),PARTITION Gruppe_MAX VALUES LESS THAN (MAXVALUE));

Zuweisen unterschiedlicher Tablespaces ALTER TABLE BestellungADD PARTITION DEZ08 VALUES LESS THAN(to_date('2008-12-31 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TS_PAR DEPENDENT TABLES (Best_Position(PARTITION DEZ08_Child TABLESPACE TS_DEZ08_Child));

Abfragen auf PartitionenPartitiondaten direkt abfragenSELECT * FROM Bestellung PARTITION (Apr08);SELECT * FROM Bestellung SUBPARTITION (Apr08_PRODUKT);

Grössen, Mengen und Namen select segment_name, TABLESPACE_NAME ,segment_type, partition_name, BYTES/1000000 MB,BLOCKS from user_segments;

Verteilung von Partitionen auf Tablespacesselect segment_name,Partition_name,TABLESPACE_NAME from user_segments;

Tabellen,Partition,RowsSELECT table_name, partition_name, num_rows FROM user_tab_partitions

SELECT table_name, partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BESTELLUNG';

SELECT table_name, partition_name, num_rows FROM user_tab_partitions WHERE table_name LIKE 'AUFTRAGSART%';

Tabellen,Partition,Rows,Blocks,MegaBytesSELECT table_name, tablespace_name,partition_name, num_rows, blocks, ((blocks*8192)/1000000) MegaByte, high_value FROM user_tab_partitions

Partitionsgrenzen abfragenSELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name IN ('BESTELLUNG','BEST_POSITION') ORDER BY partition_position, table_name;

Auslesen von Sub Partitionscol subpartition_name format a15col partition_name format a15col SUBPARTITION_POSITION format 99999999

SELECT table_name, partition_name, subpartition_name, subpartition_position FROM user_tab_subpartitions;

SELECT table_name, partition_name, subpartition_name, num_rows FROM user_tab_subpartitions WHERE table_name = 'BESTELLUNG';

SELECT up.table_name, up.partitioning_type, uc.table_name ref_table FROM user_part_tables up,

(select r.table_name, r.constraint_name from user_constraints uc, user_constraints r

where uc.constraint_name=r.constraint_name and uc.owner=r.owner) ucWHERE up.ref_ptn_constraint_name = uc.constraint_name(+)

AND up.table_name IN ('BESTELLUNG','BEST_POSITION');

Sich refenzierende Tabellen abfragen / Referen PartitioningSELECT table_name, partitioning_type,ref_ptn_constraint_name FROM user_part_tables WHERE table_name IN ('BESTELLUNG','BEST_POSITION');

Verwaltung von PartitionenPartionen hinzufügenALTER TABLE BESTELLUNGADD PARTITION NOV08 VALUES LESS THAN (to_date('2008-11-30 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TS_PAR

Partitionen auf anderen Tablespace verlagernALTER TABLE BESTELLUNGMOVE PARTITION Jan08 TABLESPACE TS_PAR_Archiv;

ALTER TABLE BESTELLUNGMOVE SUBPARTITION Jan08_STORNOTABLESPACE TS_PAR_Archiv_Jan;

Merge von zwei Sub-PartitionsALTER TABLE BESTELLUNG MERGE SUBPARTITIONS TELEFON_STORNO, TELEFON_ANDERE

INTO SUBPARTITION TELEFON_OBJEKTETABLESPACE TS_PAR;

Ändern Defaults-TablespaceALTER TABLE BESTELLUNG MODIFY DEFAULT ATTRIBUTES FOR PARTITION Jan08TABLESPACE TS_PAR_JAN ;

Ändern der Werte bei List-PartitioningALTER TABLE Bestellung MODIFY PARTITION Produkt ADD VALUES ('WARE'); ALTER TABLE Bestellung MODIFY PARTITION Storno DROP VALUES ('KOMMISSION');

Umwandeln einer Partion in eine Tabelle-- Leere Tabelle mit der passenden Struktur erstellenCREATE TABLE Bestellung_Produkte AS SELECT * FROM bestellung WHERE 1=2-- ExchangeALTER TABLE Bestellung EXCHANGE PARTITION Produkt WITH TABLE Bestellung_Produkte;

Umbenennen einer PartitionALTER TABLE Bestellung RENAME PARTITION Andere TO Bestellung_Rest;

Truncate einer PartitionALTER TABLE Bestellung TRUNCATE PARTITION Service DROP STORAGE;

Aufspalten einer PartitionALTER TABLE Bestellung SPLIT PARTITION Jan08AT (to_date('15-JAN-2008','DD-MON-YYYY'))INTO (PARTITION Jan08_1, PARTITION Jan08_2)UPDATE GLOBAL INDEXES;

Partition Exchange (Hinzufügen einer Tabelle als weitere Partition)ALTER TABLE Bestellung EXCHANGE PARTITION Nov08 WITH TABLE Bestellung_temp INCLUDING INDEXES WITHOUT VALIDATION;

Relevante Dictionary Views für PartitioningDBA_PART_TABLES Table-LevelDBA_TAB_PARTITIONS Partition-/Subpartition-LevelDBA_PART_KEY_COLUMNS Partition-/Subpartition- DBA_SUBPART_KEY_COLUMNS Key-InformationenDBA_PART_COL_STATISTICS Statistiken und Histogramme per Partition / SubpartitionUSER_IND_PARTITIONSDBA_SUBPART_COL_STATISTICSDBA_TAB_SUBPARTITIONSUser_segments



IndexeRelevante FragestellungenUmschlüsselung im DWHBitmap / B*Tree EinsatzPlatzverbrauchWelche Indexe gibt es in einem Schema?Überprüfen, ob Indexe gebraucht werden Clustering-FaktorLokale Indexe Partitionierung von IndexenPrefixed / Non-Prefixed IndexStar-TransformationWie und wo wird im DWH indiziert?Umgang mit Indexen während eines Massenloads beim ETL

Wie und wo wird indiziert

Suche nachbestehenden IndexenSuche nach Indexen bezogen auf eine bestimmte TabelleSELECT index_name, partitioned FROM user_indexes WHERE table_name = 'BESTELLUNG';

Status-Abfragenselect index_name, status from user_indexes;

Auflisten aller Indexe eines Schemas inkl. GrößeSELECT index_name,Table_name,status, num_rows,index_type, blevel, leaf_blocks, distinct_keys FROM user_indexes

Auflisten aller lokalen IndexSELECT ip.index_name, ip.partition_position, ip.partition_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'BESTELLUNG_PART_RANGE' ORDER BY index_name, partition_position;

SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'BESTELLUNG';

Anzeigen des Platzverbrauchs der Indexeselect substr(us.segment_name,1,20) Name, ui.index_type Typ, round((us.bytes/1000000),2) Mbyte, us.tablespace_name TB_Space from user_segments us, user_indexes ui where us.segment_type = 'INDEX' and ui.index_type = 'BITMAP'and ui.index_name = us.segment_name;

Beispiele für Definitionen (Bitmap / B*tree)Drop index idx_Bildungsgruppe_BM; Create bitmap index idx_Bildungsgruppe_BM on I_Kunde(Bildungsgruppe);

Drop index idx_Bildungsgruppe_BT; Create index idx_Bildungsgruppe_BT on I_Kunde(Bildungsgruppe);

SELECT sum(UMSATZ) Umsatz FROM F_UMSATZ WHEREzeit_ID = to_date('10.03.2011','DD.MM.YYYY')/Parallel SekundenP0 ohne Bitmap-Index 3,38P0 mit Bitmap-Index 0,2

Behandlung von Indexen beim Laden1. INDEX auf Unusable setzen

[Alter index index_name usable] 2. ETL-Massen-Load durchführen

3. INDEX Rebuild [Alter index index_name rebuild]

Oder 1. DROP Index index_name2. CREATE Index index_name

Rebuild Partitioned IndexALTER TABLE <table_name>MODIFY PARTITION <partition_name>REBUILD UNUSABLE LOCAL INDEXES;

Rebuild Sub-Partitioned IndexALTER TABLE <table_name>MODIFY SUBPARTITION <subpartition_name>REBUILD UNUSABLE LOCAL INDEXES;

Verschieben eines Index auf einen anderen TablespaceALTER INDEX <index_name>REBUILD PARTITION <partition_name>TABLESPACE <new_tablespace_name>;

ALTER TABLE part1MOVE PARTITION p1 TABLESPACE new_tbsp UPDATE INDEXES(my_parts_idx (PARTITION p1 TABLESPACE my_tbsp));

Aktualisierung der Index zusammen mit Tabellen-UpdatesALTER TABLE T1 DROP PARTITION P1 UPDATE GLOBAL INDEXES

Einschalten Usage - Monitoring für einen Indexalter index PK_BESTELLNR_PART_RANGE_HASH monitoring usage.

Welcher Index wurde tatsächlich genutzt: Usage - AbfrageSELECT INDEX_NAME, TABLE_NAME, MONITORING, USED FROM SYS.V$OBJECT_USAGE;

Clustering Faktor und Anzahl Leaf-Blöcke abfragenselect index_name, tablespace_name, blevel, leaf_blocks,distinct_keys, clustering_factor from user_indexes

Partitionierung von IndexenSkript Local Partion IndexCREATE INDEX idx_region_id ON F_Umsatz_range (region_id) LOCAL;

Partitionierung der Indexe entsprechend der zugehörigen Table-Partionen (Local Partiton Index) und zweisen unterschiedlicher TablespacesCREATE INDEX idx_bestellnr_part_range ON bestellung_part_range (bestellnr) LOCAL ( PARTITION B0, PARTITION B1, PARTITION B2, PARTITION B3, PARTITION B4);

Partitionierung des Index unabhängig von den Table-Partitionen (Global Partition Index)CREATE INDEX idx_UMSATZ_RANGE on f_umsatz_range (Kunden_id) GLOBAL PARTITION BY RANGE (Kunden_id) ( PARTITION index100 VALUES LESS THAN (100), PARTITION index500 VALUES LESS THAN (500), PARTITION index1000 VALUES LESS THAN (1000), PARTITION index_MAX VALUES LESS THAN (MAXVALUE));

Partitionierte Indexe suchenSELECT index_name, partitioned FROM user_indexes WHERE table_name = 'BESTELLUNG';

SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'BESTELLUNG';

Rebuild lokaler IndexALTER TABLE <table_name>MODIFY PARTITION <partition_name>REBUILD UNUSABLE LOCAL INDEXES;

Rebuild Sub Partition IndexALTER TABLE <table_name>MODIFY SUBPARTITION <subpartition_name>REBUILD UNUSABLE LOCAL INDEXES;

Verschieben auf anderen TablespaceALTER INDEX <index_name>REBUILD PARTITION <partition_name>TABLESPACE <new_tablespace_name>;

Relevante Dictionary Views für IndexingUSER_IND_STATISTICS USER_INDEXESINDEX_HISTOGRAM INDEX_STATSUSER_IND_PARTITIONS SYS.V$OBJECT_USAGEUSER_INDEXTYPES USER_INDEXTYPE_OPERATORSUSER_IND_COLUMNS USER_IND_EXPRESSIONS USER_IND_PARTITIONS USER_IND_SUBPARTITIONS



Star SchemaAllgemeine Regeln Star Schema mit dnormalisierten Dimensionen verwenden und

kein Snowflake -> intuitiver für Anwender und weniger Joins

Fakten-Tabelle so granular wie möglich aufbauen. Orientieren an der Granularität der Vorsysteme-> Schafft mehr Flexibilität in den Auswertungen.

Verwandte Fakten-Tabellen schaffen. Verbindungen über Dimensionstabellen aufbauen (Conformed Dimensions).-> Man erhält mehr Abfrage-Kombinationen und mehr Auswahl für die Endbenutzer.

Operativ genutzte Daten (z. B. Kontaktdaten für Kampagnen)in separate Dimensionen oder Tabellen auslagern (1:1- Auslagerung)

Überfrachtete Dimensionen mit zu vielen Attributen aufspalten in 2 oder mehrere. Die Faktentabelle mit der großen Masse der Daten ist davon nicht betroffen.-> macht das Star Schema übersichtlicher

Faktentabellen besitzen i. d. R. Keinen Primary Key. Wenn doch solche Schlüssel eingeführt werden, dann sind es technische Schlüssel, die mit dem Ladelauf zusammenhängen, um eventuell Daten wieder herauszulöschen.

Fakten-Tabellen sind meist Kandidaten für Partitioning. Dimensionen mit nur einem Attribut in die Fakten-Tabelle

verlagern (Degenerated Dimension. Star Schema einfach und verständlich halten

o Level-bezogene Präfixe nutzeno Sprechende Column-Namen nutzeno Codes vermeiden

Konsistenz, Mengen und Indexe im Star-Schema

Relevante FragestellungenWann zieht die Star TransformationVoraussetzungen prüfen

Prüfen ob FK der Fakten-Tab in PK der Dimensionenselect count(*) from f_umsatz where artikel_id not in (select artikel_ID from d_artikel);

Größe der Indexe ausgebenselect segment_name, round((bytes/1000000),2) Mbyte ,tablespace_name from user_segments where segment_type = 'INDEX'

Index-Typen anzeigen select index_name, index_type from user_indexes;

Größe aller Bitmap-Indexeselect us.segment_name, round((us.bytes/1000000),2) Mbyte ,us.tablespace_name from user_segments us, user_indexes ui where us.segment_type = 'INDEX' and ui.index_type = 'BITMAP'and ui.index_name = us.segment_name;

Star Transformation aktivierenalter system set star_transformation_enabled=TRUE;

Menge der WHERE-Bedingungen überprüfenStar-Query zieht nur, wenn neben den PK/FK-Join-Bedingungen mindestens 2 zusätzliche einschränkende Bedingungen abgefragt werden. Die Einschränkungen können auf den FK-Feldern der Fakten oder auf einem beliebigen Feld der Dimensions-tabellen liegen. [siehe Beispielabfrage 3]



Materialized ViewsRelevante FragestellungenEinsatzgründe für Materialized (Verwaltung + Performance)Definition (Erstellung)Planung und KonzeptStalenessKennzahlen-Systemen (Nested Materialized ViewsRewrite-FähigkeitRefresh-Varianten (Log / PCT)

Hilfen/Konzepte für Materialized ViewsAllgemeine Hinweise zur Definition von Materialized Views Zählfunktionen einbauen Count(*) / Count(feldname) Bei Joins i. d. R. die granularsten Hierarchie-Level in der

MAV joinen. Aufeinander aufbauende Materialized Views nutzen.

-> Das verhindert redundanten Join-Aufwand bei dem Refresh. Im Star-Schema werden die meisten MAVs zwischen Fakten und

den jeweiligen Dimensionen gebildet (auf unterstem Level). Danach wird die Faktentabelle mit 2 Dimensionen verbunden und dann mit 3 usw.

Die hier dargestellten Materialized Views sorgen dafür, dass alle oben beschriebenen Beispielabfragen nur weniger als 0,2 Sekunden benötigen. Materialized Views werden Oracle-intern wie Tabellen

behandelt, daher gelten einige der Verhaltensweisen von Tabellen und auch Abfragen auch für Materialized Views. Um sie dennoch zu unterscheiden, kann man eine Namenskonvention (z. B. MAV_...) einführen, wie in diesem Beispiel)

Informationszugriffen planen und dokumentieren

Relevante ParameterALTER SESSION SET query_rewrite_enabled=TRUE;ALTER SESSION SET query_rewrite_enabled=FALSE;ALTER SESSION SET query_rewrite_integrity=STALE_TOLERATED

MVIEW suchenselect mview_name,REWRITE_ENABLED,STALENESS,REWRITE_CAPABILITY from user_mviews;

Größe und Anzahl Zeilen von Mviews abfragenselect table_name, pct_free,blocks,blocks/1000000*8 GB,num_rows, compression,cache,empty_blocks from user_tableswhere upper(table_name) like 'MAV_%';

Beispieldefinition MAV_Zeit_Umsatz

Unterstützte Kennzahlen/AbfragenGesamtumsatz pro Tag/Monat/Quartal/Zeit

drop materialized View Mav_Zeit_umsatz;[Beispielabfrage 5,6,7]

create MATERIALIZED VIEW Mav_Zeit_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, Z.Zeit_ID, count(*) FROM F_UMSATZ U, D_Zeit ZWHERE U.Zeit_ID = Z.Zeit_IDGroup by Z.Zeit_ID;

Parallel Sekunden Physical Reads

P4 ohne Partitioning 67 276045

Beispieldefinition MAV_Artikel_UmsatzUnterstützte Kennzahlen/Abfragen

Aggregiert auf

Gesamtumsatz/menge pro Artikel/Gruppe/Sparte

Artikelgruppe und -sparte„“

drop materialized View Mav_Artikel_umsatz;

[Beispielabfrage 9,11,(19)]

create MATERIALIZED VIEW Mav_artikel_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, A.Artikel_ID, count(*) FROM F_UMSATZ U, D_Artikel AWHERE U.Artikel_ID = A.Artikel_IDGroup by A.Artikel_ID;

Parallel Sekunden Physical ReadsP4 ohne Partitioning 90 276045

Beispieldefinition MAV_Region_UmsatzUnterstützte Kennzahlen/AbfragenGesamtumsatz/menge pro Ort/Kreis/Land/Region

drop materialized View Mav_Region_umsatz;

[Beispielabfrage 4]

create MATERIALIZED VIEW Mav_Region_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, count(*) FROM F_UMSATZ U, D_Region RWHERE U.Region_ID = R.Region_IDGroup by R.Region_ID;

Parallel Sekunden Physical ReadsP4 ohne Partitioning

90 276045

Beispieldefinition MAV_Kunde_UmsatzUnterstützte Kennzahlen/AbfragenGesamtumsatz/menge pro Kunde/Bildungsgruppe, Einkommensgruppe, Wohnart, Berufsgruppe

drop materialized View Mav_Kunde_umsatz;

[Beispielabfrage 20]

create MATERIALIZED VIEW Mav_Kunde_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, k.Kunden_ID, count(*) FROM F_UMSATZ U, D_Kunde KWHERE U.Kunden_ID = k.Kunden_IDGroup by k.Kunden_ID;


P4 ohne Partitioning 70 276045

Beispieldefinition MAV_Region_Artikel_UmsatzUnterstützte Kennzahlen/AbfragenGesamtumsatz/menge pro Ort/Kreis/Land/RegionArtikel, Artikelgruppe und -sparte

[Beispielabfrage 11,12,13,14,15,16,(19)]

drop materialized View Mav_Region_Artikel_umsatz;



create MATERIALIZED VIEW Mav_Region_Artikel_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, a.Artikel_ID, count(*) FROM F_UMSATZ U, D_Region R, d_Artikel AWHERE U.Region_ID = R.Region_ID and U.Artikel_ID = A.Artikel_IDGroup by R.Region_ID,a.Artikel_ID;


12,0 276045

Beispieldefinition MAV_Region_Zeit_UmsatzUnterstützte Kennzahlen/AbfragenGesamtumsatz/menge pro Ort/Kreis/Land/RegionTag/Monat/Quartal/Jahr

[Beispielabfrage 8]

drop materialized View Mav_Region_Zeit_umsatz;

create MATERIALIZED VIEW Mav_Region_Zeit_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, Z.Zeit_ID, count(*) FROM F_UMSATZ U, D_Region R, d_Zeit ZWHERE U.Region_ID = R.Region_ID and U.Zeit_ID = Z.Zeit_IDGroup by R.Region_ID,z.Zeit_ID;


P4 ohne Partitioning

14,0 276045

Beispieldefinition MAV_Kunde_Zeit_Umsatz

Unterstützte Kennzahlen/AbfragenGesamtumsatz/menge pro Ort/Kreis/Land/RegionTag/Monat/Quartal/Jahr

drop materialized View Mav_Kunde_Zeit_umsatz;

create MATERIALIZED VIEW Mav_Kunde_Zeit_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, K.Kunden_ID, Z.Zeit_ID, count(*), count(u.umsatz), count(u.menge)FROM F_UMSATZ U, D_Kunde K, d_Zeit ZWHERE U.Region_ID = K.Kunden_ID and U.Zeit_ID = Z.Zeit_IDGroup by K.Kunden_ID,z.Zeit_ID;

Parallel Sekunden Physical ReadsP4 ohne Partitioning 3,7 276045

Beispieldefinition MAV_Region_Zeit_Artikel_Umsatz

Unterstützte Kennzahlen/AbfragenGesamtumsatz/menge pro Ort/Kreis/Land/RegionTag/Monat/Quartal/JahrArtikel/Artikelgruppe und -sparte

[Beispielabfrage 10]

drop materialized View Mav_Region_Zeit_Artikel_umsatz;

create MATERIALIZED VIEW Mav_Region_Zeit_Artikel_umsatzBUILD IMMEDIATE REFRESH COMPLETE ON DEMAND

ENABLE QUERY REWRITEasSELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, Z.Zeit_ID, a.Artikel_ID, count(*) FROM F_UMSATZ U, D_Region R, d_Zeit Z, d_artikel AWHERE U.Region_ID = R.Region_ID and U.Artikel_ID = A.Artikel_ID and U.Zeit_ID = Z.Zeit_IDGroup by R.Region_ID,z.Zeit_ID,A.Artikel_ID;


17 276045

Beispiel-DefinitionCREATE 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;

Beispiel für eine Partitionierte MAVCREATE TABLE f_umsatz_Par (umsatz NUMBER(14,0), menge NUMBER(14,0), bestell_datum DATE, artikel_id NUMBER, kunde_id NUMBER, region_id NUMBER, zeit_id NUMBER)PARTITION BY RANGE(bestell_datum) )(PARTITION p_2009_Jan VALUES LESS THAN (to_date('01.02.2009','dd.mm.yyyy')), PARTITION p_2009_feb VALUES LESS THAN (to_date('01.03.2009','dd.mm.yyyy')), PARTITION p_2009_mar VALUES LESS THAN (to_date('01.04.2009','dd.mm.yyyy'));

Definition Materialized View LogCREATE MATERIALIZED VIEW LOG ON f_umsatz WITH ROWID, SEQUENCE(umsatz, menge, umsatz_nach_rabatt, rabatt_wert_firmenkunde,Rabatt_wert_privatkunde, bestell_datum, artikel_id, kunde_id, region_id, zeit_id) INCLUDING NEW VALUES;

Löschen Materilized View Logdrop materialized view log on f_Bestellungen;

Build FunktionenBUILD IMMEDIATEBUILD DEFERREDON PREBUILD

Refresh FunktionenCOMPLETEFASTFORCENEVERON COMMITON DEMAND

Feststellen, ob PCT Tracking funktioniertselect mview_name, num_PCT_TABLES from user_mviews;

Refresh auf eine MAVEXECUTE dbms_mview.refresh('MV_standard_PCT_richtig','P');P: PCT, (Partition Change Tracking, nur, wenn Partition Schlüssel in der where-Klausel der MAV) C: Complete (immer komplettes Refresh)F: Fast (Inkrementelles Lesen, wenn möglich, View-Log oder PCT?: Force, alle Varianten, die schnellste wird genommen

PMARKER Funktion(Eingebaut ist DBMS_MVIEW.PMARKER(u.rowid) – Funktion um PCT zu ermöglichen, wenn Partition Key Feld nicht in der WHERE-Klausel der MAV enthalten ist.)CREATE MATERIALIZED VIEW MV_standard_PCT_falsch_PMENABLE QUERY REWRITE AS SELECT DBMS_MVIEW.PMARKER(u.rowid), z.jahr_nummer Jahr,z.monat_desc Monat, sum(u.umsatz) Summe, a.artikel_id ID, count(u.umsatz)FROM f_umsatz_par u, d_artikel a, d_zeit z WHERE a.artikel_id = u.artikel_id AND

u.zeit_id = z.datum_id


Data Warehouse Technik im Fokus - Skripte in Kurzform 25 /44 GROUP BY DBMS_MVIEW.PMARKER(u.rowid), z.jahr_nummer, z.monat_desc, a.artikel_id;

Komprimieren von Materialized ViewsALTER MATERIALIZED VIEW MV_standard COMPRESS;EXECUTE DBMS_MVIEW.REFRESH('MV_STANDARD',atomic_refresh=>TRUE);EXECUTE dbms_stats.gather_table_stats('MAV','MV_standard');SELECT table_name, tablespace_name, compression, BLOCKSFROM user_tables;

Refresh auf alle von einer Tabelle abhängigen MAVs (REFRESH DEPENDENT)1.Beispielvariable failures number;EXECUTE DBMS_MVIEW.REFRESH_DEPENDENT(

:failures,'F_UMSATZ','C',refresh_after_errors=>true);print failures2. BeispielDBMS_MVIEW.REFRESH_DEPENDENT ('employees,deptartments,hr.regions','cf');

Aktualisieren aller Materialized ViewsDBMS_MVIEW.REFRESH_ALL: -- hat keine Parameter

Anzeigen Materialized Views mit Zustand und View LogsSELECT mview_name, update_log, stale_since, staleness FROM user_mviews

Anzeigen Materialized Views mit Zustand und PCT RegionsSELECT mview_name, staleness, num_fresh_pct_regions, num_stale_pct_regions FROM user_mviews;

Stimmigkeit von Dimensionen prüfen\ora-home\RDBMS\ADMIN\utldim.sql à Legt Tabelle DIMENSION_EXCEPTIONS an

variable stmt_id varchar2(30);execute :stmt_id := 'CUST_DIM_VAL';execute dbms_dimension.validate_dimension ('MAV.D_ARTIKEL',FALSE,TRUE,:stmt_id );

SELECT distinct owner, table_name, dimension_name, relationship FROM dimension_exceptions WHERE statement_id = :stmt_id;

DBMS_MVIEW.Explain_mview(Besser über Enterprise Manager analysieren)start D:\O11\db11\RDBMS\ADMIN\utlxmv.sqlà Legt Tabelle mv_capabilities_table an

exec dbms_mview.explain_mview( - 'SELECT z.jahr_nummer,z.monat_desc , - sum(u.umsatz) ,a.artikel_id ID,count(u.umsatz) - FROM f_umsatz_par u, d_artikel a, d_zeit z - WHERE a.artikel_id = u.artikel_id - AND u.bestell_datum = z.datum_desc - GROUP BY z.jahr_nummer, z.monat_desc, a.artikel_id');

PL/SQL procedure successfully completed.

SELECT capability_name, possible p, substr(related_text,1,20) obj, substr(msgtxt,1,100) erklaerung FROM mv_capabilities_table;

Auflisten von Dimension-Tablesselect dimension_name OWNER,DIMENSION_NAME,INVALID,COMPILE_STATE,REVISION from user_dimensions

Anzeigen der Struktur einer Dimensional TableEXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('D_ARTIKEL');

Relevante Dictionary ViewsALL_MVIEWSDBA_MVIEWSUSER_MVIEWSUSER_MVIEW_DETAIL_PARTITIONUSER_MVIEW_DETAIL_RELATIONSUSER_MVIEW_DETAIL_SUBPARTITIONUSER_DIMENSIONS



Optimierung für schnelles Lesen Memory-CacheTabellen in Cache legen / aus Cache entfernenalter table D_ARTIKEL cache;alter table D_ARTIKEL nocache;

Welche Tabellen liegen im CacheSELECT owner, table_name FROM dba_tables WHERE LTRIM(cache) = 'Y';

Query Result CacheAktivieren mitalter system set result_cache_mode=FORCE; (MANUAL)RESULT_CACHE_MAX_RESULTRESULT_CACHE_MAX_SIZERESULT_CACHE_MODERESULT_CACHE_REMOTE_EXPIRATION

Ausnutzen Result Cache durch Hint in der AbfrageSELECT /*+ result_cache */ COUNT(*), SUM(BESTELL_TOTAL) FROM F_UMSATZ;

Abfragen auf im Cache befindliche StatementsSELECT name, type, row_count, invalidations, scan_count FROM v$result_cache_objects;

Result-Cache-Memory-Reportexecute dbms_result_cache.memory_report()

Result Cache leerenexec dbms_result_cache.flush;

Relvante Dictionary Views für Result-CacheV$RESULT_CACHE_OBJECTS

Parallelisierung

Relevante FragestellungenAktivieren / DeaktivierenAbfragen der eingestellten ParallelitätGezieltes Steuern der Parallelität für einzelne Tabellen

Parallelisierung aktivierenALTER SESSION FORCE PARALLEL QUERYALTER SESSION ENABLE PARALLEL DML;ALTER SESSION ENABLE PARALLEL DDL;

Automatisches Steuern mitALTER SESSION SET PARALLEL_AUTOMATIC_TUNING = TRUE; (veraltet)

Ab 11.2: ALTER SESSION/SYSTEM SET PARALLEL_DEGREE_POLICY=MANUAL/LIMITD/AUTOMANUAL: Kein automated DOP / Kein Statement Queuing /Kein In-

Memory Parallel ExecutionLIMITED: Eingeschränkter automated DOP auf Tabellen mit

Default Parallelisierung , Kein In-Memory Parallel Execution

AUTO: Alle Statements werden parallelisiert, Statement Queuing / In-Memory Parallel Execution

Eine einzelne Tabelle auf parallel” setzenALTER TABLE tab_Name PARALLEL n;ALTER TABLE tab_Name NOPARALLEL;

Parallelisierter Select-ZugriffSELECT /*+ parallel(B) parallel(A) */ a,b,c FROMBESTELLUNG B, ARTIKEL A;INSERT /*+ APPEND PARALLEL(table_name)*/ INTO..

Prüfen welche Art der Parallelisierung eingestellt istselect userName,PDML_ENABLED,PDML_STATUS,PDDL_STATUS,PQ_STATUS from v$session;

Aktuelle SQL-Statements und deren Parallelisierung abfragen select status, username, to_date(SQL_EXEC_START,'DD-MON-YY') , PX_SERVERS_REQUESTED PX_Req, PX_SERVERS_ALLOCATED PX_Alc,substr(sql_text,1,20) from V$SQL_MONITOR where username = 'DWH';(wird erst nach 5 Sekunden Laufzeit aktiviert)

Relevante Dictionary Views für Parallelisierungv$sessionv$sql_monitor SQL-Monitor im Enterprise Manger nutzen

Optimizer - Statistiken sammelnStatistiken für eine TabelleEXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','F_UMSATZ',estimate_percent=>100);

Statistiken für einen IndexEXEC DBMS_STATS.GATHER_INDEX_STATS ('DWH','PK_ARTIKEL',estimate_percent=>100);

Statistiken für eine Materialized View DefinitionEXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','MAV_REGION_ZEIT_UMSATZ',estimate_percent=>100);(Werden wie Tabellen behandelt)

Statistiken für ein SchemaEXEC dbms_stats.gather_schema_stats(ownname => 'ETLDB',estimate_percent => 5,block_sample => TRUE)

Automatische Aktualisierung für ein Schema einrichtenbegin dbms_stats.gather_schema_stats( ownname => 'PERF'

,options => 'GATHER AUTO',estimate_percent => 5,block_sample => TRUE);

end;

Automatisiertes SamplingEXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

Abfrage ob automatisiertes Sammel aktiviert istSELECT client_name, status FROM dba_autotask_operation; CLIENT_NAME STATUS-------------------------------------- --------auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLED

Zustand/Aktualität der Statistiken abfragenselect table_name,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED ,STALE_STATS from user_tab_statistics;

Zustand von Index-Statistiken abfragenselect Index_name,TABLE_NAME,LAST_ANALYZED,STALE_STATS from user_ind_statistics;

Histogramme abfragenselect COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM from sys.USER_TAB_COL_Statistics where TABLE_NAME ='BESTELLUNG'

Histogramme sammelnbeginDBMS_STATS.GATHER_TABLE_STATS(Ownname=>'TIF',Tabname=>'BESTELLUNG' , METHOD_OPT => 'FOR COLUMNS SIZE 10 KUNDENCODE');end;

Abfragen der Grenzwerte der Histogram-BucketsSELECT ENDPOINT_NUMBER, ENDPOINT_VALUEFROM USER_TAB_HISTOGRAMSWHERE TABLE_NAME = 'F_UMSATZ' and COLUMN_NAME = 'ARTIKEL_ID' order by ENDPOINT_NUMBER

Löschen von StatistikenBEGIN DBMS_STATS.DELETE_table_STATS ( OWNNAME => 'DWH', TABNAME => 'D_ARTIKEL');END;

Löschen von Histogrammen für einzelne SpaltenBEGIN dbms_stats.delete_column_stats( ownname=>'DWH', tabname=>'F_UMSATZ', colname=>'ARTIKEL_ID', col_stat_type=>'HISTOGRAM');END;

Markieren von Tabellen um inkrementelles Aktualisieren zu ermöglichenExec DBMS_STATS.SET_TABLE_PREFS(<OWNER>, <TABLE_NAME>, 'INCREMENTAL', ‘TRUE’);

exec DBMS_STATS.SET_TABLE_PREFS('DWH','F_UMSATZ', 'INCREMENTAL', 'TRUE');

Relvante Dictionary Views für StatistikenUSER_TAB_STATISTICS USER_TAB_COL_STATISTICSUSER_IND_STATISTICS

USER_HISTOGRAMSUSER_TAB_HISTOGRAMS



Umgebungsinformationen auslesenDatenbank-Informationen, Version, Patchstände

Relevante FragestellungenDB-NamenVersionPatchständeInstallierte Komponenten

Database-ID abfragenselect dbid from v$database; DBID ---------- 1053456982

Installierte Komponentenselect comp_id,substr(comp_name,1,30) Name ,version,status from dba_registry;

Patchstand abfragenAuf der Betriebssystemebene mit Opatch lsinventory –detail(Opatch befindet sic him Oracle – Home Verzeichnis

Patch-HistorieSELECT * FROM sys.registry$history

Datenbankversion abfragenSELECT * FROM v$version

Relevante Dictionary Viewsdba_registryV$versionV$SESSION V$DATABASE

User-Informationen / Einstellungen /SecurityWelche USER gibt es in einer Umgebung und welchen Zustand haben sieselect username, account_status, lock_date from dba_users order by username;

Benutzer anlegenCreate user DWH identified by DWH default tablespace users;GRANT create session to DWH;Create user OLTP identified by OLTP default tablespace users;GRANT create session to OLTP;

Eigene Tabellen zum Lesen für alle freigebenGrant select any table to public;

Allgemeine Benutzerinformationen abfragenSQL> desc dba_Users

select s.user#, u.user_ID, s.USERNAME,u. PROFILE ,u.DEFAULT_TABLESPACE, substr(s.PROGRAM,1,30) Programm,s.Module from v$session s,dba_users u where s.user# = u.user_IDand s.user# != 0/

Ausschalten Expiration Date in Entwicklungs- und Test-UmgebungALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED;

Ändern des Lock-Zustads eines Usersalter user ANONYMOUS account UNLOCK;

Welche Rechte wurden einem User vergeben-- Beispiel DWHSELECT PRIVILEGE FROM sys.dba_sys_privs WHERE grantee = 'DWH'UNIONSELECT PRIVILEGE FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role) WHERE rp.grantee = 'DWH' ORDER BY 1

Plattenplatz der Segment-Objekte eines Users column segment_name format a30

SELECT sg.segment_name, sg.SEGMENT_TYPE, sg.extents, sg.tablespace_name, sg.blocks, ts.BLOCK_SIZE, round((sg.blocks*ts.BLOCK_SIZE)/1000000,2) MB_SIZE FROM dba_segments sg, dba_tablespaces ts WHERE owner = 'PERF' and

ts.TABLESPACE_NAME = sg.TABLESPACE_NAME

Logins pro Userselect username,count(1) from v$session group by username

User Platzverbrauch Tablespaceset heading on set pagesize 200 set newpage 0 set line 200SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS FROM V$SESSION T1, V$SORT_USAGE T2 WHERE T1.SADDR = T2.SESSION_ADDR ;

Maximale Parallelität von Benutzeraktivitätenselect username, sid, process_name, sql_id, px_maxdop,PX_SERVERS_REQUESTED, substr(sql_text,1,80) from v$sql_monitor where username = 'PETER';

USERNAME SID PROCE SQL_ID PX_MAXDOP PX_SERVERS_REQUESTED SUBSTR(SQL_TEXT,1,80)----------------------------------------------------------------------------------------------------------------------PETER 191 ora apzgjdwwcxb2j 8 16 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4

Relevante Dictionary ViewsV$SESSION DBA_USERS v$sql_monitor

Tablespaces, Auflistungen, Mengen und Größen

Relevante FragestellungenWelche Tabellen belegen den meisten Platz?Wo lohnt sich das „Platzsparen“?Wieviel freien Platz hat die Datenbank noch?Definition von Tablespaces für performantes Massenladen?

Anlegen eines Tablespace mit Zuweisung eines DatafilesCREATE SMALLFILE TABLESPACE "TS_DWH" DATAFILE 'D:\APP\ORADATA\ORCL\DF_DWH' SIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

Größe aller Data Filesselect sum(bytes)/1024/1024 Meg from dba_data_files;

Liste aller Tablespace mit Größenangabenselect substr (tablespace_name, 1, 15) "tablespace", substr (file_name, 1, 33) "Datei", bytes, blocks, status, autoextensible, maxbytes, maxblocks, increment_by from dba_data_files order by tablespace_name, file_name;

Temp-Spaceselect nvl(sum(bytes),0)/1024/1024 Meg from dba_temp_files;

Größe Redo Logsselect sum(bytes)/1024/1024 Meg from sys.v_$log;

Finden der größten Tabellenselect table_name, Owner, blocks from dba_tables where owner = 'MON' order by blocks

Top 10 größten Tabellen eines Usersselect table_name, Blocks from user_tables where rownum < 10 order by blocks desc

Data Files + Redo Logs + Tempselect a.data_size+b.temp_size+c.redo_size total_sizefrom ( select sum(bytes) data_size from dba_data_files ) a, ( select nvl(sum(bytes),0) temp_size from dba_temp_files ) b, ( select sum(bytes) redo_size from sys.v_$log ) c;

Freier Datenbank-Platz-- freespace report col Database Size format a20 col Free space format a20 select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' Database Size , round(free.p / 1024 / 1024) || ' MB' Free space from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p /

Benutzter Datenbank-PlatzSELECT SUM(bytes)/1024/1024 Meg FROM dba_segments;


Data Warehouse Technik im Fokus - Skripte in Kurzform 28 /44Belegter und benutzter Temp-TablespaceSELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) DWHERE A.tablespace_name = D.nameGROUP by A.tablespace_name, D.mb_total;

Blockgrößen auslesenselect tablespace_name, block_size from dba_tablespaces;TABLESPACE_NAME BLOCK_SIZE------------------------------ ----------SYSTEM 8192SYSAUX 8192UNDOTBS1 8192TEMP 8192USERS 8192EXAMPLE 8192MON_G 8192MON 8192MON_D 8192MON_E 8192MON_F 8192

Liste Tabellen und Berechnung der Größe in Byte, MB, GBSELECT table_name, num_rows, blocks, blocks*8 KB,blocks*8/1000 MB,blocks*8/1000000 GB FROM user_tables;

TABLE_NAME NUM_ROWS BLOCKS KB------------------------------------- ---------- ----------BESTELLUNG_PART_RANGE 163840000 962355 7698840BESTELLUNG_PART_RANGE_4 163840000 962355 7698840

Tabellengrößen und PlatzverbrauchSELECT table_name, num_rows, blocks, blocks*8 KB,blocks*8/1000 MB,blocks*8/1000000 GB FROM user_tables;TABLE_NAME NUM_ROWS BLOCKS KB MB GB------------------------ ---------- ---------- ---------- ---------- BESTELLUNG_PART_RANGE 163840000 962355 7698840 7698,84 7,69884BESTELLUNG_PART_RANGE_4 163840000 962355 7698840 7698,84 7,69884

Größen und Mengen mit zusätzlichem Tablespace-Namenselect table_name, tablespace_name, num_rows, blocks, blocks*8 KB, blocks*8/1000 MB,blocks*8/1000000 GB FROM user_tables;SELECT table_name, tablespace_name, partitioned, num_rows, compression, compress_for, blocks, blocks*8 KB FROM user_tables;SELECT table_name, def_tablespace_name, partitioning_type, subpartitioning_type, partition_count, status FROM user_part_tables;SELECT partition_name, partition_position, num_rows, blocks, tablespace_name, high_value FROM user_tab_partitions WHERE table_name = 'BESTELLUNG_PART_RANGE';

Welche Objekte gehören zu einem Tablespaceselect owner,segment_name,segment_type, Blocks, bytes/1000000 MB from dba_segments where tablespace_name='DWH_SPINDEL'

Größenangaben bezogen auf Partitionencolumn segment_name format a25column partition_name format a20column tablespace_name format a20SELECT segment_name, partition_name, segment_type, tablespace_name, extents, blocks, bytes/1024 KB FROM user_segments WHERE segment_name = 'BESTELLUNG_PART_RANGE';SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS BLOCKS KB---- -------------------- ---------- ---------- ----------BESTELLUNG_PART_RANGE APR10 TABLE PARTITION MON 123 53248 425984BESTELLUNG_PART_RANGE APR11 TABLE PARTITION MON 132 62464 499712BESTELLUNG_PART_RANGE AUG10 TABLE PARTITION MON 137 67584 540672

Auflisten des Wachstums einzelner TablespacesCREATE TABLE stats$segment_info ASSELECT SYSDATE snap_time,owner, segment_name,segment_type, tablespace_name, sum(bytes) bytes FROM dba_segmentsGROUP BY SYSDATE, owner, segment_name, segment_type, tablespace_name;

INSERT INTO stats$segment_infoSELECT SYSDATE snap_time, owner, segment_name,segment_type, tablespace_name, sum(bytes) bytes FROM dba_segmentsGROUP BY SYSDATE, owner, segment_name, segment_type, tablespace_name;

column this_mon new_value this_moncolumn last_mon new_value last_moncolumn prev_mon new_value prev_mon

SELECT to_char(SYSDATE,'MON YYYY') this_mon, to_char(add_months(SYSDATE,-1),'MON YYYY') last_mon, to_char(add_months(SYSDATE,-2),'MON YYYY') prev_mon FROM dual;

column mins format 99999

column size_mb heading '&&this_mon' format 99999 column size_mb2 heading '&&prev_mon' format 99999 column size_mb1 heading '&&last_mon' format 99999column growth heading 'Growth(%)| in |last month' format 9999.99SELECT tablespace_name,-- to_char(snap_time, 'MON YYYY') month MAX(decode(trunc(snap_time, 'MON'), trunc(add_months(SYSDATE, -2), 'MON'),size_m,0)) size_mb2, MAX(decode(trunc(snap_time, 'MON'), trunc(add_months(SYSDATE, -1), 'MON'),size_m,0)) size_mb1, MAX(decode(trunc(snap_time, 'MON'), trunc(SYSDATE,'MON'),size_m,0)) size_mb, 100*(MAX(decode(trunc(snap_time, 'MON') ,trunc(SYSDATE, 'MON'), size_m, 0)) - MAX(decode(trunc(snap_time, 'MON'), trunc(add_months(SYSDATE, -1), 'MON'), size_m, 0))) /MAX(decode(trunc(snap_time, 'MON'), trunc(add_months(SYSDATE, -1), 'MON'), size_m, 1)) growthFROM (SELECT tablespace_name, snap_time, sum(bytes)/1024/1024 size_M FROM stats$segment_info WHERE snap_time > trunc(add_months(SYSDATE, -2), 'MON')-2 GROUP BY tablespace_name, snap_time) AGROUP BY tablespace_name;--ORDER BY tablespace_name;

Growth(%) in--TABLESPACE_NAME SIZE_MB2 SIZE_MB1 SIZE_MB last month---- ------------DWH1 0 0 73.3125 7331.25--EXAMPLE 0 0 77.4375 7743.75--PERFSTAT 0 0 96.75 9675.00--SYSAUX 0 0 616.3125 ########--SYSTEM 0 0 694.0625 ########--TEST_ALERT 0 0 3 300.00--UNDOTBS1 0 0 21.25 2125.00--USERS 0 0 3.125 312.50

Segment-InformationenSQL> select distinct segment_type from user_segments;SEGMENT_TYPE------------------TABLE SUBPARTITIONTABLE PARTITIONTABLEINDEX

Gesamtauswertung belegter Plattenplatz und Freespace pro Tablespace und DatafilesSET LINESIZE 145SET PAGESIZE 9999SET VERIFY OFF COLUMN tablespace FORMAT a18 HEADING 'Tablespace Name'COLUMN filename FORMAT a50 HEADING 'Filename'COLUMN filesize FORMAT 999.999,999,999,999 HEADING 'File Size'COLUMN used FORMAT 999.999,999,999,999 HEADING 'Used (in bytes)'COLUMN pct_used FORMAT 999 HEADING 'Pct. Used' BREAK ON reportCOMPUTE SUM OF filesize ON reportCOMPUTE SUM OF used ON reportCOMPUTE AVG OF pct_used ON report 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) pct_usedFROM sys.dba_data_files d , v$datafile v , ( select file_id, SUM(bytes) bytes from sys.dba_free_space GROUP BY file_id) sWHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name)UNIONSELECT 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_usedFROM sys.dba_temp_files d , v$temp_extent_pool t , v$tempfile vWHERE (t.file_id (+)= d.file_id) AND (d.file_id = v.file#)/

Relevante Dictionary ViewsV$TABLESPACE V$TEMPFILEV$DATAFILE DBA_SEGMENTS


Data Warehouse Technik im Fokus - Skripte in Kurzform 29 /44DBA_TABLES V$LOGDBA_FREE_SPACE DBA_TEMP_FILESV$TEMP_EXTENT_POOL V$TEMPFILE

Database Files

Relevante FragestellungenAuffinden der Datafiles in denen die DWH-Tabellen liegen.Ein Data File soll gelöscht werden, man weiss aber nicht, welche Tabellen dort „vergraben“ sind.

Auflistung aller Dateienselect file_name,file_id from dba_data_files;

In welchen Datafiles liegen bestimmte Tabellencolumn segment_name format a20column owner format a10column file_name format a50select e.owner, e.segment_name, e.file_id, d.file_name from dba_extents e, dba_data_files d where e. owner = 'DWH' and e.segment_type = 'TABLE' and e.segment_name = 'BESTELLUNG' and e.file_id = d.file_idorder by e.file_id;

Welche Tabellen liegen in einem bestimmten Data File[die File ID mit obigem Kommando herausfinden und unten eintragen]select e.owner, e.segment_name, e.file_id, d.file_name from dba_extents e, dba_data_files d where e. owner = 'DWH' and e.segment_type = 'TABLE' and d.file_id = 7 and e.file_id = d.file_idorder by e.segment_name;

Single Block / Multi Block ReadsSELECT f.FILE_NAME datei, v.SINGLEBLKRDS, v.PHYRDS - v.SINGLEBLKRDS MultiFROM v$filestat v, dba_data_files fWHERE v.FILE# = f.file_id;

Database Files mit asynchronem Lesen und Schreibencol name format a50SELECT name, asynch_io FROM v$datafile f,v$iostat_file iWHERE f.file# = i.file_noAND filetype_name = 'Data File'/NAME ASYNCH_IO------------------------------------------ ---------D:\ORA\ORADATA\O112\SYSTEM01.DBF ASYNC_OND:\ORA\ORADATA\O112\SYSAUX01.DBF ASYNC_OND:\ORA\ORADATA\O112\UNDOTBS01.DBF ASYNC_OND:\ORA\ORADATA\O112\USERS01.DBF ASYNC_OND:\ORA\ORADATA\O112\EXAMPLE01.DBF ASYNC_ONG:\ORADATA\MON_X.DBF ASYNC_ONG:\ORADATA\MON.DBF ASYNC_ONG:\ORADATA\MON2 ASYNC_ONG:\ORADATA\MON3 ASYNC_ONG:\ORADATA\MON4 ASYNC_ON

Relevante Dictionary Viewsdba_data_files dba_extents

Lesestatistiken / BenutzungsverhaltenGelesene und geschriebene Blöckeselect substr (name, 1, 40) "Datei", phyblkrd "Blk Lesen", phyblkwrt "Blk Schreiben", phyblkrd + phyblkwrt "BlK Gesamt" from v$datafile df, v$filestat fs where df.file# = fs.file# order by phyblkrd + phyblkwrt desc;

Datei Blk Lesen Blk Schreiben BlK Gesamt--------------------------------------------- ------------- ----------F:\ORA_DATA\DWHTIF 3441934 83362 3525296E:\APP\ORADATA\ORCL\SYSAUX01.DBF 51449 45799 97248E:\APP\ORADATA\ORCL\SYSTEM01.DBF 36970 4476 41446E:\APP\ORADATA\ORCL\USERS01.DBF 15206 7886 23092E:\APP\ORADATA\ORCL\DWH_SSD 12577 4955 17532E:\APP\ORADATA\ORCL\UNDOTBS01.DBF 55 17234 17289E:\APP\ORADATA\ORCL\EXAMPLE 3728 2284 6012

F:\ORA_DATA\DWH_SPINDEL 27 9 36E:\APP\ORADATA\ORCL\T1 17 9 26

SecurityBenutzer anlagenCreate user XXX identified by XXX [Default tablespace TS-name];Drop user XXX [CASCADE]

Ein Profil anlegencreate profile pr_develop limit password_reuse_max 10 password_reuse_time 30 sessions_per_user unlimited cpu_per_session unlimited cpu_per_call 3000 connect_time 45 ;

Eine Rolle anlegencreate role DWH_ACCESS;grant select on DWH.F_UMSATZ to DWH_ACCESS;grant select, delete on DWH_ACCESS;grant execute on proc_ABC to DWH_ACCESS;

Rollenrechte weitergebenGRANT DWH_ACCESS to user_name;

Default-Rolle einem Benutzer zuweisenalter user user_name default role DWH_ACCESS;

Rollen für einen Bnutzer aktivieren / deaktivieren set role r01, r02, r03;set role all;set role none;

Was machen die Benutzer geradeselect EXECUTIONS, USERS_EXECUTING, username, sql_text from v$session se , v$sql sq where se.sql_address = sq.address;

Relevante Dictionary ViewsObjektrechte Systemrechteall_tab_privs user_sys_privsuser_tab_privs dba_sys_privsdba_tab_privs session_privsall_col_privsuser_col_privsdba_col_privs

Weitergegebene Rechte Erhaltene Rechteall_tab_privs_made all_tab_privs_recduser_tab_privs_made user_tab_privs_recdall_col_privs_made all_col_privs_recduser_col_privs_made user_col_privs_made

Quotas Useruser_ts_quotas user_usersall_ts_quotas all_usersdba_ts_quotas dba_users

Rollenrole_sys_privsrole_tab_privsrole_role_privs



Laden des DWHRelevante FragestellungenDatenbank-basiertes LadenDatenbank-Techniken für den ETL ProzessMengen-basiertes LadenArbeiten mit und ohne ConstraintsDatenprüfungen mit Native-SQLBewegen großer DatenmengenKomplexe Logik bei hoher Performance

Schnelles Laden / Mengenbasiertes LadenGrunsätzliches Schema für mengenbasiertes LadenInsert into TARGET_TABLE select * from SOURCE_TABLE;

Direct Path LoadEs gibt 4 Möglichkeiten einen Direct Path zu erzeugen:

Insert /*+ APPEND */ into TARGET_TABLE select * from SOURCE_TABLE;

[CTAS]Create table NEW_TABLE as select * from SOURCE_TABLE;

SQL LOADER -> DIRECT=TRUE -> Schlüsselwort.

External Table -> DIRECT=TRUE -> Schlüsselwort.

Insert ohne Log-DateiInsert /*+ NOLOG */ into zieltabelle select .....,(Der Hint wird von der DB akzeptiert, ist aber ohne Wirkung.Soll ohne Log-Datei geladen werden, dann muss man entweder den Direct Path Load nutzen, oder die DB im norarchive-Log-Mode betreiben.)

Sequence-Objekt anlegenCREATE SEQUENCE Kun_seq INCREMENT BY 1 START WITH 1;

Insert into … select Kun_seq.NEXTVAL from dual

Schnelles Schreiben CTAS (Create Table As Select)Create table f_umsatz_tmp as select * from F_Umsatz;

Schnelles Löschen von Daten(Möglichst Einzel-Deletes umgehen)Truncate table f_umsatz_tmp;Drop table f_umsatz_tmp;(Möglichst mit Partition Exchange arbeiten. Dann lassen sich Lösch-Operationen auf Partition-Ebene durchführen und sind schneller)Drop Partition ....

Databas LinkZugriff auf Remote-Oracle-Datenbank (Database Link)CREATE DATABASE LINK "DWH" CONNECT TO "DWH" IDENTIFIED BY "DWH" USING 'ORA10' ;

VerwendungSelect * from SOURCE_TABLEqDWH;

Sequence für Zähl-Felder / Schlüssel aufbauenDefintion eines Sequence-Objektescreate sequence kd_seq increment by 1 start with 1;

Zugriff um den nächsten Zählerwert abzugreifenselect seq_kd.nextval from dual;

Abfragen des aktuellen Standsselect LAST_NUMBER from user_sequences where sequence_name = 'SEQ_KD'

Relevante Dictionary Viewsuser_sequences

TriggerEinsatz in den Source- (OLTP) – Systemen zum erkennen von Delta-Daten. Kann die Quell-Systeme belasten.

Beispiel für einen Trigger (Insert,Delete,Update)CREATE OR REPLACE TRIGGER Bestellung BEFORE DELETE OR INSERT OR UPDATE ON Bestellung FOR EACH ROWWHEN (new.Bestellnr > 0)DECLARE sal_diff number;BEGIN INSERT INTO log_Bestellung

(Alte_Bestell_Nr,Neue_Bstell_Nr)

VALUES(old.Bestellnr,new.Bestellnr); END;

Text-Dateien ImportierenSQL Loadersqlldr userid=DWH/DWH control=c:\orte.ctl log=C:\orte.log

Beispiel für Loader Control – File

-> orte.ctl ->

OPTIONS (SKIP=1, BINDSIZE=50000, ERRORS=50, ROWS=200, DIRECT=TRUE, PARALLEL=TRUE, READSIZE=65536, RESUMABLE=TRUE,RESUMABLE_TIMEOUT=7200)UNRECOVERABLE LOAD DATACHARACTERSET WE8MSWIN1252INFILE '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 Table BeispielCREATE TABLE Gemeinde_EX ( Gemeinde_Nr VARCHAR2(8), Gemeinde VARCHAR2(50) )ORGANIZATION EXTERNAL(TYPE oracle_loaderDEFAULT DIRECTORY ExttabACCESS PARAMETERS (RECORDS DELIMITED BY newline BADFILE 'Gemeinde.bad‚ DISCARDFILE 'Gemeinde.dis‚ LOGFILE 'Gemeinde.log‚ SKIP 20 FIELDS TERMINATED BY ; OPTIONALLY ENCLOSED BY '‚ )LOCATION ('Gemeinde_CSV.TXT'))

Directory-Objekte Directory anlegenCREATE DIRECTORY Exttab AS 'D:\Szenario\Exttab';

Auflisten bestehender Directory-Definitionenselect substr(owner,1,15), substr(DIRECTORY_NAME,1,20),substr(DIRECTORY_PATH,1,30) from dba_directories;

Logischen Directory-Name ändernalter table ex_orte default directory LC_Texte_2;

Name der Daten-(CSV-)Datei ändernalter table ex_orte location ('ORTE_Y.CSV');

DatapumpDatapump - Beispiel mit Steuerdateiexpdp dwh/dwh@o11 parfile=Para_EX.txt-> Datei Para_EX kann die Parameter haben:

SCHEMAS=(DWH)INCLUDE=TABLE IN ('BESTELLUNG')QUERY=BESTELLUNG:WHERE BESTELLDATUM > to_date('01.08.06','DD.MM.YY')DIRECTORY=DP_OUTDUMPFILE=EXP1.dmpLOGFILE=DP_OUT:EXP_LOG.dmp

impdp dwh2/dwh2@o11 DIRECTORY=DP_OUTDUMPFILE=EXP1.DMP LOGFILE=DP_OUT:imp_log REMAP_SCHEMA=DWH:DWH2

Allgemeines BeispielExpdp hf TABLES=employees,jobs DUMPFILE=dpump_dir1:Table.dmp NOLOGFILE=y

Umändern des Default-Output-DirectoriesCREATE OR REPLACE DIRECTORY expimp_dir AS 'H:\';

Danach Angabe des so definierten DIRECTORIE über den DIRECTORY-Parameter in dem EXPDP – Aufruf.

Datapump-Schätzung benötigter PlattenplatzExpdp hr DIRECTORY=dpump_dir1 ESTIMATE_ONLY=y TABLES=employees, departments, locations LOGFILE=estimate.log


Data Warehouse Technik im Fokus - Skripte in Kurzform 31 /44Datapump Data only ImportImpdp hr TABLES=employees CONTENT=DATA_ONLY DUMPFILE= dpump_dir1:Table.dmp NOLOGFILE=y

Datapump Schema ModeImpdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX TABLE_EXISTS_ACTION=REPLACE

Datapump Network ModeImpdp hr TABLES=employees REMAP_SCHEMA=hr:scott DIRECTORY=dpump_dir1 NETWORK_LINK=dblink

Interaktiver Modus mit CTRL C aktivierbarBefehl Beschreibung-------------------------------------------------------------ADD_FILE Fügt Dump-Datei zu Dump-Dateigruppe hinzu.CONTINUE_CLIENT Kehrt in den Logging-Modus zurück. Job wird neu gestartet, wenn frei.EXIT_CLIENT Beendet Client-Session, Job wird weiter ausgeführt.FILESIZE Standarddateigröße (Byte) für nachfolgende ADD_FILE-Befehle.HELP Fasst interaktive Befehle zusammen.KILL_JOB Hebt Zuordnung von Job auf und löscht Job.PARALLEL Ändert die Anzahl von aktiven Workern für den aktuellen Job. PARALLEL=<number of workers>.REUSE_DUMPFILES überschreibt Ziel-Dump-Datei sofern vorhanden (N).START_JOB Startet/nimmt aktuellen Job wieder auf.STATUS Gibt an, wie oft (in Sekunden) der Job-Status überwacht werden soll, wobei der Standardwert (0) den neuen Status anzeigt, wenn verfügbar. STATUS[=interval]STOP_JOB Fährt die Job-Ausführung ordnungsgemäß herunter und beendet den Client. STOP_JOB=IMMEDIATE nimmt ein sofortiges Herunterfahren des Data Pump Jobs vor.

Datapump und External Tables-- In Quellumgebung:CREATE TABLE ex_bestellungORGANIZATION EXTERNAL(TYPE ORACLE_DATAPUMPDEFAULT DIRECTORY dp_outLOCATION ('best.dmp'))AS SELECT * FROM bestellung;-- In Zielumgebung CREATE TABLE ex_bestellung_2(BESTELLNR NUMBER,KUNDENCODE NUMBER,BESTELLDATUM DATE,LIEFERDATUM DATE, BESTELL_TOTAL NUMBER(12,2),ORDER_ID NUMBER)ORGANIZATION EXTERNAL(TYPE ORACLE_DATAPUMPDEFAULT DIRECTORY DP_OUTLOCATION ('best.dmp'))REJECT LIMIT UNLIMITED;-- In Zielumgebung

CREATE TABLE ex_bestellung_3ORGANIZATION EXTERNAL(TYPE ORACLE_DATAPUMPDEFAULT DIRECTORY DP_OUTLOCATION ('best.dmp'))REJECT LIMIT UNLIMITED;

Transportable TablespaceTransportable Tablespace-Verfahren--- 1. Erstellen TablespaceCREATE TABLESPACE ts_bestellung DATAFILE 'D:\oracle\oradata\dworcl\TS_BEST.tbs' SIZE 100M REUSEAUTOEXTEND ON NEXT 10M;--- 2. Zuweisen von tabelendaten zum TablespaceCREATE TABLE ts_best TABLESPACE ts_bestellung AS SELECT * FROM bestellung ;--- 3. Setzen des Tablespace auf Read Only ALTER TABLESPACE ts_bestellung READ ONLY;--- 4. Export Metadaten des Tablespace CREATE OR REPLACE DIRECTORY ts_dir AS 'D:\Workshop\TS_DIR';expdp sys/****@orcldw TRANSPORT_TABLESPACES=ts_bestellung DIRECTORY=ts_dir DUMPFILE=ts_best_file--- 5. Konvertieren der Metadaten RMAN:CONVERT TABLESPACE ts_bestellung TO PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT '...... ;

---- 6. Kopieren der TS-Datei und der Metadaten

---- 7. Import impdp system/****@orcldw TRANSPORT_TABLESPACES=ts_bestellung DIRECTORY=ts_dir DUMPFILE=ts_best_file

Error LoggingError LoggingEine Tabelle Errorlog-fähig machenBEGIN DBMS_ERRLOG.CREATE_ERROR_LOG( dml_table_name => 'EL_KUNDE', -- required err_log_table_name => 'EL_KUNDE_ERRORS' --optional );END;/Es entstehen zusätzliche Spalten, die das System bei Constraint-Verletzungen pflegt:SQL> desc el_kunde_errors Name Null? Typ ----------------------- -------- -------------------- ORA_ERR_NUMBER$ NUMBER ORA_ERR_MESG$ VARCHAR2(2000) ORA_ERR_ROWID$ ROWID ORA_ERR_OPTYP$ VARCHAR2(2) ORA_ERR_TAG$ VARCHAR2(2000) KUNDENNR VARCHAR2(4000) VORNAME VARCHAR2(4000) NACHNAME VARCHAR2(4000) ORTNR VARCHAR2(4000) STRASSE VARCHAR2(4000) TELEFON VARCHAR2(4000)

Eindeutigkeitsprüfung ohne DML_Error_Logging-- Quelltabelle mit 1000003 Millionen Sätzen davon 3 mit -- doppelter Kundennr aufbauen:drop table kd_xx;

create table kd_xx as select to_number(substr(level,1,10)) kundennr,'a' Vorname,'a' nachname,1 ortnr,'a' Str,'q' Tel from dual connect by level < 1000001;

-- nicht eindeutige Sätze einfügeninsert into kd_XX (kundennr) values(74);insert into kd_XX (kundennr) values(784);insert into kd_XX (kundennr) values(7874);

-- Nicht eindeutige Werte heraussuchenDrop table non_uni/ Create table non_uni as select distinct kundennr from (select count(*) c, kundennr from kd_xx group by kundennr) where c > 1/

-- Nur die Sätze in eine weitere Zieltabelle überführen, die eindeutig sindinsert into el_kunde select * from kd_xx where kundennr not in (select * from non_uni);

Regular ExpressionsRegular Expression in Constraints verwendenALTER TABLE check_kunde ADD CONSTRAINT Ch_KD_Name CHECK(REGEXP_LIKE(NACHNAME, '[^[:digit:]]'));

Umgang mit einzelnen Zeichen:* Match 0 or more times ? Match 0 or 1 time + Match 1 or more times {m} Match exactly m times {m,} Match at least m times {m, n} Match at least m times but no more than n times \n Cause the previous expression to be repeated n times

Bestehende Zeichengruppen[:alnum:] Alphanumeric characters [:alpha:] Alphabetic characters [:blank:] Blank Space Characters [:cntrl:] Control characters (nonprinting) [:digit:] Numeric digits [:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars [:lower:] Lowercase alphabetic characters [:print:] Printable characters [:punct:] Punctuation characters [:space:] Space characters (nonprinting), such as carriage return, newline,

vertical tab, and form feed [:upper:] Uppercase alphabetic characters [:xdigit:] Hexidecimal characters



Arbeiten ohne Constraints und mengenbasiertes SQL / PrüfenGemeint ist das Prüfen von zu ladenden Daten ohne Datenabnk-Constraints, um die Performance zu erhöhen. Prüfungen lassen sich nach Typen kategorisieren (Siehe Graphik unten). Für die einzelnen Lösungen sind unten exemplarische Beispiele mit mengenbasiertem SQL aufgeführt.

Aus- und Einschalten aller Constraintsspool test.sqlselect 'alter table '||table_name||' disable constraint'||constraint_name||';' from user_constraints where table_name=('TABELLENNAME');spool off

Prüfungen Kategorie A: Feld/Column-bezogene Prüfungen.CASE in SQL-Statements / Manuelles PrüfenSELECT CASE WHEN isnumeric('999') = 1 THEN 'numerisch' ELSE 'nicht numerisch' END ErgebnisFROM dual;

IsNumeric-PrüfungCREATE OR REPLACE FUNCTION isnumeric ( p_string in varchar2) return boolean AS l_number number; BEGIN l_number := p_string; RETURN TRU; EXCEPTION WHEN others THEN RETURN FALSE; END;

--CREATE OR REPLACE FUNCTION isnumeric ( p_string in varchar2) return number AS l_number number; BEGIN l_number := p_string; RETURN 0; EXCEPTION WHEN others THEN RETURN 1; END;

IsDate-Prüfungcreate or replace function IsDate (str varchar2) return varchar2 is inDate varchar2(40); FUNCTION dateCheck (inputDate varchar2, inputMask varchar2) RETURN varchar2IS dateVar date;BEGIN dateVar:= to_date(inputDate,inputMask); return 'true'; exception when others then return 'false';END; BEGIN inDate:= trim(str); if dateCheck(inDate, 'mm-dd-yyyy') = 'false' AND dateCheck(inDate, 'mm-dd-yy') = 'false' AND dateCheck(inDate, 'yyyy-mm-dd') = 'false' AND dateCheck(inDate, 'yy-mm-dd') = 'false' AND dateCheck(inDate, 'yyyy-mon-dd') = 'false‚ AND dateCheck(inDate, 'yy-mon-dd') = 'false‚ AND dateCheck(inDate, 'dd-mon-yyyy') = 'false‚ AND dateCheck(inDate, 'dd-mon-yy') = 'false‚ AND dateCheck(inDate, 'mmddyy') = 'false‚ AND dateCheck(inDate, 'mmddyyyy') = 'false‚ AND dateCheck(inDate, 'yyyymmdd') = 'false' AND dateCheck(inDate, 'yymmdd') = 'false‚ AND dateCheck(inDate, 'yymmdd') = 'false' AND dateCheck(inDate, 'yymondd') = 'false‚ AND dateCheck(inDate, 'yyyymondd') = 'false‚ AND dateCheck(inDate, 'mm/dd/yyyy') = 'false' AND dateCheck(inDate, 'yyyy/mm/dd') = 'false‚

AND dateCheck(inDate, 'mm/dd/yy') = 'false' AND dateCheck(inDate, 'yy/mm/dd') = 'false‚ AND dateCheck(inDate, 'mm.dd.yyyy') = 'false' AND dateCheck(inDate, 'mm.dd.yy') = 'false' AND dateCheck(inDate, 'yyyy.mm.dd') = 'false' AND dateCheck(inDate, 'yy.mm.dd') = 'false' then return 'false'; else return 'true'; end if; --exception --when others then return 'false';END;

Prüfungen Kategorie B: Satz-bezogene Prüfungen.Abhängigkeiten von anderen Feldern im selben Satz

Beispieltabelle:F1 F2 F3 F43 7 3 49 5 1 4

select CASE WHEN (F1 = 3 and F2 = F3 + F4) then 1 ELSE 0 endfrom fx

Satzübergfreifendes Zusammenzählen von Feldwerten (analytische Funktion)

select Artikelname, Artikelgruppe, Wert, sum(wert) over (partition by Artikelname) Artikelgesamtwert, sum(wert) over (partition by Artikelgruppe) Gruppengesamtwert, case when (round(((sum(wert) over (partition by Artikelname))/(sum(wert) over (partition by

Artikelgruppe))*100),0) ) < 20 then 'ROT' ELSE 'GRUEN' end Prozent from Artikel

Prüfungen Kategorie C: Satz-übergreifende Prüfungen.Eindeutigkeitsprüfungen

Beispieltabelle Prüfregel:

Eindeutigkeit von BESTELLNR ist zu prüfen

insert /*+ APPEND */ into err_non_unique_bestellung select bestellnr from (select count(bestellnr) n, bestellnr from bestellung group by bestellnr) where n > 1;

Aggregatbildung und Bewerten von satzübergreifenden Summen


Artikel ------------------ ARTIKELNAME ARTIKELGRUPPE WERT

Wenn der Umsatz pro Gruppe unter 20% des Gesamtwertes fällt, dann ROT

select Artikelname, Artikelgruppe, Wert, sum(wert) over (partition by Artikelname) Artikelgesamtwert, sum(wert) over (partition by Artikelgruppe) Gruppengesamtwert, case when (round(((sum(wert) over (partition by Artikelname))/(sum(wert) over (partition by

Artikelgruppe))*100),0) ) < 20 then 'ROT' ELSE 'GRUEN' end Prozent


Data Warehouse Technik im Fokus - Skripte in Kurzform 33 /44from Artikel

Prüfungen Kategorie D: Tabellen-übergreifende Prüfungen.Foreign Key Prüfung


Es darf keine Bestellung ohne zugehörigen Kunde geben.

insert /*+ APPEND */ into err_orphan_Bestellung select bestellnr

from bestellung where

Kundennr not in (select Kundennr from kunde);

Prüfungen Kategorie E: Zeit- / Zusammenhangbezogene Prüfungen.

Prüfungen Kategorie F: Verteilungen + Mengen - Prüfungen.(Hängen von der jeweligen fachlichen Prüfung ab. Sie sind analog zu den Prüfungen wie zuvor beschrieben durchzuführen. Eventuell sogar mit einer Table-Function. Siehe dort)

SQL-Mittel beim LadenPivot/Unpivot BeispieleWITH pivot_data AS ( SELECT Artikelnr,Bestellmenge FROM best_pos)SELECT * FROM pivot_dataPIVOT( SUM(Bestellmenge) FOR Artikelnr IN (1,2,3,4,5,6));

Ergebnis:1 2 3 4 5 6---------- ---------- ---------- ---------- ---------- 3398 2948 2972 2702 2900 2752

select * from sales_by_quarter pivot (sum(revenue) for quarter

in ('Q1','Q2','Q3','Q4')) order by salesrep ;

select * from quarterly_sales

unpivot include nulls (revenue for quarter in (q1,q2,q3,q4)) order by salesrep, quarter ;

Multiple Inserts /Manuelles Aufspalten von korrekten und nicht korrekten Sätzen

1. Schritt: Füllen einer temp. TabelleINSERT INTO EL_KUNDE_TMP (KUNDENNR, VORNAME, NACHNAME, ORTNR, STRASSE, TELEFON, KUNDENNR_IS_NUMERIC)(SELECT NUMMER, NAME, NAME, NUMMER, NAME, NUMMER, CASE isnumeric(NUMMER) WHEN 1 THEN 1 ELSE 0 END FROM SRC1);

2. Schritt: Auswerten Prüffeld mit Mult. InsertsINSERT ALL WHEN KUNDENNR_IS_NUMERIC = 1 THEN INTO EL_KUNDE (KUNDENNR, VORNAME, NACHNAME, ORTNR, STRASSE, TELEFON)

VALUES (KUNDENNR, VORNAME, NACHNAME, ORTNR, STRASSE, TELEFON)

WHEN KUNDENNR_IS_NUMERIC = 0 THEN INTO EL_KUNDE_FEHLER (KUNDENNR, VORNAME, NACHNAME, ORTNR, STRASSE, TELEFON, KUNDENNR_IS_NUMERIC) VALUES (KUNDENNR, VORNAME, NACHNAME, ORTNR, STRASSE, TELEFON, KUNDENNR_IS_NUMERIC) (SELECT KUNDENNR, VORNAME, NACHNAME, ORTNR, STRASSE, TELEFON, KUNDENNR_IS_NUMERIC FROM EL_KUNDE_TMP);

Merge-BeispielMERGE 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 TELEFAXFROM KUNDEN_STAMM KUNDEN_STAMM) MERGE_SUBQUERYON ( 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;

Flashback

Relevante FragestellungenAbsichern von Ladeläufen und potentielles Zurückrollen von fehlerhaft geladenen Sätzen ermöglichen.Flashback auf die gesamte Datenbank oder auf einzelne Tabellen.Absichern von Direct-Path-Loads

Log-Modus / Archiv-Modus prüfenSELECT flashback_on, log_mode FROM gv$database;

Aktuelle SCN abfragenSELECT current_scn FROM gv$database;

Retention-Zeit abfragenSELECT name, value FROM gv$parameter WHERE name LIKE '%flashback%';

Ändern Retention-ZeitALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880;(Anzahl der Minuten 60 * 24 = 1440 = 1 Tag)

Abfragen der letzten LogsSELECT * from gv$flashback_database_log;

SELECT oldest_flashback_scn, oldest_flashback_time FROM gv$flashback_database_log;

Desc gv$flashback_database_logINST_ID NUMBER OLDEST_FLASHBACK_SCN NUMBER OLDEST_FLASHBACK_TIME DATE RETENTION_TARGET NUMBER FLASHBACK_SIZE NUMBER ESTIMATED_FLASHBACK_SIZE

Zurücksetzen einer Tabelle auf ältere SCN mit FlashbackFLASHBACK TABLE X TO SCN 16552768;

startup mount exclusive;FLASHBACK TABLE dwh.X TO SCN 16552768;

Beispiel FlasbackCREATE TABLE x (Nummer number);ALTER TABLE x ENABLE ROW MOVEMENT; INSERT INTO X VALUES (1); / Commit;


BESTELLUNGBESTELLNRORTNR KUNDENNR BESTELLDATUMANZAHLPOS

KUNDEKUNDENNRKUNDENNAME BERUFSGRUPPE SEGMENTKUNDENTYP

PKPK FK

Data Warehouse Technik im Fokus - Skripte in Kurzform 34 /44SELECT ora_rowscn FROM x; à ORA_ROWSCN: 25489616INSERT INTO X VALUES (1);INSERT INTO X VALUES (1);INSERT INTO X VALUES (1);INSERT INTO X VALUES (1); / Commit;SELECT ora_rowscn FROM x;

ORA_ROWSCN---------- 25489743 25489743 25489743 25489743 25489743select count(*) from x; -> 5

Flashback table x to scn 25489616;select count(*) from x; -> 1

Relevante Dictionary Viewsgv$flashback_database_loggv$parametergv$database

Table Function

Relevante FragestellungenIn dem mengenbasierte Modus von SQL bleiben und dennoch komplexere PL/SQL-Lösungen erstellen.

Table Functions gibt es in 2 Varianten:- Pipe-Variante (Rücklieferung einzelner Sätze)- Object-Set (Rücklieferung kompletter Tabellen)-------------------------------------------------------------

Record / Object - DefinitionDefinition einer Record-Struktur: Bei der späteren Übergabe der Daten von der Table-Funktion zu dem aufrufenden Befehl wird diese Feld-Struktur zu Grunde gelegt.

DROP TYPE bestellung_x_t;CREATE TYPE bestellung_x_t as OBJECT (bestellnr NUMBER(10),kundencode NUMBER(10),bestelldatum DATE,bestell_total NUMBER(12,2));

Definition einer Tabellen-Struktur-- Es wird die zuvor festgelegte Record-Struktur genutzt-- In der FROM-Klausel des aufrufenden SELECT-Statements-- wird ein Tabellen-Objekt verwendet. Dieses wird hier -- festgelegt.

DROP TYPE bestellung_x_t_table;CREATE TYPE bestellung_x_t_table as TABLE of bestellung_x_t;-------------------------------------------------------------

Table-Function-DefinitionDie Definition der Table-Function(in diesem Beispiel werden sehr einfache Aktinen in der Table Function gemacht, um das Prinzip aufzuzeigen)

CREATE OR REPLACE FUNCTION f_bestellung_y RETURN bestellung_x_t_table PIPELINED IS bestellnr NUMBER(10); kundencode NUMBER(10); bestelldatum DATE; bestell_total NUMBER(12,2); i number(10);max_loops number := 1000000;

BEGINi := 0;LOOP i := i+1; exit when i > max_loops;

bestellnr := 1; kundencode :=12; bestelldatum := sysdate ; bestell_total := 10 ;

PIPE ROW (bestellung_x_t(bestellnr,kundencode,bestelldatum, bestell_total));END LOOP; RETURN ;END;/---- BeispielaufrufeSELECT * FROM TABLE(f_bestellung_y());

SQL / PL/SQL - FunktionenLeeres PL/SQL-Function-Template

CREATE OR REPLACE FUNCTION "FAKULTAET"("ZAHL" IN NUMBER) RETURN NUMBER IS --initialize variables here

-- main bodyBEGIN NULL; -- allow compilation

RETURN NULL; EXCEPTION WHEN OTHERS THEN NULL; -- enter any exception code here RETURN NULL;END;

Numerische Funktionenabs(a) absoluter Wert von a ceil(a) kleinste ganze Zahl größer als a floor(a) größte ganze Zahl kleiner als a mod(m,n) m Modulo n (Rest von m geteilt durch n) power(m,n) m hoch n round(n[,m]) n auf m Stellen gerundet sign(a) Vorzeichen von a (0, 1 oder -1) sin(a) Sinus von a (weitere trigonometrische Funktionen verfügbar) sqrt(a) Wurzel aus a trunc(a[,m]) a auf m Stellen abgeschnitten exp(n) liefert e hoch n (e=2,17828...) ln(n) natürlicher Algorithmus von zu e log(m,n) Logarithmus von n zu Basis m

Beispieleselect round (21.76) from dual;Ergebnis: 22

select trunc (21.76) from dual;Ergebnis: 21

select trunc (21.76, -1) from dual;Ergebnis: 20

Der zweite Parameter bei round und trunc gibt die Position der Stelle an, an der gerundet bzw. abgeschnitten werden soll. Positive Zahlen bedeuten Stellen nach dem Komma, negative vor dem Komma. Gibt man den Wert nicht an greift der Defaultwert 0, der ganzzahlig rundet bzw. abschneidet.

Stringfunktionenconcat(s1, s2) s1 und s2 konkateniert (entspricht s1||s2) lower(s) s in Kleinbuchstaben

upper(s) s in Großbuchstaben initcap(s) erstes Zeichen eines Wortes groß, Rest klein

lpad(s1, n [,s2]) s1 auf n Zeichen mit s2 von links aufgefüllt (Defaultwert für s2 ist ein Blank) rpad(s1, n [,s2]) s1 auf n Zeichen mit s2 von rechts aufgefüllt (Defaultwert für s2 ist ein Blank) ltrim(s1[,s2]) alle führenden Zeichen aus s2 in s1 entfernen (Defaultwert für s2 ist ein Blank) rtrim(s1[,s2]) alle endenden Zeichen aus s2 in s1 entfernen (Defaultwert für s2 ist ein Blank) trim(s1) Kombination aus ltrim und rtrim (ab 8i) replace(s1,s2[,s3]) suche s2 in s1 und ersetze ihn durch s3 bzw. NULL translate(s1,s2,s3) in s1 werden alle Zeichen aus s2 durch solche aus s3 ersetzt substr(s,m[,n]) Teilstring von s ab Stelle m, n Zeichen lang (n nicht angegeben => bis Stringende) instr(s1, s2[,n[,m]]) suche s2 in s1 und zwar ab der n-ten Stelle das m-te Auftreten (Defaultwerte für n und m sind 1), Ergebnis ist die gefundene Position in s1 oder 0 length(s) die Länge von s

BeispieleTeilstring bestimmensubstr('TAKTUM Informatik', 1, 6)Ergebnis: 'TAKTUM'

Sucheninstr('TAKTUM Informatik', 'Info')Ergebnis: 8

Zeichen erzeugenchr(65)Ergebnis: 'A'

Auf diese Weise können auch nicht druckbare Sonderzeichen in einen String eingefügt werden. Allerdings muss dann der Zeichensatz der Datenbank bekannt sein.

Zeichenkodierung erzeugenascii('A')Ergebnis: 65

Teilstring erseztenreplace('SCHADE', 'D', 'LK')Ergebnis: 'SCHALKE'

Zeichen austauschentranslate('ABC67LR5', '0123456789','**********')Ergebnis: 'ABC**LR*'

translate('ABC67LR5', '*0123456789','*')Ergebnis: 'ABCLR'


Data Warehouse Technik im Fokus - Skripte in Kurzform 35 /44Auf diese Weise lassen sich unerwünschte Zeichen elegant aus einer Zeichenkette entfernen.

Datum-Funktionenadd_months(d,n) Datum d plus n Monate last_day(d) Datum des letzten Tages des Monats, in dem d enthalten ist months_between(d1, d2) Anzahl der Monate zwischen d1 und d2 round(d[,fmt]) Datum d gerundet je nach Format (Defaultwert für fmt ist 'dd' (Tag)) sysdate aktuelles Datum und Uhrzeit trunc(d[,fmt]) Datum d abgeschnitten je nach Format (Defaultwert für fmt ist 'dd' (Tag)) Beispielesysdate+1Ergebnis: morgen um die gleiche Zeit

round(sysdate)Ergebnis: heute um 00:00:00 Uhr

last_day(to_date('10.12.2002', 'dd.mm.yyyy'))Ergebnis: 31.12.2002 00:00:00months_between(to_date('25.12.2002', 'dd.mm.yyyy'), to_date('10.11.2002', 'dd.mm.yyyy'))Ergebnis: 1,48387097

months_between(to_date('25.12.2002', 'dd.mm.yyyy'), to_date('25.11.2002', 'dd.mm.yyyy'))Ergebnis: 1

Als Basis zur Berechnung werden immer 31 Tage je Monat zugrunde gelegt.

Bedingte Abfragen (Decode / CASE)decode if then else greatest(e1[,e2] ...) größter Wert der Ausdrücke least(e1[,e2] ...) kleinster Wert der Ausdrücke nvl(e1, e2) ist e1 NULL dann e2 sonst e1 nvl2(e1, e2, e3) ist e1 NULL dann e3 sonst e2 (ab 8i) user aktueller Datenbankbenutzername userenv(s) Informationen zur Benutzerumgebung dump(e) interne Kodierung von e vsize(e) benötigter Speicherplatz in Bytes Beispieledecode (status,'A','Angelegt','E','Erledigt','S','Storniert','Unbekannt')

Je Nach Status werden unterschiedliche Zeichenketten zurück geliefert. Z.b. Bei 'E' 'Erledigt'. Ist der Status nicht 'A', 'E' oder 'S' liefert decode 'Unbekannt'.

Mit decode lassen sich Berechnungen durchführen, die sonst nur mittels Programmierung realisierbar wären. Typische Anwendungen sind Kategorisierungen.Die Decode-Funktion wird heute überwiegend durch CASE ersetzt

SELECT CASE WHEN (<column_value>= <value>) THEN WHEN (<column_value> = <value>) THEN ELSE <value>FROM <table_name>;

Sonstige Funktionennvl(artikel_nr, 999999)Ergebnis: 999999 wenn die Artikelnummer nicht gefüllt ist sonst die Artikelnummergreatest(4, 7, 1)Ergebnis: 7

vsize(sysdate) Ergebnis: 8

Konvertierungsfunktionento_char(a[,fmt]) Umwandlung der Zahl in eine Zeichenkette je nach Format fmt. to_char(d[,fmt]) Umwandlung des Datums d in eine Zeichenkette je nach Format fmt. to_date(s[,fmt]) Umwandlung der Zeichenkette s in ein Datum to_number(s[,fmt]) Umwandlung der Zeichenkette s in eine Zahl hextoraw(s) Umwandlung einer Zeichenkette s in Binärdaten rawtohex(b) Umwandlung von Binärdaten b in eine Zeichenkette mit entsprechenden Hex-Ziffern

Beispieleinsert into druckersteuerung (befehl, code) values ('6 Zeilen/Zoll', hextoraw('1B266C3644'));

to_char(23012.9, '000G000D00')Ergebnis: '023.012,90'

to_char(to_date('24.12.2002','dd.mm.yyyy'),'hh24:mi:ss')Ergebnis: '00:00:00'

Konvertierung von Datum in Zeichenkette (to_char) und umgekehrt (to_date)Datum in Zeichenkette: to_char (datum, format) Zeichenkette in Datum: to_date (zeichenkette, format) Wichtige FormatzeichenDD Tag des Monats (1 - 31) DAY Name des Tages ('MONTAG' bis 'SONNTAG') day Name des Tages ('montag' bis 'sonntag') Day Name des Tages ('Montag' bis 'Sonntag') MM Monat des Jahres ( 1 - 12)

MON Monatsname dreistellig ('JAN' bis 'DEZ') mon Monatsname dreistellig ('jan' bis 'dez') Mon Monatsname dreistellig ('Jan' bis 'Dez') MONTH Monatsname ('JANUAR' bis 'DEZEMBER') month Monatsname ('januar' bis 'dezember') Month Monatsname ('Januar' bis 'Dezember') YY Jahr zweistellig (00 bis 99) YYYY Jahr vierstellig HH24 Uhrzeit: Stunde (0 - 24) MI Uhrzeit: Minute (0-60) SS Uhrzeit: Sekunde (0-60) IW Kalenderwoche nach ISO Q Quartal (1, 2, 3, 4) - / , . ; : . Formatierungszeichen beliebiger Text Beispieleselect to_char (datum, 'dd.mm.yyyy hh24:mi:ss') Datum from auftrag;

select to_char (to_date ('10.08.1999', 'dd.mm.yyyy'), 'dd.mm.yyyy hh24:mi:ss') from dual;

Aufgabe: Bestimme die Anzahl der Aufträge vom 21. September 1999:

Lösung 1:select *from auftragwhere datum = to_date ('21.09.1999', 'dd.mm.yyyy');

=> Problem: Was ist mit der Uhrzeit?

Lösung 2:select *from auftragwhere to_char (datum, 'dd.mm.yyyy') = '21.09.1999';

oder

select *from auftragwhere trunc(datum) = to_date ('21.09.1999', 'dd.mm.yyyy');

Jeder Wert vom Datentyp date ist sekundengenau! Wird bei der Umwandlung mit to_date die Uhrzeit nicht angegeben, so erhält der umgewandelte Wert die Uhrzeit 0 Uhr 0 Minuten 0 Sekunden.Beispieleto_date ('10.08.1999', 'dd.mm.yyyy')Ergebnis: Uhrzeit 0 Uhr 0 Min. 0 Sek

to_date ('10.08.1999 13', 'dd.mm.yyyy hh24')Ergebnis: Uhrzeit: 13 Uhr 0 Min. 0 Sek

Zeitformat-Umwandlung-- to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS')'YYYY-MM-DD-HH24:MI:SS'

Select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual; à 30-08-2011 15:53:52Select to_char(sysdate,'DD.MM.YYYY HH24:MI:SS') from dual; -> 30.08.2011 15:54:26Select to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS') from dual; -> 30-Aug-2011 15:54:39

Konvertierung von Zahlen in Zeichenketten (to_char) und umgekehrt (to_number)Zahl in Zeichenketten: to_char (zahl, format) Zeichenkette in Zahl: to_number (zeichenkette, format) Wichtige Formatzeichen9 Zahl 0 bis 9 ohne führende Null 0 Zahl 0 bis 9 mit führender Null S Vorzeichen + oder - D Dezimalpunkt oder Komma G Tausenderpunkt oder Komma FM Abschneiden von führenden Blanks Beispieleselect to_char (anzahl * preis, '999G990D00') Umsatzfrom auftrag_poswhere auftrag_nr = 1;

select preis * to_number ('1,8', '9D9') from auftrag_poswhere auftrag_nr = 1;



Verwalten des Systems / SystembeobachtungRelevante FragestellungenSystemzustände abfragenDatenmengen abfragenBenutzerzugriffe monitorenPerformance messen

Anzeigen der gesetzten Schwellwerte für Alertsselect metrics_name, warning_value, critical_value, object_type, object_name, status from dba_thresholds

Alerts abfragenSQL> select reason from dba_outstanding_alerts;

REASON------------------------------------------------------Tablespace [TEST_ALERT] is [83 percent] fullTablespace [PERF] is [93 percent] fullTablespace [PART] is [87 percent] full

Alerts abfragen (historisch)column OBJECT_NAMe format a50column REASON format a50column SUGGESTED_ACTION format a50

select CREATION_TIME,reason, OBJECT_NAME, suggested_action from dba_alert_history;

Alert-Datei-AblageShow Parameter background_dump_dest

Wait Classes abfragenselect wait_class#, wait_class, count(*) from v$event_name group by wait_class#, wait_class order by wait_class# / 0 Other 717 1 Application 17 2 Configuration 24 3 Administrative 54 4 Concurrency 32 5 Commit 2 6 Idle 94 7 Network 35 8 User I/O 45 9 System I/O 3010 Scheduler 7 11 Cluster 5012 Queueing 9

Wait Eventsselect event, wait_class, total_waits,total_waits_fg tw, TOTAL_TIMEOUTS_FG tt,TOTAL_TIMEOUTS_FG from v$system_event where wait_class != 'Idle' and TIME_WAITED_FG >= 1000;

Menge der UndosSELECT to_char(end_time,'DD-MM-YYYY HH24:MI:SS') , to_char(begin_time,'DD-MM-YYYY HH24:MI:SS') , undoblks FROM v$undostat;

Informationen über die SessionSortvorgänge auf Platte oder im Speichercolumn name format a30 SELECT a.sid,a.value,b.name,c.username from V$SESSTAT a, V$STATNAME b, v$session c WHERE a.statistic#=b.statistic# AND b.name LIKE 'sorts %'

and a.sid = c.sid ORDER BY 1; SID VALUE NAME USERNAME---------- ---------- --------------------------------------- 67 12 sorts (memory) MON 67 64 sorts (rows) MON 67 0 sorts (disk) MON 125 0 sorts (memory) 125 0 sorts (disk) 125 0 sorts (rows) 126 0 sorts (disk) 126 91 sorts (rows) 126 10 sorts (memory) 127 0 sorts (memory) 127 0 sorts (disk)

Session-Informationencolumn username format a15column Machine format a30column username format a10column SCHEMA format a10column machine format a18

column sid format 9999column program format a15column process format a10column action format a20

SELECT instance_name, SID, -- NUMBER SERIAL#, -- NUMBER USER#, -- NUMBER USERNAME, -- VARCHAR2(30) SCHEMANAME as schema, -- VARCHAR2(30) substr(machine,1,18) as machine, terminal, substr(PROGRAM,1,10) as program, -- VARCHAR2(48) COMMAND, -- NUMBER upper(decode(nvl(COMMAND, 0), 0, '---------------', 1, 'Create Table', 2, 'Insert ...', 3, 'Select. ..', 4, 'Create Cluster',5, 'Alter Cluster', 6, 'Update. ..', 7, 'Delete. ..', 8, 'Drop. ..', 9, 'Create Index', 10, 'Drop Index', 11, 'Alter Index', 12, 'Drop Table', 13, '--', 14, '--', 15, 'Alter Table',16, '--', 17, 'Grant', 18, 'Revoke', 19, 'Create Synonym', 20, 'Drop Synonym', 21, 'Create View', 22, 'Drop View', 23, '--', 24, '--', 25, '--', 26, 'Lock Table', 27, 'No Operation', 28, 'Rename', 29, 'Comment', 30, 'Audit', 31, 'NoAudit', 32, 'Create Ext DB', 33, 'Drop Ext. DB', 34, 'Create Database', 35, 'Alter Database',36, 'Create RBS', 37, 'Alter RBS', 38, 'Drop RBS', 39, 'Create Tablespace', 40, 'Alter Tablespace', 41, 'Drop tablespace', 42, 'Alter Session', 43, 'Alter User', 44, 'Commit', 45, 'Rollback', 46, 'Savepoint')) job, LOCKWAIT, -- VARCHAR2(8) t1.STATUS, -- VARCHAR2(8) PROCESS, -- VARCHAR2(9) TYPE, -- VARCHAR2(10) to_char(LOGON_TIME,'DD.MM.YYYY HH24:MI:SS'), -- DATE ACTION, seconds_in_wait FROM v$session t1, v$instance order by USERNAME, SERIAL# /INSTANCE_NAME SID SERIAL# USER# USERNAME schema machine TERMINAL program COMMAND JOB LOCKWAIT STATUS PROCESS TYPE TO_CHAR(LOGON_TIME, ACTION SECONDS_IN_WAIT---------------- ----- ---------- ---------- ---------- ---------- ------------------ ---------------- --------------- ---------- ----------------- -------- -------- ---------- ---------- ------------------- -------------------- ---------------orcl 20 9 79 OWBSYS OWBSYS aschlauc unknown JDBC Thin 47 ACTIVE 1234 USER 13.09.2011 14:15:50 INFRASTRUCTURE 1orcl 149 2527 151 TIF TIF DE-ORACLE\ASCHLAUC ASCHLAUC sqlplus.ex 3 SELECT. .. ACTIVE 1240:5556 USER 15.09.2011 18:20:43 0orcl 3 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------- ACTIVE 284 BACKGROUND 13.09.2011 14:13:34 3orcl 4 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------- ACTIVE 4684 BACKGROUND 13.09.2011 14:13:34 1orcl 5 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------- ACTIVE 2704 BACKGROUND 13.09.2011 14:13:35 1orcl 6 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------- ACTIVE 3356 BACKGROUND 13.09.2011 14:13:35 2orcl 7 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------- ACTIVE 4308 BACKGROUND 13.09.2011 14:13:35 1201orcl 8 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------- ACTIVE 4100 BACKGROUND 13.09.2011 14:13:36 0

~~~

AWR (Analytic Workload Repository)Einstellungenselect name,value, description from v$parameter where name = 'statistics_level';

Eingestellte IntervalleSelect * from dba_hist_wr_control;

Platzverbrauch AWR messenSelect occupant_name, space_usage_kbytes from V$SYSAUX_OCCUPANTS where occupant_name = 'SM/AWR'


Data Warehouse Technik im Fokus - Skripte in Kurzform 37 /44Auflistung bestehender Snapshotsselect SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME, FLUSH_ELAPSED,SNAP_LEVEL from dba_hist_snapshot;

AWR-Snapshot manuell anlegenexecute dbms_workload_repository.create_snapshot('ALL');

Snapshot-Nummern ausfindig machenselect SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where snap_id > 200 order by snap_ID Oderselect SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where snap_id in (199,200)

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ------- ---------------------------------------------- ---- 199 02-SEP-11 05.00.11.718 PM 02-SEP-11 06.00.41.875 PM 200 02-SEP-11 06.00.41.875 PM 02-SEP-11 07.00.11.984 PM

AWR-Bericht erstellenSpool xxxselect output from table(dbms_workload_repository.awr_report_text(1053456982,1,199,200)); Spool off;[select output from table(dbms_workload_repository.awr_report_text(1053456982,1,227,228)) ;]

Relevante Dictionary Views für Alerts und SessionV$SESSION

ADDM Informationen abfragenWelche Informationen liegen vorselect task_id,recommendation_count as rc, description from dba_advisor_taskswhere created >= trunc(sysdate);

ADDM-Analyse startendeclare task_id number; task_name varchar2(30) := 'ADDMdbc 3'; task_desc varchar2(30) := 'ADDM Feature Test';begin dbms_advisor.create_task('ADDM',task_id, task_name, task_desc, null); dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT',3088); dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT',3089 ); dbms_advisor.set_task_parameter(task_name, 'INSTANCE',1); dbms_advisor.set_task_parameter(task_name, 'DB_ID',1255780629); dbms_advisor.execute_task(task_name);end;

ADDM-Bericht anzeigenSet long 1000000Set pages 0Set longchunksize 1000Column get_clob format a80Select dbms_advisor.get_task_report('ADDMdbc 3', 'TEXT', 'TYPICAL') from dual;

Relevante Dictionary Views für Alerts und SessionDBA_THRESHOLDS DBA_ALERT_HISTORYDBA_OUTSTANDING_ALERTS V$EVENT_NAMEV$SESSION V$INSTANCEV$DATABASE DBA_ADVISOR_TASKS

TracingTrace-Output-VerzeichnisSQL> show parameter user_dump_destuser_dump_dest string d:\ora\diag\rdbms\orcl\orcl\trace

Identifizierung einer zu prüfenden Sessionselect sid,serial#,terminal,program,module from v$session;

130 1 ASCHLAUC ORACLE.EXE (LGWR) 131 1 ASCHLAUC ORACLE.EXE (SMON) 132 1 ASCHLAUC ORACLE.EXE (MMON) 135 177 ASCHLAUC sqlplus.exe sqlplus.exe 141 1 ASCHLAUC ORACLE.EXE (CJQ0)

Aktivieren des SQL-Traceexecute dbms_monitor.session_trace_enable(135,177,true);-- TRUE / FALS mit bzw. Ohne waits und zusätzliche Analysen

Deaktivierenexecute dbms_monitor.session_trace_disable(135,181);

Beispiel-Trace-SessionSQL> select count(*) from dwh.wh_transaktionen; 4216SQL> execute dbms_monitor.session_trace_disable(135,181);

-- Output in Trace-Datei

*** 2011-09-05 08:08:53.468=====================PARSING IN CURSOR #1 len=62 dep=0 uid=0 oct=47 lid=0 tim=33718840968 hv=3081195784 ad='34ab52dc' sqlid='2bqy8r6vufn88'BEGIN dbms_monitor.session_trace_enable(135,181,false); END;END OF STMTEXEC #1:c=0,e=1082,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=33718840965

*** 2011-09-05 08:09:02.890CLOSE #1:c=0,e=45,dep=0,type=0,tim=33728275300=====================PARSING IN CURSOR #3 len=41 dep=0 uid=0 oct=3 lid=0 tim=33728275601 hv=1078826809 ad='34ab4260' sqlid='2b69gpx04v5tt'select count(*) from dwh.wh_transaktionenEND OF STMTPARSE #3:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3695442063,tim=33728275598EXEC #3:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3695442063,tim=33728275918FETCH #3:c=0,e=792,p=0,cr=49,cu=0,mis=0,r=1,dep=0,og=1,plh=3695442063,tim=33728276801STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=49 pr=0 pw=0 time=0 us)'STAT #3 id=2 cnt=4216 pid=1 pos=1 obj=86150 op='TABLE ACCESS FULL WH_TRANSAKTIONEN (cr=49 pr=0 pw=0 time=8940 us cost=16 size=0 card=4216)'FETCH #3:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3695442063,tim=33728277502

*** 2011-09-05 08:09:15.453CLOSE #3:c=0,e=28,dep=0,type=0,tim=33740829401=====================PARSING IN CURSOR #2 len=57 dep=0 uid=0 oct=47 lid=0 tim=33740830066 hv=208267310 ad='34bc4a1c' sqlid='faaagm066mu1f'BEGIN dbms_monitor.session_trace_disable(135,181); END;END OF STMT

Unleserlichen Trace-Output mit TKPROF formatierentkprof orcl_ora_4488.trc c:\abc.txt explain=sys/sys sort=fchqry

Session-bezogene InformationenSession Daten abfragenSQL> SELECT sid, serial#FROM gv$session WHERE username = 'MON'; -> 134 / 63

Größe SGA und entsprechende SpeicherbereicheShow sga;bzw.select * from v$sgainfo;

Abfragen der idealen Memory-AusnutzungSELECT value FROM v$pgastatWHERE name='maximum PGA allocated';

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION

Abfragen der SGA / Welche Objekte sind im SpeicherSET PAUSE ONSET PAUSE 'Press Return to Continue'SET HEADING ONSET LINESIZE 300SET PAGESIZE 60COLUMN object_name FORMAT A32

SELECT t.name AS tablespace_name, o.object_name, SUM(DECODE(bh.status, 'free', 1, 0)) AS free, SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur, SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur, SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr, SUM(DECODE(bh.status, 'read', 1, 0)) AS read, SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec, SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec FROM v$bh bh JOIN dba_objects o ON o.object_id = bh.objd JOIN v$tablespace t ON t.ts# = bh.ts# where t.name not in ('SYSAUX','SYSTEM') GROUP BY t.name, o.object_name order by o.object_name;

SELECT t.name AS tablespace_name, o.object_name, SUM(DECODE(bh.status, 'free', 1, 0)) AS free,


Data Warehouse Technik im Fokus - Skripte in Kurzform 38 /44 SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur, SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur, SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr, SUM(DECODE(bh.status, 'read', 1, 0)) AS read, SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec, SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec FROM v$bh bh JOIN dba_objects o ON o.object_id = bh.objd JOIN v$tablespace t ON t.ts# = bh.ts# where t.name = 'ETLDB' GROUP BY t.name, o.object_name order by o.object_name;

Sessions schnell ‘killen’select SID,serial#,schemaname,module from v$session where TYPE = 'USER';alter system kill session 'sid,serial#';

Schnelle Übersicht über aktuell laufende Sessions / wer / wo / wasset line 200column machine format a30select SID,username,status,schemaname,osuser,machine,module from v$session where TYPE = 'USER';SID USERNAME STATUS SCHEMANAME OSUSER MACHINE MODULE---------------- 6 SYS ACTIVE SYS aschlauc DE-ORACLE\ASCHLAUC long_proc 63 SYS INACTIVE SYS Administrator alfred SQL Developer 132 MON INACTIVE MON aschlauc DE-ORACLE\ASCHLAUC SQL*Plus

Verhindern von Memory Paging-- lock_SGA : SGA immer im Hauptspeicher ---belassen, kein Auslagern auf Platte alter system set lock_sga=TRUE scope=SPFILE;

Herausfinden Memory und Sessioncolumn USERNAME format a10column name format a30column machine format a40

select s.sid, s.username, s.program, s.machine, sa.name, sum(ss.value) valuefrom v$sesstat ss, v$statname sa, v$session swhere ss.sid = s.sid and (sa.name like '%pga%' or sa.name like '%uga%')and sa.statistic# = ss.statistic#and s.username ='PETER'group by s.sid,s.username, s.program, s.machine, sa.name order by 1,2,3

SID USERNAME PROGRAM MACHINE NAME VALUE------------------------------ ---------- 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session pga memory 987364 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session pga memory max 987364 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session uga memory 438608 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session uga memory max 438608

Relevante Dictionary ViewsV$SESSTAT V$STATNAME V$SESSION V$MEMORY_TARGET_ADVICEV$PGASTAT V$EVENT_NAMEV$SYSTEM_EVENT

ASH (Active Session History), Session + User InformationenGröße ASH - BufferSELECT *FROM gv$sgastat WHERE name = 'ASH buffers';

Desc gv$active_session_history,

SELECT DISTINCT wait_classFROM gv$event_nameORDER BY 1;

Session Daten abfragenSQL> SELECT sid, serial#FROM gv$session WHERE username = 'MON'; -> 134 / 63

Die aktivsten SQLs in der letzten StundeSELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOADFROM gv$active_session_historyWHERE sample_time > SYSDATE - 1/24AND session_type = 'BACKGROUND'GROUP BY sql_idORDER BY COUNT(*) DESC;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOADFROM gv$active_session_history

WHERE sample_time > SYSDATE - 1/24AND session_type = 'FOREGROUND'GROUP BY sql_idORDER BY COUNT(*) DESC;

Die aktivsten IO-OperationenSELECT sql_id, COUNT(*)FROM gv$active_session_history ash, gv$event_name evtWHERE ash.sample_time > SYSDATE - 1/24AND ash.session_state = 'WAITING'AND ash.event_id = evt.event_idAND evt.wait_class = 'User I/O'GROUP BY sql_idORDER BY COUNT(*) DESC;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display_cursor('...sql_id_#...'));

Sample-Time abfragenSELECT sample_time, event, wait_time FROM gv$active_session_history WHERE session_id = 134 AND session_serial# = 63

SAMPLE_TIME EVENT WAIT_TIME---------------------------------- ---------------- -05-SEP-11 08.47.44.282 PM 105-SEP-11 08.46.42.283 PM 1

Aktives SQL AbfragenSELECT sql_text, application_wait_timeFROM gv$sqlWHERE 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);

Übersicht über SQL-Statements in der Vergangenheitselect to_char(s.begin_interval_time,'mm-dd hh24') 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 c8from dba_hist_sqlstat sql, dba_hist_snapshot s, dba_hist_SQLTEXT twhere 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;

Session-InformationenSQL> desc v$session

Relevante Dictionary ViewsGV$ACTIVE_SESSION_HISTORY GV$EVENT_NAMEGV$SQL DBA_HIST_SQLSTAT DBA_HIST_SNAPSHOT DBA_HIST_SQLTEXTV$SESSION

SQL-MonitoringSQL CacheDer folgende Befehl zeigt für die SQL-Befehle, die sich im SQL-Cache befinden, wie oft sie ausgeführt wurden und wieviele Blockzugriffe sie zur Abarbeitung benötigten. Ausserdem wird die Trefferquote des Befehls im Datencache angezeigt. Damit lassen sich sehr schnell schlecht optimierte SQL-Befehle herausfinden. Trefferquoten kleiner 70% deuten regelmäßig darauf hin, dass eine Tabelle vollständig ohne Index-Zugriffe gelesen wird. Ggf. ist ein weiterer Index hinzuzufügen, um den Befehl zu optimieren. Da die Statistik nur die Befehle anzeigt, die sich gerade im SQL-Cache befinden, muss die folgende Abfrage ggf. mehrfach täglich zu unterschiedlichen Zeitpunkten aufgerufen werden.

select to_char(executions, '999G999G990') executions,sql_id,to_char(buffer_gets, '999G999G990') gets,to_char(buffer_gets/greatest(nvl(executions,1),1), '999G999G990') gets je exec,to_char (round(100*(1-(disk_reads/greatest(nvl(buffer_gets,1),1))),2), '990D00') Trefferquote,substr(sql_text,1,100)from v$sqlwhere buffer_gets > 1000order by buffer_gets desc;



USERNAME executions SQL_ID gets gets je exec TREFFER SUBSTR(S.SQL_TEXT,1,100) ---------- ----------- ------------- ---------- ------------ MON 1 azj40p0u6tykq 947,998 947,998 0.12 select distinct VERTRIEBSKANAL from BESTELLUNG_P SYS 143 6gvch1xu9ca3g 153,871 1,076 98.75 DECLARE job BINARY_INTEGER := :job; next_date DA

Kurzabfrage auf ein bestimmtes Select-Statement mit einer bestimmten Tabelle

column text format a50column Module format a10column username format a10

select substr(sql_text,1,45)Text,username,module,PX_SERVERS_ALLOCATED/2 parallel, Buffer_gets,disk_reads

from v$SQL_MONITOR WHERE UPPER(SQL_TEXT) LIKE '%F_UMSATZ%';

TEXT USERNAME MODULE PARALLEL BUFFER_GETS DISK_READS--------------------------------- ---------- --------------- ----------- ----------SELECT sum(u.UMSATZ) Umsatz, DWH QL*Plus 2 40480 3269 R.REGION,SELECT a.sparte_name, z.Jahr_Nummer, r.land,v DWH QL*Plus 2 133 0SELECT sum(u.UMSATZ) Umsatz, DWH QL*Plus 2 40480 7409 R.REGION,CREATE bitmap index idx_ZEIT_ID_BM on F_UMSAT DWH QL*Plus 303230 2185SELECT a.sparte_name, z.Jahr_Nummer, r.land,v DWH QL*Plus 2 133 23SELECT sum(u.UMSATZ) Umsatz, DWH QL*Plus 2 40480 0 R.REGION,

SQL-Statistik abfragen / Wurde ein Ergebniss aus dem Cache oder von der Platte gelesen?Set autotrace on statistics(Führt zu zusätzlichen Statistik-Zeilen unterhalb der Ausgabe. Ist der Wert „physical reads“ auf 0, dann wurde aus dem Cache gelesen)z. B. Statistics-------------------------------------------------------- 0 recursive calls 0 db block gets 7088 consistent gets 0 physical reads 692 redo size 347 bytes sent via SQL*Net to client 475 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Abfrage auf v$sql_Monitorcolumn sql_text format a75column userName format a10select userName, status,ELAPSED_TIME, SQL_ID ,SQL_EXEC_ID, to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS'), substr(SQL_TEXT ,1,75) SQL_Text from v$sql_monitor order by SQL_EXEC_START;

Eindeutigkeit über SQL_ID SQL_EXEC_START SQL_EXEC_ID

select count(*), case when count(*) != 1 then (count(*)-1)/2 when count(*) = 1 then count(*) end Parallelitaet,SQL_ID,SQL_EXEC_ID,to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS')from v$sql_monitor group by SQL_ID,SQL_EXEC_ID,SQL_EXEC_STARTorder by to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS'),SQL_ID,SQL_EXEC_ID;

Feststellen welche Benutzer mit welchem SQL und welcher Parallelität zugegriffen habenListe aller SQLs ausser SYS seit letzten Hochfahren der DBRechenzeit aufsummiert über alle Prozessecolumn sql_text format a75column userName format a10

select b.username,a.anz_Proz,a.Parallelitaet,a.MilliSek_Rech_Zeit, a.SQL_ID,a.SQL_EXEC_ID,a.laufzeit,b.SQL_Text from

(select count(*) anz_Proz,sum(ELAPSED_TIME)/1000000 MilliSek_Rech_Zeit,

case when count(*) != 1 then (count(*)-1)/2 when count(*) = 1 then count(*)

end Parallelitaet,SQL_ID,SQL_EXEC_ID,to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS')

laufzeitfrom v$sql_monitor group by SQL_ID,SQL_EXEC_ID,SQL_EXEC_START

order by to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS'),SQL_ID,SQL_EXEC_ID) a,

(select userName, status,ELAPSED_TIME, SQL_ID ,SQL_EXEC_ID, to_char(SQL_EXEC_START,'DD-MM-YYYY HH24:MI:SS') laufzeit, substr(SQL_TEXT ,1,500) SQL_Text from v$sql_monitor order by SQL_EXEC_START) bwhere

a.SQL_ID = b.SQL_ID anda.SQL_EXEC_ID = b.SQL_EXEC_ID anda.laufzeit = b.laufzeit andsubstr(b.sql_text,1,2) != ' ' andb.username != 'SYS'

/USERNAME ANZ_PROZ PARALLEL MILLISEK_RECH_ZEIT SQL_ID SQL_EXEC_ID LAUFZEIT SQL_TEXT ----------------- ------------- ------------------ ------------- MON 9 4 146. 742371 9p3gpbg7sz1cr 16777216 22-08-2011 08:19:12 select distinct vertriebskanal from BESTELLUNG_PART_RANGE_4 MON 9 4 146.848209 9p3gpbg7sz1cr 16777217 22-08-2011 08:20:43 select distinct vertriebskanal from BESTELLUNG_PART_RANGE_4 MON 1 158.071063 9p3gpbg7sz1cr 16777218 22-08-2011 08:23:37 select distinct vertriebskanal from BESTELLUNG_PART_RANGE_4 PETER 1 158.029279 apzgjdwwcxb2j 16777217 22-08-2011 08:27:52 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 5 2 106.608017 apzgjdwwcxb2j 16777218 22-08-2011 08:29:54 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 9 4 146.151807 apzgjdwwcxb2j 16777219 22-08-2011 08:30:57 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 9 4 146.675706 apzgjdwwcxb2j 16777220 22-08-2011 08:51:21 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 9 4 146.563312 apzgjdwwcxb2j 16777222 22-08-2011 10:15:54 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 9 4 146.890659 apzgjdwwcxb2j 16777223 22-08-2011 10:43:44 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 1 158.497194 apzgjdwwcxb2j 16777224 22-08-2011 10:46:01 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4

SQLTun-Report über SQL ID aufrufenspool c:\abcselect dbms_sqltune.report_sql_monitor( type => 'html', sql_id => 'f1fj6drvxwk9h') as sql_monitor from dual;spool off;

Lese-Statistiken auf einzelne Tabellen gezielt abfragenSelect 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;

Relevante Dictionary ViewsV$SQL DBA_HIST_SEG_STAT

DBA_HIST_SEG_STAT_OBJV$SQL_MONITOR DBA_HIST_SNAPSHOT

PlanmanagementAutomatisiertes Sammeln von Plänen einschaltenalter system set optimizer_capture_sql_plan_baselines=true;

Parameter zur Behandlung von PlänenSQL> show parameter optimizerNAME TYPE VALUE------------------------------------ ----------- ---------optimizer_capture_sql_plan_baselines boolean TRUEoptimizer_dynamic_sampling integer 2optimizer_features_enable string 11.2.0.1optimizer_index_caching integer 0optimizer_index_cost_adj integer 100optimizer_mode string ALL_ROWSoptimizer_secure_view_merging boolean TRUEoptimizer_use_invisible_indexes boolean FALSEoptimizer_use_pending_statistics boolean FALSEoptimizer_use_sql_plan_baselines boolean TRUE

Nutzen der Pläne einschaltenalter system set optimizer_use_sql_plan_baselines=true;

Anzeigen von Plänen in Plan_Baselines - Eingeschränkt auf einen Userselect SQL_Handle, Plan_name,enabled, accepted, fixed from dba_SQL_PLAN_BASELINES

select SQL_Handle, Plan_name,enabled, accepted, fixed, ELAPSED_TIME, substr(SQL_TEXT,1, 50), creator from dba_SQL_PLAN_BASELINEs wherecreator = 'MON';


Data Warehouse Technik im Fokus - Skripte in Kurzform 40 /44SQL_HANDLE PLAN_NAME ENA ACC FIX ELAPSED_TIME SUBSTR(SQL_TEXT,1,50)------------------------------ ------------------------------ SYS_SQL_115024ccba5e158c SQL_PLAN_12n14tkx5w5cc52d2775d YES YES NO 0 DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1SYS_SQL_294c437e331fa51f SQL_PLAN_2km23gstjz98zdf463620 YES YES NO 0 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISSYS_SQL_2bdf77bedbcdea50 SQL_PLAN_2rrvrrvdwvukhed88afee YES YES NO 0 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERESYS_SQL_4e5c4235c17d5d62 SQL_PLAN_4wr226r0rurb22e8a86b7 YES YES NO 0 SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SISYS_SQL_85372e07e425b213 SQL_PLAN_8adtf0zk2bchm35b3cdca YES YES NO 0 SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1SYS_SQL_967d7c5636192728 SQL_PLAN_9czbwasv1k9t8499f732f YES YES NO 0 select distinct VERTRIEBSKANAL from BESTELLUNG_PARSYS_SQL_b96c99b551913735 SQL_PLAN_bkv4tqp8t2dtp6be2eac7 YES YES NO 0 select * from tabSYS_SQL_bff897b9dbcabe27 SQL_PLAN_bzy4rr7dwpgj7ed88afee YES YES NO 0 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DA

Betrachten eines gespeicherten Plansselect * from table(

dbms_xplan.Display_sql_Plan_BASELINE(SQL_HANDLE=> 'SYS_SQL_967d7c5636192728',format=>'basic'));

PLAN_TABLE_OUTPUT------------------------------------------------------------SQL handle: SYS_SQL_967d7c5636192728SQL text: select distinct VERTRIEBSKANAL from BESTELLUNG_PART_RANGE_4-----------------------------------------------------------Plan name: SQL_PLAN_9czbwasv1k9t8499f732f Plan id: 1235186479Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

PLAN_TABLE_OUTPUT--------------------------------------------------------Plan hash value: 2860783498-----------------------------------------------------------| Id | Operation | Name|-----------------------------------------------------------| 0 | SELECT STATEMENT | || 1 | PX COORDINATOR | || 2 | PX SEND QC (RANDOM) | :TQ10001 || 3 | HASH UNIQUE| || 4 | PX RECEIVE| || 5 | PX SEND HASH | :TQ10000 || 6 | PX BLOCK ITERATOR | || 7 | TABLE ACCESS FULL| BESTELLUNG_PART_RANGE_4 |-----------------------------------------------------------

Evolve eines neuen Plansset serveroutput onset long 10000declare report clob;begin report := dbms_spm.evolve_sql_plan_baseline( sql_handle => 'SYS_SQL_85372e07e425b213'); dbms_output.put_line(report);end;/

Relevante Dictionary ViewsDBA_SQL_PLAN_BASELINES

Lese-Performance messenAbschätzen Lesegeschwindigkeit (IO-Performancen) bei dem Lesen einzelner Tabellen -- Auslesen der Datenmenge einer Tabelle (siehe dort)-- Messen der Zeit für einen Full Table Scan-- Berechnen SQL> set timing onSQL> select count(*) from bestellung_part_Range_4; -- liest komplette Tabelle / Full Table Scan COUNT(*)---------- 163840000 Abgelaufen: 00:00:31.32 SQL> select 7.7/31 from dual; -- Berechnen: --Größe der Tabelle (GB) / Zeit für --Full Table Scan (Sec) = GB/sec 7.7/31 ----------,248387097

Ergibt 0,2 GB pro Sekunde Lesegeschwindigkeit-- Test mit unterschiedlichen ParallelitätenParallel Sekunden GB/Sec Sekunden(2 Sessions)Sekunden(4 Sessions)1 59~0,13 1,00/1,41/1,50/1,51

2 43~0,184 30~0,25 0,53/0,57 1,28/1,40/1,52/1,556 31~0,258 30~0,25

Relevante Dictionary ViewsV$SESSTAT V$STATNAMEV$SESSION V$IOSTAT_FILEDBA_DATA_FILES

IO Messung / calibrate IOdisk_asynch_io boolean TRUEfilesystemio_options string SETALL

Calibrate-Status abfragen[Kennt der Optimizer die Leistungsfähigkeit des Systems]select status from V$IO_CALIBRATION_STATUS;

Calibrate IO abfragenDECLARE lat INTEGER; iops INTEGER; mbps INTEGER;BEGIN-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat); dbms_resource_manager.calibrate_io (4, 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;/max_iops = 164latency = 11max_mbps = 95

Perfstat

-- Tablespace für Perfstat-Tabellen anlegenCREATE SMALLFILE TABLESPACE PERFSTAT NOLOGGING DATAFILE 'D:\app\aschlauc\oradata\o11\PERFSTAT.DBF' SIZE 150M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

-- Perfstat User einrichten (als SYS User)$ORACLE_HOME/rdbms/admin/spcreate.sql

-- Snapshot erstellen (als PERFSTAT User)EXECUTE statspack.snapEXECUTE statspack.snap (i_snap_level => 7)

-- Snapshot stündlich ausführen lassen @?/rdbms/admin/spauto.sql --> stündlicher Aufruf

-- Spapshot Bericht erstellen lassen D:\ora\product\11.2.0\dbhome_1\RDBMS\ADMIN>spreport.sql

OrionInfos überORION -HelpORION: ORacle IO Numbers -- Version 11.2.0.1.0

ORION runs IO performance tests that model Oracle RDBMS IO workloads.It measures the performance of small (2-32K) IOs and large (128K+) IOsat various load levels.

Aufruf überC:\>orion -run dss -testname 'D:\Orion\laufwerk' -num_disks 4 -cache_size 0ORION: ORacle IO Numbers -- Version 11.2.0.1.0D:\Orion\laufwerk_20110902_1032Calibration will take approximately 77 minutes.Using a large value for -cache_size may take longer.

Output in Summary-File:

Command line:-run dss -testname 'D:\Orion\laufwerk' -num_disks 4 -cache_size 0

These options enable these settings:Test: D:\Orion\laufwerkSmall IO size: 8 KBLarge IO size: 1024 KBIO types: small random IOs, large random IOsSequential stream pattern: one LUN per stream Writes: 0%Cache size: 0 MBDuration for each data point: 240 secondsSmall Columns:, 0Large Columns:, 4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56, 60


Data Warehouse Technik im Fokus - Skripte in Kurzform 41 /44Total Data Points: 19

Name: \\.\D: Size: 500105217024Name: \\.\E: Size: 500105217024Name: \\.\F: Size: 500105217024Name: \\.\G: Size: 5001052170244 files found.Maximum Large MBPS=109.67 @ Small=0 and Large=56

Lesestatistiken: Werden Daten genutzt?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 335536106.09.2010:23:00 4030816 673296 4028177 67281607.09.2010:12:32 8060160 4029344 8054609 402643207.09.2010:15:50 6886881 1

col c1 heading ‘Begin|Interval|time’ format a8col c2 heading ‘SQL|ID’ format a13col c3 heading ‘Exec|Delta’ format 9,999col c4 heading ‘Buffer|Gets|Delta’ format 9,999col c5 heading ‘Disk|Reads|Delta’ format 9,999col c6 heading ‘IO Wait|Delta’format 9,999col c7 heading ‘Application|Wait|Delta’ format 9,999col c8 heading ‘Concurrency|Wait|Delta’ format 9,999col c9 heading 'SQL-Text' format a50break on c1

select to_char(s.begin_interval_time,'mm-dd hh24') 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 c8from dba_hist_sqlstat sql, dba_hist_snapshot s, dba_hist_SQLTEXT twhere 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;

Relevante Dictionary ViewsDBA_HIST_SEG_STAT DBA_HIST_SEG_STAT_OBJ DBA_HIST_SNAPSHOTDBA_HIST_SQLSTAT DBA_HIST_SQLTEXT



Backup im Data WarehouseAllgemeine ÜberlegungenArgumente für ein DWH spezifisches Backup-Konzept In einem DWH sind meist größere Datenmengen zu sichern Große datenmengen in einem DWH verursachen mehr Backup-

Kosten Der größte Teil der daten in einem DWH verändert sich

nicht mehr. Daher macht regelmäßiges Komplettsichern wenig Sinn

Änderungsvorgänge findet über den ETL-Prozess in einer kontrollierten Weise statt. Daher kennt man genau die datenbestände, die sich geändert haben.

Backup gehört zu den teuersten Aufgaben in einem zentralen Rechenzentrum.

Was muss gesichert werden. Nicht gesichert werden muss:

o Der Integration Layer (Stage)o Temporäre Tabellen, die im rahmen des PEL genutzt

werdeno Read Only Tablespaces von älteren Daten in den dafür

vorgesehenen Partitioneno Data Marts (User View Layer), die aus dem Enterprise

Layer wieder hergestellt werden können Gesichtert werden:

o Die Masse der kleineren i. d. R. nicht partitionierten Tabellen

o Die Nicht-Read-Only Partitionen von partitionierten Tabellen.

Wie wird gesichertEs wird i. d. R. mit RMAN (Oracle Recovery Manager) gesichert. RMAN erkennt schadhafte Blöcke und Verhindert das

Wegschreiben von diesen Blöcken so dass ein späteres RECOVERY möglich ist.

RMAN erkennt READ-ONLY Tablespaces RMAN kann inkrementelles Delta-Backups durchführen.

Meist macht man einmal pro Woche eine Vollsicherung (unter Berücksichtigung der oben genannten Regeln). Innerhalb der Woche führt man ein inkrementelles Sichern durch.

Archive Log

Relevante FragestellungenBefindet sich die Datenbank in dem Archivelog-Modus?Der Archive-Modus stört bei Massen-INSERTS im ETL.

Festeststellen des Log-Modus der Datenbank(als SYS-User)SELECT LOG_MODE FROM SYS.V$DATABASE;

Ein-(Aus-)schalten des Archivelog-Modusshutdown immediate;startup mountalter database archivelog;[alter database noarchivelog;]alter database open;

Archive-Zustand anzeigen lassenArchive log list;

Wohin wird das Archive-Log geschrieben und wie groß ist die Recovery AreaSHOW Parameter db_recovery_file_dest

Wie voll ist die Recovery Area aktuellSELECT * FROM v$recovery_file_dest;

Recovery Area vergrößernalter system set db_recovery_file_dest_size=7824M scope=both;

Relevante Dictionary Views für Alerts und SessionV$DATABASE V$BACKUP_REDOLOGV$ARCHIVED_LOG V$LOGV$ARCHIVE_DEST V$LOG_HISTORYV$ARCHIVE_PROCESSES

RMANRMAN startenRman

An Zieldatenbank einwählenConnect target orcl

Welche Backups sind überflüssigREPORT OBSOLETE;

Welche Files benötigen ein BackupREPORT NEED BACKUP;

Leeren Recover Areadelete archivelog all;

Welche Files können nicht wiederhergestellt werdenREPORT UNRECOVERABLE;

Welche Sicherungen liegen vorLIST BACKUP;

Alle Einstellungen von RMANSHOW ALL;

Definieren eines Backup-KanalsCONFIGURE CHANNEL n DEVICE SBT/DISK FORMAT ‘location‘;CONFIGURE COMPRESSION ALGORITHM‘BASIC/LOW/MEDIUM/HIGH‘;CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

Flashback aktivieren(nur wenn Archive-Mode aktiviert ist)shutdown immediate;startup mount exclusive;ALTER DATABASE FLASHBACK ON;ALTER DATABASE OPEN;

Verwendung der Recovery AreaSELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest'

Prüfen, ob Flashback aktiviert istSELECT flashback_on, log_mode FROM gv$database;

Prüfung des benötigten PlatzesSELECT ESTIMATED_FLASHBACK_SIZE,RETENTION_TARGET, FLASHBACK_SIZE FROMV$FLASHBACK_DATABASE_LOG

Retention-Zeit Flashback Area einstellenALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440

Abfragen einer Tabelle von einem bestimmten SCN-ZustandSELECT * FROM x AS OF SCN 12555060;

Abfragen einer Tabelle zu einer bestimmten ZeitSELECT * FROM x AS OF TIMESTAMP to_timestamp('2012-02-15 10:15:00', 'YYYY-MM-DD HH:MI:SS');

Tabellen mit Flashback zurückholenFLASHBACK TABLE dwh.x TO SCN 16552768;FLASHBACK TABLE x TO SCN 16553108;

Datenbank mit Flashback zurücksetzen (Kommandobeispiele)FLASHBACK DATABASE TO SCN 5964663FLASHBACK DATABASE TO BEFORE SCN 5964663FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)FLASHBACK DATABASE TO SEQUENCE 12345

Ältest mögliche Rückhol-Position feststellenSELECT OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME FROMV$FLASHBACK_DATABASE_LOG



Anhang(DWH-Administrations-Checkliste - wird aktualisiert)

Informationsbedarf planenAnforderungen FachanwenderAnforderungen Folgesysteme

Synchronisation der InformationenVerhindern von doppelter Information(3-) Schichtenmodell

Passende Aufbereitung und PräsentationMultidimensionales Modell (Star)Kennzahlensystem (Materialized Views-Konzept)Dimensionen-Objekte in der DatenbankSaubere Parent/Child-Beziehungen zwischen Dimensions-Leveln

PCT bei Materialiezd Views nutzenAktualisierungskonzept für Materialiezed ViewsRegelmäßige Prüfung ob Materialized Views genutzt werden.Regelmäßiges Suchen nach neuen sinnvollen Materialized Views.Staroptimierung

Passende Bitmap-Indizierung Schlüsselverteilung im Star-Schema prüfenStar-Transformatio einschalten

Statistiken aktuell halten

Unused-Indexes prüfenStatistiken auf Tabellen und Index aktuell halten ETL-Prozess mit Checkpunkten versehenETL-(Teil-)Prozess(e) wiederholbar machen Vor dem Laden

Betroffene Indexe löschenConstraints ausschaltenLogging prüfenDirect Path prüfen

Datenqualität mit Bordmitteln machenPartitioning gezielt einsetzen (Range / List)Partition Exchange Load (PEL) gezielt einsetzenETL-Strecke monitoren

Welches sind die aufwendigen Teilstrecken?Resourcen-VerbrauchWerden nur die benötigten Daten gelesen?Verlagerung von teuren Transformationen in die Datenbank (Table Functions).

DatendurchsatzIst bekannt wo bei der Hardware-Umgebung die Schwachstellen liegen?

Wird die Parallelisierung sinnvoll eingesetztParallelisierung auf Einzelobjekte im ETL-Lauf und beiBatch-Reporting. Pauschale automatisch geregelte Parallelisierung beim Online-Reporting.

Umgang mit StorageGibt es ein LifeCycle-Konzept?Ist bekannt, welche daten oft / weniger oft genutzt werden?Einsatz von Flashspeicher?

Testdaten erstellen

Relevante FragestellungenTestdaten für Warehouse-Systeme zu Testzwecken schnell erstellenDas Arbeiten mit Testdaten (Erstellen und Abfragen) verrät sehr viel über die Art und Praktikabilität des Datenmodells.

SQL-Trigger-Tabelle für Dummy-FROM-Klausel erstellencreate table zahl as select level nr from dual connect by level < 1000000;

Tabelle mit laufender Nummer erstellenDas folgende Beispiel erzeugt eine Zähltabelle mit aufsteigend laufender Nummer vorneweg und einer Zufallszahl von 1 bis 100.Die neue Tabelle hat 1000 Zeilen.

create table i as select

level nr, dbms_random.value(1,100) Zahl

from dual connect by level < 1000;

Das folgende Beispiel erzeugt eine Bücher-Dimension mit aufsteigend laufender Nummer vorneweg und numerischen und alphanumerischen Zufallswerten. Die Zieltabelle erhälz gültige Spaltennamen.

create table d_buch as select level buch_id,

ran_abc_mixed(15) Titel, ran_m_n(1,20) isbn, ran_m_n(1,100) preis, ran_abc_mixed(20) autor

from dual connect by level < 2000[Auf der Community-Web-Seite gibt es vorbereitete Skripte für umfangreichere Testdatenerzeugung: http://www.oracledwh.de/downloads/AutoIndex-2.2.4/index.php?dir=downloads/DWH_Utilities/Testdatengenerator_V1/]

Vorgehensweise bei der Erstellung der Demo-UmgebungBeispieldaten sind zu finden unter:

http://www.oracledwh.de/downloads/AutoIndex-2.2.4/index.php?dir=downloads/Kurs_Materialien_und_DWH_TIF_und_Angebote/DWH_Reader_und_Kurzreferenz/&file=Beispieldaten_Star_Skriptesammlung.zip

1. Das Beispiel installiert man am besten in ein separates Benutzer-Schema in der Datenbank:

create user DWHTIF identified by DWHTIF default Tablespace DWHTIF;

(Der Tablespace DWHTIF müßte natürlich vorhanden sein).

Man kann die Tabellen einzeln erstellen, so wie es in der Folge unter den Punkten2 - 5 dargestellt ist, oder man wählt die einfachste Variante:

Man ruft nacheinander die beiden Skripte:01_DDL_Star_Skriptesammlung.SQL02_DML_Beispieldaten_Komplett.sql(03_Indexe_Statistiken.sql)

2. Als erstes sind die 6 Tabellen des in der Skriptesammlung zu Beginn abgedruckten Starschema zu erstellen. Die DDL dazu mit Cut/Paste z. B. in SQL Plus kopieren.a. F_Umsatzb. D_Zeitc. D_Kunded. D_Regione. D_Artikelf. D_Vertriebskanal(Alternativ dazu auch die Daten DDL_Star_Skriptesammlung)

3. Die Daten für D_Kunde,D_Artikel, D_Vertriebskanal,D_Region sind als Einzel-Inserts in den dazu passenden Textdateien. Sie werden geladen:aus dem jeweiligen Dateiverzeichnis heraus SQLPLUS aufrufen und die Dateien mit [START Dateiname] laden.

4. Die Daten der Zeittabelle entstehen durch eine kleine PL/SQL-Prozedur, der man durch Parameter den gewünschten Zeitraum mitgeben kann.Hierzu ist zunächst die Prozedur Time_Gen in der Textdatei BeispielDaten_D_Zeit zu laden. (Mit Cut/Paste in SQL Plus).Danach kann sie aufgereufen werden mit exec time_gen('01012000','31122015'); Das nebenstehende Beispiel liefert Tagessätze für die Jahre 2000 bis 2015.

5. Als letztes sollte die Faktentabelle F_Umsatz erstellt werden. Die Inhalte dieser Tabelle erstellt man über eine Prozedur Umsatz_Gen, die in der Textdatei Beispieldaten_F_Umsatz abgebildet ist (Cut/Paste in SQL Plus)Über einen Aufruf erzeugt man dann die gewünschte Anzahl an Sätzen:EXEC Umsatz_gen(100000); In diesem Beispiel entstehen 100000 Sätze.Um einen effektiven Test durchzuführen, benötigt man viel mehr Sätze. Hat man z. B. 100000 Sätze mit der Generierungsprozedur Umsatz_Gen erzeugt, sollte man diese Anzahl durch mehrfaches Kopieren erhöhen:Insert into F_Umsatz select * from F_Umsatz;

Um 51,2 Millionen Sätze zu erhalten (das ist die Beispielmenge in dieser Skriptesammlung), muss man auf der Basis von 100000 Sätzen den Kopiervorgang 9 mal wiederholen.

6. Danach kann man die nötigen Constraints, Indexe usw. einrichten, so wie sie in der Skritesammelung dargestellt sind.

Hilfsprozeduren zu den BeispielmodellenZeitdimensioncreate or replace procedure Time_Gen

(startdate DATE, enddate DATE) ASincdate DATE;recno INTEGER;

Beginincdate := startdate;recno := 1;

while incdate <= enddate LOOP insert into D_ZEIT(ZEIT_ID,

Datum_ID,





http://www.oracledwh.de/downloads/AutoIndex-2.2.4/index.php?dir=downloads/DWH_Utilities/Testdatengenerator_V1/%5D

http://www.oracledwh.de/downloads/AutoIndex-2.2.4/index.php?dir=downloads/DWH_Utilities/Testdatengenerator_V1/%5D

Data Warehouse Technik im Fokus - Skripte in Kurzform 44 /44 Tag_des_Monats, Tag_des_Jahres, Woche_des_Jahres, Monats_Nummer, Monat_Desc, Quartals_Nummer, Jahr_Nummer)

Values (incdate, recno, TO_NUMBER(TO_CHAR(incdate, 'DD')), TO_NUMBER(TO_CHAR(incdate, 'DDD')), TO_NUMBER(TO_CHAR(incdate, 'WW')), TO_NUMBER(TO_CHAR(incdate, 'MM')), TO_CHAR(incdate, 'Month'), TO_NUMBER(TO_CHAR(incdate, 'Q')), TO_NUMBER(TO_CHAR(incdate, 'YYYY')));

recno := recno + 1;incdate := incdate + 1;

END LOOP;END;/

--Aufruf der Prozedur z.B.:-- alter session set nls_date_format = 'DDMMYYYY';-- exec time_gen('01011990','31122015');--alter session set nls_date_format = 'DD-MON-YY';

Daten in der Faktentabelle F_UMSATZcreate or replace procedure Umsatz_gen (anzahl number) AS

recno INTEGER; v_umsatz INTEGER; v_menge INTEGER;

BEGIN recno := 1; while recno <= anzahl LOOP v_umsatz := round(dbms_random.value(1,4000)); v_menge := round(dbms_random.value(1,100)); insert into F_UMSATZ select round(dbms_random.value(1,129)) ARTIKEL_ID, round(dbms_random.value(1,1031)) KUNDEN_ID, to_date(sysdate-(round(dbms_random.value(-1000, 5000)))) ZEIT_ID, round(dbms_random.value(1,7020)) REGION_ID, round(dbms_random.value(1,7)) VERTRIEBS_ID, v_umsatz UMSATZ, v_menge MENGE, v_umsatz * v_menge UMSATZ_GESAMT from DUAL; commit; recno := recno + 1; END LOOP;END;/

--Aufruf der Prozedur z.B.:-- EXEC Umsatz_gen(100000)-- Der Aufruf vorher erzeugt nur 100000 Sätze-- Zum Vervielfältigen den folgenden Aufruf wählen:-- INSERT /*+ APPEND */ into F_UMSATZ select * from F_UMSATZ;-- Commit;-- Diesen Befehl sooft wiederholen, bis die gewünschte Menge erreicht ist


Zu dieser Skriptesammlung - Oracle Data Warehouse ... · Web viewZugriff auf...

Documents

Transcript of Zu dieser Skriptesammlung - Oracle Data Warehouse ... · Web viewZugriff auf...