CMD_DWH_Kurzreferenz_V3
Werbung
Data Warehouse Technik im Fokus - Skripte in Kurzform 1 /41 Oracle Data Warehouse Kurzreferenz Skripte, Systemabfragen und Empfehlungen Stand Juni 2013 V4 (03.10.2013) Document1 Data Warehouse Technik im Fokus - Skripte in Kurzform Zu dieser Skriptesammlung 5 Allgemeiner Umgang mit Tabellen Allgemeine Hilfen 6 Einrichten und Einstellen einer Testumgebung 6 Mess-Einstellungen + allgemeine Settings in SQLPlus 6 Gelöschte Tabellen endgültig löschen 6 Ausschalten der Recyclebin-Funktion 6 Speicher leeren 6 SELECT-Ausgabeformatierung 6 Systemparameter abfragen 6 Dictionary-Views auslesen 6 Ändern der Berücksichtigung Groß-/Kleinschreibweise bei Logon 6 SQL Plus 6 Metadaten-DDL für Objekte aus dem Dictionary generieren lassen 6 Enterprise Manager 6 Aufruf im Browser z. B. mit Starten der Console Wenn es mal klemmt 6 6 6 Datenbank, Listener und BS-Variablen 6 Starten der Datenbank Listener starten und Status abfragen Erreichbarkeit einer Datenbank über SID-Abfrage 6 6 6 Beispielumgebung 7 7 Skript –Tabellen OLTP-System 7 Beispiel-Star Schema 7 Vergleichs-Demo-Umgebung Steckbrief 7 Skript Fakten-Tabelle 7 Skript Kunden-Dimension 7 Skript Artikel-Dimension 8 Skript Regionen-Dimension 8 Skript Zeit-Dimension 8 Skript Vertriebskanal-Dimension 8 Skript Unique Keys für die Dimensionstabellen [nicht nötig] 8 Skript Bitmap-Indexe auf FK-Felderder Fakten-Tabelle F_UMSATZ 8 Skript Drop Bitmap Indexe 8 Skript Primary-Key-Felder auf Dimensions-Tabellen [nicht nötig] 8 Skript Dimensional Table D_ARTIKEL 8 Skript Dimensional Table D_Region 8 Skript Dimensional Table D_Zeit 8 Skript Dimensional Table D_Kunde 8 Beispielabfragen auf das Beispiel-Star Schema 9 Beispielabfrage 1a Generische Abfrage auf vorgenanntes StarSchema 9 Beispielabfrage 1b Einfache Abfrage Summe Umsatz an einem Tag 9 Beispielabfrage 1c Summe Umsatz an einem Tag und Vertriebskanal 9 Beispielabfrage 2 Einschränkungen über alle Dimensionen 9 Beispielabfrage3 (Ein-/Ausschalten Where-Argumente /Test Star Query 9 Beispielabfrage 4: Top 3 Länder bezogen auf Einzelverkäufe 9 Beispielabfrage 5: Umsatz nach Quartalen 9 Beispielabfrage 6: Nach Umsatzstärke sortierte QuartalsRangfolge 9 Beispielabfrage 7:: Das umsatzstärkste Quartal pro Jahr 9 Beispielabfrage 8: Stärkster Umsatzmonat pro Bundesland 10 Beispielabfrage9: Top 10 Artikel bezogen auf Menge / Rank /Subquery 10 Beispielabfrage9a: Top 10 Artikel bezogen auf Menge / Rank /Subquery 10 Beispielabfrage10: -- Top 10 Artikel bezogen auf Umsatz in einem Jahr 10 Beispielabfrage 11: - Top 3 Produkte je Produktgruppe sortiert nach Rangfolge 10 Beispielabfrage 12: Die Top-Produktgruppe pro Bundesland 10 Beispielabfrage 13: Land mit dem stärksten Umsatz je Produktgruppe 11 Beispielabfrage 14: Die Top und Flop Produktgruppe pro Bundesland 11 Beispielabfrage 15: Die Top 2 Produktgruppen pro Bundesland 11 Beispielabfrage 16: Top/Bottom 2 Produktgruppen pro Bundesland 11 Beispielabfrage 17: Über Quartale kumulierte Umsätze pro Kunde 11 Beispielabfrage 18: Über Quartale kumulierte Umsätze pro Kunde (Jahr + Kreis) 11 Beispielabfrage 19: Wieviel Prozent des Gesamtumsatzes pro Land machen die 3 Top Produkte aus 12 Beispielabfrage 20: ¼ der Kunden tragen zu ? % des Umsatzes bei? 12 Beispielabfrage21: Durchschnittliche Bestellquote eines Kunden über 3 Monate? 12 Beispielabfrage 22: - CUBE 12 Beispielabfrage23 Vergleiche Umsätze mit Vorjahreszeitraum 12 Beispielabfrage 24 Year-To-Date. Kumulierung der Monatsumsätze von Jahresbeginn an 12 Beispielabfrage 26 Verwendung von Grouping-Sets 12 Tabellen Document1 14 14 Logging/Nologging-Modus für eine einzelne Tabelle ein/ausschalten 14 Allgemeine Informationen 14 Tabellengrößen und Platzverbrauch 14 Blick in die Column-Struktur einer Tabellen / Distinct Values Nulls etc 14 Anlegen einer leeren Tabelle mit bestimmter Struktur 14 Anlegen von temporären Tabellen 14 Spaltennamen nchträglich ändern 14 Ungenutzten Platz freigeben und komprimieren 14 Blocknummern auslesen 14 Tablespace mit Datafile anlegen 14 Größe von Tabellen, Segmenten, Extents auslesen 14 Umgang mit Constraints 14 Abfragen auf bestehende Constraints im aktuellen Schema Ausschalte / Einschalten von Constraints 14 14 Compression 14 Anlegen einer komprimierten Tabelle Komprimieren eines Tablespace Prüfen, ob Tabellen komprimierte sind Tabellen und Partitionen komprimieren Komprimierte Tabelle aus seiner unkomprimierten erstellen Komprimierungsgrad für bestimmte Tabellen auslesen 14 14 14 14 14 14 Partitioning Beispiel-OLTP Schema 2 /41 15 Wo und warum wird in dem Data Warehouse Partitioniert 15 Was wird partitioniert 15 Partitioning-Varianten 15 Skript Beispiel Range Partitioning Beispielskript F_UMSATZ_RANGE Skript zum schnellen Erstellen der Testtabelle aus F_UMSATZ Skript Beispiel Range Partitioning Beispielskript F_BESTELLUNG_RANGE Beispielabfrage mit Einschränkung auf Zeit MAXVALUE zum Aufnehmen von Werten ohne Zuordnung Skript Beispiel Range Partitioning nach Alphabet Erstellen Hash Partitioned Table au seiner anderen Allgemeines Hash-Partitioning-Beispiel Skript Beispiel Hash Partitioning und Tablespace-/DatafileZuordnung Bitmap-Indexe auf Hash-partitionierte Tabelle Drop Index Skript Beispiel List Partitioning Skript Beispiel für Sub-Partition (Range-List) Beispielabfrage auf Range/List Zweites Range/List – Beispiel Skript Beispiel Reference Partitioning Skript Beispiel Interval-Partition mit Monatspartitionen Skript Beispiel Interval-Partition mit numerischer Bereichsangabe Ändern bestehender Range-Partition-Tables auf Interval Partitioning Skript Beispiel Virtuel Column Partition Zuweisen unterschiedlicher Tablespaces 15 15 15 15 15 15 15 15 15 15 16 16 16 16 16 16 16 16 16 16 17 Abfragen auf Partitionen 17 Partitiondaten direkt abfragen Grössen, Mengen und Namen Verteilung von Partitionen auf Tablespaces Tabellen,Partition,Rows Tabellen,Partition,Rows,Blocks,MegaBytes Partitionsgrenzen abfragen Auslesen von Sub Partitions Sich refenzierende Tabellen abfragen / Referen Partitioning 17 17 17 17 17 17 17 17 Verwaltung von Partitionen 17 Partionen hinzufügen Partitionen auf anderen Tablespace verlagern Merge von zwei Sub-Partitions Ändern Defaults-Tablespace Ändern der Werte bei List-Partitioning Umwandeln einer Partion in eine Tabelle Umbenennen einer Partition Truncate einer Partition Aufspalten einer Partition Partition Exchange (Hinzufügen einer Tabelle als weitere Partition) 17 17 17 17 17 17 17 17 17 Indexe 17 18 Wie und wo wird indiziert Suche nachbestehenden Indexen 18 18 Suche nach Indexen bezogen auf eine bestimmte Tabelle Status-Abfragen Auflisten aller Indexe eines Schemas inkl. Größe Auflisten aller lokalen Index Anzeigen des Platzverbrauchs der Indexe Beispiele für Definitionen (Bitmap / B*tree) Behandlung von Indexen beim Laden Rebuild Partitioned Index Rebuild Sub-Partitioned Index Verschieben eines Index auf einen anderen Tablespace Aktualisierung der Index zusammen mit Tabellen-Updates Einschalten Usage - Monitoring für einen Index Welcher Index wurde tatsächlich genutzt: Usage - Abfrage 18 18 18 18 18 18 18 18 18 18 18 18 18 Data Warehouse Technik im Fokus - Skripte in Kurzform Clustering Faktor und Anzahl Leaf-Blöcke abfragen 18 Partitionierung von Indexen 18 Skript Local Partion Index 18 Partitionierung der Indexe entsprechend der zugehörigen TablePartionen (Local Partiton Index) und zweisen unterschiedlicher Tablespaces 18 Partitionierung des Index unabhängig von den Table-Partitionen (Global Partition Index) 18 Partitionierte Indexe suchen 18 Rebuild lokaler Index 18 Rebuild Sub Partition Index 18 Verschieben auf anderen Tablespace 18 Star Schema 19 Allgemeine Regeln Konsistenz, Mengen und Indexe im Star-Schema 19 19 Prüfen ob FK der Fakten-Tab in PK der Dimensionen Größe der Indexe ausgeben Index-Typen anzeigen Größe aller Bitmap-Indexe Star Transformation aktivieren Menge der WHERE-Bedingungen überprüfen 19 19 19 19 19 19 Materialized Views 20 Hilfen/Konzepte für Materialized Views 20 Allgemeine Hinweise zur Definition von Materialized Views Informationszugriffen planen und dokumentieren Relevante Parameter MVIEW suchen Größe und Anzahl Zeilen von Mviews abfragen Beispieldefinition MAV_Zeit_Umsatz Beispieldefinition MAV_Artikel_Umsatz Beispieldefinition MAV_Region_Umsatz Beispieldefinition MAV_Kunde_Umsatz Beispieldefinition MAV_Region_Artikel_Umsatz Beispieldefinition MAV_Region_Zeit_Umsatz Beispieldefinition MAV_Kunde_Zeit_Umsatz Beispieldefinition MAV_Region_Zeit_Artikel_Umsatz Beispiel-Definition Beispiel für eine Partitionierte MAV Definition Materialized View Log Löschen Materilized View Log Build Funktionen Refresh Funktionen Feststellen, ob PCT Tracking funktioniert Refresh auf eine MAV PMARKER Funktion Komprimieren von Materialized Views Refresh auf alle von einer Tabelle abhängigen MAVs Aktualisieren aller Materialized Views Anzeigen Materialized Views mit Zustand und View Logs Anzeigen Materialized Views mit Zustand und PCT Regions Stimmigkeit von Dimensionen prüfen DBMS_MVIEW.Explain_mview Auflisten von Dimension-Tables Anzeigen der Struktur einer Dimensional Table 20 20 20 20 20 20 20 20 20 20 21 21 21 21 21 21 21 21 21 21 21 21 22 22 22 22 22 22 22 22 22 3 /41 Datenbank-Informationen, Version, Patchstände 24 Database-ID abfragen Installierte Komponenten Patchstand abfragen Patch-Historie Datenbankversion abfragen 24 24 24 24 24 User-Informationen / Einstellungen /Security 24 Welche USER gibt es in einer Umgebung und welchen Zustand haben sie 24 Benutzer anlegen 24 Eigene Tabellen zum Lesen für alle freigeben 24 Allgemeine Benutzerinformationen abfragen 24 Ausschalten Expiration Date in Entwicklungs- und Test-Umgebung 24 Ändern des Lock-Zustads eines Users 24 Welche Rechte wurden einem User vergeben 24 Plattenplatz der Segment-Objekte eines Users 24 Logins pro User 24 User Platzverbrauch Tablespace 24 Maximale Parallelität von Benutzeraktivitäten 24 Tablespaces, Auflistungen, Mengen und Größen 24 Anlegen eines Tablespace mit Zuweisung eines Datafiles Größe aller Data Files Liste aller Tablespace mit Größenangaben Temp-Space Größe Redo Logs Finden der größten Tabellen Top 10 größten Tabellen eines Users Data Files + Redo Logs + Temp Freier Datenbank-Platz Benutzter Datenbank-Platz Belegter und benutzter Temp-Tablespace Blockgrößen auslesen Liste Tabellen und Berechnung der Größe in Byte, MB, GB Tabellengrößen und Platzverbrauch Größen und Mengen mit zusätzlichem Tablespace-Namen Welche Objekte gehören zu einem Tablespace Größenangaben bezogen auf Partitionen Auflisten des Wachstums einzelner Tablespaces Segment-Informationen Gesamtauswertung belegter Plattenplatz und Freespace pro Tablespace und Datafiles 24 24 24 24 24 24 24 24 24 24 25 25 25 25 25 25 25 25 25 Database Files 26 Auflistung aller Dateien In welchen Datafiles liegen bestimmte Tabellen Welche Tabellen liegen in einem bestimmten Data File Single Block / Multi Block Reads Database Files mit asynchronem Lesen und Schreiben 26 26 26 26 26 Lesestatistiken / Benutzungsverhalten 26 25 Gelesene und geschriebene Blöcke 26 Security 26 26 26 26 26 26 26 26 Memory-Cache 23 Benutzer anlagen Ein Profil anlegen Eine Rolle anlegen Rollenrechte weitergeben Default-Rolle einem Benutzer zuweisen Rollen für einen Bnutzer aktivieren / deaktivieren Was machen die Benutzer gerade Tabellen in Cache legen / aus Cache entfernen Welche Tabellen liegen im Cache 23 23 Laden des DWH Query Result Cache 23 Schnelles Laden / Mengenbasiertes Laden 27 Aktivieren mit Ausnutzen Result Cache durch Hint in der Abfrage Abfragen auf im Cache befindliche Statements Result-Cache-Memory-Report Result Cache leeren 23 23 23 23 23 Parallelisierung 23 Grunsätzliches Schema für mengenbasiertes Laden Direct Path Load Insert ohne Log-Datei Sequence-Objekt anlegen Schnelles Schreiben CTAS (Create Table As Select) Schnelles Löschen von Daten 27 27 27 27 27 27 Parallelisierung aktivieren Automatisches Steuern mit Eine einzelne Tabelle auf parallel” setzen Parallelisierter Select-Zugriff Prüfen welche Art der Parallelisierung eingestellt ist Aktuelle SQL-Statements und deren Parallelisierung abfragen 23 23 23 23 23 23 Databas Link 27 Zugriff auf Remote-Oracle-Datenbank (Database Link) Verwendung 27 27 Sequence für Zähl-Felder / Schlüssel aufbauen 27 Optimizer - Statistiken sammeln 23 Defintion eines Sequence-Objektes Zugriff um den nächsten Zählerwert abzugreifen Abfragen des aktuellen Stands 27 27 27 Statistiken für eine Tabelle Statistiken für einen Index Statistiken für eine Materialized View Definition Statistiken für ein Schema Automatische Aktualisierung für ein Schema einrichten Automatisiertes Sampling Abfrage ob automatisiertes Sammel aktiviert ist Zustand/Aktualität der Statistiken abfragen Zustand von Index-Statistiken abfragen Histogramme abfragen Histogramme sammeln Abfragen der Grenzwerte der Histogram-Buckets Löschen von Statistiken Löschen von Histogrammen für einzelne Spalten Markieren von Tabellen um inkrementelles Aktualisieren zu ermöglichen 23 23 23 23 23 23 23 23 23 23 23 23 23 23 Trigger 27 Optimierung für schnelles Lesen Umgebungsinformationen auslesen Document1 23 24 23 27 Beispiel für einen Trigger (Insert,Delete,Update) 27 Text-Dateien Importieren 27 SQL Loader Beispiel für Loader Control – File External Table Beispiel 27 27 27 Directory-Objekte 27 Directory anlegen Auflisten bestehender Directory-Definitionen Logischen Directory-Name ändern Name der Daten-(CSV-)Datei ändern 27 27 27 27 Datapump 27 Datapump - Beispiel mit Steuerdatei Allgemeines Beispiel Umändern des Default-Output-Directories Datapump-Schätzung benötigter Plattenplatz Datapump Data only Import 27 27 27 27 28 Data Warehouse Technik im Fokus - Skripte in Kurzform Datapump Schema Mode Datapump Network Mode Interaktiver Modus mit CTRL C aktivierbar Datapump und External Tables 28 28 28 28 Transportable Tablespace 28 4 /41 Sessions schnell ‘killen’ 35 Schnelle Übersicht über aktuell laufende Sessions / wer / wo / was 35 Verhindern von Memory Paging 35 Herausfinden Memory und Session 35 Transportable Tablespace-Verfahren 28 ASH (Active Session History), Session + User Informationen 35 Error Logging 28 Error Logging Eindeutigkeitsprüfung ohne DML_Error_Logging 28 28 Regular Expressions 28 Regular Expression in Constraints verwenden Umgang mit einzelnen Zeichen: Bestehende Zeichengruppen 28 28 28 Größe ASH - Buffer Session Daten abfragen Die aktivsten SQLs in der letzten Stunde Die aktivsten IO-Operationen Sample-Time abfragen Aktives SQL Abfragen Übersicht über SQL-Statements in der Vergangenheit Session-Informationen 35 35 35 35 35 35 35 35 SQL-Monitoring 35 Aus- und Einschalten aller Constraints 29 CASE in SQL-Statements / Manuelles Prüfen 29 IsNumeric-Prüfung 29 IsDate-Prüfung 29 Abhängigkeiten von anderen Feldern im selben Satz 29 Satzübergfreifendes Zusammenzählen von Feldwerten (analytische Funktion) 29 Eindeutigkeitsprüfungen 29 Aggregatbildung und Bewerten von satzübergreifenden Summen 29 Foreign Key Prüfung 30 SQL Cache Kurzabfrage auf ein bestimmtes Select-Statement mit einer bestimmten Tabelle SQL-Statistik abfragen / Wurde ein Ergebniss aus dem Cache oder von der Platte gelesen? Abfrage auf v$sql_Monitor Feststellen welche Benutzer mit welchem SQL und welcher Parallelität zugegriffen haben SQLTun-Report über SQL ID aufrufen Lese-Statistiken auf einzelne Tabellen gezielt abfragen 35 SQL-Mittel beim Laden Planmanagement 36 Automatisiertes Sammeln von Plänen einschalten Parameter zur Behandlung von Plänen Nutzen der Pläne einschalten Anzeigen von Plänen in Plan_Baselines - Eingeschränkt auf einen User Betrachten eines gespeicherten Plans Evolve eines neuen Plans 36 36 36 Lese-Performance messen 37 Arbeiten ohne Constraints und mengenbasiertes SQL / Prüfen 29 30 Pivot/Unpivot Beispiele 30 Multiple Inserts /Manuelles Aufspalten von korrekten und nicht korrekten Sätzen 30 Merge-Beispiel 30 36 36 36 36 36 36 36 37 37 Flashback 30 Log-Modus / Archiv-Modus prüfen Aktuelle SCN abfragen Retention-Zeit abfragen Ändern Retention-Zeit Abfragen der letzten Logs Zurücksetzen einer Tabelle auf ältere SCN mit Flashback Beispiel Flasback 30 30 30 30 30 30 30 Table Function 31 Record / Object - Definition Definition einer Tabellen-Struktur Table-Function-Definition 31 31 31 SQL / PL/SQL - Funktionen 31 Leeres PL/SQL-Function-Template Numerische Funktionen Stringfunktionen Datum-Funktionen Bedingte Abfragen (Decode / CASE) Sonstige Funktionen Konvertierungsfunktionen Konvertierung von Datum in Zeichenkette (to_char) und umgekehrt (to_date) Zeitformat-Umwandlung Konvertierung von Zahlen in Zeichenketten (to_char) und umgekehrt (to_number) 31 31 31 31 32 32 32 Backup im Data Warehouse 32 32 Festeststellen des Log-Modus der Datenbank 39 Ein-(Aus-)schalten des Archivelog-Modus 39 Archive-Zustand anzeigen lassen 39 Wohin wird das Archive-Log geschrieben und wie groß ist die Recovery Area 39 Wie voll ist die Recovery Area aktuell 39 Recovery Area vergrößern 39 32 Verwalten des Systems / Systembeobachtung 33 Anzeigen der gesetzten Schwellwerte für Alerts Alerts abfragen Alerts abfragen (historisch) Alert-Datei-Ablage Wait Classes abfragen Wait Events Menge der Undos 33 33 33 33 33 33 33 Informationen über die Session 33 Sortvorgänge auf Platte oder im Speicher Session-Informationen 33 33 AWR (Analytic Workload Repository) 33 Einstellungen Eingestellte Intervalle Platzverbrauch AWR messen Auflistung bestehender Snapshots AWR-Snapshot manuell anlegen Snapshot-Nummern ausfindig machen AWR-Bericht erstellen 33 33 33 34 34 34 34 ADDM Informationen abfragen 34 Welche Informationen liegen vor ADDM-Analyse starten ADDM-Bericht anzeigen Relevante Dictionary Views für Alerts und Session 34 34 34 34 Tracing 34 Trace-Output-Verzeichnis Identifizierung einer zu prüfenden Session Aktivieren des SQL-Trace Deaktivieren Beispiel-Trace-Session Unleserlichen Trace-Output mit TKPROF formatieren 34 34 34 34 34 34 Session-bezogene Informationen 34 Session Daten abfragen Größe SGA und entsprechende Speicherbereiche Abfragen der idealen Memory-Ausnutzung Abfragen der SGA / Welche Objekte sind im Speicher 34 34 34 34 Document1 Abschätzen Lesegeschwindigkeit (IO-Performancen) Lesen einzelner Tabellen bei dem 37 IO Messung / calibrate IO 37 Calibrate-Status abfragen Calibrate IO abfragen 37 37 Perfstat Orion Lesestatistiken: Werden Daten genutzt? 37 37 38 39 Allgemeine Überlegungen 39 Argumente für ein DWH spezifisches Backup-Konzept Was muss gesichert werden. Wie wird gesichert 39 39 39 Archive Log 39 RMAN 39 RMAN starten An Zieldatenbank einwählen Welche Backups sind überflüssig Welche Files benötigen ein Backup Leeren Recover Area Welche Files können nicht wiederhergestellt werden Welche Sicherungen liegen vor Alle Einstellungen von RMAN Definieren eines Backup-Kanals Flashback aktivieren Verwendung der Recovery Area Prüfen, ob Flashback aktiviert ist Prüfung des benötigten Platzes Retention-Zeit Flashback Area einstellen Abfragen einer Tabelle von einem bestimmten SCN-Zustand Abfragen einer Tabelle zu einer bestimmten Zeit Tabellen mit Flashback zurückholen Datenbank mit Flashback zurücksetzen (Kommandobeispiele) Ältest mögliche Rückhol-Position feststellen 39 39 39 39 39 39 39 39 39 39 39 39 39 39 39 39 39 39 39 Anhang 40 (DWH-Administrations-Checkliste - wird aktualisiert) Testdaten erstellen 40 40 SQL-Trigger-Tabelle für Dummy-FROM-Klausel erstellen Tabelle mit laufender Nummer erstellen 40 40 Vorgehensweise bei der Erstellung der Demo-Umgebung Hilfsprozeduren zu den Beispielmodellen 40 40 Zeitdimension Daten in der Faktentabelle F_UMSATZ 40 41 Data Warehouse Technik im Fokus - Skripte in Kurzform Zu dieser Skriptesammlung Wem 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 ExpertenSkripten, 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 DatenbankFeatures 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-Hinweise Für diesen Punkt relevante Fragestellungen Fü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/AutoIndex2.2.4/index.php?dir=downloads/Kurs_Materialien_und_DWH_TIF_und _Angebote/&file=Beispieldaten_Star_Skriptesammlung.zip Eine Vorgehensweise für die Installation finden Sie am Ende von dieser Skriptesammlung. Document1 5 /41 Data Warehouse Technik im Fokus - Skripte in Kurzform 6 /41 Allgemeine Hilfen Metadaten-DDL für Objekte aus dem Dictionary generieren lassen Einrichten und Einstellen einer Testumgebung select dbms_metadata.get_ddl('INDEX','IDX_BILDUNGSGRUPPE_BM') FROM dual SELECT dbms_metadata.get_ddl('TABLE','BESTELLUNG_PART_RANGE_HASH') FROM dual; 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öschen PURGE RECYCLEBIN Oder gleich arbeiten mit [DROP TABLE tab_name PURGE;] Ausschalten der Recyclebin-Funktion alter session set recyclebin=off; alter system flush shared_pool; alter system flush BUFFER_CACHE; alter system flush GLOBAL 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 auslesen column comments format a50 select * from dict where TABLE_NAME like 'V$SESSION%'; TABLE_NAME COMMENTS ----------------------------------------------------------V$SESSION Synonym for V_$SESSION V$SESSION_BLOCKERS Synonym for V_$SESSION_BLOCKERS V$SESSION_CONNECT_INFO Synonym for V_$SESSION_CONNECT_INFO V$SESSION_CURSOR_CACHE Synonym for V_$SESSION_CURSOR_CACHE Ändern der Berücksichtigung Groß-/Kleinschreibweise bei Logon alter system set sec_case_sensitive_logon=FALSE; SQL Plus user/passwort@ORACLE_SID -> geht über Listener user/passwort -> ohne Listener sys/passwort@ORACLE_SID as sysdba sys/passwort as sysdba “/ as sysdba” -- listet alle SQLPLUS-Befehle auf Weitere Hilfe mit Help [commando] Document1 SQL> help get @@ APPEND BREAK CLEAR CONNECT DEL EDIT GET INPUT PAUSE QUIT REPFOOTER RESERVED WORDS (PL/SQL) SET SPOOL STARTUP TTITLE WHENEVER OSERROR emctl start dbconsole Wenn es mal klemmt Bei Fehlern wie: The OracleDBConsole[ServiceName] service could not be started. A service specific error occurred: 2 Hilft oft nur das Neukonfigurieren -Linux Dbstart -Windows set ORACLE_SID=orcl set ORACLE_HOME=D:\ora %oracle_home%\BIN\oradim -STARTUP -SID orcl -SYSPWD sys Entweder Beispiel @ ACCEPT ATTRIBUTE CHANGE COMPUTE DEFINE DISCONNECT EXIT HOST PASSWORD PROMPT REMARK RESERVED WORDS (SQL) SAVE SHUTDOWN START TIMING VARIABLE XQUERY Starten der Console Starten der Datenbank format a25 format a25 Systemparameter abfragen Help Index https://192.168.1.14:1158/em https://hostname:1158/em Datenbank, Listener und BS-Variablen SELECT-Ausgabeformatierung sqlplus sqlplus sqlplus sqlplus sqlplus Aufruf im Browser z. B. mit emca -deconfig dbcontrol db emca -config dbcontrol db Speicher leeren column feld column feld Enterprise Manager / ARCHIVE LOG BTITLE COLUMN COPY DESCRIBE EXECUTE HELP LIST PRINT RECOVER REPHEADER RUN SHOW SQLPLUS STORE UNDEFINE WHENEVER SQLERROR Listener starten und Status abfragen Lsnrctl start Lsnrctl status Erreichbarkeit einer Datenbank über SID-Abfrage Tnsping sid-name Data Warehouse Technik im Fokus - Skripte in Kurzform 7 /41 Beispielumgebung In 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 ERModellierung. Aus dieser Informations-Grundmenge lässt sich das weiter unten dargestllte Star-Schema ableiten. Vergleichs-Demo-Umgebung Steckbrief Skript –Tabellen OLTP-System CREATE TABLE BESTELLUNG ( BESTELLNR KUNDENNR ORTNR BESTELLDATUM NUMBER, NUMBER, NUMBER, DATE); CREATE TABLE kunde ( KUNDENNR KUNDENNAME BERUFSGRUPPE SEGMENT NUMBER(10) , VARCHAR2(20) , VARCHAR2(20) , NUMBER(10)); CREATE TABLE BESTELLPOSITION ( BESTELLNR POSITIONSNR MENGE ARTIKELNR number(4) , number(4) , NUMBER(4) , NUMBER(10)); CREATE TABLE ARTIKEL ( ARTIKEL_ID ARTIKEL_NAME GRUPPE_NR NUMBER(3), VARCHAR2(50), NUMBER(3); CREATE TABLE ARTIKEL_GRUPPEN ( GRUPPE_NR GRUPPE_NAME SPARTE_NR NUMBER(3), VARCHAR2(50), NUMBER(3); CREATE TABLE ARTIKEL_SPARTEN ( SPARTE_NR SPARTE_NAME NUMBER(3), 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 FaktenTabelle (F_UMSATZ) verfügt über jeweils einen Bitmap-Index auf jedem Foreign-Key-Feld. Ein Foreign-Key-Constraint muss nicht definiert sein. Document1 (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 10 D_KUNDE 28 .000224 1029 10 D_REGION 73 .000584 7202 10 D_ZEIT 43 .000344 5844 10 D_VERTRIEBSKANAL 5 .00004 7 10 F_UMSATZ 276890 2.21512 51200000 10 Eigenschaft des Demosystems Compression Bitmap auf Fakten-FK-Felder Unique Key PK-Felder der Dimensionen Partitioning Results Cache Null-Werte Server-CPU-Kerne Server-RAM Server-BS Oracle-DB Storage Durchsatz Nein(Ja) Ja Ja Nein(Ja) Nein Keine 4 16 GB Linux OEL 6 11.2.0.3 (EE) Direkt ~180 MB/sec Skript Fakten-Tabelle CREATE TABLE F_UMSATZ( ARTIKEL_ID KUNDEN_ID ZEIT_ID REGION_ID KANAL_ID UMSATZ MENGE UMSATZ_GESAMT ); NUMBER(10), NUMBER(10), DATE, NUMBER(10), NUMBER(10), NUMBER(10), NUMBER(10), NUMBER(10) Skript Kunden-Dimension CREATE TABLE KUNDENNR GESCHLECHT VORNAME NACHNAME TITEL ANREDE GEBDAT BRANCHE WOHNART KUNDENART BILDUNG ANZ_KINDER EINKOMMENSGRUPPE ORTNR BERUFSGRUPPE STATUS STRASSE TELEFON TELEFAX KONTAKTPERSON FIRMENRABATT BERUFSGRUPPEN_NR BILDUNGS_NR EINKOMMENS_NR WOHNART_NR HAUSNUMMER PLZ ORT KUNDENKARTE ZAHLUNGSZIEL_TAGE KUNDEN_ID D_KUNDE ( NUMBER, VARCHAR2(10), VARCHAR2(50), VARCHAR2(50), VARCHAR2(20), VARCHAR2(10), DATE, VARCHAR2(30), VARCHAR2(30), NUMBER, VARCHAR2(30), VARCHAR2(5), VARCHAR2(30), NUMBER, VARCHAR2(30), VARCHAR2(1), VARCHAR2(100), VARCHAR2(30), VARCHAR2(30), NUMBER, VARCHAR2(10), VARCHAR2(1), VARCHAR2(1), VARCHAR2(1), VARCHAR2(1), NUMBER(10,0), VARCHAR2(10), VARCHAR2(100), NUMBER, VARCHAR2(4000), NUMBER Data Warehouse Technik im Fokus - Skripte in Kurzform TOTAL TOTAL_NR VARCHAR2(20), NUMBER); Skript Artikel-Dimension CREATE TABLE D_ARTIKEL ( ARTIKEL_NAME ARTIKEL_ID GRUPPE_NR GRUPPE_NAME SPARTE_NAME SPARTE_NR ) ; VARCHAR2(50), NUMBER(3,0), NUMBER(3,0), VARCHAR2(50), VARCHAR2(50), NUMBER(3,0), Skript Regionen-Dimension CREATE TABLE D_REGION ORTNR ORT KREISNR KREIS LAND LANDNR REGION REGIONNR REGION_ID ) ; ( NUMBER(8,0), VARCHAR2(50), NUMBER(8,0), VARCHAR2(50), VARCHAR2(50), NUMBER(8,0), VARCHAR2(50), NUMBER(8,0), NUMBER(22,0) Skript Zeit-Dimension CREATE TABLE D_ZEIT ZEIT_ID DATUM_ID TAG_DES_MONATS TAG_DES_JAHRES WOCHE_DES_JAHRES MONATS_NUMMER MONAT_DESC QUARTALS_NUMMER JAHR_NUMMER ) ; DATE , NUMBER(4), NUMBER(2,0), NUMBER(3,0) , NUMBER(2,0) , NUMBER(2,0) , VARCHAR2(9) , NUMBER(1,0) , NUMBER(4,0) CREATE 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] "D_KUNDE_PK" ON "D_KUNDE" ("KUNDEN_ID"); "D_ARTIKEL_PK" ON "D_ARTIKEL" "D_REGION_PK" ON "D_REGION" ("REGION_ID"); "D_ZEIT_PK" ON "D_ZEIT" ("ZEIT_ID"); "D_KANAL_PK" ON "D_VERTRIEBSKANAL" Skript Bitmap-Indexe auf FK-Felderder Fakten-Tabelle F_UMSATZ CREATE CREATE CREATE CREATE CREATE bitmap bitmap bitmap bitmap bitmap index index index index index idx_ARTIKEL_ID_BM on F_UMSATZ(ARTIKEL_ID); idx_ZEIT_ID_BM on F_UMSATZ(ZEIT_ID); idx_KUNDEN_ID_BM on F_UMSATZ(KUNDEN_ID); idx_REGION_ID_BM on F_UMSATZ(REGION_ID); idx_KANAL_ID_BM on F_UMSATZ(KANAL_ID); Skript Drop Bitmap Indexe DROP DROP DROP DROP DROP INDEX INDEX INDEX INDEX INDEX idx_ARTIKEL_ID_BM; idx_ZEIT_ID_BM; idx_KUNDEN_ID_BM; idx_REGION_ID_BM; 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_ARTIKEL DROP 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_nr HIERARCHY 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_Region DROP 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.regionnr Document1 HIERARCHY Ort Kreis Land Region ATTRIBUTE ATTRIBUTE ATTRIBUTE ATTRIBUTE h_region ( CHILD OF CHILD OF CHILD OF ) Ort DETERMINES Kreis DETERMINES Land DETERMINES Region DETERMINES (d_region.ort, d_region.ortnr) (d_region.kreis) (d_region.land) (d_region.region); Skript Dimensional Table D_Zeit DROP 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_Nummer HIERARCHY 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 Vertriebskanal-Dimension CREATE UNIQUE INDEX CREATE UNIQUE INDEX ("ARTIKEL_ID"); CREATE UNIQUE INDEX CREATE UNIQUE INDEX CREATE UNIQUE INDEX ("KANAL_ID"); 8 /41 Skript Dimensional Table D_Kunde DROP 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.Ortnr HIERARCHY 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) Data Warehouse Technik im Fokus - Skripte in Kurzform Beispielabfragen auf das Beispiel-Star Schema Beispielabfrage 1a Generische Abfrage auf vorgenanntes Star-Schema SELECT 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_id AND U.Kanal_ID = V.Kanal_ID GROUP by a.sparte_name, z.Jahr_Nummer, r.land, v.vertriebskanal ; Parallel Sekunden P0 39,1 P2 22,5 P4 13,4 P8 16,8 P16 20 Beispielabfrage 1b Einfache Abfrage Summe Umsatz an einem Tag SELECT /*+ no cache */ sum(UMSATZ) Umsatz FROM F_UMSATZ WHERE zeit_ID = to_date('10.03.2011','DD.MM.YYYY') UMSATZ ---------13883392 Parallel P0 Sekunden 2,6 Beispielabfrage 1c Summe Umsatz an einem Tag und Vertriebskanal SELECT /*+ no cache */ sum(UMSATZ) Umsatz FROM F_UMSATZ U, D_vertriebskanal V WHERE u.kanal_id = v.kanal_id and zeit_ID = to_date('10.03.2011','DD.MM.YYYY') and V.vertriebskanal = 'Shop'; Parallel P0 Sekunden 2,5 Beispielabfrage 2 Einschränkungen über alle Dimensionen SELECT 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_ID AND U.Zeit_ID = Z.Zeit_ID AND U.REGION_ID = R.Region_ID AND U.Artikel_ID = A.Artikel_ID AND U.Kanal_ID = V.Kanal_ID AND Z.JAHR_NUMMER = 2011 AND 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_nummer Order by Z.Quartals_nummer; Parallel Sekunden P0 2,6 P2 2,6 P4 4,2 P8 4,6 P16 4,2 Beispielabfrage3 (Ein-/Ausschalten Where-Argumente /Test Star Query SELECT 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 AND U.REGION_ID = R.REGION_ID AND U.artikel_id = a.artikel_id and z.jahr_nummer = 2010 and R.Land = 'Bayern' GROUP by a.sparte_name, z.Jahr_Nummer, r.land; Document1 9 /41 Parallel P0 P2 P4 P8 P16 Sekunden 11,3 6,4 3,2 5,8 5,3 Beispielabfrage 4: Top 3 Länder bezogen auf Einzelverkäufe select * 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 13674496 Bayern 13261312 Schleswig Holstein 8107008 Parallel P0 P2 P4 P8 P16 Sekunden 12 10,5 3,7 3,8 4,1 Beispielabfrage 5: Umsatz nach Quartalen select sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr, z.Quartals_nummer Quartal from f_umsatz u, d_zeit z where z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011 group by z.Jahr_nummer,z.Quartals_nummer order by z.Jahr_nummer,z.Quartals_nummer UMSATZ JAHR QUARTAL ---------- ---------- ---------1579442176 2006 1 1558222336 2006 2 1522254336 2006 3 1646631936 2006 4 1533571072 2007 1 1580020224 2007 2 ~~~ Parallel P0 P2 P4 P8 P16 Sekunden 10,1 7,1 2,6 4,7 2,5 Beispielabfrage 6: Nach Umsatzstärke sortierte Quartals-Rangfolge select sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr ,z.Quartals_nummer Quartal, RANK() OVER (PARTITION by z.Jahr_nummer ORDER sum(U.umsatz) ASC ) AS Rangfolge from f_umsatz u, d_zeit z where z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011 group by z.Jahr_nummer,z.Quartals_nummer order by z.Jahr_nummer,Rangfolge; UMSATZ JAHR QUARTAL RANGFOLGE ---------- ---------- ---------- ---------1522254336 2006 3 1 1558222336 2006 2 2 1579442176 2006 1 3 1646631936 2006 4 4 1533571072 2007 1 1 1563792384 2007 3 2 1566097920 2007 4 3 1580020224 2007 2 4 1521408512 2008 2 1 1551963136 2008 1 2 1610153472 2008 3 3 ~~~ Parallel P0 P2 P4 P8 P16 Sekunden 9,0 5,1 2,6 2,7 2,8 Beispielabfrage 7:: Das umsatzstärkste Quartal pro Jahr select * from BY Data Warehouse Technik im Fokus - Skripte in Kurzform (select sum(u.umsatz) Umsatz, z.Jahr_nummer Jahr ,z.Quartals_nummer "Top-Quartal", RANK() OVER (PARTITION by z.Jahr_nummer ORDER sum(U.umsatz) ASC ) AS Rangfolge from f_umsatz u, d_zeit z where z.zeit_id = u.zeit_id and z.Jahr_nummer between 2006 and 2011 group by z.Jahr_nummer,z.Quartals_nummer) where Rangfolge = 1 order by Jahr; Innenkabel 3 Adern 2 BY UMSATZ JAHR Top-Quartal RANGFOLGE ---------- ---------- ----------- ---------1522254336 2006 3 1 1533571072 2007 1 1 1521408512 2008 2 1 1488578048 2009 1 1 1467155968 2010 4 1 1528411648 2011 1 1 Parallel P0 P2 P4 P8 P16 Sekunden 9,6 5,1 2,2 2,8 1,8 Beispielabfrage 8: Stärkster Umsatzmonat pro Bundesland SELECT FROM * (SELECT land Bundesland, monat_desc Monat, jahr_nummer Jahr, sum(umsatz) as Umsatz, RANK() OVER(PARTITION BY r.land ORDER BY sum(u.umsatz) DESC) AS rangfolge FROM d_zeit z, d_region r, f_umsatz u WHERE z.zeit_id = u.zeit_id AND r.region_id = u.region_id AND z.jahr_nummer = '2010' GROUP BY r.land, z.monat_desc, jahr_nummer ORDER BY jahr_nummer ) WHERE rangfolge < 2 order by Bundesland; BUNDESLAND -------------------Baden Wuerttemberg Bayern Berlin Bremen Hamburg Hessen Niedersachsen Nordrhein Westfalen Rheinland Pfalz Saarland Schleswig Holstein Parallel P0 P2 P4 P8 P16 MONAT JAHR UMSATZ RANGFOLGE -------------- ---------- ---------October 2010 59450368 1 July 2010 163219968 1 December 2010 807424 1 June 2010 75264 1 June 2010 1023488 1 November 2010 36823040 1 December 2010 85943808 1 February 2010 26347520 1 January 2010 157769728 1 August 2010 5460992 1 May 2010 87066624 1 Sekunden 6,2 2,8 3,4 1,6 1,7 Beispielabfrage9: Top 10 Artikel bezogen auf Menge / Rank /Subquery SELECT * FROM (SELECT Artikel_Name as Artikel, sum(U.menge) AS Gesamtmenge, RANK() OVER (ORDER BY sum(U.menge) DESC ) AS Rangfolge from F_umsatz U, D_Artikel A WHERE U.artikel_id = a.artikel_id group by a.artikel_name) WHERE rownum < 11; ARTIKEL GESAMTMENGE RANGFOLGE ----------------------------- ----------- ---------Wasserhahn 23144960 1 Muffe 18mm 22593536 2 Aussenkabel 4 Adern 2 22431744 3 Luesterklemmen 2 22123008 4 Topf_Guss_Klein 21908480 5 Schubkarre 21797888 6 Gluebirne Halogen 200 Watt 21556224 7 Aussenkabel 3 Adern 1,5 21442048 8 Aussenkabel 5 Adern 2 21420032 9 Document1 10 /41 21405184 Parallel P0 P2 P4 P8 P16 10 Sekunden 16,8 8,5 4,3 4,4 4,3 Beispielabfrage9a: Top 10 Artikel bezogen auf Menge / Rank /Subquery SELECT * FROM (SELECT Gruppe_Name , sum(U.menge) AS Gesamtmenge, RANK() OVER (ORDER BY sum(U.menge) DESC ) AS Rangfolge 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 Handtuchhalter Wandspiegel 50x80 Fahrradhalter Bindeseil Gluebirne Kerze 40 Watt Zusatzlicht_Front Innenkabel 3 Adern 1,5 Aussenkabel 5 Adern 1,5 Holzschauben_2Kg Rheinland Bayern Rheinland Rheinland Bayern Rheinland Bayern Bayern Bayern Rheinland Parallel P0 P2 P4 P8 P16 Pfalz 2010 2010 Pfalz 2010 Pfalz 2010 2010 Pfalz 2010 2010 2010 2010 Pfalz 2010 25700352 25004544 24927744 24101376 23774208 23672832 22036480 21995520 21216768 21105152 570368 756736 534016 713216 559104 470016 500224 480768 419840 573952 45.05 1 33.04 2 46.67 3 33.75 4 42.52 5 50.36 6 44.05 7 45.75 8 50.53 9 36.77 10 Sekunden 9,8 3,3 1,7 1,9 1,8 Beispielabfrage 11: - Top 3 Produkte je Produktgruppe sortiert nach Rangfolge SELECT 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 -----------------------Wasserhahn Wandspiegel 50x60 Waschbecken 50x80 Elektrohobel Schleifmaschine 600 Watt Bohrmaschine 600 Watt Aussenkabel 4 Adern 2 Gluebirne Kerze 25 Watt Luesterklemmen 2 Hacke_5Kg Schubkarre Wasservorratsbehaelter PROD_GRP UMSATZ RANGFOLGE ------------------ ---------- -Bad_Sanitaer 881696768 1 Bad_Sanitaer 853362688 2 Bad_Sanitaer 837804032 3 Elektro-Werkzeuge 813489152 1 Elektro-Werkzeuge 793825792 2 Elektro-Werkzeuge 782154752 3 Elektroartikel 880760320 1 Elektroartikel 858101760 2 Elektroartikel 848047616 3 Gartenbedarf 888975872 1 Gartenbedarf 856668160 2 Gartenbedarf 844909056 3 ~~~ Parallel P0 P2 P4 P8 P16 Sekunden 19 10,2 5,3 6,9 8,2 Beispielabfrage 12: Die Top-Produktgruppe pro Bundesland SELECT * FROM (SELECT R.Land Bundesland, gruppe_name Prod_Grp, sum(U.Umsatz) AS Umsatz, Data Warehouse Technik im Fokus - Skripte in Kurzform Rank() OVER (PARTITION by R.Land ORDER 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 -------------------Baden Wuerttemberg Bayern Berlin Bremen Hamburg Hessen Niedersachsen Nordrhein Westfalen Rheinland Pfalz Saarland Schleswig Holstein Parallel P0 P2 P4 P8 P16 BY PROD_GRP UMSATZ RANGFOLGE --------------- ---------- --Bad_Sanitaer 2517655040 1 Bad_Sanitaer 6928505856 1 KFZ-Zubehoer 5216256 1 Haushaltswaren 7249920 1 Elektroartikel 11321344 1 Bad_Sanitaer 1473691648 1 Bad_Sanitaer 3481312768 1 Bad_Sanitaer 1065768960 1 Bad_Sanitaer 7339374592 1 Bad_Sanitaer 97358848 1 Bad_Sanitaer 4261867520 1 Sekunden 25,3 12,2 6,3 6,7 7,2 SELECT * 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; BUNDESLAND UMSATZ MAX_UMS_LAND -------------------------- -----------Rheinland Pfalz 183886336 183886336 Rheinland Pfalz 34775552 34775552 Rheinland Pfalz 123813376 123813376 Rheinland Pfalz 87101952 87101952 Rheinland Pfalz 92366336 92366336 Rheinland Pfalz 85024256 85024256 Bayern 83915776 83915776 Sekunden 33.1 13.0 6,5 6,8 8,7 Beispielabfrage 14: Die Top und Flop Produktgruppe pro Bundesland SELECT * FROM (SELECT R.Land Bundesland, gruppe_name Prod_Grp, sum(U.Umsatz) AS Umsatz, Rank() OVER (PARTITION by R.Land ORDER 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 ------------------Baden Wuerttemberg Baden Wuerttemberg Bayern Bayern Berlin Berlin Bremen BY PROD_GRP UMSATZ RANGFOLGE -------------------- ---------- ----Bad_Sanitaer 2517655040 1 Elektro-Werkzeuge 452903936 7 Bad_Sanitaer 6928505856 1 Elektro-Werkzeuge 1380406784 7 KFZ-Zubehoer 5216256 1 Heimwerker 2012160 7 Haushaltswaren 7249920 1 ~~~ Parallel P0 P2 P4 P8 P16 Sekunden 24,5 12.8 6,2 9,6 6,2 Beispielabfrage 15: Die Top 2 Produktgruppen pro Bundesland 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_folge FROM f_umsatz U, d_artikel A , d_region r Document1 WHERE U.artikel_id = a.artikel_id U.region_id =r.region_id GROUP by r.land,a.gruppe_name ORDER by r.land) WHERE Top_folge < 3; Beispielabfrage 13: Land mit dem stärksten Umsatz je Produktgruppe PROD_GRP -----------------Bad_Sanitaer Elektro-Werkzeuge Elektroartikel Gartenbedarf Haushaltswaren Heimwerker KFZ-Zubehoer Parallel P0 P2 P4 P8 P16 11 /41 LAND -------------------Baden Wuerttemberg Baden Wuerttemberg Bayern Bayern Berlin Berlin Bremen Bremen Hamburg PROD_GRP ---------------Bad_Sanitaer Elektroartikel Bad_Sanitaer Elektroartikel KFZ-Zubehoer Bad_Sanitaer Haushaltswaren Gartenbedarf Elektroartikel and TOP_UMSATZ TOP_FOLGE ---------- --2517655040 1 1634190848 2 6928505856 1 4916009472 2 5216256 1 5194240 2 7249920 1 7211008 2 11321344 1 ~~~ Parallel P0 P2 P4 P8 P16 Sekunden 24,8 13,4 6,6 8,9 9,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_folge 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,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_folge 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,a.gruppe_name ORDER by r.land) WHERE Bottom_folge < 3) Bottom where top.Bundesland = bottom.Bundesland and top.Top_folge = bottom.Bottom_folge order by Top.Bundesland; BUNDESLAND PROD_GRP TOP UMSATZ TOP Folge PROD_GRP BOTTOM BOTTOM UMSATZ FOLGE --------------------- ---------------- ---------- -- -------------------------------- --- Baden Wuerttemberg Baden Wuerttemberg Bayern Bayern Berlin Berlin Bremen Bremen Hamburg Hamburg Bad_Sanitaer Elektroartikel Bad_Sanitaer Elektroartikel Bad_Sanitaer KFZ-Zubehoer Haushaltswaren Gartenbedarf Heimwerker Elektroartikel 2517655040 1634190848 6928505856 4916009472 5194240 5216256 7249920 7211008 7856640 11321344 1 2 1 2 2 1 1 2 2 1 Elektro-Werkzeuge Heimwerker Elektro-Werkzeuge Heimwerker Elektroartikel Heimwerker Bad_Sanitaer Elektro-Werkzeuge Gartenbedarf Bad_Sanitaer 452903936 1180500480 1380406784 3230448640 3089408 2012160 1293824 1307136 1458688 1054720 1 2 1 2 2 1 1 2 2 1 ~~~ Parallel P0 P2 P4 P8 P16 Sekunden 50,1 25,8 13,1 16,9 14,4 Beispielabfrage 17: Über Quartale kumulierte Umsätze pro Kunde SELECT 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_Summe FROM d_kunde K, f_Umsatz U, d_zeit Z WHERE k.kunden_id = u.kunden_id AND to_char(Z.zeit_id) = to_char(u.zeit_id) GROUP by K.NachName, z.jahr_nummer, z.quartals_nummer; Parallel Sekunden P0 75,2 P2 31,7 P4 15,3 P8 23,7 P16 13,8 Beispielabfrage 18: Über Quartale kumulierte Umsätze pro Kunde (Jahr + Kreis) SELECT k.nachName,r.kreis, z.jahr_nummer, Data Warehouse Technik im Fokus - Skripte in Kurzform 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_Summe FROM d_kunde K, f_Umsatz U, d_zeit Z, d_region r WHERE k.kunden_id = u.kunden_id AND U.zeit_id = z.zeit_id AND R.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; Parallel P0 P2 P4 P8 P16 Sekunden 2,1 1,6 1,2 1,5 1,2 Beispielabfrage 19: Wieviel Prozent des Gesamtumsatzes pro Land machen die 3 Top Produkte aus select 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_Prozent from (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 r WHERE U.artikel_id = a.artikel_id and U.region_id = r.region_id GROUP by r.land, a.artikel_name ORDER by r.land) WHERE Rangfolge < 4 group by land) a, (SELECT land, sum(umsatz) Gesamt_pro_Land from f_umsatz U, d_region r where U.region_id = r.region_id group by land) b where a.land = b.land; LAND TOP_3_IN_MILL GESAMT_PRO_LAND_IN_MILL ANTEIL_PROZENT ------------------- ------------- ----------------------- -------------Hamburg 11.45 39.26 29.18 Niedersachsen 362.68 12827.92 2.83 Schleswig Holstein 491.98 16262.01 3.03 Nordrhein Westfalen 152.71 4155.6 3.67 Berlin 5.68 28.19 20.13 Bremen 5.53 28.58 19.35 Baden Wuerttemberg 280.01 9413.63 2.97 Rheinland Pfalz 800.82 27477.07 2.91 Saarland 26.04 389.81 6.68 Bayern 739.02 26510.6 2.79 Hessen 180.83 5314.5 3.4 Parallel P0 P2 P4 P8 P16 Sekunden 38,2 19,0 9,8 19,1 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 U WHERE k.kunden_id = u.kunden_id GROUP by K.nachName), (SELECT sum(u.umsatz) as Gesamt_Umsatz FROM f_Umsatz U) GROUP by anteil,Gesamt_umsatz; Parallel Sekunden P0 19,3 P2 10,6 P4 6,9 P8 7,2 P16 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_AVG FROM d_kunde K, F_Umsatz U, D_zeit Z WHERE k.kunden_id = u.kunden_id AND Document1 12 /41 Z.zeit_id = u.zeit_id AND Z.Jahr_Nummer in (2011,2010) AND k.nachname = 'Bauer' GROUP by K.nachName, z.Jahr_Nummer, z.Monats_Nummer ORDER by z.Jahr_Nummer, z.Monats_Nummer; Parallel Sekunden P0 1,2 P2 1,0 P4 0,5 P8 2,8 P16 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 FROM d_artikel a, f_umsatz U, d_region R WHERE U.artikel_id = a.artikel_id AND R.Region_id = U.Region_id AND Land = 'Schleswig Holstein' GROUP by cube (gruppe_name,Region,land); Parallel Sekunden P0 8,6 P2 4,4 P4 2,1 P8 2,3 P16 2,1 Beispielabfrage23 Vergleiche Umsätze mit Vorjahreszeitraum SELECT 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 Z WHERE k.kunden_id = u.kunden_id AND Z.zeit_id = u.zeit_id AND z.Jahr_nummer in (2010,2011) AND k.nachname = 'Bauer' group by K.nachName, z.Jahr_Nummer, z.Monats_Nummer order by z.Jahr_Nummer, z.Monats_Nummer; Parallel P0 P2 P4 P8 P16 Sekunden 1,28 1,0 2,6 0,8 2,1 Beispielabfrage 24 Year-To-Date. Kumulierung der Monatsumsätze von Jahresbeginn an select 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 A WHERE U.artikel_id = a.artikel_id GROUP by ROLLUP (a.gruppe_name,a.artikel_name) ORDER by a.gruppe_name Beispielabfrage 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 , Data Warehouse Technik im Fokus - Skripte in Kurzform 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 grouping_id FROM f_umsatz U, d_artikel A WHERE 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 OR GROUPING(Sparte_name) = 1 ORDER by ) AS GROUPING(Artikel_name),GROUPING(gruppe_name),GROUPING(Sparte_n ame) Document1 13 /41 Data Warehouse Technik im Fokus - Skripte in Kurzform Tabellen Allgemeiner Umgang mit Tabellen Relevante Fragestellungen Logging / Nologging -> möglichst umgehen und ausschalten Grössen und Mengen Storage-Verhalten von Tabellen (PCTFREE) -> möglichst 0 -> möglichst viel in einem Block speichern Temporäre Tabellen im Rahmen des ETL-Prozesses (CTAS) Logging/Nologging-Modus für eine einzelne Tabelle ein-/ausschalten alter table wh_transaktionen nologging; Allgemeine Informationen 14 /41 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_bytes from user_segments where t.TABLE_NAME and e.SEGMENT_NAME and t.TABLE_NAME = s, user_tables t, user_extents e = s.segment_name = s.SEGMENT_NAME 'F_UMSATZ'; Relevante Dictionary Views für Tabellen USER_TAB_STATISTICS USER_TAB_PARTITIONS user_tab_columns user_tablespaces USER_TABLES SYS.V$OBJECT_USAGE User_extents User_segments select table_name,blocks,num_rows,logging from user_tables; Tabellengrößen und Platzverbrauch select 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 etc select 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 ----------F_UMSATZ F_UMSATZ F_UMSATZ F_UMSATZ F_UMSATZ F_UMSATZ F_UMSATZ F_UMSATZ COLUMN_NAME -------------ARTIKEL_ID KUNDEN_ID ZEIT_ID REGION_ID KANAL_ID UMSATZ MENGE UMSATZ_GESAMT TYPE NUM_NULLS NUM_DISTINCT USE ------------- ---------- --------NUMBER 0 129 NO NUMBER 0 1031 NO DATE 0 6001 NO NUMBER 0 7020 NO NUMBER 0 7 NO NUMBER 0 4000 NO NUMBER 0 100 NO NUMBER 0 61832 NO Anlegen einer leeren Tabelle mit bestimmter Struktur create 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 ändern Alter Table F_UMSATZ rename column ALTER_NAME to NEUER_NAME; Ungenutzten Platz freigeben und komprimieren alter table F_UMSATZ enable row movement; alter table F_UMSATZ shrink space cascade; Parallel Sekunden P2 480 (8 Minuten) create table f_umsatz_tmp pctfree 0 as select * from f_Umsatz; Parallel P2 Sekunden 33 create table F_UMSATZ_comp compress as select * from F_UMSATZ; Parallel Sekunden P2 99,7 Ergebnisse (nach Aktualisierung der Statistiken): TABLE_NAME PCT_FREE BLOCKS (1)F_UMSATZ 10 276890 (2)F_UMSATZ 10 276045 (3)F_UMSATZ_TMP 0 249137 (4)F_UMSATZ_COMP 0 235724 GB NUM_ROWS COMPRESS EMPTY_BLOCKS 2.21512 51200000 DISABLED 0 2.20836 51200000 DISABLED 0 1.993096 51200000 DISABLED 0 1.885792 51200000 ENABLED 0 Zu (1) F_umsatz vor alter … shrink Zu (2) F_umsatz nach alter shrink -> 0,3% Ersparnis Zu (3) Kopie von F_Umsatz mit PCT_FREE = 0 -> 9,9 % Ersparnis Zu (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 auslesen select region_id, dbms_rowid.rowid_block_number(rowid) blk_num from d_region order by blk_num; Tablespace mit Datafile anlegen CREATE 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 auslesen select 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, Document1 Umgang mit Constraints Abfragen auf bestehende Constraints im aktuellen Schema select constraint_name,CONSTRAINT_TYPE,TABLE_NAME,STATUS user_constraints; from Status-Werte in der Abfrage können sein C (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 Constraints alter 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 Views user_constraints Compression Relevante Fragestellungen Unterschied Advanced Compression / Standard Compression Erzeugung einer komprimierten Tabelle Ursachen der unterschiedlich hohen Komprimierungsrate Selektivität der Columns abfragen (Siehe auch Hinweise und ausgeführtes Beispiel unter dem Abschnitt Tabellen) Anlegen einer komprimierten Tabelle CREATE TABLE tab_name(…) COMPRESS create table F_UMSATZ_comp compress F_UMSATZ; as select * from Komprimieren eines Tablespace CREATE TABLESPACE ... COMPRESS; Prüfen, ob Tabellen komprimierte sind select table_name, compression from user_tables order by table_name; Tabellen und Partitionen komprimieren ALTER TABLE MOVE COMPRESS ALTER TABLE MOVE PARTITION COMPRESS Komprimierte Tabelle aus seiner unkomprimierten erstellen create table bestellung_comp compress as select * from bestellung; Komprimierungsgrad für bestimmte Tabellen auslesen select 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 Compression user_tables Data Warehouse Technik im Fokus - Skripte in Kurzform 15 /41 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')), Partitioning PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')), PARTITION next_month VALUES LESS THAN (MAXVALUE)); Relevante Fragestellungen Skript zum schnellen Erstellen der Testtabelle aus F_UMSATZ Einsatzgründe für Partitioning (Verwaltung + Performance) Partition Key Partitioning-Varianten Maxvalue-Funktion Sub-Partitioning Verteilung von Partitions auf Datafiles Partition-Struktur-Abfragen Verwaltung 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. Einschrä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 PARTITION PARTITION PARTITION dec11 VALUES LESS jan12 VALUES LESS feb12 VALUES LESS next_month VALUES THAN THAN THAN LESS (TO_DATE('2012-01-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, (TO_DATE('2012-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, THAN (MAXVALUE) TABLESPACE DWH_SPINDEL); Beispielabfrage mit Einschränkung auf Zeit SELECT sum(UMSATZ) Umsatz FROM F_UMSATZ_range WHERE zeit_ID = to_date('10.03.2011','DD.MM.YYYY') UMSATZ ---------13883392 Parallel Sekunden P0 ohne Range-Partitioning 3,38 P0 mit Range-Partitioning 0,1 MAXVALUE zum Aufnehmen von Werten ohne Zuordnung CREATE 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 Alphabet CREATE TABLE kunde_part_range_alpha ( kundennummer NUMBER, vorname VARCHAR2(20), kundenname VARCHAR2(40) ) PARTITION BY RANGE (kundenname) ( PARTITION kunde_ae VALUES LESS THAN part_range1, PARTITION kunde_fl VALUES LESS THAN part_range2, PARTITION kunde_mr VALUES LESS THAN part_range3, PARTITION kunde_sz VALUES LESS THAN part_range4 ); ('F%') TABLESPACE ('M%') TABLESPACE ('S%') TABLESPACE (MAXVALUE) TABLESPACE Erstellen Hash Partitioned Table au seiner anderen 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-Varianten Skript Beispiel Range Partitioning Beispielskript F_UMSATZ_RANGE CREATE 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 Document1 mar10 apr10 mai10 jun10 jul10 aug10 sep10 oct10 nov10 dec10 jan11 feb11 mar11 apr11 mai11 jun11 jul11 aug11 sep11 VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN Allgemeines Hash-Partitioning-Beispiel CREATE 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-Zuordnung CREATE CREATE CREATE CREATE (TO_DATE('2010-03- 01','SYYYY-MM-DD')), PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION Create table F_Umsatz_Hash PARTITION BY HASH (KUNDEN_ID) PARTITIONS 64 pctfree 0 As select * from f_Umsatz; (TO_DATE('2010-04-01','SYYYY-MM-DD')), (TO_DATE('2010-05-01','SYYYY-MM-DD')), (TO_DATE('2010-06-01','SYYYY-MM-DD')), (TO_DATE('2010-07-01','SYYYY-MM-DD')), (TO_DATE('2010-08-01','SYYYY-MM-DD')), (TO_DATE('2010-09-01','SYYYY-MM-DD')), (TO_DATE('2010-10-01','SYYYY-MM-DD')), (TO_DATE('2010-11-01','SYYYY-MM-DD')), (TO_DATE('2010-12-01','SYYYY-MM-DD')), (TO_DATE('2011-01-01','SYYYY-MM-DD')), (TO_DATE('2011-02-01','SYYYY-MM-DD')), (TO_DATE('2011-03-01','SYYYY-MM-DD')), (TO_DATE('2011-04-01','SYYYY-MM-DD')), (TO_DATE('2011-05-01','SYYYY-MM-DD')), (TO_DATE('2011-06-01','SYYYY-MM-DD')), (TO_DATE('2011-07-01','SYYYY-MM-DD')), (TO_DATE('2011-08-01','SYYYY-MM-DD')), (TO_DATE('2011-09-01','SYYYY-MM-DD')), (TO_DATE('2011-10-01','SYYYY-MM-DD')), TABLESPACE TABLESPACE TABLESPACE TABLESPACE T1 T2 T3 T4 DATAFILE DATAFILE DATAFILE DATAFILE 'F:\ORA_DATA\T1.dbf' 'F:\ORA_DATA\T2.dbf' 'F:\ORA_DATA\T3.dbf' 'F:\ORA_DATA\T4.dbf' SIZE SIZE SIZE SIZE 10M; 10M; 10M; 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 0 STORE IN (T1, T2, T3, T4); Bitmap-Indexe auf Hash-partitionierte Tabelle Create 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 Create 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 Index DROP DROP DROP DROP index index index index idx_ARTIKEL_ID_BM_H; idx_ZEIT_ID_BM_H ; idx_KUNDE_ID_BM_H; idx_REGION_ID_BM_H; Skript Beispiel List Partitioning CREATE 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) SUBPARTITION TEMPLATE ( 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 PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION mar10 apr10 mai10 jun10 jul10 aug10 sep10 oct10 nov10 dec10 jan11 feb11 mar11 apr11 mai11 jun11 jul11 aug11 sep11 VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN (TO_DATE('2010-04-01','SYYYY-MM-DD')), (TO_DATE('2010-05-01','SYYYY-MM-DD')), (TO_DATE('2010-06-01','SYYYY-MM-DD')), (TO_DATE('2010-07-01','SYYYY-MM-DD')), (TO_DATE('2010-08-01','SYYYY-MM-DD')), (TO_DATE('2010-09-01','SYYYY-MM-DD')), (TO_DATE('2010-10-01','SYYYY-MM-DD')), (TO_DATE('2010-11-01','SYYYY-MM-DD')), (TO_DATE('2010-12-01','SYYYY-MM-DD')), (TO_DATE('2011-01-01','SYYYY-MM-DD')), (TO_DATE('2011-02-01','SYYYY-MM-DD')), (TO_DATE('2011-03-01','SYYYY-MM-DD')), (TO_DATE('2011-04-01','SYYYY-MM-DD')), (TO_DATE('2011-05-01','SYYYY-MM-DD')), (TO_DATE('2011-06-01','SYYYY-MM-DD')), (TO_DATE('2011-07-01','SYYYY-MM-DD')), (TO_DATE('2011-08-01','SYYYY-MM-DD')), (TO_DATE('2011-09-01','SYYYY-MM-DD')), (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/List SELECT /*+ no cache, no parallel */ sum(UMSATZ) Umsatz FROM F_UMSATZ[_RANGE[_LIST]] U, D_vertriebskanal V WHERE u.kanal_id = v.kanal_id and zeit_ID = to_date('10.03.2011','DD.MM.YYYY') and V.vertriebskanal = 'Shop'; Parallel Sekunden Physical Reads P0 ohne Partitioning 3,38 276045 P0 mit Range-Partitioning 0,09 782 P0 mit Range/List0,08 248 Partitioning (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'), Document1 16 /41 SUBPARTITION storno SUBPARTITION andere ) ( PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION ); jan11 VALUES LESS feb11 VALUES LESS mar11 VALUES LESS apr11 VALUES LESS mai11 VALUES LESS jun11 VALUES LESS jul11 VALUES LESS aug11 VALUES LESS sep11 VALUES LESS oct11 VALUES LESS nov11 VALUES LESS dec11 VALUES LESS jan12 VALUES LESS feb12 VALUES LESS next_month VALUES THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN LESS VALUES ('RETOURE','KOMMISSION'), VALUES (DEFAULT) (TO_DATE('2011-02-01', 'SYYYY-MM-DD')) (TO_DATE('2011-03-01', 'SYYYY-MM-DD')) (TO_DATE('2011-04-01', 'SYYYY-MM-DD')) (TO_DATE('2011-05-01', 'SYYYY-MM-DD')) (TO_DATE('2011-06-01', 'SYYYY-MM-DD')) (TO_DATE('2011-07-01', 'SYYYY-MM-DD')) (TO_DATE('2011-08-01', 'SYYYY-MM-DD')) (TO_DATE('2011-09-01', 'SYYYY-MM-DD')) (TO_DATE('2011-10-01', 'SYYYY-MM-DD')) (TO_DATE('2011-11-01', 'SYYYY-MM-DD')) (TO_DATE('2011-12-01', 'SYYYY-MM-DD')) (TO_DATE('2012-01-01', 'SYYYY-MM-DD')) (TO_DATE('2012-02-01', 'SYYYY-MM-DD')) (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) THAN (MAXVALUE) TABLESPACE DWH_SPINDEL TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE TABLESPACE DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, DWH_SPINDEL, Skript Beispiel Reference Partitioning CREATE 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 Monatspartitionen CREATE TABLE BESTELLUNG ( BESTELLNR NUMBER(10) NOT NULL, KUNDENCODE NUMBER(10), BESTELLDATUM DATE, LIEFERDATUM DATE, BESTELL_TOTAL NUMBER(12, 2), AUFTRAGSART VARCHAR2(30), ORDER_ID NUMBER ) PARTITION BY RANGE (BESTELLDATUM) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION Jan07 VALUES LESS THAN (TO_DATE(' 2007-01-31 00:00:00', 'SYYYYMM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE TS_PAR, PARTITION Feb07 VALUES LESS THAN (TO_DATE(' 2007-02-28 00:00:00', 'SYYYY-MMDD 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 Bereichsangabe create 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 Partitioning ALTER TABLE table_name SET INTERVAL (interval value); Skript Beispiel Virtuel Column Partition CREATE TABLE "BESTELLUNG_VIRTUELL" ( "BESTELLNR" NUMBER(10) NOT NULL, "KUNDENCODE" NUMBER(10), "BESTELLDATUM" DATE, "LIEFERDATUM" DATE, "BESTELL_TOTAL" NUMBER(12, 2), "AUFTRAGSART" VARCHAR2(30), "VERTRIEBSKANAL" NUMBER, Data Warehouse Technik im Fokus - Skripte in Kurzform 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 Bestellung ADD 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 Partitionen Partitiondaten direkt abfragen SELECT * 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 Tablespaces select segment_name,Partition_name,TABLESPACE_NAME from user_segments; Tabellen,Partition,Rows SELECT table_name, partition_name, num_rows user_tab_partitions FROM 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,MegaBytes SELECT table_name, tablespace_name,partition_name, num_rows, blocks, ((blocks*8192)/1000000) MegaByte, high_value FROM user_tab_partitions Partitionsgrenzen abfragen SELECT 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 Partitions col subpartition_name format a15 col partition_name format a15 col 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) uc WHERE up.ref_ptn_constraint_name = uc.constraint_name(+) AND up.table_name IN ('BESTELLUNG','BEST_POSITION'); Sich refenzierende Tabellen abfragen / Referen Partitioning SELECT table_name, partitioning_type,ref_ptn_constraint_name FROM user_part_tables WHERE table_name IN ('BESTELLUNG','BEST_POSITION'); Verwaltung von Partitionen Partionen hinzufügen ALTER TABLE BESTELLUNG ADD 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 verlagern ALTER TABLE BESTELLUNG MOVE PARTITION Jan08 TABLESPACE TS_PAR_Archiv; ALTER TABLE BESTELLUNG MOVE SUBPARTITION Jan08_STORNO TABLESPACE TS_PAR_Archiv_Jan; Merge von zwei Sub-Partitions ALTER TABLE BESTELLUNG MERGE SUBPARTITIONS TELEFON_STORNO, TELEFON_ANDERE INTO SUBPARTITION TELEFON_OBJEKTE Document1 17 /41 TABLESPACE TS_PAR; Ändern Defaults-Tablespace ALTER TABLE BESTELLUNG MODIFY DEFAULT ATTRIBUTES FOR PARTITION Jan08 TABLESPACE TS_PAR_JAN ; Ändern der Werte bei List-Partitioning ALTER 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 erstellen CREATE TABLE Bestellung_Produkte AS SELECT * FROM bestellung WHERE 1=2 -- Exchange ALTER TABLE Bestellung EXCHANGE PARTITION Produkt WITH TABLE Bestellung_Produkte; Umbenennen einer Partition ALTER TABLE Bestellung RENAME PARTITION Andere TO Bestellung_Rest; Truncate einer Partition ALTER TABLE Bestellung TRUNCATE PARTITION Service DROP STORAGE; Aufspalten einer Partition ALTER TABLE Bestellung SPLIT PARTITION Jan08 AT (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 Partitioning DBA_PART_TABLES Table-Level DBA_TAB_PARTITIONS Partition-/Subpartition-Level DBA_PART_KEY_COLUMNS Partition-/SubpartitionDBA_SUBPART_KEY_COLUMNS Key-Informationen DBA_PART_COL_STATISTICS Statistiken und Histogramme per Partition / Subpartition USER_IND_PARTITIONS DBA_SUBPART_COL_STATISTICS DBA_TAB_SUBPARTITIONS User_segments Data Warehouse Technik im Fokus - Skripte in Kurzform 18 /41 Oder Indexe Relevante Fragestellungen Umschlüsselung im DWH Bitmap / B*Tree Einsatz Platzverbrauch Welche Indexe gibt es in einem Schema? Überprüfen, ob Indexe gebraucht werden Clustering-Faktor Lokale Indexe Partitionierung von Indexen Prefixed / Non-Prefixed Index Star-Transformation Wie und wo wird im DWH indiziert? Umgang mit Indexen während eines Massenloads beim ETL Wie und wo wird indiziert 1. 2. DROP Index index_name CREATE Index index_name Rebuild Partitioned Index ALTER TABLE <table_name> MODIFY PARTITION <partition_name> REBUILD UNUSABLE LOCAL INDEXES; Rebuild Sub-Partitioned Index ALTER TABLE <table_name> MODIFY SUBPARTITION <subpartition_name> REBUILD UNUSABLE LOCAL INDEXES; Verschieben eines Index auf einen anderen Tablespace ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace_name>; ALTER TABLE part1 MOVE PARTITION p1 TABLESPACE new_tbsp UPDATE INDEXES (my_parts_idx (PARTITION p1 TABLESPACE my_tbsp)); Aktualisierung der Index zusammen mit Tabellen-Updates ALTER TABLE T1 DROP PARTITION P1 UPDATE GLOBAL INDEXES Einschalten Usage - Monitoring für einen Index alter index PK_BESTELLNR_PART_RANGE_HASH monitoring usage. Welcher Index wurde tatsächlich genutzt: Usage - Abfrage SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED FROM SYS.V$OBJECT_USAGE; Clustering Faktor und Anzahl Leaf-Blöcke abfragen select index_name, tablespace_name, blevel, leaf_blocks,distinct_keys, clustering_factor from user_indexes Partitionierung von Indexen Skript Local Partion Index Suche nachbestehenden Indexen Suche nach Indexen bezogen auf eine bestimmte Tabelle SELECT index_name, partitioned FROM user_indexes WHERE table_name = 'BESTELLUNG'; Status-Abfragen select index_name, status from user_indexes; Auflisten aller Indexe eines Schemas inkl. Größe SELECT index_name,Table_name,status, num_rows,index_type, blevel, leaf_blocks, distinct_keys FROM user_indexes Auflisten aller lokalen Index SELECT 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; CREATE INDEX idx_region_id ON F_Umsatz_range (region_id) LOCAL; Partitionierung der Indexe entsprechend der zugehörigen TablePartionen (Local Partiton Index) und zweisen unterschiedlicher Tablespaces CREATE 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)); 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'; Partitionierte Indexe suchen Anzeigen des Platzverbrauchs der Indexe 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'; select 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 WHERE zeit_ID = to_date('10.03.2011','DD.MM.YYYY') / Parallel Sekunden P0 ohne Bitmap-Index 3,38 P0 mit Bitmap-Index 0,2 Behandlung von Indexen beim Laden 1. 2. 3. Document1 INDEX auf Unusable setzen [Alter index index_name usable] ETL-Massen-Load durchführen INDEX Rebuild [Alter index index_name rebuild] SELECT index_name, partitioned FROM user_indexes WHERE table_name = 'BESTELLUNG'; Rebuild lokaler Index ALTER TABLE <table_name> MODIFY PARTITION <partition_name> REBUILD UNUSABLE LOCAL INDEXES; Rebuild Sub Partition Index ALTER TABLE <table_name> MODIFY SUBPARTITION <subpartition_name> REBUILD UNUSABLE LOCAL INDEXES; Verschieben auf anderen Tablespace ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace_name>; Relevante Dictionary Views für Indexing USER_IND_STATISTICS INDEX_HISTOGRAM USER_IND_PARTITIONS USER_INDEXTYPES USER_IND_COLUMNS USER_IND_PARTITIONS USER_INDEXES INDEX_STATS SYS.V$OBJECT_USAGE USER_INDEXTYPE_OPERATORS USER_IND_EXPRESSIONS USER_IND_SUBPARTITIONS Data Warehouse Technik im Fokus - Skripte in Kurzform Star Schema Allgemeine 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 nutzen o Sprechende Column-Namen nutzen o Codes vermeiden Konsistenz, Mengen und Indexe im Star-Schema Relevante Fragestellungen Wann zieht die Star Transformation Voraussetzungen prüfen Prüfen ob FK der Fakten-Tab in PK der Dimensionen select count(*) from f_umsatz where artikel_id not in (select artikel_ID from d_artikel); Größe der Indexe ausgeben select 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-Indexe select 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 aktivieren alter system set star_transformation_enabled=TRUE; Menge der WHERE-Bedingungen überprüfen Star-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] Document1 19 /41 Data Warehouse Technik im Fokus - Skripte in Kurzform Materialized Views Relevante Fragestellungen Einsatzgründe für Materialized (Verwaltung + Performance) Definition (Erstellung) Planung und Konzept Staleness Kennzahlen-Systemen (Nested Materialized Views Rewrite-Fähigkeit Refresh-Varianten (Log / PCT) Hilfen/Konzepte für Materialized Views Allgemeine 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. 20 /41 Beispieldefinition MAV_Artikel_Umsatz Unterstützte Kennzahlen/ Abfragen Gesamtumsatz/menge pro Artikel/Gruppe/Sparte Aggregiert auf Artikelgruppe und sparte „“ drop materialized View Mav_Artikel_umsatz; [Beispielabfrage 9,11,(19)] create MATERIALIZED VIEW Mav_artikel_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, A.Artikel_ID, count(*) FROM F_UMSATZ U, D_Artikel A WHERE U.Artikel_ID = A.Artikel_ID Group by A.Artikel_ID; Parallel P4 ohne Partitioning Sekunden 90 Physical Reads 276045 Beispieldefinition MAV_Region_Umsatz Unterstützte Kennzahlen/ Abfragen Gesamtumsatz/menge pro Ort/Kreis/Land/Region drop materialized View Mav_Region_umsatz; [Beispielabfrage 4] 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 create MATERIALIZED VIEW Mav_Region_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, count(*) FROM F_UMSATZ U, D_Region R WHERE U.Region_ID = R.Region_ID Group by R.Region_ID; Parallel Sekunden Physical Reads P4 ohne Partitioning 90 276045 Relevante Parameter ALTER SESSION SET ALTER SESSION SET ALTER SESSION SET query_rewrite_enabled=TRUE; query_rewrite_enabled=FALSE; query_rewrite_integrity=STALE_TOLERATED Beispieldefinition MAV_Kunde_Umsatz MVIEW suchen Unterstützte Kennzahlen/Abfragen Gesamtumsatz/menge pro Kunde/Bildungsgruppe, Einkommensgruppe, Wohnart, Berufsgruppe select mview_name,REWRITE_ENABLED,STALENESS,REWRITE_CAPABILITY from user_mviews; drop materialized View Mav_Kunde_umsatz; Größe und Anzahl Zeilen von Mviews abfragen [Beispielabfrage 20] select table_name, pct_free,blocks,blocks/1000000*8 GB,num_rows, compression,cache,empty_blocks from user_tables where upper(table_name) like 'MAV_%'; Beispieldefinition MAV_Zeit_Umsatz Unterstützte Kennzahlen/Abfragen Gesamtumsatz pro Tag/Monat/Quartal/Zeit drop materialized View Mav_Zeit_umsatz; [Beispielabfrage 5,6,7] create MATERIALIZED VIEW Mav_Zeit_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, Z.Zeit_ID, count(*) FROM F_UMSATZ U, D_Zeit Z WHERE U.Zeit_ID = Z.Zeit_ID Group by Z.Zeit_ID; Parallel Sekunden Physical Reads P4 ohne Partitioning 67 276045 Document1 create MATERIALIZED VIEW Mav_Kunde_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, k.Kunden_ID, count(*) FROM F_UMSATZ U, D_Kunde K WHERE U.Kunden_ID = k.Kunden_ID Group by k.Kunden_ID; Parallel Sekunden Physical Reads P4 ohne Partitioning 70 276045 Beispieldefinition MAV_Region_Artikel_Umsatz Unterstützte Kennzahlen/ Abfragen Gesamtumsatz/menge pro Ort/Kreis/Land/Region Artikel, Artikelgruppe und -sparte [Beispielabfrage 11,12,13,14,15,16,(19)] drop materialized View Mav_Region_Artikel_umsatz; Data Warehouse Technik im Fokus - Skripte in Kurzform create MATERIALIZED VIEW Mav_Region_Artikel_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, a.Artikel_ID, count(*) FROM F_UMSATZ U, D_Region R, d_Artikel A WHERE U.Region_ID = R.Region_ID and U.Artikel_ID = A.Artikel_ID Group by R.Region_ID,a.Artikel_ID; Parallel Sekunden Physical Reads P4 ohne Partitioning 12,0 276045 21 /41 as SELECT 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 A WHERE U.Region_ID = R.Region_ID and U.Artikel_ID = A.Artikel_ID and U.Zeit_ID = Z.Zeit_ID Group by R.Region_ID,z.Zeit_ID,A.Artikel_ID; Parallel Sekunden Physical Reads P4 ohne Partitioning 17 276045 Beispiel-Definition Beispieldefinition MAV_Region_Zeit_Umsatz Unterstützte Kennzahlen/Abfragen Gesamtumsatz/menge pro Ort/Kreis/Land/Region Tag/Monat/Quartal/Jahr [Beispielabfrage 8] drop materialized View Mav_Region_Zeit_umsatz; create MATERIALIZED VIEW Mav_Region_Zeit_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT sum(u.UMSATZ) Umsatz, sum(u.menge) Menge, R.Region_ID, Z.Zeit_ID, count(*) FROM F_UMSATZ U, D_Region R, d_Zeit Z WHERE U.Region_ID = R.Region_ID and U.Zeit_ID = Z.Zeit_ID Group by R.Region_ID,z.Zeit_ID; Parallel Sekunden Physical Reads P4 ohne Partitioning 14,0 276045 CREATE MATERIALIZED VIEW MV_Standard BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS SELECT z.jahr_nummer Jahr, z.monat_desc Monat, sum(u.umsatz) Summe, a.artikel_id ID, count(u.umsatz) FROM f_umsatz u,d_artikel a, d_zeit z WHERE a.artikel_id = u.artikel_id AND u.zeit_id = z.datum_id GROUP BY z.jahr_nummer, z.monat_desc, a.artikel_id; Beispiel für eine Partitionierte MAV CREATE 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 Log Unterstützte Kennzahlen/Abfragen Gesamtumsatz/menge pro Ort/Kreis/Land/Region Tag/Monat/Quartal/Jahr CREATE 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; drop materialized View Mav_Kunde_Zeit_umsatz; Löschen Materilized View Log create MATERIALIZED VIEW Mav_Kunde_Zeit_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE as SELECT 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 Z WHERE U.Region_ID = K.Kunden_ID and U.Zeit_ID = Z.Zeit_ID Group by K.Kunden_ID,z.Zeit_ID; Build Funktionen Beispieldefinition MAV_Kunde_Zeit_Umsatz Parallel P4 ohne Partitioning Sekunden 3,7 Physical Reads 276045 Beispieldefinition MAV_Region_Zeit_Artikel_Umsatz Unterstützte Kennzahlen/Abfragen Gesamtumsatz/menge pro Ort/Kreis/Land/Region Tag/Monat/Quartal/Jahr Artikel/Artikelgruppe und -sparte [Beispielabfrage 10] drop materialized View Mav_Region_Zeit_Artikel_umsatz; create MATERIALIZED VIEW Mav_Region_Zeit_Artikel_umsatz BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE Document1 drop materialized view log on f_Bestellungen; BUILD IMMEDIATE BUILD DEFERRED ON PREBUILD Refresh Funktionen COMPLETE FAST FORCE NEVER ON COMMIT ON DEMAND Feststellen, ob PCT Tracking funktioniert select mview_name, num_PCT_TABLES from user_mviews; Refresh auf eine MAV EXECUTE 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 WHEREKlausel der MAV enthalten ist.) CREATE MATERIALIZED VIEW MV_standard_PCT_falsch_PM ENABLE 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 GROUP BY DBMS_MVIEW.PMARKER(u.rowid), z.jahr_nummer, z.monat_desc, a.artikel_id; Data Warehouse Technik im Fokus - Skripte in Kurzform Komprimieren von Materialized Views ALTER 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, BLOCKS FROM user_tables; Refresh auf alle von einer Tabelle abhängigen MAVs (REFRESH DEPENDENT) 1.Beispiel variable failures number; EXECUTE DBMS_MVIEW.REFRESH_DEPENDENT( :failures,'F_UMSATZ','C',refresh_after_errors=>true); print failures 2. Beispiel DBMS_MVIEW.REFRESH_DEPENDENT ('employees,deptartments,hr.regions','cf'); Aktualisieren aller Materialized Views DBMS_MVIEW.REFRESH_ALL: -- hat keine Parameter Anzeigen Materialized Views mit Zustand und View Logs SELECT mview_name, update_log, stale_since, staleness FROM user_mviews Anzeigen Materialized Views mit Zustand und PCT Regions SELECT 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 DIMENSION_EXCEPTIONS an Legt Tabelle 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-Tables select dimension_name OWNER,DIMENSION_NAME,INVALID, COMPILE_STATE,REVISION from user_dimensions Anzeigen der Struktur einer Dimensional Table EXECUTE DBMS_DIMENSION.DESCRIBE_DIMENSION('D_ARTIKEL'); Relevante Dictionary Views ALL_MVIEWS DBA_MVIEWS USER_MVIEWS USER_MVIEW_DETAIL_PARTITION USER_MVIEW_DETAIL_RELATIONS USER_MVIEW_DETAIL_SUBPARTITION USER_DIMENSIONS Document1 22 /41 Data Warehouse Technik im Fokus - Skripte in Kurzform Optimierung für schnelles Lesen Optimizer - Statistiken sammeln Statistiken für eine Tabelle EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','F_UMSATZ',estimate_percent=>100); Statistiken für einen Index EXEC DBMS_STATS.GATHER_INDEX_STATS ('DWH','PK_ARTIKEL',estimate_percent=>100); Memory-Cache Statistiken für eine Materialized View Definition Tabellen in Cache legen / aus Cache entfernen EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','MAV_REGION_ZEIT_UMSATZ',estimate_percent=>100); (Werden wie Tabellen behandelt) alter table D_ARTIKEL cache; alter table D_ARTIKEL nocache; Statistiken für ein Schema Welche Tabellen liegen im Cache SELECT owner, table_name = 'Y'; 23 /41 FROM dba_tables WHERE LTRIM(cache) Query Result Cache EXEC dbms_stats.gather_schema_stats( ownname => 'ETLDB', estimate_percent => 5,block_sample => TRUE) Automatische Aktualisierung für ein Schema einrichten begin dbms_stats.gather_schema_stats( ownname => 'PERF' ,options => 'GATHER AUTO' ,estimate_percent => 5 ,block_sample => TRUE); Aktivieren mit alter system set result_cache_mode=FORCE; (MANUAL) RESULT_CACHE_MAX_RESULT RESULT_CACHE_MAX_SIZE RESULT_CACHE_MODE RESULT_CACHE_REMOTE_EXPIRATION Ausnutzen Result Cache durch Hint in der Abfrage SELECT /*+ result_cache */ COUNT(*), SUM(BESTELL_TOTAL) FROM F_UMSATZ; Abfragen auf im Cache befindliche Statements end; Automatisiertes Sampling EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS( 'OE',DBMS_STATS.AUTO_SAMPLE_SIZE); Abfrage ob automatisiertes Sammel aktiviert ist execute dbms_result_cache.memory_report() SELECT client_name, status FROM dba_autotask_operation; CLIENT_NAME STATUS -------------------------------------- -------auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED Result Cache leeren Zustand/Aktualität der Statistiken abfragen SELECT name, type, row_count, invalidations, scan_count FROM v$result_cache_objects; Result-Cache-Memory-Report exec dbms_result_cache.flush; Relvante Dictionary Views für Result-Cache V$RESULT_CACHE_OBJECTS Parallelisierung select table_name,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN,SAMPLE_SIZ E,LAST_ANALYZED ,STALE_STATS from user_tab_statistics; Zustand von Index-Statistiken abfragen select Index_name,TABLE_NAME,LAST_ANALYZED,STALE_STATS from user_ind_statistics; Histogramme abfragen Relevante Fragestellungen Aktivieren / Deaktivieren Abfragen der eingestellten Parallelität Gezieltes Steuern der Parallelität für einzelne Tabellen Parallelisierung aktivieren ALTER SESSION FORCE PARALLEL QUERY ALTER SESSION ENABLE PARALLEL DML; ALTER SESSION ENABLE PARALLEL DDL; Automatisches Steuern mit ALTER SESSION SET PARALLEL_AUTOMATIC_TUNING = TRUE; (veraltet) Ab 11.2: ALTER SESSION/SYSTEM SET PARALLEL_DEGREE_POLICY=MANUAL/LIMITD/AUTO MANUAL: Kein automated DOP / Kein Statement Queuing /Kein InMemory Parallel Execution LIMITED: 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” setzen ALTER TABLE tab_Name PARALLEL n; ALTER TABLE tab_Name NOPARALLEL; Parallelisierter Select-Zugriff SELECT /*+ parallel(B) parallel(A) */ a,b,c FROM BESTELLUNG B, ARTIKEL A; INSERT /*+ APPEND PARALLEL(table_name)*/ INTO.. Prüfen welche Art der Parallelisierung eingestellt ist select COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM from sys.USER_TAB_COL_Statistics where TABLE_NAME ='BESTELLUNG' Histogramme sammeln begin DBMS_STATS.GATHER_TABLE_STATS(Ownname=>'TIF', Tabname=>'BESTELLUNG' , METHOD_OPT => 'FOR COLUMNS SIZE 10 KUNDENCODE'); end; Abfragen der Grenzwerte der Histogram-Buckets SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'F_UMSATZ' and COLUMN_NAME = 'ARTIKEL_ID' order by ENDPOINT_NUMBER Löschen von Statistiken BEGIN DBMS_STATS.DELETE_table_STATS ( OWNNAME => 'DWH', TABNAME => 'D_ARTIKEL'); END; Löschen von Histogrammen für einzelne Spalten BEGIN 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öglichen Exec DBMS_STATS.SET_TABLE_PREFS(<OWNER>, <TABLE_NAME>, 'INCREMENTAL', ‘TRUE’); select userName,PDML_ENABLED,PDML_STATUS,PDDL_STATUS,PQ_STATUS from v$session; exec DBMS_STATS.SET_TABLE_PREFS('DWH','F_UMSATZ', 'INCREMENTAL', 'TRUE'); Aktuelle SQL-Statements und deren Parallelisierung abfragen Relvante Dictionary Views für Statistiken 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 Parallelisierung v$session v$sql_monitor SQL-Monitor im Enterprise Manger nutzen Document1 USER_TAB_STATISTICS USER_TAB_COL_STATISTICS USER_IND_STATISTICS USER_HISTOGRAMS USER_TAB_HISTOGRAMS Data Warehouse Technik im Fokus - Skripte in Kurzform 24 /41 Logins pro User Umgebungsinformationen auslesen Datenbank-Informationen, Version, Patchstände Relevante Fragestellungen DB-Namen Version Patchstände Installierte Komponenten Database-ID abfragen select dbid from v$database; DBID ---------1053456982 Installierte Komponenten select comp_id,substr(comp_name,1,30) Name ,version,status from dba_registry; Patchstand abfragen Auf der Betriebssystemebene mit Opatch lsinventory –detail (Opatch befindet sic him Oracle – Home Verzeichnis Patch-Historie select username,count(1) from v$session group by username User Platzverbrauch Tablespace set heading on set pagesize 200 set newpage 0 set line 200 SELECT 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äten select 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 Views V$SESSION DBA_USERS v$sql_monitor Tablespaces, Auflistungen, Mengen und Größen SELECT * FROM sys.registry$history Datenbankversion abfragen SELECT * FROM v$version Relevante Dictionary Views dba_registry V$version V$SESSION V$DATABASE User-Informationen / Einstellungen /Security Welche USER gibt es in einer Umgebung und welchen Zustand haben sie select username, account_status, lock_date from dba_users order by username; Benutzer anlegen Create 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 freigeben Grant select any table to public; Allgemeine Benutzerinformationen abfragen Relevante Fragestellungen Welche 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 Datafiles CREATE 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 Files select sum(bytes)/1024/1024 Meg from dba_data_files; Liste aller Tablespace mit Größenangaben select 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; SQL> desc dba_Users Temp-Space 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_ID and s.user# != 0 / select nvl(sum(bytes),0)/1024/1024 Meg from dba_temp_files; Ausschalten Expiration Date in Entwicklungs- und Test-Umgebung select table_name, Blocks from user_tables order by blocks desc ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED; Ändern des Lock-Zustads eines Users alter user ANONYMOUS account UNLOCK; Welche Rechte wurden einem User vergeben -- Beispiel DWH SELECT PRIVILEGE FROM sys.dba_sys_privs WHERE grantee = 'DWH' UNION SELECT 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 Document1 Größe Redo Logs select sum(bytes)/1024/1024 Meg from sys.v_$log; Finden der größten Tabellen select table_name, Owner, blocks from dba_tables where owner = 'MON' order by blocks Top 10 größten Tabellen eines Users where rownum < 10 Data Files + Redo Logs + Temp select a.data_size+b.temp_size+c.redo_size total_size from ( 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-Platz SELECT SUM(bytes)/1024/1024 Meg FROM dba_segments; Data Warehouse Technik im Fokus - Skripte in Kurzform Belegter und benutzter Temp-Tablespace SELECT column size_mb heading '&&this_mon' format 99999 column size_mb2 heading '&&prev_mon' format 99999 column size_mb1 heading '&&last_mon' format 99999 column growth heading 'Growth(%)| in |last month' format 9999.99 SELECT 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)) growth FROM (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) A GROUP BY tablespace_name; --ORDER BY tablespace_name; 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 ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; Blockgrößen auslesen select tablespace_name, block_size from dba_tablespaces; TABLESPACE_NAME BLOCK_SIZE ------------------------------ ---------SYSTEM 8192 SYSAUX 8192 UNDOTBS1 8192 TEMP 8192 USERS 8192 EXAMPLE 8192 MON_G 8192 MON 8192 MON_D 8192 MON_E 8192 MON_F 8192 Liste Tabellen und Berechnung der Größe in Byte, MB, GB SELECT 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 7698840 BESTELLUNG_PART_RANGE_4 163840000 962355 7698840 Tabellengrößen und Platzverbrauch SELECT table_name, num_rows, blocks, blocks*8 KB, blocks*8/1000 MB,blocks*8/1000000 GB FROM user_tables; TABLE_NAME -----------------------BESTELLUNG_PART_RANGE BESTELLUNG_PART_RANGE_4 25 /41 NUM_ROWS BLOCKS KB MB GB ---------- ---------- ---------- ---------163840000 962355 7698840 7698,84 7,69884 163840000 962355 7698840 7698,84 7,69884 Größen und Mengen mit zusätzlichem Tablespace-Namen Growth(%) in --TABLESPACE_NAME ---- -----------DWH1 --EXAMPLE --PERFSTAT --SYSAUX --SYSTEM --TEST_ALERT --UNDOTBS1 --USERS BREAK ON report COMPUTE SUM OF filesize COMPUTE SUM OF used COMPUTE AVG OF pct_used SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENTS ---- -------------------- ---------- ---------- ---------BESTELLUNG_PART_RANGE APR10 TABLE PARTITION MON 123 53248 BESTELLUNG_PART_RANGE APR11 TABLE PARTITION MON 132 62464 BESTELLUNG_PART_RANGE AUG10 TABLE PARTITION MON 137 67584 BLOCKS KB 425984 499712 540672 Auflisten des Wachstums einzelner Tablespaces CREATE TABLE stats$segment_info AS SELECT SYSDATE snap_time,owner, segment_name,segment_type, tablespace_name, sum(bytes) bytes FROM dba_segments GROUP BY SYSDATE, owner, segment_name, segment_type, tablespace_name; INSERT INTO stats$segment_info SELECT SYSDATE snap_time, owner, segment_name,segment_type, tablespace_name, sum(bytes) bytes FROM dba_segments GROUP BY SYSDATE, owner, segment_name, segment_type, tablespace_name; column this_mon new_value this_mon column last_mon new_value last_mon column 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 Document1 SIZE_MB last month 73.3125 77.4375 96.75 616.3125 694.0625 3 21.25 3.125 7331.25 7743.75 9675.00 ######## ######## 300.00 2125.00 312.50 Gesamtauswertung belegter Plattenplatz und Freespace pro Tablespace und Datafiles Welche Objekte gehören zu einem Tablespace column segment_name format a25 column partition_name format a20 column tablespace_name format a20 SELECT segment_name, partition_name, segment_type, tablespace_name, extents, blocks, bytes/1024 KB FROM user_segments WHERE segment_name = 'BESTELLUNG_PART_RANGE'; 0 0 0 0 0 0 0 0 SQL> select distinct segment_type from user_segments; SEGMENT_TYPE -----------------TABLE SUBPARTITION TABLE PARTITION TABLE INDEX SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY OFF Größenangaben bezogen auf Partitionen SIZE_MB1 0 0 0 0 0 0 0 0 Segment-Informationen select 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'; select owner,segment_name,segment_type, Blocks, bytes/1000000 MB from dba_segments where tablespace_name='DWH_SPINDEL' SIZE_MB2 COLUMN tablespace COLUMN filename COLUMN filesize Size' COLUMN used FORMAT bytes)' COLUMN pct_used FORMAT a18 HEADING 'Tablespace Name' FORMAT a50 HEADING 'Filename' FORMAT 999.999,999,999,999 HEADING 'File 999.999,999,999,999 HEADING 'Used (in FORMAT 999 HEADING 'Pct. Used' ON report ON report 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_used FROM sys.dba_data_files d , v$datafile v , ( select file_id, SUM(bytes) bytes from sys.dba_free_space GROUP BY file_id) s WHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name) UNION SELECT d.tablespace_name tablespace , d.file_name filename , d.file_id file_id , d.bytes filesize , NVL(t.bytes_cached, 0) used , TRUNC((t.bytes_cached / d.bytes) * 100) pct_used FROM sys.dba_temp_files d , v$temp_extent_pool t , v$tempfile v WHERE (t.file_id (+)= d.file_id) AND (d.file_id = v.file#) / Relevante Dictionary Views V$TABLESPACE V$DATAFILE V$TEMPFILE DBA_SEGMENTS Data Warehouse Technik im Fokus - Skripte in Kurzform DBA_TABLES DBA_FREE_SPACE V$TEMP_EXTENT_POOL V$LOG DBA_TEMP_FILES V$TEMPFILE Database Files Auffinden 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 Dateien from dba_data_files; In welchen Datafiles liegen bestimmte Tabellen column segment_name format a20 column owner format a10 column file_name format a50 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 e.segment_name = 'BESTELLUNG' and e.file_id = d.file_id order 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_id order by e.segment_name; Single Block / Multi Block Reads SELECT f.FILE_NAME datei, v.SINGLEBLKRDS, v.PHYRDS - v.SINGLEBLKRDS Multi FROM v$filestat v, dba_data_files f WHERE v.FILE# = f.file_id; Database Files mit asynchronem Lesen und Schreiben col name format a50 SELECT name, asynch_io FROM v$datafile f,v$iostat_file i WHERE f.file# = i.file_no AND filetype_name = 'Data File' / NAME ASYNCH_IO ------------------------------------------ --------D:\ORA\ORADATA\O112\SYSTEM01.DBF ASYNC_ON D:\ORA\ORADATA\O112\SYSAUX01.DBF ASYNC_ON D:\ORA\ORADATA\O112\UNDOTBS01.DBF ASYNC_ON D:\ORA\ORADATA\O112\USERS01.DBF ASYNC_ON D:\ORA\ORADATA\O112\EXAMPLE01.DBF ASYNC_ON G:\ORADATA\MON_X.DBF ASYNC_ON G:\ORADATA\MON.DBF ASYNC_ON G:\ORADATA\MON2 ASYNC_ON G:\ORADATA\MON3 ASYNC_ON G:\ORADATA\MON4 ASYNC_ON Relevante Dictionary Views dba_data_files dba_extents Lesestatistiken / Benutzungsverhalten Gelesene und geschriebene Blöcke select 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 3525296 E:\APP\ORADATA\ORCL\SYSAUX01.DBF 51449 45799 97248 E:\APP\ORADATA\ORCL\SYSTEM01.DBF 36970 4476 41446 E:\APP\ORADATA\ORCL\USERS01.DBF 15206 7886 23092 E:\APP\ORADATA\ORCL\DWH_SSD 12577 4955 17532 E:\APP\ORADATA\ORCL\UNDOTBS01.DBF 55 17234 17289 E:\APP\ORADATA\ORCL\EXAMPLE 3728 2284 6012 F:\ORA_DATA\DWH_SPINDEL 27 9 36 Document1 E:\APP\ORADATA\ORCL\T1 17 9 Security Benutzer anlagen Relevante Fragestellungen select file_name,file_id 26 /41 Create user XXX identified by XXX [Default tablespace TSname]; Drop user XXX [CASCADE] Ein Profil anlegen create 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 anlegen create 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 weitergeben GRANT DWH_ACCESS to user_name; Default-Rolle einem Benutzer zuweisen alter 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 gerade select EXECUTIONS, USERS_EXECUTING, username, sql_text from v$session se , v$sql sq where se.sql_address = sq.address; Relevante Dictionary Views Objektrechte all_tab_privs user_tab_privs dba_tab_privs all_col_privs user_col_privs dba_col_privs Systemrechte user_sys_privs dba_sys_privs session_privs Weitergegebene Rechte all_tab_privs_made user_tab_privs_made all_col_privs_made user_col_privs_made Erhaltene Rechte all_tab_privs_recd user_tab_privs_recd all_col_privs_recd user_col_privs_made Quotas user_ts_quotas all_ts_quotas dba_ts_quotas User user_users all_users dba_users Rollen role_sys_privs role_tab_privs role_role_privs 26 Data Warehouse Technik im Fokus - Skripte in Kurzform Laden des DWH Relevante Fragestellungen Datenbank-basiertes Laden Datenbank-Techniken für den ETL Prozess Mengen-basiertes Laden Arbeiten mit und ohne Constraints Datenprüfungen mit Native-SQL Bewegen großer Datenmengen Komplexe Logik bei hoher Performance Schnelles Laden / Mengenbasiertes Laden Grunsätzliches Schema für mengenbasiertes Laden Insert into TARGET_TABLE select * from SOURCE_TABLE; Direct Path Load Es 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 /*+ 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 anlegen CREATE 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 .... Text-Dateien Importieren SQL Loader sqlldr 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 DATA CHARACTERSET WE8MSWIN1252 INFILE 'C:\orte.csv' BADFILE 'orte.bad' DISCARDFILE 'orte.dis‚ INTO TABLE dwh.tb_orte WHEN ort_id != BLANKS APPEND REENABLE DISABLED_CONSTRAINTS FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" (orte_nr POSITION(1) INTEGER EXTERNAL , ort CHAR , plz CHAR , bundesland CHAR , region CHAR , nummernfeld INTEGER EXTERNAL ) CREATE TABLE Gemeinde_EX ( Gemeinde_Nr VARCHAR2(8), Gemeinde VARCHAR2(50) ) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY Exttab ACCESS 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 anlegen CREATE DIRECTORY Exttab AS 'D:\Szenario\Exttab'; Auflisten bestehender Directory-Definitionen Databas Link Zugriff auf Remote-Oracle-Datenbank (Database Link) CONNECT TO "DWH" Verwendung Select * from SOURCE_TABLEqDWH; Sequence für Zähl-Felder / Schlüssel aufbauen Defintion eines Sequence-Objektes create sequence kd_seq increment by 1 start with 1; Zugriff um den nächsten Zählerwert abzugreifen select seq_kd.nextval from dual; Abfragen des aktuellen Stands select LAST_NUMBER from user_sequences where sequence_name = 'SEQ_KD' Relevante Dictionary Views user_sequences Trigger Einsatz 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 ROW WHEN (new.Bestellnr > 0) DECLARE sal_diff number; BEGIN INSERT INTO log_Bestellung (Alte_Bestell_Nr,Neue_Bstell_Nr) Document1 VALUES(old.Bestellnr,new.Bestellnr); END; External Table Beispiel Insert ohne Log-Datei CREATE DATABASE LINK "DWH" IDENTIFIED BY "DWH" USING 'ORA10' ; 27 /41 select substr(owner,1,15), substr(DIRECTORY_NAME,1,20),substr(DIRECTORY_PATH,1,30) from dba_directories; Logischen Directory-Name ändern alter table ex_orte default directory LC_Texte_2; Name der Daten-(CSV-)Datei ändern alter table ex_orte location ('ORTE_Y.CSV'); Datapump Datapump - Beispiel mit Steuerdatei expdp 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_OUT DUMPFILE=EXP1.dmp LOGFILE=DP_OUT:EXP_LOG.dmp impdp dwh2/dwh2@o11 DIRECTORY=DP_OUT DUMPFILE=EXP1.DMP LOGFILE=DP_OUT:imp_log REMAP_SCHEMA=DWH:DWH2 Allgemeines Beispiel Expdp hf TABLES=employees,jobs DUMPFILE=dpump_dir1:Table.dmp NOLOGFILE=y Umändern des Default-Output-Directories CREATE 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 Plattenplatz Expdp hr DIRECTORY=dpump_dir1 ESTIMATE_ONLY=y TABLES=employees, departments, locations LOGFILE=estimate.log Data Warehouse Technik im Fokus - Skripte in Kurzform 28 /41 Datapump Data only Import Impdp hr TABLES=employees CONTENT=DATA_ONLY DUMPFILE= dpump_dir1:Table.dmp NOLOGFILE=y Datapump Schema Mode Impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX TABLE_EXISTS_ACTION=REPLACE Datapump Network Mode Impdp hr TABLES=employees REMAP_SCHEMA=hr:scott DIRECTORY=dpump_dir1 NETWORK_LINK=dblink Interaktiver Modus mit CTRL C aktivierbar Befehl Beschreibung ------------------------------------------------------------ADD_FILE CONTINUE_CLIENT gestartet, wenn EXIT_CLIENT FILESIZE Befehle. HELP KILL_JOB PARALLEL Job. Fügt Dump-Datei zu Dump-Dateigruppe hinzu. Kehrt in den Logging-Modus zurück. Job wird neu frei. Beendet Client-Session, Job wird weiter ausgeführt. Standarddateigröße (Byte) für nachfolgende ADD_FILEFasst interaktive Befehle zusammen. Hebt Zuordnung von Job auf und löscht Job. Ändert die Anzahl von aktiven Workern für den aktuellen 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_bestellung ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY dp_out LOCATION ('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_DATAPUMP DEFAULT DIRECTORY DP_OUT LOCATION ('best.dmp')) REJECT LIMIT UNLIMITED; -- In Zielumgebung CREATE TABLE ex_bestellung_3 ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DP_OUT LOCATION ('best.dmp') ) REJECT LIMIT UNLIMITED; Transportable Tablespace Transportable Tablespace-Verfahren --- 1. Erstellen Tablespace CREATE TABLESPACE ts_bestellung DATAFILE 'D:\oracle\oradata\dworcl\TS_BEST.tbs' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M; --- 2. Zuweisen von tabelendaten zum Tablespace CREATE 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 Document1 Error Logging Error Logging Eine Tabelle Errorlog-fähig machen BEGIN 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ügen insert into kd_XX (kundennr) values(74); insert into kd_XX (kundennr) values(784); insert into kd_XX (kundennr) values(7874); -- Nicht eindeutige Werte heraussuchen Drop 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 sind insert into el_kunde select * from kd_xx where kundennr not in (select * from non_uni); Regular Expressions Regular Expression in Constraints verwenden ALTER 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 Data Warehouse Technik im Fokus - Skripte in Kurzform Arbeiten ohne Constraints und mengenbasiertes SQL / Prüfen Gemeint ist das Prüfen von zu ladenden Daten ohne DatenabnkConstraints, 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. 29 /41 AND dateCheck(inDate, 'mm.dd.yyyy') AND dateCheck(inDate, 'mm.dd.yy') = AND dateCheck(inDate, 'yyyy.mm.dd') AND dateCheck(inDate, 'yy.mm.dd') = then return 'false'; else return 'true'; end if; --exception --when others then return 'false'; END; = 'false' 'false' = 'false' 'false' Prüfungen Kategorie B: Satz-bezogene Prüfungen. Abhängigkeiten von anderen Feldern im selben Satz Beispieltabelle: F1 F2 F3 3 7 3 9 5 1 Aus- und Einschalten aller Constraints spool test.sql select '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üfen SELECT CASE WHEN isnumeric('999') = 1 THEN 'numerisch' ELSE 'nicht numerisch' END Ergebnis FROM dual; IsNumeric-Prüfung CREATE 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üfung create or replace function IsDate (str varchar2) return varchar2 is inDate varchar2(40); FUNCTION dateCheck (inputDate varchar2, inputMask varchar2) RETURN varchar2 IS 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‚ Document1 F4 4 4 select CASE WHEN (F1 = 3 and F2 = F3 + F4) then 1 ELSE 0 end from 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 Beispieltabelle Prüfregel: 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 from Artikel Data Warehouse Technik im Fokus - Skripte in Kurzform Prüfungen Kategorie D: Tabellen-übergreifende Prüfungen. Foreign Key Prüfung Beispieltabelle Prüfregel: 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 Laden Pivot/Unpivot Beispiele WITH pivot_data AS ( SELECT Artikelnr,Bestellmenge FROM best_pos) SELECT * FROM pivot_data PIVOT( 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. Tabelle INSERT 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. Inserts INSERT 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-Beispiel MERGE INTO Kunde_TGT USING (SELECT KUNDEN_STAMM.KUNDENNR KUNDENNR, KUNDEN_STAMM.VORNAME VORNAME, KUNDEN_STAMM.NACHNAME NACHNAME, KUNDEN_STAMM.STATUS STATUS, KUNDEN_STAMM.STRASSE STRASSE, KUNDEN_STAMM.TELEFON TELEFON, KUNDEN_STAMM.TELEFAX TELEFAX FROM KUNDEN_STAMM KUNDEN_STAMM) MERGE_SUBQUERY Document1 30 /41 ON ( Kunde_TGT.KUNDENNR = MERGE_SUBQUERY.KUNDENNR) WHEN NOT MATCHED THEN INSERT (Kunde_TGT.KUNDENNR, Kunde_TGT.VORNAME, Kunde_TGT.NACHNAME, Kunde_TGT.STATUS, Kunde_TGT.STRASSE, Kunde_TGT.TELEFON, Kunde_TGT.TELEFAX) VALUES (MERGE_SUBQUERY.KUNDENNR, MERGE_SUBQUERY.VORNAME, MERGE_SUBQUERY.NACHNAME, MERGE_SUBQUERY.STATUS, MERGE_SUBQUERY.STRASSE, MERGE_SUBQUERY.TELEFON, MERGE_SUBQUERY.TELEFAX) WHEN MATCHED THEN UPDATE SET VORNAME = MERGE_SUBQUERY.VORNAME, NACHNAME = MERGE_SUBQUERY.NACHNAME, STATUS = MERGE_SUBQUERY.STATUS, STRASSE = MERGE_SUBQUERY.STRASSE, TELEFON = MERGE_SUBQUERY.TELEFON, TELEFAX = MERGE_SUBQUERY.TELEFAX; Flashback Relevante Fragestellungen Absichern 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üfen SELECT flashback_on, log_mode FROM gv$database; Aktuelle SCN abfragen SELECT current_scn FROM gv$database; Retention-Zeit abfragen SELECT name, value FROM gv$parameter WHERE name LIKE '%flashback%'; Ändern Retention-Zeit ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880; (Anzahl der Minuten 60 * 24 = 1440 = 1 Tag) Abfragen der letzten Logs SELECT * from gv$flashback_database_log; SELECT oldest_flashback_scn, oldest_flashback_time FROM gv$flashback_database_log; Desc gv$flashback_database_log INST_ID OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE Zurücksetzen einer Tabelle auf ältere SCN mit Flashback FLASHBACK TABLE X TO SCN 16552768; startup mount exclusive; FLASHBACK TABLE dwh.X TO SCN 16552768; Beispiel Flasback CREATE TABLE x (Nummer number); ALTER TABLE x ENABLE ROW MOVEMENT; INSERT INTO X VALUES (1); / Commit; SELECT ora_rowscn FROM x; ORA_ROWSCN: 25489616 INSERT 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 25489743 select count(*) from x; -> 5 Flashback table x to scn 25489616; select count(*) from x; -> 1 Relevante Dictionary Views gv$flashback_database_log gv$parameter gv$database NUMBER NUMBER DATE NUMBER NUMBER Data Warehouse Technik im Fokus - Skripte in Kurzform Table Function Relevante Fragestellungen In 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 - Definition Definition 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-Definition Die 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; BEGIN i := 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; / ---- Beispielaufrufe SELECT * FROM TABLE(f_bestellung_y()); SQL / PL/SQL - Funktionen Leeres PL/SQL-Function-Template CREATE OR REPLACE FUNCTION "FAKULTAET"("ZAHL" IN NUMBER) RETURN NUMBER IS --initialize variables here -- main body BEGIN NULL; ln(n) natürlicher Algorithmus von zu e log(m,n) Logarithmus von n zu Basis m Beispiele select 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. Stringfunktionen concat(s1, s2) s1 und s2 upper(s) s in Großbuchstaben konkateniert (entspricht initcap(s) erstes Zeichen s1||s2) lower(s) s in eines Wortes groß, Rest klein Kleinbuchstaben 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 Beispiele Teilstring bestimmen substr('TAKTUM Informatik', 1, 6) Ergebnis: 'TAKTUM' Suchen instr('TAKTUM Informatik', 'Info') Ergebnis: 8 Zeichen erzeugen chr(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 erzeugen ascii('A') Ergebnis: 65 Teilstring ersezten replace('SCHADE', 'D', 'LK') Ergebnis: 'SCHALKE' Zeichen austauschen translate('ABC67LR5', '0123456789','**********') Ergebnis: 'ABC**LR*' translate('ABC67LR5', '*0123456789','*') Ergebnis: 'ABCLR' Auf diese Weise lassen sich unerwünschte Zeichen elegant aus einer Zeichenkette entfernen. -- allow compilation RETURN NULL; EXCEPTION WHEN OTHERS THEN NULL; -- enter any exception code here RETURN NULL; END; Numerische Funktionen abs(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...) Document1 31 /41 Datum-Funktionen add_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)) Beispiele sysdate+1 Ergebnis: 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:00 months_between(to_date('25.12.2002', 'dd.mm.yyyy'), to_date('10.11.2002', 'dd.mm.yyyy')) Ergebnis: 1,48387097 Data Warehouse Technik im Fokus - Skripte in Kurzform months_between(to_date('25.12.2002', 'dd.mm.yyyy'), to_date('25.11.2002', 'dd.mm.yyyy')) Ergebnis: 1 32 /41 Lösung 1: select * from auftrag where datum = to_date ('21.09.1999', 'dd.mm.yyyy'); Als Basis zur Berechnung werden immer 31 Tage je Monat zugrunde gelegt. => Problem: Was ist mit der Uhrzeit? Bedingte Abfragen (Decode / CASE) Lösung 2: select * from auftrag where to_char (datum, 'dd.mm.yyyy') = '21.09.1999'; 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 Beispiele decode (status,'A','Angelegt','E','Erledigt','S','Storniert','Unbekan nt') 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 Funktionen nvl(artikel_nr, 999999) Ergebnis: 999999 wenn die Artikelnummer nicht gefüllt ist sonst die Artikelnummer greatest(4, 7, 1) Ergebnis: 7 oder select * from auftrag where 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. Beispiele to_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:52 Select to_char(sysdate,'DD.MM.YYYY HH24:MI:SS') from dual; -> 30.08.2011 15:54:26 Select 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) to_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 Zahl in Zeichenketten: to_char (zahl, format) Zeichenkette in Zahl: to_number (zeichenkette, format) Wichtige Formatzeichen 9 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 Beispiele select to_char (anzahl * preis, '999G990D00') Umsatz from auftrag_pos where auftrag_nr = 1; Beispiele insert into druckersteuerung (befehl, code) values ('6 Zeilen/Zoll', hextoraw('1B266C3644')); select preis * to_number ('1,8', '9D9') from auftrag_pos where auftrag_nr = 1; vsize(sysdate) Ergebnis: 8 Konvertierungsfunktionen 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 Formatzeichen DD 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 Beispiele select 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: Document1 Data Warehouse Technik im Fokus - Skripte in Kurzform Verwalten des Systems / Systembeobachtung Relevante Fragestellungen Systemzustände abfragen Datenmengen abfragen Benutzerzugriffe monitoren Performance messen Anzeigen der gesetzten Schwellwerte für Alerts select metrics_name, warning_value, critical_value, object_type, object_name, status from dba_thresholds Alerts abfragen SQL> select reason from dba_outstanding_alerts; REASON -----------------------------------------------------Tablespace [TEST_ALERT] is [83 percent] full Tablespace [PERF] is [93 percent] full Tablespace [PART] is [87 percent] full Alerts abfragen (historisch) column OBJECT_NAMe format a50 column REASON format a50 column SUGGESTED_ACTION format a50 select CREATION_TIME,reason, OBJECT_NAME, suggested_action from dba_alert_history; Alert-Datei-Ablage Show Parameter background_dump_dest Wait Classes abfragen select wait_class#, wait_class, count(*) from v$event_name group by wait_class#, wait_class order by wait_class# / 0 Other 717 1 Application 2 Configuration 24 3 Administrative 4 Concurrency 32 5 Commit 6 Idle 94 7 Network 8 User I/O 45 9 System I/O 10 Scheduler 7 11 Cluster 12 Queueing 9 17 54 2 35 30 50 Wait Events select 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 Undos SELECT 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 Session Sortvorgänge auf Platte oder im Speicher column 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-Informationen column column column column column column username format a15 Machine format a30 username format a10 SCHEMA format a10 machine format a18 sid format 9999 Document1 33 /41 column program format a15 column process format a10 column 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 1 orcl 149 2527 151 TIF TIF DE-ORACLE\ASCHLAUC ASCHLAUC sqlplus.ex 3 SELECT. .. ACTIVE 1240:5556 USER 15.09.2011 18:20:43 0 orcl 3 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------ACTIVE 284 BACKGROUND 13.09.2011 14:13:34 3 orcl 4 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------ACTIVE 4684 BACKGROUND 13.09.2011 14:13:34 1 orcl 5 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------ACTIVE 2704 BACKGROUND 13.09.2011 14:13:35 1 orcl 6 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------ACTIVE 3356 BACKGROUND 13.09.2011 14:13:35 2 orcl 7 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------ACTIVE 4308 BACKGROUND 13.09.2011 14:13:35 1201 orcl 8 1 0 SYS ASCHLAUC ASCHLAUC ORACLE.EXE 0 --------------ACTIVE 4100 BACKGROUND 13.09.2011 14:13:36 0 ~~~ AWR (Analytic Workload Repository) Einstellungen select name,value, description from v$parameter where name = 'statistics_level'; Eingestellte Intervalle Select * from dba_hist_wr_control; Platzverbrauch AWR messen Select occupant_name, space_usage_kbytes from V$SYSAUX_OCCUPANTS where occupant_name = 'SM/AWR' Data Warehouse Technik im Fokus - Skripte in Kurzform Auflistung bestehender Snapshots -- Output in Trace-Datei select SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME, FLUSH_ELAPSED,SNAP_LEVEL from dba_hist_snapshot; AWR-Snapshot manuell anlegen execute dbms_workload_repository.create_snapshot('ALL'); Snapshot-Nummern ausfindig machen select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where snap_id > 200 order by snap_ID Oder select 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 erstellen Spool xxx select output from table(dbms_workload_repository.awr_report_text(1053456982,1,19 9,200)); Spool off; [select output from table(dbms_workload_repository.awr_report_text(1053456982,1,22 7,228)) ;] Relevante Dictionary Views für Alerts und Session V$SESSION ADDM Informationen abfragen Welche Informationen liegen vor select task_id,recommendation_count as rc, description from dba_advisor_tasks where created >= trunc(sysdate); ADDM-Analyse starten declare 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 anzeigen Set long 1000000 Set pages 0 Set longchunksize 1000 Column get_clob format a80 Select dbms_advisor.get_task_report('ADDMdbc 3', 'TEXT', 'TYPICAL') from dual; Relevante Dictionary Views für Alerts und Session DBA_THRESHOLDS DBA_OUTSTANDING_ALERTS V$SESSION V$DATABASE 34 /41 DBA_ALERT_HISTORY V$EVENT_NAME V$INSTANCE DBA_ADVISOR_TASKS *** 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 STMT EXEC #1:c=0,e=1082,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=337 18840965 *** 2011-09-05 08:09:02.890 CLOSE #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_transaktionen END OF STMT PARSE #3:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3695442063, tim=33728275598 EXEC #3:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3695442063, tim=33728275918 FETCH #3:c=0,e=792,p=0,cr=49,cu=0,mis=0,r=1,dep=0,og=1,plh=369544206 3,tim=33728276801 STAT #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,t im=33728277502 *** 2011-09-05 08:09:15.453 CLOSE #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 formatieren tkprof orcl_ora_4488.trc c:\abc.txt explain=sys/sys sort=fchqry Session-bezogene Informationen Session Daten abfragen SQL> SELECT sid, serial# FROM gv$session WHERE username = 'MON'; -> 134 / 63 Größe SGA und entsprechende Speicherbereiche Show sga; bzw. select * from v$sgainfo; Abfragen der idealen Memory-Ausnutzung SELECT value FROM v$pgastat WHERE 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 Speicher Tracing Trace-Output-Verzeichnis SQL> show parameter user_dump_dest user_dump_dest string d:\ora\diag\rdbms\orcl\orcl\trace Identifizierung einer zu prüfenden Session select sid,serial#,terminal,program,module from v$session; 130 131 132 135 141 1 1 1 177 1 ASCHLAUC ASCHLAUC ASCHLAUC ASCHLAUC ASCHLAUC ORACLE.EXE (LGWR) ORACLE.EXE (SMON) ORACLE.EXE (MMON) sqlplus.exe sqlplus.exe ORACLE.EXE (CJQ0) Aktivieren des SQL-Trace execute dbms_monitor.session_trace_enable(135,177,true); -- TRUE / FALS mit bzw. Ohne waits und zusätzliche Analysen Deaktivieren execute dbms_monitor.session_trace_disable(135,181); Beispiel-Trace-Session SQL> select count(*) from dwh.wh_transaktionen; 4216 SQL> execute dbms_monitor.session_trace_disable(135,181); Document1 SET PAUSE ON SET PAUSE 'Press Return to Continue' SET HEADING ON SET LINESIZE 300 SET PAGESIZE 60 COLUMN 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 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’ 35 /41 WHERE sample_time > SYSDATE - 1/24 AND session_type = 'FOREGROUND' GROUP BY sql_id ORDER BY COUNT(*) DESC; Die aktivsten IO-Operationen SELECT sql_id, COUNT(*) FROM gv$active_session_history ash, gv$event_name evt WHERE ash.sample_time > SYSDATE - 1/24 AND ash.session_state = 'WAITING' AND ash.event_id = evt.event_id AND evt.wait_class = 'User I/O' GROUP BY sql_id ORDER BY COUNT(*) DESC; select SID,serial#,schemaname,module from v$session where TYPE = 'USER'; alter system kill session 'sid,serial#'; set linesize 121 Schnelle Übersicht über aktuell laufende Sessions / wer / wo / was SELECT * FROM TABLE(dbms_xplan.display_cursor('...sql_id_#...')); set line 200 column machine format a30 select 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 DEORACLE\ASCHLAUC long_proc 63 SYS INACTIVE SYS Administrator alfred SQL Developer 132 MON INACTIVE MON aschlauc DEORACLE\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 Session column USERNAME format a10 column name format a30 column machine format a40 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 SAMPLE_TIME EVENT WAIT_TIME ---------------------------------- ---------------- 05-SEP-11 08.47.44.282 PM 1 05-SEP-11 08.46.42.283 PM 1 Aktives SQL Abfragen SELECT sql_text, application_wait_time FROM gv$sql WHERE sql_id IN ( SELECT sql_id FROM gv$active_session_history WHERE TO_CHAR(sample_time) = '05-SEP-11 08.44.53.283 PM' AND session_id = 134 AND session_serial# = 63); SQL> desc v$session V$STATNAME V$MEMORY_TARGET_ADVICE V$EVENT_NAME ASH (Active Session History), Session + User Informationen Größe ASH - Buffer SELECT * FROM gv$sgastat WHERE name = 'ASH buffers'; Desc gv$active_session_history, SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1; Session Daten abfragen SQL> SELECT sid, serial# FROM gv$session WHERE username = 'MON'; -> 134 / 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 c8 from dba_hist_sqlstat sql, dba_hist_snapshot s, dba_hist_SQLTEXT t where s.snap_id = sql.snap_id and sql.PARSING_SCHEMA_NAME = 'DWH1' and t.SQL_ID = sql.SQL_ID and sql.sql_id = '01978kjxb5yd2' and to_char(s.begin_interval_time,'mm-dd hh24') = '09-12 13' order by c1, c2; Session-Informationen Relevante Dictionary Views 63 Die aktivsten SQLs in der letzten Stunde SELECT sql_id,COUNT(*), ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history WHERE sample_time > SYSDATE - 1/24 AND session_type = 'BACKGROUND' GROUP BY sql_id ORDER BY COUNT(*) DESC; SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history Document1 SELECT sample_time, event, wait_time FROM gv$active_session_history WHERE session_id = 134 AND session_serial# = 63 Übersicht über SQL-Statements in der Vergangenheit select s.sid, s.username, s.program, s.machine, sa.name, sum(ss.value) value from v$sesstat ss, v$statname sa, v$session s where 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 V$SESSTAT V$SESSION V$PGASTAT V$SYSTEM_EVENT Sample-Time abfragen Relevante Dictionary Views GV$ACTIVE_SESSION_HISTORY GV$SQL DBA_HIST_SNAPSHOT V$SESSION GV$EVENT_NAME DBA_HIST_SQLSTAT DBA_HIST_SQLTEXT SQL-Monitoring SQL Cache Der 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$sql where buffer_gets > 1000 order by buffer_gets desc; Data Warehouse Technik im Fokus - Skripte in Kurzform 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 a50 column Module format a10 column 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 0 SELECT 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 2185 SELECT a.sparte_name, z.Jahr_Nummer, r.land,v DWH QL*Plus 2 133 23 SELECT 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_Monitor column sql_text format a75 column userName format a10 select 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_START order 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 haben Liste aller SQLs ausser SYS seit letzten Hochfahren der DB Rechenzeit aufsummiert über alle Prozesse column sql_text format a75 column 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') laufzeit from v$sql_monitor group by SQL_ID,SQL_EXEC_ID,SQL_EXEC_START Document1 36 /41 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) b where a.SQL_ID = b.SQL_ID and a.SQL_EXEC_ID = b.SQL_EXEC_ID and a.laufzeit = b.laufzeit and substr(b.sql_text,1,2) != ' ' and b.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 aufrufen spool c:\abc select dbms_sqltune.report_sql_monitor( type => 'html', sql_id => 'f1fj6drvxwk9h') as sql_monitor from dual; spool off; Lese-Statistiken auf einzelne Tabellen gezielt abfragen 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; Relevante Dictionary Views V$SQL V$SQL_MONITOR DBA_HIST_SEG_STAT DBA_HIST_SEG_STAT_OBJ DBA_HIST_SNAPSHOT Planmanagement Automatisiertes Sammeln von Plänen einschalten alter system set optimizer_capture_sql_plan_baselines=true; Parameter zur Behandlung von Plänen SQL> show parameter optimizer NAME TYPE VALUE ------------------------------------ ----------optimizer_capture_sql_plan_baselines boolean optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.2.0.1 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean optimizer_use_invisible_indexes boolean optimizer_use_pending_statistics boolean optimizer_use_sql_plan_baselines boolean --------TRUE TRUE FALSE FALSE TRUE Nutzen der Pläne einschalten alter system set optimizer_use_sql_plan_baselines=true; Anzeigen von Plänen in Plan_Baselines - Eingeschränkt auf einen User select 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 where creator = 'MON'; Data Warehouse Technik im Fokus - Skripte in Kurzform SQL_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=:1 SYS_SQL_294c437e331fa51f SQL_PLAN_2km23gstjz98zdf463620 YES YES NO 0 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DIS SYS_SQL_2bdf77bedbcdea50 SQL_PLAN_2rrvrrvdwvukhed88afee YES YES NO 0 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE SYS_SQL_4e5c4235c17d5d62 SQL_PLAN_4wr226r0rurb22e8a86b7 YES YES NO 0 SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SI SYS_SQL_85372e07e425b213 SQL_PLAN_8adtf0zk2bchm35b3cdca YES YES NO 0 SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1 SYS_SQL_967d7c5636192728 SQL_PLAN_9czbwasv1k9t8499f732f YES YES NO 0 select distinct VERTRIEBSKANAL from BESTELLUNG_PAR SYS_SQL_b96c99b551913735 SQL_PLAN_bkv4tqp8t2dtp6be2eac7 YES YES NO 0 select * from tab SYS_SQL_bff897b9dbcabe27 SQL_PLAN_bzy4rr7dwpgj7ed88afee YES YES NO 0 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DA Betrachten eines gespeicherten Plans select * from table( dbms_xplan.Display_sql_Plan_BASELINE( SQL_HANDLE=> 'SYS_SQL_967d7c5636192728', format=>'basic')); PLAN_TABLE_OUTPUT -----------------------------------------------------------SQL handle: SYS_SQL_967d7c5636192728 SQL text: select distinct VERTRIEBSKANAL from BESTELLUNG_PART_RANGE_4 ----------------------------------------------------------Plan name: SQL_PLAN_9czbwasv1k9t8499f732f Plan id: 1235186479 Enabled: 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 Plans set serveroutput on set long 10000 declare report clob; begin report := dbms_spm.evolve_sql_plan_baseline( sql_handle => 'SYS_SQL_85372e07e425b213'); dbms_output.put_line(report); end; / Relevante Dictionary Views DBA_SQL_PLAN_BASELINES Lese-Performance messen Abschä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 on SQL> 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äten Parallel Sekunden GB/Sec Sekunden(2 Sessions)Sekunden(4 Sessions) 1 59~0,13 1,00/1,41/1,50/1,51 Document1 37 /41 2 4 6 8 43~0,18 30~0,25 31~0,25 30~0,25 0,53/0,57 1,28/1,40/1,52/1,55 Relevante Dictionary Views V$SESSTAT V$SESSION DBA_DATA_FILES V$STATNAME V$IOSTAT_FILE IO Messung / calibrate IO disk_asynch_io boolean filesystemio_options TRUE string SETALL Calibrate-Status abfragen [Kennt der Optimizer die Leistungsfähigkeit des Systems] select status from V$IO_CALIBRATION_STATUS; Calibrate IO abfragen DECLARE 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 = 164 latency = 11 max_mbps = 95 Perfstat -- Tablespace für Perfstat-Tabellen anlegen CREATE 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.snap EXECUTE 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 Orion Infos über ORION -Help ORION: 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+) IOs at various load levels. Aufruf über C:\>orion -run dss -testname 'D:\Orion\laufwerk' -num_disks 4 -cache_size 0 ORION: ORacle IO Numbers -- Version 11.2.0.1.0 D:\Orion\laufwerk_20110902_1032 Calibration 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\laufwerk Small IO size: 8 KB Large IO size: 1024 KB IO types: small random IOs, large random IOs Sequential stream pattern: one LUN per stream Writes: 0% Cache size: 0 MB Duration for each data point: 240 seconds Small Columns:, 0 Large Columns:, 4, 8, 12, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56, 60 Data Warehouse Technik im Fokus - Skripte in Kurzform Total Data Points: 19 Name: \\.\D: Size: 500105217024 Name: \\.\E: Size: 500105217024 Name: \\.\F: Size: 500105217024 Name: \\.\G: Size: 500105217024 4 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 3355361 06.09.2010:23:00 4030816 673296 4028177 672816 07.09.2010:12:32 8060160 4029344 8054609 4026432 07.09.2010:15:50 6886881 1 col c1 heading col c2 heading col c3 heading col c4 heading col c5 heading col c6 heading col c7 heading col c8 heading col c9 heading break on c1 ‘Begin|Interval|time’ format ‘SQL|ID’ format a13 ‘Exec|Delta’ format 9,999 ‘Buffer|Gets|Delta’ format ‘Disk|Reads|Delta’ format ‘IO Wait|Delta’format 9,999 ‘Application|Wait|Delta’ format ‘Concurrency|Wait|Delta’ format 'SQL-Text' format a50 a8 9,999 9,999 9,999 9,999 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 c8 from dba_hist_sqlstat sql, dba_hist_snapshot s, dba_hist_SQLTEXT t where s.snap_id = sql.snap_id and sql.PARSING_SCHEMA_NAME = 'DWH1' and t.SQL_ID = sql.SQL_ID and sql.sql_id = '01978kjxb5yd2' and to_char(s.begin_interval_time,'mm-dd hh24') = '09-12 13' order by c1, c2; Relevante Dictionary Views DBA_HIST_SEG_STAT DBA_HIST_SEG_STAT_OBJ DBA_HIST_SNAPSHOT DBA_HIST_SQLSTAT DBA_HIST_SQLTEXT Document1 38 /41 Data Warehouse Technik im Fokus - Skripte in Kurzform 39 /41 An Zieldatenbank einwählen Backup im Data Warehouse Allgemeine Überlegungen Argumente 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 BackupKosten 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 werden o Read Only Tablespaces von älteren Daten in den dafür vorgesehenen Partitionen o 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. Connect target orcl Welche Backups sind überflüssig REPORT OBSOLETE; Welche Files benötigen ein Backup REPORT NEED BACKUP; Leeren Recover Area delete archivelog all; Welche Files können nicht wiederhergestellt werden REPORT UNRECOVERABLE; Welche Sicherungen liegen vor LIST BACKUP; Alle Einstellungen von RMAN SHOW ALL; Definieren eines Backup-Kanals CONFIGURE 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 Area SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest' Prüfen, ob Flashback aktiviert ist SELECT flashback_on, log_mode FROM gv$database; Prüfung des benötigten Platzes SELECT ESTIMATED_FLASHBACK_SIZE, RETENTION_TARGET, FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG Retention-Zeit Flashback Area einstellen ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 Abfragen einer Tabelle von einem bestimmten SCN-Zustand Wie wird gesichert Es 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 SELECT * FROM x AS OF SCN 12555060; Abfragen einer Tabelle zu einer bestimmten Zeit SELECT * FROM x AS OF TIMESTAMP to_timestamp('2012-02-15 10:15:00', 'YYYY-MM-DD HH:MI:SS'); Tabellen mit Flashback zurückholen FLASHBACK TABLE dwh.x TO SCN 16552768; FLASHBACK TABLE x TO SCN 16553108; Datenbank mit Flashback zurücksetzen (Kommandobeispiele) FLASHBACK FLASHBACK FLASHBACK FLASHBACK DATABASE DATABASE DATABASE DATABASE TO TO TO TO SCN 5964663 BEFORE SCN 5964663 TIMESTAMP (SYSDATE -1/24) SEQUENCE 12345 Ältest mögliche Rückhol-Position feststellen Relevante Fragestellungen Befindet 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-Modus shutdown immediate; startup mount alter database archivelog; [alter database noarchivelog;] alter database open; Archive-Zustand anzeigen lassen Archive log list; Wohin wird das Archive-Log geschrieben und wie groß ist die Recovery Area SHOW Parameter db_recovery_file_dest Wie voll ist die Recovery Area aktuell SELECT * FROM v$recovery_file_dest; Recovery Area vergrößern alter system set db_recovery_file_dest_size=7824M scope=both; Relevante Dictionary Views für Alerts und Session V$DATABASE V$ARCHIVED_LOG V$ARCHIVE_DEST V$ARCHIVE_PROCESSES RMAN RMAN starten Rman Document1 V$BACKUP_REDOLOG V$LOG V$LOG_HISTORY SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG Data Warehouse Technik im Fokus - Skripte in Kurzform Anhang (DWH-Administrations-Checkliste - wird aktualisiert) Informationsbedarf planen Anforderungen Fachanwender Anforderungen Folgesysteme Synchronisation der Informationen Verhindern von doppelter Information (3-) Schichtenmodell Passende Aufbereitung und Präsentation Multidimensionales Modell (Star) Kennzahlensystem (Materialized Views-Konzept) Dimensionen-Objekte in der Datenbank Saubere Parent/Child-Beziehungen zwischen DimensionsLeveln PCT bei Materialiezd Views nutzen Aktualisierungskonzept für Materialiezed Views Regelmäß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üfen Star-Transformatio einschalten Statistiken aktuell halten Unused-Indexes prüfen Statistiken auf Tabellen und Index aktuell halten ETL-Prozess mit Checkpunkten versehen ETL-(Teil-)Prozess(e) wiederholbar machen Vor dem Laden Betroffene Indexe löschen Constraints ausschalten Logging prüfen Direct Path prüfen Datenqualität mit Bordmitteln machen Partitioning gezielt einsetzen (Range / List) Partition Exchange Load (PEL) gezielt einsetzen ETL-Strecke monitoren Welches sind die aufwendigen Teilstrecken? Resourcen-Verbrauch Werden nur die benötigten Daten gelesen? Verlagerung von teuren Transformationen in die Datenbank (Table Functions). Datendurchsatz Ist bekannt wo bei der Hardware-Umgebung die Schwachstellen liegen? Wird die Parallelisierung sinnvoll eingesetzt Parallelisierung auf Einzelobjekte im ETL-Lauf und bei Batch-Reporting. Pauschale automatisch geregelte Parallelisierung beim Online-Reporting. Umgang mit Storage Gibt es ein LifeCycle-Konzept? Ist bekannt, welche daten oft / weniger oft genutzt werden? Einsatz von Flashspeicher? 40 /41 from dual connect by level < 2000 [Auf der Community-Web-Seite gibt es vorbereitete Skripte für umfangreichere Testdatenerzeugung: http://www.oracledwh.de/downloads/AutoIndex2.2.4/index.php?dir=downloads/DWH_Utilities/Testdatengenerator _V1/] Vorgehensweise bei der Erstellung der DemoUmgebung Beispieldaten sind zu finden unter: http://www.oracledwh.de/downloads/AutoIndex2.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 Punkten 2 - 5 dargestellt ist, oder man wählt die einfachste Variante: Man ruft nacheinander die beiden Skripte: 01_DDL_Star_Skriptesammlung.SQL 02_DML_Beispieldaten_Komplett.sql (03_Indexe_Statistiken.sql) 2. 3. 4. 5. Testdaten erstellen Relevante Fragestellungen Testdaten für Warehouse-Systeme zu Testzwecken schnell erstellen Das 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 erstellen create table zahl level < 1000000; as select level nr from dual connect by 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. Tabelle mit laufender Nummer erstellen Das 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 ran_abc_mixed(15) ran_m_n(1,20) ran_m_n(1,100) ran_abc_mixed(20) Document1 buch_id, Titel, isbn, preis, autor 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_Umsatz b. D_Zeit c. D_Kunde d. D_Region e. D_Artikel f. D_Vertriebskanal (Alternativ dazu auch die Daten DDL_Star_Skriptesammlung) 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. 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. 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; 6. Danach kann man die nötigen Constraints, Indexe usw. einrichten, so wie sie in der Skritesammelung dargestellt sind. Hilfsprozeduren zu den Beispielmodellen Zeitdimension create or replace procedure Time_Gen (startdate DATE, enddate DATE) AS incdate DATE; recno INTEGER; Begin incdate := startdate; recno := 1; while incdate <= enddate LOOP insert into D_ZEIT(ZEIT_ID, Datum_ID, Tag_des_Monats, Data Warehouse Technik im Fokus - Skripte in Kurzform Tag_des_Jahres, Woche_des_Jahres, Monats_Nummer, Monat_Desc, Quartals_Nummer, Jahr_Nummer) Values (incdate, recno, TO_NUMBER(TO_CHAR(incdate, TO_NUMBER(TO_CHAR(incdate, TO_NUMBER(TO_CHAR(incdate, TO_NUMBER(TO_CHAR(incdate, TO_CHAR(incdate, 'Month'), TO_NUMBER(TO_CHAR(incdate, TO_NUMBER(TO_CHAR(incdate, 'DD')), 'DDD')), 'WW')), 'MM')), 'Q')), '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_UMSATZ create 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 Document1 41 /41
