CMD_DWH_Kurzreferenz_V3
Werbung
Data Warehouse Technik im Fokus - Skripte in Kurzform 1 /42 Oracle Data Warehouse Kurzreferenz Skripte, Systemabfragen und Empfehlungen Stand Oktober 2015 V5 (18.10.2015) Document1 Data Warehouse Technik im Fokus - Skripte in Kurzform Zu dieser Skriptesammlung 5 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 Ausschalten des Password-expire-Verhaltens in Testumgebungen 6 SQL Plus 6 Metadaten-DDL für Objekte aus dem Dictionary generieren lassen 6 Enterprise Manager 6 Aufruf im Browser z. B. mit [gilt nur bis Oracle 11] Starten der Console Wenn es mal klemmt Aufruf EM Express im Browser ab Oracle 12 6 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 Bereitstellung der In Memory Datenbank - Umgebung 6 Beispielumgebung 7 Beispiel-OLTP Schema 7 Skript –Tabellen OLTP-System 7 Beispiel-Star Schema 7 Skript Skript Skript Skript Skript Skript Skript Skript Skript Skript nötig] Skript Skript Skript Skript Fakten-Tabelle 7 Kunden-Dimension 7 Artikel-Dimension 7 Regionen-Dimension 7 Zeit-Dimension 7 Vertriebskanal-Dimension 7 Unique Keys für die Dimensionstabellen [nicht nötig] 7 Bitmap-Indexe auf FK-Felderder Fakten-Tabelle F_UMSATZ 8 Drop Bitmap Indexe 8 Primary-Key-Felder auf Dimensions-Tabellen [nicht 8 Dimensional Table D_ARTIKEL 8 Dimensional Table D_Region 8 Dimensional Table D_Zeit 8 Dimensional Table D_Kunde 8 Vergleichstests 8 Vergleichs-Demo-Umgebung Steckbrief Eigenschaften der Demosysteme Zur Bewertung der Testreihe und der Vergleichszahlen Die erste Testreihe Die zweite Testreihe 8 8 8 8 8 Beispielabfragen auf das Beispiel-Star Schema 9 Schalterstellungen für die unterschiedlichen Mess-Szenarien 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 10 Beispielabfrage3 (Ein-/Ausschalten Where-Argumente /Test Star Query 10 Beispielabfrage 4: Top 3 Länder bezogen auf Einzelverkäufe 10 Beispielabfrage 5: Umsatz nach Quartalen 10 Beispielabfrage 6: Nach Umsatzstärke sortierte QuartalsRangfolge 10 Beispielabfrage 7:: Das umsatzstärkste Quartal pro Jahr 10 Beispielabfrage 8: Stärkster Umsatzmonat pro Bundesland 11 Beispielabfrage9: Top 10 Artikel bezogen auf Menge / Rank /Subquery 11 Beispielabfrage9a: Top 10 Artikel bezogen auf Menge / Rank /Subquery 11 Beispielabfrage10: -- Top 10 Artikel bezogen auf Umsatz in einem Jahr 11 Beispielabfrage 11: - Top 3 Produkte je Produktgruppe sortiert nach Rangfolge 11 Beispielabfrage 12: Die Top-Produktgruppe pro Bundesland 11 Beispielabfrage 13: Land mit dem stärksten Umsatz je Produktgruppe 12 Beispielabfrage 14: Die Top und Flop Produktgruppe pro Bundesland 12 Beispielabfrage 15: Die Top 2 Produktgruppen pro Bundesland 12 Beispielabfrage 16: Top/Bottom 2 Produktgruppen pro Bundesland 12 Beispielabfrage 17: Über Quartale kumulierte Umsätze pro Kunde 12 Beispielabfrage 18: Über Quartale kumulierte Umsätze pro Kunde (Jahr + Kreis) 13 Beispielabfrage 19: Wieviel Prozent des Gesamtumsatzes pro Land machen die 3 Top Produkte aus 13 Beispielabfrage 20: ¼ der Kunden tragen zu ? % des Umsatzes bei? 13 Document1 2 /42 Beispielabfrage21: Durchschnittliche Bestellquote eines Kunden über 3 Monate? 13 Beispielabfrage 22: - CUBE 13 Beispielabfrage23 Vergleiche Umsätze mit Vorjahreszeitraum 13 Beispielabfrage 24 Year-To-Date. Kumulierung der Monatsumsätze von Jahresbeginn an 13 Beispielabfrage 25 Ausgabe von Summenzeilen einer Aggregation (GROUP BY ROLLUP) 14 Beispielabfrage 26 Verwendung von Grouping-Sets 14 Tabellen 14 Allgemeiner Umgang mit Tabellen 14 Logging/Nologging-Modus für eine einzelne Tabelle ein/ausschalten 14 Allgemeine Informationen 14 Tabellengrößen und Platzverbrauch 14 Tabellengrößen , Cache, Parallel, Compression 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 15 Abfragen auf bestehende Constraints im aktuellen Schema Ausschalten / Einschalten von Constraints 15 15 Compression 15 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 Utility zum Testen einer möglichen Kompressionsrate Gleiche Variante wie zuvor, nur Beispiel zum Testen der InMemory-Compression Partitioning 15 15 15 15 15 15 15 15 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 16 16 16 16 16 16 16 16 16 16 16 16 16 17 17 17 17 17 17 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 18 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) 18 18 18 18 18 18 18 18 18 In Memory Column / Row Store Abfrage der Größe des In Memory-Speicher-Bereichs Ansicht komplette SGA mit In-Memory-Storage 18 18 18 18 Data Warehouse Technik im Fokus - Skripte in Kurzform 3 /42 Ein- / Ausschalten der In Memory Query Funktion 18 Markieren von Tabellen, um diese in den Column Store zu laden 18 Tabelle aus In-Memory-Speicher löschen 18 Erstellen einer partitionierten Tabelle inm Kontext on In Memory 18 Ausschließen von bestimmten Spalten aus der In-MemorySpeicherung 18 Priorisierung von Spalten 18 Komprimierung von einzelnen Spalten 18 Unterschiedliche Intensität der Komprimierung 18 Abfrage auf Objekte, die in dem Column Store (In Memory) gehalten sind 18 Abfrage über die In Memory-Zustände von Tabellen 18 Abfragen von In Memory - Zugriffen 19 Verlagung einer kompletten Datenbank in den Column-Store (Buffer Pool) 19 Abfragen der Cache Database Situation 19 Tabellen in Cache legen / aus Cache entfernen Welche Tabellen liegen im Cache 23 23 Query Result Cache 23 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 Parallelisierung aktivieren Automatisches Steuern mit Eine einzelne Tabelle auf parallel” setzen Parallelisierter Select-Zugriff Prüfen welche Art der Parallelisierung eingestellt ist Abfragen weleche Tabellen mit einem festgelegten Parallelisierungswert belegt sind Aktuelle SQL-Statements und deren Parallelisierung abfragen 23 23 23 23 23 Indexe Optimizer - Statistiken sammeln 23 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 24 24 19 Wie und wo wird indiziert Suche nachbestehenden Indexen 19 19 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 Clustering Faktor und Anzahl Leaf-Blöcke abfragen 19 19 19 19 19 19 19 19 19 19 19 19 19 19 Partitionierung von Indexen 19 Skript Local Partion Index 19 Partitionierung der Indexe entsprechend der zugehörigen TablePartionen (Local Partiton Index) und zweisen unterschiedlicher Tablespaces 19 Partitionierung des Index unabhängig von den Table-Partitionen (Global Partition Index) 19 Partitionierte Indexe suchen 20 Rebuild lokaler Index 20 Rebuild Sub Partition Index 20 Verschieben auf anderen Tablespace 20 Star Schema 20 Allgemeine Regeln Konsistenz, Mengen und Indexe im Star-Schema 20 20 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 20 20 20 20 20 20 Materialized Views 20 Hilfen/Konzepte für Materialized Views 20 Allgemeine Hinweise zur Definition von Materialized Views 20 Relevante Parameter 20 MVIEW suchen 21 Größe und Anzahl Zeilen von Mviews abfragen 21 Beispieldefinition MAV_Zeit_Umsatz 21 Beispieldefinition MAV_Artikel_Umsatz 21 Beispieldefinition MAV_Region_Umsatz 21 Beispieldefinition MAV_Kunde_Umsatz 21 Beispieldefinition MAV_Region_Artikel_Umsatz 21 Beispieldefinition MAV_Region_Zeit_Umsatz 21 Beispieldefinition MAV_Kunde_Zeit_Umsatz 21 Beispieldefinition MAV_Region_Zeit_Artikel_Umsatz 22 Beispiel-Definition 22 Beispiel für eine Partitionierte MAV 22 Definition Materialized View Log 22 Löschen Materilized View Log 22 Build Funktionen 22 Refresh Funktionen 22 Feststellen, ob PCT Tracking funktioniert 22 Refresh auf eine MAV 22 PMARKER Funktion 22 Komprimieren von Materialized Views 22 Refresh auf alle von einer Tabelle abhängigen MAVs (REFRESH DEPENDENT) 22 Aktualisieren aller Materialized Views 22 Anzeigen Materialized Views mit Zustand und View Logs 22 Anzeigen Materialized Views mit Zustand und PCT Regions 22 Stimmigkeit von Dimensionen prüfen 22 DBMS_MVIEW.Explain_mview 22 DBMS_MVIEW.EXPLAIN_REWRITE 22 Auflisten von Dimension-Tables 23 Anzeigen der Struktur einer Dimensional Table 23 Optimierung für schnelles Lesen Memory-Cache Document1 23 23 Umgebungsinformationen auslesen 23 23 24 24 Datenbank-Informationen, Version, Patchstände 24 Database-ID abfragen Installierte Komponenten Patchstand abfragen Patch-Historie Datenbankversion abfragen 24 24 24 24 24 Tablespaces, Auflistungen, Mengen und Größen 24 Anlegen eines Tablespace mit Zuweisung eines Datafiles Vergrößern eines Tablespace mit ADD DATAFILE Größe aller Data Files Auflistung aller Datafiles 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 24 24 24 24 24 24 24 25 25 25 25 Database Files 25 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 25 25 25 25 26 Lesestatistiken / Benutzungsverhalten 26 Gelesene und geschriebene Blöcke Security 25 26 26 Profile 26 Profil erstellen Ändern eines Profils Abfragen der Settings für ein Profile Ein Profil einem Benutzer zuordnen 26 26 26 26 Rollen 26 Eine Rolle anlegen und Rechte auf Objekte zuweisen Rollenrechte weitergeben Default-Rolle einem Benutzer zuweisen Rollen für einen Bnutzer aktivieren / deaktivieren 26 26 26 26 User-Informationen / Einstellungen 26 Welche USER gibt es in einer Umgebung und welchen Zustand haben sie Benutzer anlegen und löschen Rechte zuweisen um sich einloggen zu können Eigene Tabellen zum Lesen für alle freigeben Allgemeine Benutzerinformationen abfragen 26 26 26 26 26 Data Warehouse Technik im Fokus - Skripte in Kurzform Ausschalten Expiration Date in Entwicklungs- und Test-Umgebung 26 Ändern des Lock-Zustads eines Users 26 Welche Rechte wurden einem User vergeben 26 Plattenplatz der Segment-Objekte eines Users 27 Logins pro User 27 User Platzverbrauch Tablespace 27 Maximale Parallelität von Benutzeraktivitäten 27 Was machen die Benutzer gerade 27 Kontrollieren Welcher Benutzer macht was / AUDITING 27 Auditing auf eine Tabelle einschalten Abfrage der AUDIT-Ergebnisse Audit auf einen User Ausschalten des Audit Feststellen welchem Benutzer welche Rechte gegeben wurden 27 27 27 27 27 Fine Grained Auditing 27 Aktivieren des Fine Grained Auditing Abfragen des Fine Grained Auditing Löschen einer Policy 27 27 27 Resource Manager / Ressourcen kontrollieren 27 4 /42 IsDate-Prüfung 30 Abhängigkeiten von anderen Feldern im selben Satz 31 Satzübergfreifendes Zusammenzählen von Feldwerten (analytische Funktion) 31 Eindeutigkeitsprüfungen 31 Aggregatbildung und Bewerten von satzübergreifenden Summen 31 Foreign Key Prüfung 31 SQL-Mittel beim Laden 31 Pivot/Unpivot Beispiele 31 Multiple Inserts /Manuelles Aufspalten von korrekten und nicht korrekten Sätzen 31 Merge-Beispiel 32 Flashback 32 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 32 32 32 32 32 32 32 Reihenfolge der Erstellung von Ressourcen-Plänen 27 Table Function 32 Pending- Area einrichten / leeren /submitten Consumer Groups einrichten 27 27 Record / Object - Definition Definition einer Tabellen-Struktur Table-Function-Definition 32 32 32 Consumer-Gruppe einrichten Welche Consumer Gruppen gibt es 27 28 SQL / PL/SQL - Funktionen 32 Ressourcen Pläne 28 Methoden nach denen gesteuert werden kann Plan erstellen Plan Direktive erstellen Pending Area Prüfen Abfrage auf bestehende Pläne Zuweisen von Benutzern zu Consumer – Gruppen Abschliessen und Aktiv-Setzen der Pending-Area als letzten Schritt Welche User gehören zu welchen Consume Gruppen Plan aktiv setzen Abfragen auf aktiven Plan Abfragen auf verbrauchte Ressourcen der Consumer Gruppen 28 28 28 28 28 28 Leeres PL/SQL-Function-Template Template für PL/SQL-Prozedur mit Cursor Erzeugen Universal Identifier Aktivieren von Serveroutput 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) 32 33 33 33 33 33 33 33 33 33 Laden des DWH 28 28 28 28 28 28 Verwalten des Systems / Systembeobachtung 34 34 34 34 Schnelles Laden / Mengenbasiertes Laden 28 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 28 28 28 28 28 28 Anzeigen der gesetzten Schwellwerte für Alerts Alerts abfragen Alerts abfragen (historisch) Alert-Datei-Ablage Wait Classes abfragen Wait Events Menge der Undos 34 34 34 34 34 34 34 Database Link 28 Zugriff auf Remote-Oracle-Datenbank (Database Link) Verwendung 28 28 Informationen über die Session 34 Sortvorgänge auf Platte oder im Speicher Session-Informationen 34 34 Sequence für Zähl-Felder / Schlüssel aufbauen 28 Defintion eines Sequence-Objektes Zugriff um den nächsten Zählerwert abzugreifen Abfragen des aktuellen Stands 28 28 28 AWR (Analytic Workload Repository) 35 Trigger 29 Einstellungen Eingestellte Intervalle Platzverbrauch AWR messen Auflistung bestehender Snapshots AWR-Snapshot manuell anlegen Snapshot-Nummern ausfindig machen AWR-Bericht erstellen 35 35 35 35 35 35 35 ADDM Informationen abfragen 35 Welche Informationen liegen vor ADDM-Analyse starten ADDM-Bericht anzeigen Relevante Dictionary Views für Alerts und Session 35 35 35 35 Tracing 35 Trace-Output-Verzeichnis Identifizierung einer zu prüfenden Session Aktivieren des SQL-Trace Deaktivieren Beispiel-Trace-Session Unleserlichen Trace-Output mit TKPROF formatieren 35 35 35 35 36 36 Session-bezogene Informationen 36 Beispiel für einen Trigger (Insert,Delete,Update) 29 Text-Dateien Importieren 29 SQL Loader Beispiel für Loader Control – File External Table Beispiel 29 29 29 Directory-Objekte 29 Directory anlegen Auflisten bestehender Directory-Definitionen Logischen Directory-Name ändern Name der Daten-(CSV-)Datei ändern 29 29 29 29 Datapump 29 Datapump - Beispiel mit Steuerdatei Allgemeines Beispiel Umändern des Default-Output-Directories Datapump-Schätzung benötigter Plattenplatz Datapump Data only Import Datapump Schema Mode Datapump Network Mode Interaktiver Modus mit CTRL C aktivierbar Datapump und External Tables 29 29 29 29 29 29 29 29 29 Transportable Tablespace 29 Transportable Tablespace-Verfahren 29 Error Logging 30 Error Logging Eindeutigkeitsprüfung ohne DML_Error_Logging 30 30 Regular Expressions 30 Regular Expression in Constraints verwenden Umgang mit einzelnen Zeichen: Bestehende Zeichengruppen 30 30 30 Arbeiten ohne Constraints und mengenbasiertes SQL / Prüfen 30 Aus- und Einschalten aller Constraints CASE in SQL-Statements / Manuelles Prüfen IsNumeric-Prüfung über separate Funktion Numieric-Prüfung mit Regexp Document1 30 30 30 30 Session Daten abfragen 36 Größe SGA und entsprechende Speicherbereiche 36 Abfragen der idealen Memory-Ausnutzung 36 Abfragen der SGA / Welche Objekte sind im Speicher 36 Sessions schnell ‘killen’ 36 Schnelle Übersicht über aktuell laufende Sessions / wer / wo / was 36 Verhindern von Memory Paging 36 Herausfinden Memory und Session 36 ASH (Active Session History), Session + User Informationen 37 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 Aufbereitung Statements pro user mit SQL – Text Session-Informationen 37 37 37 37 37 37 37 37 37 Data Warehouse Technik im Fokus - Skripte in Kurzform SQL-Monitoring 37 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 37 Planmanagement 38 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 38 38 38 Lese-Performance messen 39 Abschätzen Lesegeschwindigkeit (IO-Performancen) Lesen einzelner Tabellen 37 Zu dieser Skriptesammlung 37 38 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 Datenbank- 38 38 38 38 38 39 bei dem 39 IO Messung / calibrate IO 39 Calibrate-Status abfragen Calibrate IO abfragen 39 39 Perfstat Orion Lesestatistiken: Werden Daten genutzt? 39 39 39 Backup im Data Warehouse 40 Allgemeine Überlegungen 40 Argumente für ein DWH spezifisches Backup-Konzept Was muss gesichert werden. Wie wird gesichert 40 40 40 Archive Log 40 Festeststellen des Log-Modus der Datenbank 40 Ein-(Aus-)schalten des Archivelog-Modus 40 Archive-Zustand anzeigen lassen 40 Wohin wird das Archive-Log geschrieben und wie groß ist die Recovery Area 40 Wie voll ist die Recovery Area aktuell 40 Recovery Area vergrößern 40 RMAN 40 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 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 Anhang 40 (DWH-Administrations-Checkliste - wird aktualisiert) Testdaten erstellen 40 41 SQL-Trigger-Tabelle für Dummy-FROM-Klausel erstellen Tabelle mit laufender Nummer erstellen 41 41 Vorgehensweise bei der Erstellung der Demo-Umgebung Hilfsprozeduren zu den Beispielmodellen 41 41 Zeitdimension Daten in der Faktentabelle F_UMSATZ 41 42 Document1 5 /42 Features den Erfordernissen in dieser Architektur unter. Dadurch entsteht automatisch eine durch Data Warehouse-Anforderungen motivierte Fokussierung bei der Datenbank-Technologie. Die Sammlung orientiert sich an einem einheitlichen Beispiel. Zu Beginn steht der Entwurf eines Star-Schemas. (Damit soll nicht gesagt werden, dass ein Data Warehouse nur aus einem Star-Schema besteht). Dieses Beispiel kann man durch die mitgelieferten Sourcen bzw. abgedruckten Skripte auch in einer eigenen Umgebung realisieren. Damit erhält man sofort eine Übungsumgebung, um die Wirkungsweise der jeweiligen Datenbank-Features zu testen. Zu dem Starschema gehören auch passende Abfragen, die möglichst viele potentiellen Problemstellungen bei der Abfrage des Schemas abdecken. Die Abfragen sind einfach und damit überschaubar gehalten. Die Beispielperformance-Werte liefern eine erste Orientierung, welcher Optimierungserfolg bei den jeweiligen Features erreichbar sein sollte. Die Werte stammen nur von einem schlichten Desktop-Rechner (also keine Hochleistungsmaschine). Das reicht, um die prinzielle Vorgehensweise für Optimierungen aufzuzeigen. Aber bereits in dieser Beispiel-Umgebung fällt es bei einigen Abfragen schwer, Performance-Steigerungen durch einzelne Features aufzuzeigen, da man sich auch bei einem Datenvolumen von mehr als 50 Millionen Sätzen in Anwortzeitbereichen von weniger als einer Sekunde bewegt. Und hier sind oftmals noch Netzübertragungs- und Bildschirmaufbauzeiten enthalten. Für das Starschema wird exemplarisch die passende (Bitmap-) Indizierung und Partitionierung vorgestellt. Dann folgen die nötigen Materialized Views. Die Summe aller Techniken führt in dem ausgeführten Beispiel zu fast nicht mehr spürbaren Antwortzeiten bei allen Beispielabfragen. Um eine leichtere Orientierung zu geben, sind an einigen Stellen farbige Blöcke eingebaut. Diese bedeuten: Performance-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. Data Warehouse Technik im Fokus - Skripte in Kurzform 6 /42 VARIABLE XQUERY Allgemeine Hilfen WHENEVER OSERROR Metadaten-DDL für Objekte aus dem Dictionary generieren lassen Einrichten und Einstellen einer Testumgebung Hier wird davon ausgegangen, dass man mit SQL Plus arbeitet. Alternativen sind z. B. der SQL Developer Mess-Einstellungen + allgemeine Settings in SQLPlus -- SET TIMING ON -- SET AUTOTRACE ON / set autotrace traceonly -- SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] -- set pagesize 99 -- set heading off -- set line 400 -- set long nnnn (vergößern des Ausgabe-Puffer) SELECT dbms_metadata.get_ddl('TABLE','BESTELLUNG_PART_RANGE_HASH') FROM dual; select dbms_metadata.get_ddl('INDEX','IDX_BILDUNGSGRUPPE_BM') FROM dual Enterprise Manager Aufruf im Browser z. B. mit [gilt nur bis Oracle 11] https://192.168.1.14:1158/em https://hostname:1158/em Starten der Console Gelöschte Tabellen endgültig löschen emctl start dbconsole PURGE RECYCLEBIN Oder gleich arbeiten mit [DROP TABLE tab_name PURGE;] Wenn es mal klemmt Ausschalten der Recyclebin-Funktion alter session set recyclebin=off; Speicher leeren Bei Fehlern wie: The OracleDBConsole[ServiceName] service could not be started. A service specific error occurred: 2 Hilft oft nur das Neukonfigurieren alter system flush shared_pool; alter system flush BUFFER_CACHE; alter system flush GLOBAL emca -deconfig dbcontrol db emca -config dbcontrol db SELECT-Ausgabeformatierung https://localhost:5500/em https://localhost:5500/em/login column feld column feld WHENEVER SQLERROR Aufruf EM Express im Browser ab Oracle 12 format a25 format a25 Port setzen: SQL> connect sys/<password>@<container> as sysdba Connected Systemparameter abfragen Entweder SHOW PARAMETER … oder die View V$PARAMETER abfragen. SQL> exec dbms_xdb_config.sethttpsport(5501); 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; PL/SQL procedure completed Gesetzten Port abfragen SQL> select dbms_xdb_config.getHttpsPort() from dual; GETHTTPSPORT -----------5500 Datenbank, Listener und BS-Variablen Starten der Datenbank -Linux Dbstart -Windows set ORACLE_SID=orcl set ORACLE_HOME=D:\ora %oracle_home%\BIN\oradim -STARTUP -SID orcl -SYSPWD sys Listener starten und Status abfragen Lsnrctl start Lsnrctl status Erreichbarkeit einer Datenbank über SID-Abfrage Tnsping sid-name Ausschalten des Password-expire-Verhaltens in Testumgebungen alter profile default limit password_life_time unlimited; Bereitstellung der In Memory Datenbank Umgebung SQL Plus sqlplus sqlplus sqlplus sqlplus sqlplus user/passwort@ORACLE_SID -> geht über Listener user/passwort -> ohne Listener sys/passwort@ORACLE_SID as sysdba sys/passwort as sysdba “/ as sysdba” Help Index -- listet alle SQLPLUS-Befehle auf Weitere Hilfe mit Help [commando] Beispiel @ ACCEPT ATTRIBUTE CHANGE COMPUTE DEFINE DISCONNECT EXIT HOST PASSWORD PROMPT REMARK RESERVED WORDS (SQL) SAVE SHUTDOWN START TIMING Document1 SQL> help get @@ APPEND BREAK CLEAR CONNECT DEL EDIT GET INPUT PAUSE QUIT REPFOOTER RESERVED WORDS (PL/SQL) SET SPOOL STARTUP TTITLE / ARCHIVE LOG BTITLE COLUMN COPY DESCRIBE EXECUTE HELP LIST PRINT RECOVER REPHEADER RUN SHOW SQLPLUS STORE UNDEFINE An dieser Stelle wir eine einfache Testumgebung auf einem Laptop erstellt. Die Datenbank wird mit einem Initialisierungsparameter gestartet, der ihr sagt, wie groß der Column Store (In Memory Bereich) ist. Achtung: Der In Memory-Bereich ist Teil der SGA. Daher muss auch der SGA_TARGET-Parameter hochgesetzt werden [z. B. Eintrag in INIT.ORA] INMEMORY_SIZE=4000000000 Zu erkennen ist die Auflistung und die Ausgabe In-Memory Area Data Warehouse Technik im Fokus - Skripte in Kurzform 7 /42 Skript Fakten-Tabelle Beispielumgebung In dieser 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. 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. In Memory – Funktionen werden ohne Indizierung dargestellt. 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 TOTAL TOTAL_NR 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, 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) Skript Vertriebskanal-Dimension 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] CREATE UNIQUE INDEX CREATE UNIQUE INDEX ("ARTIKEL_ID"); CREATE UNIQUE INDEX CREATE UNIQUE INDEX CREATE UNIQUE INDEX ("KANAL_ID"); Document1 "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" Data Warehouse Technik im Fokus - Skripte in Kurzform 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); 8 /42 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) Vergleichstests Vergleichs-Demo-Umgebung Steckbrief (In dieser Unterlage werden Beispielabfragen und Abfragezeiten angegeben, die in der hier beschriebenen Umgebung getestet wurden. Die Daten können entsprechend der Anleitung am Ende dieser Skriptesammlung geladen werden) TABLE_NAME BLOCKS GB NUM_ROWS PCT_FREE ------------------------- ------- ---------- -------D_ARTIKEL 5 .00004 129 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 Eigenschaften der Demosysteme 1 2 3 Eigenschaft Klassisches Linux Demosystem (DesktopRechner) Laptop Laptop für In MemoryTests Compression Nein(Ja) Nein Bitmap auf Fakten-FKFelder Unique Key PK-Felder der Dimensionen Partitioning Results Cache Null-Werte Server-CPUKerne Server-RAM Server-BS Oracle-DB Storage Ja Ja In MemoryCompression Nein Ja Ja Nein Nein(Ja) Nein Keine 4 echte Kerne Nein Nein Keine 2 Core (4 Threads) 16 GB Windows 7 12.2.0.2 Eine Platte Nein Nein Keine 2 Core (4 Threads) 16 GB Windows 7 12.2.0.2 In Memory 56 MB/sec Rechnerisch 5 GB / Sec 4 GB 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 HIERARCHY h_region ( Ort CHILD OF Kreis CHILD OF Land CHILD OF Region ) ATTRIBUTE Ort DETERMINES (d_region.ort, d_region.ortnr) ATTRIBUTE Kreis DETERMINES (d_region.kreis) ATTRIBUTE Land DETERMINES (d_region.land) ATTRIBUTE Region DETERMINES (d_region.region); Skript Dimensional Table D_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 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, Document1 Durchsatz In Memory Größe 16 GB Linux OEL 6 11.2.0.3 (EE) Direkt (Mehrere Platten ~180 MB/sec - Zur Bewertung der Testreihe und der Vergleichszahlen Die 3 Testreihen sind nicht komplett vergleichbar. 1) Das System 1 ist ein echtes 4-Core-System mit 4 Platten und einer Verteilung der Daten über alle Platten hinweg. Man erkennt dies vorallem an dem Durchsatz ~ 180 MB/Sec. Die Tests der Spalten 2 und 3 laufen nur auf einem Laptop-Rechner mit 2 Cores (und 4 Threads) (Intel Core i5-3320M). In diesem Rechner befindet sich nur eine einzige Platte (56 MB / Sec) 2) Die zweite wichtige Unterscheidungsmerkmal ist Linux auf dem ersten System und Windows 7 auf dem Laptop. Linux ist sicherlich das schnellere Ssstem. 3) Zum dritten liegen 2 Jahre Datenbankentwiclung zwischen dem Oracle-Release 11.2 (die erste Spalte) und dem Oracle-Release 12.1.0.2. Die Testreihen sind dennoch in einer Tabelle zusammengefasst worden: Die erste Testreihe Die erste Testreihe (Spalte 1) ist 2 Jahre älter. Man kann an ihr gut den Effekt der Parallelisierung erkennen. Die Maschine verfügt über 4 echte CPU-Kerne und 4 Platten. Parallelisiert wird mit 0,2,4,8, 16. An den Antwortzeiten ist gut zuerkennen, dass ein Parallelisierungswert von 4 optimal für diese Hardware ist. Bis zu einer Parallelisierung werden die Performancewerte besser aber bei einer höheren Parallelisierung werden die Antwortzeiten wieder schlechter. Das Plattensystem gibt nicht mehr her. Parallelisierte Leseprozesse müssen aufeinader warten. Die zweite Testreihe Die zweite Testreihe lief auf einem einfachen Laptop. Der Test demonstriert die In Memory Column Store Option des Datenbank Data Warehouse Technik im Fokus - Skripte in Kurzform Releases 12.1.0.2.. Die Antwortzeiten sind nur bedingt mit der ersten Spalte vergleichbar. Wie stark die Unterschiede der beiden Maschinen sind, erkennt man an dem Vergleich zwischen der ersten und zweiten Spalte. Die Parallelisierung in der Spalte 2 (klassische Festplatte) ergibt keinen Sinn, die Werte werden bei steigender Parallelisierung schlechter. In der Spalte 3 (In Memory Option) steigen die Performancewert bis zu einem Wert von 4. Danach wird die Performance wieder schlechter. 4 entspricht der Anzahl der Threats, die auf dem Rechner zur Verfügung stehen. 9 /42 Beispielabfragen auf das Beispiel-Star Schema Schalterstellungen für die unterschiedlichen Mess-Szenarien Alter Alter Alter Alter Alter Alter table table table table table table D_ARTIKEL Parallel 8; D_KUNDE Parallel 8; D_REGION Parallel 8 D_VERTRIEBSKANAL Parallel 8; D_ZEIT Parallel 8; F_UMSATZ Parallel 8; Oder alter session set parallel_degree_policy=AUTO; Alter Alter Alter Alter Alter Alter table table table table table table D_ARTIKEL noParallel; D_KUNDE noParallel; D_REGION noParallel; D_VERTRIEBSKANAL noParallel; D_ZEIT noParallel; F_UMSATZ noParallel; ALTER SESSION set inmemory_query = disable; ALTER SESSION set inmemory_query = enable; 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 Demo-Systeme wie oben beschrieben P0 P2 P4 P8 P16 1 2 3 39,1 22,5 13,4 16,8 20 01:52.19 54.61 30.30 30.30 21 21 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 P2 P4 P8 Demo-Systeme wie oben beschrieben 1 2 3 0,03 Ohne Bitmap 1,62 2,6 0,08 27 0,18 0,18 0,06 Kommentar: Das Beispiel zeigt eindrucksvoll, wie wichtig Bitmap-Indexe sind und zeigt auch, dass die Parallelisierung nicht im von Vorteil ist. Die Abfrage über die Tabelle mit einem Bitmap-Index auf das Feld, das in der WHERE-Klausel erfasst ist, läuft schneller als die In Memory – Abfrage. Selbst bei zugeschalteter Parallelisierung ist dies schneller, die Parallelisierung wird einfach umgangen. ------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 12 | 1751 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 12 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| F_UMSATZ | 8532 | 99K| 1751 (0)| 00:00:01 | | 3 | BITMAP CONVERSION TO ROWIDS | | | | | | |* 4 | BITMAP INDEX SINGLE VALUE | IDX_ZEIT_ID_BM | | | | | Ausführungsplan bei Bitmap-Indizierung und Parallelisierung 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'; Demo-Systeme wie oben beschrieben Parallel P0 P2 P4 P8 Document1 1 2 3 0,14 Ohne Bitmap 1,62 2,5 0,08 27 0,18 3,68 0,05 Data Warehouse Technik im Fokus - Skripte in Kurzform Kommentar: Hier gelten ähnliche Werte wie zuvor. In dem Ausführungsplan ist zusätzlich erkennbar, wie der Optimizer das Lesen über In Memory-Strukturen und BitmapIndexe mischt. --------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 27 | 264 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 27 | | | | 2 | NESTED LOOPS | | 1219 | 32913 | 264 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 1219 | 32913 | 264 (0)| 00:00:01 | |* 4 | TABLE ACCESS INMEMORY FULL | D_VERTRIEBSKANAL | 1 | 12 | 1 (0)| 00:00:01 | | 5 | BITMAP CONVERSION TO ROWIDS| | | | | | | 6 | BITMAP AND | | | | | | |* 7 | BITMAP INDEX SINGLE VALUE| IDX_ZEIT_ID_BM | | | | | |* 8 | BITMAP INDEX SINGLE VALUE| IDX_KANAL_ID_BM | | | | | | 9 | TABLE ACCESS BY INDEX ROWID | F_UMSATZ | 1219 | 18285 | 264 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Ausführungsplan bei Bitmap-Indizierung und In Memory ohne Parallelisierung 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 Demo-Systeme wie oben beschrieben P0 P2 P4 P8 P16 1 2 3 2,6 2,6 4,2 4,6 4,2 14,35 1,10 27 0,53 5,99 Beispielabfrage3 (Ein-/Ausschalten Where-Argumente /Test Star Query SELECT /*+ no cache */ 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; Parallel Demo-Systeme wie oben beschrieben P0 P2 P4 P8 P16 1 2 3 11,3 6,4 3,2 5,8 5,3 16,3 1,3 27,76 0,74 9,61 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 Rewrite-Fähigkeit] BUNDESLAND ANZAHL ------------------------------ ---------Rheinland Pfalz 13674496 Bayern 13261312 Schleswig Holstein 8107008 Parallel P0 P2 P4 Document1 10 /42 P8 P16 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 Demo-Systeme wie oben beschrieben 1 2 3 10,1 7,1 2,6 4,7 2,5 22,92 2.14 32,98 1,00 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 Demo-Systeme wie oben beschrieben 1 2 3 9,0 5,1 2,6 2,7 2,8 22,33 2,22 27,99 0,96 Beispielabfrage 7:: Das umsatzstärkste Quartal pro Jahr Select /* + no cache */ * from (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; 1 2 3 12 10,5 3,7 12,62 15,95 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 7,2 6,12 Parallel Demo-Systeme wie oben beschrieben BY Demo-Systeme wie oben beschrieben BY Data Warehouse Technik im Fokus - Skripte in Kurzform P0 P2 P4 P8 P16 1 2 3 9,6 5,1 2,2 2,8 1,8 20,87 2,26 28 1,09 Beispielabfrage 8: Stärkster Umsatzmonat pro Bundesland SELECT FROM * (SELECT 11 /42 from F_umsatz U, D_Artikel A WHERE U.artikel_id = a.artikel_id group by a.gruppe_name) Parallel Demo-Systeme wie oben beschrieben 1 P0 P2 P4 P8 P16 2 3 5,24 3,07 1,34 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; Beispielabfrage10: -- Top 10 Artikel bezogen auf Umsatz in einem Jahr BUNDESLAND -------------------Baden Wuerttemberg Bayern Berlin Bremen Hamburg Hessen Niedersachsen Nordrhein Westfalen Rheinland Pfalz Saarland Schleswig Holstein ARTIKEL BUNDESLAND JAHR WERT MENGE UMS._PRO_ART. RANGFO. ------------------------ ---------------------- -------- ------- ------------ ---- 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 Demo-Systeme wie oben beschrieben 1 2 3 6,2 2,8 3,4 1,6 1,7 26,66 1,00 0,64 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 Innenkabel 3 Adern 2 21405184 10 Parallel P0 P2 P4 P8 P16 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; 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 P0 P2 P4 P8 P16 SELECT FROM 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 1 2 3 9,8 3,3 1,7 1,9 1,8 23.14 3,38 1,61 * (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 1 2 3 Parallel 16,8 8,5 4,3 4,4 4,3 1:13,86 19,04 SELECT * FROM (SELECT Gruppe_Name , sum(U.menge) AS Gesamtmenge, RANK() OVER (ORDER BY sum(U.menge) DESC ) AS Rangfolge Pfalz 2010 2010 Pfalz 2010 Pfalz 2010 2010 Pfalz 2010 2010 2010 2010 Pfalz 2010 Beispielabfrage 11: - Top 3 Produkte je Produktgruppe sortiert nach Rangfolge ~~~ 7,09 Rheinland Bayern Rheinland Rheinland Bayern Rheinland Bayern Bayern Bayern Rheinland Demo-Systeme wie oben beschrieben Parallel Demo-Systeme wie oben beschrieben Beispielabfrage9a: Top 10 Artikel bezogen auf Menge / Rank /Subquery Document1 SELECT * FROM (SELECT P0 P2 P4 P8 P16 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 Demo-Systeme wie oben beschrieben 1 2 3 19 10,2 5,3 6,9 8,2 27,43 23,56 28,98 9,02 Beispielabfrage 12: Die Top-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 BY Data Warehouse Technik im Fokus - Skripte in Kurzform Beispielabfrage 15: Die Top 2 Produktgruppen pro Bundesland 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 PROD_GRP --------------Bad_Sanitaer Bad_Sanitaer KFZ-Zubehoer Haushaltswaren Elektroartikel Bad_Sanitaer Bad_Sanitaer Bad_Sanitaer Bad_Sanitaer Bad_Sanitaer Bad_Sanitaer UMSATZ RANGFOLGE ---------- --2517655040 1 6928505856 1 5216256 1 7249920 1 11321344 1 1473691648 1 3481312768 1 1065768960 1 7339374592 1 97358848 1 4261867520 1 Demo-Systeme wie oben beschrieben 1 2 3 25,3 12,2 6,3 6,7 7,2 21,06 4,54 28,87 2,18 P0 P2 P4 P8 P16 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 Demo-Systeme wie oben beschrieben 1 2 3 33.1 13.0 6,5 6,8 8,7 21,81 4,26 29,76 1,19 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 P0 P2 P4 P8 P16 Document1 PROD_GRP ---------------Bad_Sanitaer Elektroartikel Bad_Sanitaer Elektroartikel KFZ-Zubehoer Bad_Sanitaer Haushaltswaren Gartenbedarf Elektroartikel TOP_UMSATZ TOP_FOLGE ---------- --2517655040 1 1634190848 2 6928505856 1 4916009472 2 5216256 1 5194240 2 7249920 1 7211008 2 11321344 1 Demo-Systeme wie oben beschrieben P0 P2 P4 P8 P16 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 1 2 3 24,8 13,4 6,6 8,9 9,1 21,92 4,38 28,94 1,99 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 BY 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 Demo-Systeme wie oben beschrieben 1 2 3 50,1 25,8 13,1 16,9 14,4 43,94 8,94 56,87 4,17 Beispielabfrage 17: Über Quartale kumulierte Umsätze pro Kunde ~~~ Parallel LAND -------------------Baden Wuerttemberg Baden Wuerttemberg Bayern Bayern Berlin Berlin Bremen Bremen Hamburg Parallel 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; Parallel 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; ~~~ Beispielabfrage 13: Land mit dem stärksten Umsatz je Produktgruppe PROD_GRP -----------------Bad_Sanitaer Elektro-Werkzeuge Elektroartikel Gartenbedarf Haushaltswaren Heimwerker KFZ-Zubehoer 12 /42 Demo-Systeme wie oben beschrieben 1 2 3 24,5 12.8 6,2 9,6 6,2 22,77 4,37 28,94 2,05 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) Data Warehouse Technik im Fokus - Skripte in Kurzform GROUP by K.NachName, z.jahr_nummer, z.quartals_nummer; Parallel P0 P2 P4 P8 P16 Demo-Systeme wie oben beschrieben 1 2 3 1:15,2 31,7 15,3 23,7 13,8 1:28,74 1:27,97 32,97 28,51 13 /42 (SELECT sum(u.umsatz) as Gesamt_Umsatz FROM f_Umsatz U) GROUP by anteil,Gesamt_umsatz; Parallel Demo-Systeme wie oben beschrieben P0 P2 P4 P8 P16 1 2 3 19,3 10,6 6,9 7,2 6,2 43,43 16,31 58,57 7,26 Beispielabfrage 18: Über Quartale kumulierte Umsätze pro Kunde (Jahr + Kreis) Beispielabfrage21: Durchschnittliche Bestellquote eines Kunden über 3 Monate? SELECT SELECT k.nachName,r.kreis, z.jahr_nummer, z.quartals_nummer, sum(u.Umsatz) as Umsatz, sum(sum(u.Umsatz)) over (Partition By k.nachName ORDER BY k.nachName, z.jahr_nummer, z.quartals_nummer ROWS UNBOUNDED PRECEDING) as Umsatz_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 Demo-Systeme wie oben beschrieben 1 2 3 2,1 1,6 1,2 1,5 1,2 22,27 0,6 27,38 0,55 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 Demo-Systeme wie oben beschrieben 1 2 3 38,2 19,0 9,8 19,1 11,4 58,19 43,88 59,95 15,48 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), Document1 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 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 Demo-Systeme wie oben beschrieben P0 P2 P4 P8 P16 1 2 3 1,2 1,0 0,5 2,8 2,1 21,48 0,64 29,61 0,47 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 P0 P2 P4 P8 P16 Demo-Systeme wie oben beschrieben 1 2 3 8,6 4,4 2,1 2,3 2,1 21,30 6,39 28,34 2,81 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 Demo-Systeme wie oben beschrieben 1 2 3 1,28 1,0 2,6 0,8 2,1 20,88 0,63 28,89 0,48 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 Data Warehouse Technik im Fokus - Skripte in Kurzform f_umsatz u, d_zeit z where z.zeit_id = u.zeit_id group by z.Jahr_nummer, z.MONATS_NUMMER, z.MONAT_DESC ; Parallel Demo-Systeme wie oben beschrieben 1 P0 P2 P4 P8 P16 2 3 22,87 3,34 27,74 1,68 Beispielabfrage 25 Ausgabe von Summenzeilen einer Aggregation (GROUP BY ROLLUP) SELECT artikel_name Artikel, AS Umsatz select table_name, cache, blocks, round(blocks/1000000*8,2) GB, degree Parallel, inmemory, compression from user_tables where table_name like 'F_UMSATZ%'; 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 like '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 create table abc_0 as select * from abc where rownum < 1; FROM Anlegen von temporären Tabellen f_umsatz U, d_artikel A create global temporary table ABC_T on commit preserve rows as select * from ABC; WHERE U.artikel_id = a.artikel_id GROUP by ROLLUP Spaltennamen nchträglich ändern (a.gruppe_name,a.artikel_name) Alter Table F_UMSATZ rename column ALTER_NAME to NEUER_NAME; ORDER by Ungenutzten Platz freigeben und komprimieren a.gruppe_name Parallel Tabellengrößen , Cache, Parallel, Compression Anlegen einer leeren Tabelle mit bestimmter Struktur gruppe_name Prod_Grp, sum(U.Umsatz) 14 /42 Demo-Systeme wie oben beschrieben 1 P0 P2 P4 P8 P16 2 3 34,18 32,22 29,31 12,54 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; 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 , sum(U.Umsatz) AS Umsatz , GROUPING(Artikel_name) AS Art_g , GROUPING(gruppe_name) AS Grp_g , GROUPING(Sparte_name) AS Spr_g , GROUPING_ID(artikel_name ,gruppe_name,sparte_name ) AS grouping_id FROM f_umsatz U, d_artikel 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 GROUPING(Artikel_name),GROUPING(gruppe_name), GROUPING(Sparte_name) Tabellen 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 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 select table_name,blocks,num_rows,logging from user_tables; Tabellengrößen und Platzverbrauch column TABLE_NAME format a30 select table_name, degree Paralle, 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.) Document1 t.TABLE_NAME, t.blocks, t.EMPTY_BLOCKS, t.AVG_SPACE, t.AVG_ROW_LEN, t.NUM_ROWS, t.pct_free, t.compression, s.EXTENTS, s.bytes seg_bytes, e.blocks ext_blks, e.bytes ext_bytes from user_segments s, user_tables t, user_extents e where t.TABLE_NAME = s.segment_name and e.SEGMENT_NAME = s.SEGMENT_NAME and t.TABLE_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 Data Warehouse Technik im Fokus - Skripte in Kurzform 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) 15 /42 DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('TS_DWH','DWH','F_UMSATZ_BREIT', null, DBMS_COMPRESSION.COMP_INMEMORY_QUERY_HIGH, b_cmp, b_uncmp, row_cmp, row_uncmp,cmp_ratio,cmp_str); dbms_output.put_line('# Blocks compressed => '|| b_cmp); dbms_output.put_line('# Blocks uncompressed => '|| b_uncmp); dbms_output.put_line('Ratio => '|| cmp_ratio); dbms_output.put_line('Compression-Typ => '|| cmp_str); end; / Relevante Dictionary Views für Compression Ausschalten / 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; user_tables Partitioning Relevante Dictionary Views user_constraints Relevante Fragestellungen 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 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 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%'; 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. Utility zum Testen einer möglichen Kompressionsrate set serveroutput on declare b_cmp number; b_uncmp number; row_cmp number; row_uncmp number; cmp_ratio NUMBER; cmp_str VARCHAR2(200); begin DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('SYSTEM','U2', 'F_UMSATZ', null, 32, b_cmp, b_uncmp, row_cmp, row_uncmp,cmp_ratio,cmp_str); dbms_output.put_line('# Blocks compressed dbms_output.put_line('# Blocks uncompressed dbms_output.put_line('Ratio dbms_output.put_line('Compression-Typ end; / => => => => '|| '|| '|| '|| b_cmp); b_uncmp); cmp_ratio); cmp_str); --COMP_NOCOMPRESS NUMBER 1 No compression --COMP_FOR_OLTP NUMBER 2 OLTP compression --COMP_FOR_QUERY_HIGH NUMBER 4 High compression level for query operations --COMP_FOR_QUERY_LOW NUMBER 8 Low compression level for query operations --COMP_FOR_ARCHIVE_HIGH NUMBER 16 High compression level for archive operations --COMP_FOR_ARCHIVE_LOW NUMBER 32 Low compression level for archive operations --COMP_BLOCK NUMBER 64 Compression --COMP_RATIO_MINROWS NUMBER 1000000 Minimum required number of rows in the object for which HCC ratio is to be estimated --COMP_RATIO_ALLROWS NUMBER -1 Gleiche Variante wie zuvor, nur Beispiel zum Testen der In-MemoryCompression declare begin Document1 b_cmp b_uncmp row_cmp row_uncmp cmp_ratio cmp_str number; number; number; number; NUMBER; VARCHAR2(200); 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), Data Warehouse Technik im Fokus - Skripte in Kurzform MENGE UMSATZ_GESAMT NUMBER(10), NUMBER(10) ) PARTITION BY RANGE (ZEIT_ID) ( PARTITION jan10 VALUES LESS THAN (TO_DATE('2010-02-01','SYYYY-MM-DD')), PARTITION feb10 VALUES LESS THAN (TO_DATE('2010-03- 01','SYYYY-MM-DD')), PARTITION PARTITION 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 oct11 nov11 VALUES VALUES 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 LESS LESS THAN THAN 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')), (TO_DATE('2011-11-01','SYYYY-MM-DD')), (TO_DATE('2011-12-01','SYYYY-MM-DD')), 16 /42 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 PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01','SYYYY-MM-DD')), PARTITION next_month VALUES LESS THAN (MAXVALUE)); 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; 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; Skript zum schnellen Erstellen der Testtabelle aus F_UMSATZ Drop Index 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') DROP DROP DROP DROP Skript Beispiel Range Partitioning Beispielskript F_BESTELLUNG_RANGE Skript Beispiel List Partitioning [Beispiel analog zu vorherigem Beispiel, daher verkleinert] 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) ); 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 PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION feb11 mar11 apr11 mai11 jun11 jul11 aug11 sep11 oct11 nov11 VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES LESS LESS LESS LESS LESS LESS LESS LESS LESS LESS THAN THAN THAN THAN THAN THAN THAN THAN THAN THAN (TO_DATE('2011-03-01', (TO_DATE('2011-04-01', (TO_DATE('2011-05-01', (TO_DATE('2011-06-01', (TO_DATE('2011-07-01', (TO_DATE('2011-08-01', (TO_DATE('2011-09-01', (TO_DATE('2011-10-01', (TO_DATE('2011-11-01', (TO_DATE('2011-12-01', 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 'SYYYY-MM-DD')) 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, PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION jan12 VALUES LESS THAN (TO_DATE('2012-02-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION feb12 VALUES LESS THAN (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) TABLESPACE DWH_SPINDEL, PARTITION next_month VALUES LESS THAN (MAXVALUE) TABLESPACE DWH_SPINDEL); Beispielabfrage mit Einschränkung auf 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 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 ('F%') TABLESPACE ('M%') TABLESPACE ('S%') TABLESPACE (MAXVALUE) TABLESPACE 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 Erstellen Hash Partitioned Table au seiner anderen Create table F_Umsatz_Hash PARTITION BY HASH (KUNDEN_ID) PARTITIONS 64 pctfree 0 As select * from f_Umsatz; Allgemeines Hash-Partitioning-Beispiel Document1 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' 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')), Beispielabfrage auf Range/List Skript Beispiel Hash Partitioning und Tablespace-/Datafile-Zuordnung TABLESPACE TABLESPACE TABLESPACE TABLESPACE mar10 apr10 mai10 jun10 jul10 aug10 sep10 oct10 nov10 dec10 jan11 feb11 mar11 apr11 mai11 jun11 jul11 aug11 sep11 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)); 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; CREATE CREATE CREATE CREATE idx_ARTIKEL_ID_BM_H; idx_ZEIT_ID_BM_H ; idx_KUNDE_ID_BM_H; idx_REGION_ID_BM_H; Skript Beispiel für Sub-Partition (Range-List) MAXVALUE zum Aufnehmen von Werten ohne Zuordnung 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 ); index index index index SIZE SIZE SIZE SIZE 10M; 10M; 10M; 10M; 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 Data Warehouse Technik im Fokus - Skripte in Kurzform 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 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 [Analog dem ersten Beispiel, daher verkleinert] CREATE TABLE F_bestellung_part_range_list ( SUMME NUMBER(14,0), MENGE NUMBER(14,0), BESTELLDATUM DATE, FK_ARTIKEL_ID NUMBER, FK_KUNDEN_ID NUMBER, FK_ORT_ID NUMBER, FK_DATUM_ID NUMBER, auftragsart VARCHAR2(30) ) PARTITION BY RANGE (bestelldatum) SUBPARTITION BY LIST (auftragsart) SUBPARTITION TEMPLATE ( SUBPARTITION produkt VALUES ('ARTIKEL','TAUSCHWARE','PRODUKT'), SUBPARTITION service VALUES ('SERVICE','REISE'), SUBPARTITION storno VALUES ('RETOURE','KOMMISSION'), SUBPARTITION andere VALUES (DEFAULT) ) ( PARTITION jan11 VALUES LESS THAN (TO_DATE('2011-02-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION feb11 VALUES LESS THAN (TO_DATE('2011-03-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION mar11 VALUES LESS THAN (TO_DATE('2011-04-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION apr11 VALUES LESS THAN (TO_DATE('2011-05-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION mai11 VALUES LESS THAN (TO_DATE('2011-06-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION jun11 VALUES LESS THAN (TO_DATE('2011-07-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION jul11 VALUES LESS THAN (TO_DATE('2011-08-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION aug11 VALUES LESS THAN (TO_DATE('2011-09-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION sep11 VALUES LESS THAN (TO_DATE('2011-10-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION oct11 VALUES LESS THAN (TO_DATE('2011-11-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION nov11 VALUES LESS THAN (TO_DATE('2011-12-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION dec11 VALUES LESS THAN (TO_DATE('2012-01-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION jan12 VALUES LESS THAN (TO_DATE('2012-02-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION feb12 VALUES LESS THAN (TO_DATE('2012-03-01', 'SYYYY-MM-DD')) TABLESPACE PARTITION next_month VALUES LESS THAN (MAXVALUE) TABLESPACE DWH_SPINDEL ); 17 /42 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); 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, 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 column column column SEGMENT_NAME format a30 partition_name format a15 TABLESPACE_NAME format a15 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 Skript Beispiel Interval-Partition mit Monatspartitionen Partitionsgrenzen abfragen 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] SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name IN ('BESTELLUNG','BEST_POSITION') ORDER BY partition_position, table_name; Skript Beispiel Interval-Partition mit numerischer Bereichsangabe create table test (MA_NR number(6), NACHNAME varchar2(30), GEHALT number(6)) partition by range(GEHALT) Document1 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'); Data Warehouse Technik im Fokus - Skripte in Kurzform 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; 18 /42 NAME -------------------Fixed Size Variable Size Database Buffers Redo Buffers In-Memory Area VALUE CON_ID ---------- ---------3046152 0 1107297528 0 4966055936 0 30507008 0 4026531840 0 Ein- / Ausschalten der In Memory Query Funktion ALTER SESSION set inmemory_query = disable; ALTER SESSION set inmemory_query = enable; Markieren von Tabellen, um diese in den Column Store zu laden alter table f_Umsatz inmemory; ALTER TABLE BESTELLUNG MOVE SUBPARTITION Jan08_STORNO TABLESPACE TS_PAR_Archiv_Jan; Tabelle aus In-Memory-Speicher löschen Merge von zwei Sub-Partitions CREATE TABLE PARTITION BY (PARTITION (PARTITION ALTER TABLE BESTELLUNG MERGE SUBPARTITIONS TELEFON_STORNO, TELEFON_ANDERE INTO SUBPARTITION TELEFON_OBJEKTE 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 In Memory Column / Row Store alter table F_Umsatz no inmemory; Erstellen einer partitionierten Tabelle inm Kontext on In Memory F_UMSATZ_PAR… LIST p1 …… INMEMORY, p2 …… NO INMEMORY); Ausschließen von bestimmten Spalten aus der In-Memory-Speicherung ALTER TABLE F_UMSATZ INMEMORY NO INMEMORY (UMSATZ_GESAMT) Priorisierung von Spalten CREATE TABLE F_UMSATZ (c1 number, c2 varchar(20), c3 number) INMEMORY PRIORITY CRITICAL NO INMEMORY (c1); CREATE TABLE F_UMSATZ ( ARTIKEL_ID NUMBER(10), KUNDEN_ID NUMBER(10), ZEIT_ID DATE, REGION_ID NUMBER(10), KANAL_ID NUMBER(10), UMSATZ NUMBER(10), MENGE NUMBER(10), UMSATZ_GESAMT NUMBER(10)) INMEMORY PRIORITY CRITICAL NO INMEMORY (UMSATZ); Komprimierung von einzelnen Spalten CREATE TABLE trades (Name varchar(20), Desc varchar(200)) INMEMORY MEMCOMPRESS FOR DML(desc); Unterschiedliche Intensität der Komprimierung CREATE TABLE ORDERS …… PARTITION BY RANGE …… (PARTITION p1 …… INMEMORY NO MEMCOMPRESS PARTITION p2 …… INMEMORY MEMCOMPRESS FOR DML, PARTITION p3 …… INMEMORY MEMCOMPRESS FOR QUERY, : PARTITION p200 …… INMEMORY MEMCOMPRESS FOR CAPACITY Abfrage auf Objekte, die in dem Column Store (In Memory) gehalten sind column owner format a10 column name format a20 - Verlagern von Objekten (Tabellen, Partitionen, Materialized Views in den Column Store - Abfragen von Objekten in dem Column Store - Verlagerung von Tabellen in den Row Store (Buffer Pool) - Verlagerung einer kompletten Datenbank on den Row Store (Buffer Pool) v.owner, v.segment_name name, -v.INMEMORY_SIZE, u.NUM_ROWS, u.blocks/1000000*8 Disk_GB, v.bytes, v.BYTES_NOT_POPULATED Rest_Bytes, v.populate_status Status, round(v.inmemory_size/1000000000,6) in_mem_GB, round(v.bytes / v.inmemory_size,2) comp_ratio From v$im_segments V, user_tables u where u.TABLE_NAME = v.segment_name and v.OWNER like 'DWH' Abfrage der Größe des In Memory-Speicher-Bereichs Abfrage über die In Memory-Zustände von Tabellen Relevante Fragestellungen show parameter inmemory_size; NAME TYPE VALUE ------------------------------------ ----------- ---------inmemory_size big integer 3824M Ansicht komplette SGA mit In-Memory-Storage SELECT * FROM V$SGA; Document1 Select select table_name,INMEMORY_COMPRESSION, INMEMORY_DISTRIBUTE, INMEMORY_PRIORITY from user_tables; TABLE_NAME --------------F_UMSATZ D_KUNDE D_ARTIKEL D_REGION INMEMORY_COMPRESS ----------------FOR QUERY FOR QUERY FOR QUERY FOR QUERY INMEMORY_DISTRI --------------AUTO DISTRIBUTE AUTO DISTRIBUTE AUTO DISTRIBUTE AUTO DISTRIBUTE INMEMORY -------NONE NONE NONE NONE Data Warehouse Technik im Fokus - Skripte in Kurzform D_ZEIT FOR QUERY D_VERTRIEBSKANA FOR QUERY AUTO DISTRIBUTE NONE AUTO DISTRIBUTE NONE Abfragen von In Memory - Zugriffen select display_name, value From v$mystat m, v$statname n Where m.STATISTIC#=n.STATISTIC# AND display_name IN ('IM scan CUs columns access', 'IM scan segments minmax eligible', 'IM scan CUs pruned'); Verlagung einer kompletten Datenbank in den Column-Store (Buffer Pool) -- Feature ab DB 12.1.0.2 Datenbank in den MOUNT – Zustand bringen SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ALTER DATABASE FORCE FULL DATABASE CACHING; ALTER DATABASE OPEN; Datenbank Objekte werde erst dann in den Buffer Pool geladen, wenn sie zum ersten Mal angefasst worden sind. Dann bleiben sie dort. -> DB_CACH_SIZE - Parameter setzen. Abfragen der Cache Database Situation SELECT force_full_db_caching FROM v$database; Relevante Dictionary Views für Indexing v$im_segments V$im_seg_ext_map v$im_column_level V$IM_COL_CU USER_TABLES V$SGA V$database 19 /42 AND ui.table_name = 'BESTELLUNG_PART_RANGE' ORDER BY index_name, partition_position; SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'BESTELLUNG'; Anzeigen des Platzverbrauchs der Indexe 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. Indexe Relevante Fragestellungen INDEX auf Unusable setzen [Alter index index_name unusable] ETL-Massen-Load durchführen INDEX Rebuild [Alter index index_name rebuild] Oder 1. 2. DROP Index index_name CREATE Index index_name Rebuild Partitioned Index 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 ALTER TABLE <table_name> MODIFY PARTITION <partition_name> REBUILD UNUSABLE LOCAL INDEXES; Wie und wo wird indiziert ALTER TABLE part1 MOVE PARTITION p1 TABLESPACE new_tbsp UPDATE INDEXES (my_parts_idx (PARTITION p1 TABLESPACE my_tbsp)); 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>; 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 CREATE INDEX idx_region_id ON F_Umsatz_range (region_id) LOCAL; Suche nach Indexen bezogen auf eine bestimmte Tabelle Partitionierung der Indexe entsprechend der zugehörigen TablePartionen (Local Partiton Index) und zweisen unterschiedlicher Tablespaces 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 Document1 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), Data Warehouse Technik im Fokus - Skripte in Kurzform PARTITION index500 VALUES LESS THAN (500), PARTITION index1000 VALUES LESS THAN (1000), PARTITION index_MAX VALUES LESS THAN (MAXVALUE)); Partitionierte Indexe suchen SELECT index_name, partitioned FROM user_indexes WHERE table_name = 'BESTELLUNG'; SELECT ip.index_name, ip.composite, ip.partition_name, ip.high_value FROM user_ind_partitions ip, user_indexes ui WHERE ip.index_name = ui.index_name AND ui.table_name = 'BESTELLUNG'; 20 /42 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 ALTER TABLE <table_name> MODIFY PARTITION <partition_name> REBUILD UNUSABLE LOCAL INDEXES; 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; Rebuild Sub Partition Index Star Transformation aktivieren ALTER TABLE <table_name> MODIFY SUBPARTITION <subpartition_name> REBUILD UNUSABLE LOCAL INDEXES; Menge der WHERE-Bedingungen überprüfen Rebuild lokaler Index 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 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 alter system set star_transformation_enabled=TRUE; 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] 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. 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) Informationszugriffe planen und dokumentieren. Es gibt ein paar Tips, wie man sich die Verwaltung von Materialized Views erleichtern kann. Es besteht meist die Gefahr des unkontrollierten Definierens, so dass Materialized Views mit doppeltem oder sich überschneidenden Inhalt vorhanden sind. o Sprechende Namen wählen o Die Tabellen, die über Materialized Views gelesen werden, in einer Matrix-Darstellung doumentieren Konsistenz, Mengen und Indexe im Star-Schema Relevante Parameter Relevante Fragestellungen Wann zieht die Star Transformation Voraussetzungen prüfen Document1 ALTER SESSION SET ALTER SESSION SET ALTER SESSION SET query_rewrite_enabled=TRUE; query_rewrite_enabled=FALSE; query_rewrite_integrity=STALE_TOLERATED Data Warehouse Technik im Fokus - Skripte in Kurzform 21 /42 MVIEW suchen drop materialized View Mav_Kunde_umsatz; select mview_name,REWRITE_ENABLED,STALENESS,REWRITE_CAPABILITY from user_mviews; [Beispielabfrage 20] Größe und Anzahl Zeilen von Mviews abfragen 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 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] 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 Beispieldefinition MAV_Kunde_Umsatz Unterstützte Kennzahlen/Abfragen Gesamtumsatz/menge pro Kunde/Bildungsgruppe, Einkommensgruppe, Wohnart, Berufsgruppe 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; 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 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 Beispieldefinition MAV_Kunde_Zeit_Umsatz Unterstützte Kennzahlen/Abfragen Gesamtumsatz/menge pro Ort/Kreis/Land/Region Tag/Monat/Quartal/Jahr drop materialized View Mav_Kunde_Zeit_umsatz; 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, Data Warehouse Technik im Fokus - Skripte in Kurzform 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; Parallel P4 ohne Partitioning Sekunden 3,7 NEVER ON COMMIT ON DEMAND Feststellen, ob PCT Tracking funktioniert select mview_name, num_PCT_TABLES from user_mviews; Refresh auf eine MAV 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 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 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 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; Löschen Materilized View Log drop materialized view log on f_Bestellungen; Build Funktionen 22 /42 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; 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 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; BUILD IMMEDIATE BUILD DEFERRED ON PREBUILD DBMS_MVIEW.EXPLAIN_REWRITE Refresh Funktionen begin dbms_mview.explain_rewrite(' SELECT z.jahr_nummer Jahr, z.monat_desc Monat, COMPLETE FAST FORCE Document1 Legt Tabelle Ergebnis-Tabelle anlegen -> \RDBMS\ADMIN\utlxrw.sql Data Warehouse Technik im Fokus - Skripte in Kurzform 23 /42 sum(u.umsatz) Summe, a.artikel_id ID FROM f_umsatz u, d_artikel a, d_zeit z WHERE a.artikel_id = u.artikel_id AND u.zeit_id = z.datum_id GROUP BY z.jahr_nummer, z.monat_desc, a.artikel_id', 'MV_UMS_ART_Zeit_Join'); end; Eine einzelne Tabelle auf parallel” setzen select mv_name, message from rewrite_table; select table_name,degree from user_tables; TABLE_NAME DEGREE --------------- ---------D_VERTRIEBSKANAL 1 D_ZEIT 1 D_REGION 1 D_ARTIKEL 1 D_KUNDE 1 F_UMSATZ 4 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 userName,PDML_ENABLED,PDML_STATUS,PDDL_STATUS,PQ_STATUS from v$session; Abfragen weleche Tabellen mit einem festgelegten Parallelisierungswert belegt sind 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 Aktuelle SQL-Statements und deren Parallelisierung abfragen ALL_MVIEWS DBA_MVIEWS USER_MVIEWS USER_MVIEW_DETAIL_PARTITION USER_MVIEW_DETAIL_RELATIONS USER_MVIEW_DETAIL_SUBPARTITION USER_DIMENSIONS 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) SQL_Text from V$SQL_MONITOR where username = 'DWH'; (wird erst nach 5 Sekunden Laufzeit aktiviert) Relevante Dictionary Views für Parallelisierung Optimierung für schnelles Lesen Optimizer - Statistiken sammeln Statistiken für eine Tabelle Memory-Cache EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','F_UMSATZ',estimate_percent=>100); Tabellen in Cache legen / aus Cache entfernen Statistiken für einen Index alter table D_ARTIKEL cache; alter table D_ARTIKEL nocache; EXEC DBMS_STATS.GATHER_INDEX_STATS ('DWH','PK_ARTIKEL',estimate_percent=>100); Welche Tabellen liegen im Cache SELECT owner, table_name = 'Y'; v$session v$sql_monitor User_tables SQL-Monitor im Enterprise Manger nutzen FROM dba_tables WHERE LTRIM(cache) Statistiken für eine Materialized View Definition EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH','MAV_REGION_ZEIT_UMSATZ',estimate_percent=>100); (Werden wie Tabellen behandelt) Query Result Cache Statistiken für ein Schema Aktivieren mit EXEC dbms_stats.gather_schema_stats( ownname => 'ETLDB', estimate_percent => 5,block_sample => TRUE) alter system set result_cache_mode=FORCE; (MANUAL) RESULT_CACHE_MAX_RESULT RESULT_CACHE_MAX_SIZE RESULT_CACHE_MODE RESULT_CACHE_REMOTE_EXPIRATION Automatische Aktualisierung für ein Schema einrichten begin dbms_stats.gather_schema_stats( ownname => 'PERF' ,options => 'GATHER AUTO' ,estimate_percent => 5 ,block_sample => TRUE); Ausnutzen Result Cache durch Hint in der Abfrage SELECT /*+ result_cache */ COUNT(*), SUM(BESTELL_TOTAL) FROM F_UMSATZ; Abfragen auf im Cache befindliche Statements SELECT name, type, row_count, invalidations, scan_count FROM v$result_cache_objects; Result-Cache-Memory-Report end; Automatisiertes Sampling EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS( 'OE',DBMS_STATS.AUTO_SAMPLE_SIZE); execute dbms_result_cache.memory_report() Abfrage ob automatisiertes Sammel aktiviert ist Result Cache leeren SELECT client_name, status FROM dba_autotask_operation; CLIENT_NAME STATUS -------------------------------------- -------auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED exec dbms_result_cache.flush; Relvante Dictionary Views für Result-Cache V$RESULT_CACHE_OBJECTS Parallelisierung Relevante Fragestellungen Zustand/Aktualität der Statistiken abfragen select table_name,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN,SAMPLE_SIZ E,LAST_ANALYZED ,STALE_STATS from user_tab_statistics; Aktivieren / Deaktivieren Abfragen der eingestellten Parallelität Gezieltes Steuern der Parallelität für einzelne Tabellen Zustand von Index-Statistiken abfragen Parallelisierung aktivieren Histogramme abfragen 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 Document1 select Index_name,TABLE_NAME,LAST_ANALYZED,STALE_STATS from user_ind_statistics; 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 Data Warehouse Technik im Fokus - Skripte in Kurzform 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 24 /42 maxbytes, maxblocks, increment_by from dba_data_files order by tablespace_name, file_name; Temp-Space select nvl(sum(bytes),0)/1024/1024 Meg from dba_temp_files; 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 Exec DBMS_STATS.SET_TABLE_PREFS(<OWNER>, <TABLE_NAME>, 'INCREMENTAL', ‘TRUE’); Top 10 größten Tabellen eines Users exec DBMS_STATS.SET_TABLE_PREFS('DWH','F_UMSATZ', 'INCREMENTAL', 'TRUE'); Data Files + Redo Logs + Temp Relvante Dictionary Views für Statistiken USER_TAB_STATISTICS USER_TAB_COL_STATISTICS USER_IND_STATISTICS USER_HISTOGRAMS USER_TAB_HISTOGRAMS Umgebungsinformationen auslesen select table_name, Blocks from user_tables order by blocks desc where rownum < 10 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 DB-Namen Version Patchstände Installierte Komponenten -- 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 / Database-ID abfragen Benutzter Datenbank-Platz Datenbank-Informationen, Version, Patchstände Relevante Fragestellungen 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 sich im Oracle – Home Verzeichnis Patch-Historie SELECT * FROM sys.registry$history Datenbankversion abfragen SELECT * FROM v$version Relevante Dictionary Views dba_registry V$version V$SESSION V$DATABASE Tablespaces, Auflistungen, Mengen und Größen 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 SELECT SUM(bytes)/1024/1024 Meg FROM dba_segments; Belegter und benutzter Temp-Tablespace SELECT 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; CREATE SMALLFILE TABLESPACE "TS_DWH" DATAFILE 'D:\APP\ORADATA\ORCL\DF_DWH' SIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO TABLE_NAME NUM_ROWS BLOCKS KB ------------------------------------- ---------- ---------BESTELLUNG_PART_RANGE 163840000 962355 7698840 BESTELLUNG_PART_RANGE_4 163840000 962355 7698840 Vergrößern eines Tablespace mit ADD DATAFILE Tabellengrößen und Platzverbrauch alter tablespace ts_dwh add datafile 'G:\oraim\oradata\orcl\dwh2.dbf' SIZE 10G Größe aller Data Files select sum(bytes)/1024/1024 Meg from dba_data_files; Auflistung aller Datafiles column FILE_NAME format a30 select FILE_NAME,BYTES/1000000 MB,STATUS 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, Document1 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 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 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 Data Warehouse Technik im Fokus - Skripte in Kurzform FROM user_tab_partitions WHERE table_name = 'BESTELLUNG_PART_RANGE'; Welche Objekte gehören zu einem Tablespace select owner,segment_name,segment_type, Blocks, bytes/1000000 MB from dba_segments where tablespace_name='DWH_SPINDEL' Größenangaben bezogen auf Partitionen 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'; 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 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; Growth(%) in --TABLESPACE_NAME ---- -----------DWH1 --EXAMPLE --PERFSTAT --SYSAUX --SYSTEM --TEST_ALERT --UNDOTBS1 --USERS SIZE_MB2 SIZE_MB1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 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 Segment-Informationen SQL> select distinct segment_type from user_segments; SEGMENT_TYPE -----------------TABLE SUBPARTITION TABLE PARTITION TABLE INDEX Gesamtauswertung belegter Plattenplatz und Freespace pro Tablespace und Datafiles SET LINESIZE 145 SET PAGESIZE 9999 SET VERIFY OFF COLUMN tablespace COLUMN filename COLUMN filesize Size' COLUMN used FORMAT bytes)' COLUMN pct_used Document1 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' 25 /42 BREAK ON report COMPUTE SUM OF filesize COMPUTE SUM OF used COMPUTE AVG OF 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 DBA_TABLES DBA_FREE_SPACE V$TEMP_EXTENT_POOL V$TEMPFILE DBA_SEGMENTS V$LOG DBA_TEMP_FILES V$TEMPFILE Database Files Relevante Fragestellungen 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 select file_name,file_id 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; Data Warehouse Technik im Fokus - Skripte in Kurzform 26 /42 Database Files mit asynchronem Lesen und Schreiben Ein Profil einem Benutzer zuordnen 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 Create user DWH_Std identified by DWH_std Profile DWH_STANDARD; Relevante Dictionary Views dba_data_files dba_extents Relevante Dictionary Views DBA_USERS DBA_TS_QUOTAS USER_PASSWORD_LIMITS DBA_PROFILES RESOURCE_COST V$SESSION Rollen ; Eine Rolle anlegen und Rechte auf Objekte zuweisen create role "DWH_READ" NOT IDENTIFIED; grant SELECT ANY TABLE to "DWH_READ"; grant SELECT on "DWH"."MD_GROUPS" to "DWH_READ"; 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; Lesestatistiken / Benutzungsverhalten Rollenrechte weitergeben Gelesene und geschriebene Blöcke Default-Rolle einem Benutzer zuweisen 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 E:\APP\ORADATA\ORCL\T1 17 9 26 Security Profile Über Profile lässt sich die Ressourcen-Nutzung und die Art des Umgangs mit einem Password für unterschiedlicher Benutzergruppen steuern. Ist ein User nicht explizit einem Profil zugeordnet, dann gilt für ihn das Profil DEFAULT Profil erstellen CREATE PROFILE DWH_STANDARD LIMIT SESSIONS_PER_USER 1 CPU_PER_SESSION unlimited CPU_PER_CALL 6000 LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100 password_reuse_max 10 password_reuse_time 30 IDLE_TIME 30 CONNECT_TIME 480; GRANT DWH_ACCESS to user_name; 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; 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 User-Informationen / Einstellungen 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 und löschen Create user DWH identified by DWH default tablespace users; Create user OLTP identified by OLTP default tablespace users; Drop user XXX [CASCADE] Ändern eines Profils Alter Profile DWH_STANDARD LIMIT CPU_PER_CALL 3000; Abfragen der Settings für ein Profile Rechte zuweisen um sich einloggen zu können GRANT create session to DWH; GRANT create session to OLTP; column RESOURCE_NAME format a30 column limit format a20 Eigene Tabellen zum Lesen für alle freigeben select distinct resource_name,limit from dba_profiles where profile = 'DEFAULT'; Allgemeine Benutzerinformationen abfragen RESOURCE_NAME -----------------------------PASSWORD_LOCK_TIME IDLE_TIME CONNECT_TIME PASSWORD_GRACE_TIME LOGICAL_READS_PER_SESSION PRIVATE_SGA LOGICAL_READS_PER_CALL SESSIONS_PER_USER CPU_PER_SESSION FAILED_LOGIN_ATTEMPTS PASSWORD_LIFE_TIME PASSWORD_VERIFY_FUNCTION PASSWORD_REUSE_TIME PASSWORD_REUSE_MAX COMPOSITE_LIMIT CPU_PER_CALL 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 / Document1 Grant select any table to public; LIMIT ---------1 UNLIMITED UNLIMITED 7 UNLIMITED UNLIMITED UNLIMITED UNLIMITED UNLIMITED 10 180 NULL UNLIMITED UNLIMITED UNLIMITED UNLIMITED SQL> desc dba_Users Ausschalten Expiration Date in Entwicklungs- und Test-Umgebung 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 Data Warehouse Technik im Fokus - Skripte in Kurzform 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 27 /42 from dba_tab_privs where grantee like upper('DWH') order by privilege / Relevante Dictionary Views sys.AUD$ sys.audit_actions Plattenplatz der Segment-Objekte eines Users V$SESSION DBA_USERS v$sql_monitor column segment_name format a30 SELECT sg.segment_name, sg.SEGMENT_TYPE, sg.extents, sg.tablespace_name, sg.blocks, ts.BLOCK_SIZE, round((sg.blocks*ts.BLOCK_SIZE)/1000000,2) MB_SIZE FROM dba_segments sg, dba_tablespaces ts WHERE owner = 'PERF' and ts.TABLESPACE_NAME = sg.TABLESPACE_NAME Logins pro User 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 Fine Grained Auditing Mit Fine Grained Auditing lässt sich jede Aktion eines Benutzers dokumentieren. Die SQL-Befehle des Benutzers werden dokumentiert. Aktivieren des Fine Grained Auditing EXECUTE DBMS_FGA.ADD_POLICY( object_schema => 'DWH' object_name => 'F_UMSATZ' policy_name => 'Chk_F_Umsatz' statement_types => 'SELECT' enable => TRUE); , , , , - Abfragen des Fine Grained Auditing select TIMESTAMP,db_user,os_user,object_schema,object_name,sql _text from DBA_FGA_AUDIT_TRAIL; 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 Löschen einer Policy EXECUTE DBMS_FGA.DROP_POLICY( object_schema => 'DWH' object_name => 'F_UMSATZ' policy_name => 'Chk_F_Umsatz'); Was machen die Benutzer gerade Relevante Dictionary Views select EXECUTIONS, USERS_EXECUTING, username, sql_text from v$session se , v$sql sq where se.sql_address = sq.address; sys.AUD$ sys.audit_actions , , - V$SESSION DBA_USERS v$sql_monitor Relevante Dictionary Views V$SESSION DBA_USERS v$sql_monitor Resource Manager / Ressourcen kontrollieren Kontrollieren Welcher Benutzer macht was / AUDITING Folgende Ressourcen können gesteuert werden CPU-Nutzung Anzahl Session, die ein Benutzer öffnen kann Parallelisierung Undo-Nutzung Idle-Zeit (Zeit in der nichts passiert) Execution Zeit Anzahl I/O Zugriffe Auditing auf eine Tabelle einschalten Alle Aktionen auf Objekte, aber auch das Vergeben von Rechten und Verändern von Systemzuständen kann einzeln durch den AUDIT – Befehl überwacht werden. Was alles überwacht werden kann, steht in der Tabelle sys.audit_actions. Audit select on F_UMSATZ; AUDIT delete,select,Insert,update on F_UMSATZ; Abfrage der AUDIT-Ergebnisse Die Informationen steht in der Tabelle: sys.AUD$ column column column column column userid obj$name obj$creator ntimestamp# userhost format a10 format a20 format a10 format a28 format a30 select a.userid,a.userhost, a.obj$name,a.obj$creator,a.ntimestamp#, aa.name from sys.AUD$ a, sys.audit_actions aa where aa.action = a.action#; Reihenfolge der Erstellung von Ressourcen-Plänen 1) 2) 3) 4) 5) 6) 7) Pending Area einrichen (zum testen) Consumer Gruppen definieren Ressourcen – Pläne definieren Plan Direktiven erstellen Zuweisen von Usern zu Consumer Gruppen Pending Area aktivieren / Submitten Plan aktiv setzen Pending- Area einrichten / leeren /submitten Eine Pending-Area ist ein interner Bereich, in dem man Ressourcen-Pläne ablegt und testet, bevor sie man sie produktiv setzt exec dbms_resource_manager.create_pending_area; USERHOST OBJ$NAME OBJ$CREATO NTIMESTAMP# NAME ---------------------- --------- ---------- -----------------------DE-ORACLE\ASCHLAUC-DE F_UMSATZ DWH 08.06.14 20:02:27 SELECT exec dbms_resource_manager.clear_pending_area; exec dbms_resource_manager.submit_pending_area; Audit auf einen User audit session by dwh; Ausschalten des Audit Noaudit all; Feststellen welchem Benutzer welche Rechte gegeben wurden Connecten mit DBA-Berechtigung Eingabe des User-Namens dort wo DWH steht. select grantor || ' granted ' || privilege || ' on ' || table_name || ' owned by '|| owner || ' to '|| grantee Document1 Consumer Groups einrichten Consumer-Gruppe einrichten EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group=>'B ATCH_LOW', comment=>'Hintergrund_Batch_LAEUFE'); EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(consumer_group=>'E TL_HIGH', comment=>'ETL_LAEUFE'); Data Warehouse Technik im Fokus - Skripte in Kurzform 28 /42 Welche Consumer Gruppen gibt es Plan aktiv setzen column consumer_group format a25 column status format a25 Alter system set resource_manager_plan=DWH_PLAN; select consumer_group,status from dba_rsrc_consumer_groups; CONSUMER_GROUP STATUS ------------------------- -----------------BATCH_GROUP INTERACTIVE_GROUP OTHER_GROUPS DEFAULT_CONSUMER_GROUP SYS_GROUP LOW_GROUP ETL_GROUP DSS_GROUP DSS_CRITICAL_GROUP SYS_GROUP LOW_GROUP ETL_GROUP DSS_GROUP DSS_CRITICAL_GROUP ORA$APPQOS_0 ORA$APPQOS_7 ETL_HIGH PENDING BATCH_GROUP PENDING ORA$AUTOTASK PENDING INTERACTIVE_GROUP PENDING BATCH_LOW PENDING Ressourcen Pläne select * from v$rsrc_plan; ID NAME IS_TO CPU INS PARALLEL_SERVERS_ACTIVE PARALLEL_SERVERS_TOTAL PARALLEL_EXECUTION_MANAGED CON_ID ------ -------------- --- --- ----------------------- ---------------------- --------------------------------96071 DWH_PLAN TRUE ON OFF 0 64 FULL 0 Abfragen auf verbrauchte Ressourcen der Consumer Gruppen select name, active_sessions,cpu_wait_time,consumed_cpu_time, current_undo_consumption from v$rsrc_consumer_group; NAME ACTIVE_SESSIONS CPU_WAIT_TIME CONSUMED_CPU_TIME CURRENT_UNDO_CONSUMPTION -------------------- --------------- ------------- ----------------- ------------------BATCH_LOW 0 0 0 0 ETL_HIGH 0 0 0 0 OTHER_GROUPS 2 22 28 0 _ORACLE_BACKGROUND_GROUP_ 30 0 0 0 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 Methoden nach denen gesteuert werden kann CPU_MTH -> EMPHASIS (default) oder RATIO ACTIVE_SESS_POOL_MTH ->ACTIVE_SESS_POOL_ABSOLUTE PARALLEL_DEGREE_LIMIT_MTH -> PARALLEL_DEGREE_LIMIT_ABSOLUT SUB_PLAN -> TRUE / FALSE (default) QUEUEING_MTH -> FIFO_TIMEOUT Plan erstellen EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN 'DWH_PLAN', CPU_MTH => 'EMPHASIS') Abfragen auf aktiven Plan => Plan Direktive erstellen Exec dbms_resource_manager.create_plan_directive (plan => 'DWH_PLAN', group_or_subplan => 'ETL_HIGH',CPU_P1 => 80); Exec dbms_resource_manager.create_plan_directive (plan => 'DWH_PLAN', group_or_subplan => 'BATCH_LOW',CPU_P1 => 20); Exec dbms_resource_manager.create_plan_directive (plan => 'DWH_PLAN', group_or_subplan => 'OTHER_GROUPS',CPU_P2 => 100); Pending Area Prüfen 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 ohne Log-Datei 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 Exec dbms_resource_manager.validate_Pending_Area(); CREATE SEQUENCE Kun_seq INCREMENT BY 1 START WITH 1; Abfrage auf bestehende Pläne Insert into … select Kun_seq.NEXTVAL from dual column Plan format a30 column GROUP_OR_SUBPLAN format a20 Select plan, group_or_subplan,cpu_p1,cpu_p2, cpu_p3, status from dba_rsrc_plan_directives PLAN ----------DWH_PLAN DWH_PLAN DWH_PLAN GROUP_OR_SUBPLAN CPU_P1 CPU_P2 CPU_P3 STATUS ------------------------- ------- ------- -------ETL_HIGH 80 0 0 PENDING BATCH_LOW 20 0 0 PENDING OTHER_GROUPS 0 100 0 PENDING Zuweisen von Benutzern zu Consumer – Gruppen create user ETL_USER identified by ETL_USER; create user BATCH_USER identified by BATCH_USER; EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (attribute => dbms_resource_manager.oracle_user,value => 'ETL_USER',consumer_group => 'ETL_HIGH'); Abschliessen und Aktiv-Setzen der Pending-Area als letzten Schritt 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 .... Database Link Zugriff auf Remote-Oracle-Datenbank (Database Link) CREATE DATABASE LINK "DWH" IDENTIFIED BY "DWH" USING 'ORA10' ; CONNECT TO "DWH" Verwendung Select * from SOURCE_TABLEqDWH; exec dbms_resource_manager.submit_pending_area; Welche User gehören zu welchen Consume Gruppen column INITIAL_RSRC_CONSUMER_GROUP format a25 select username,INITIAL_RSRC_CONSUMER_GROUP from dba_users / USERNAME INITIAL_RSRC_CONSUMER_GROP -------------------- ------------------------BATCH_USER BATCH_LOW SYS SYS_GROUP SYSTEM SYS_GROUP ETL_USER ETL_HIGH 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 Document1 Data Warehouse Technik im Fokus - Skripte in Kurzform 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) VALUES(old.Bestellnr,new.Bestellnr); END; 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 ) External Table Beispiel 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 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'); 29 /42 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 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; 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 Document1 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; Data Warehouse Technik im Fokus - Skripte in Kurzform 30 /42 --- 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 '...... ; [: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 ---- 6. Kopieren der TS-Datei und der Metadaten Arbeiten ohne Constraints und mengenbasiertes SQL / Prüfen ---- 7. Import impdp system/****@orcldw TRANSPORT_TABLESPACES=ts_bestellung DIRECTORY=ts_dir DUMPFILE=ts_best_file Error Logging 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. 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:] [:alpha:] [:blank:] [:cntrl:] [:digit:] [:graph:] chars [:lower:] Document1 Alphanumeric characters Alphabetic characters Blank Space Characters Control characters (nonprinting) Numeric digits Any [:punct:], [:upper:], [:lower:], and [:digit:] Lowercase alphabetic characters 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 über separate Funktion 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; Numieric-Prüfung mit Regexp select bestellnr, case when -- wenn Feld BESTELLNR nicht numerisch REGEXP_LIKE(BESTELLNR, '[^[:digit:]]') then 1 else 0 End Num_Check_bestellnr from bestellung; 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' Data Warehouse Technik im Fokus - Skripte in Kurzform AND dateCheck(inDate, 'mm-dd-yy') = 'false' AND dateCheck(inDate, 'yyyy-mm-dd') = 'false' AND dateCheck(inDate, 'yy-mm-dd') = 'false' AND dateCheck(inDate, 'yyyy-mon-dd') = 'false‚ AND dateCheck(inDate, 'yy-mon-dd') = 'false‚ AND dateCheck(inDate, 'dd-mon-yyyy') = 'false‚ AND dateCheck(inDate, 'dd-mon-yy') = 'false‚ AND dateCheck(inDate, 'mmddyy') = 'false‚ AND dateCheck(inDate, 'mmddyyyy') = 'false‚ AND dateCheck(inDate, 'yyyymmdd') = 'false' AND dateCheck(inDate, 'yymmdd') = 'false‚ AND dateCheck(inDate, 'yymmdd') = 'false' AND dateCheck(inDate, 'yymondd') = 'false‚ AND dateCheck(inDate, 'yyyymondd') = 'false‚ AND dateCheck(inDate, 'mm/dd/yyyy') = 'false' AND dateCheck(inDate, 'yyyy/mm/dd') = 'false‚ AND dateCheck(inDate, 'mm/dd/yy') = 'false' AND dateCheck(inDate, 'yy/mm/dd') = 'false‚ AND dateCheck(inDate, 'mm.dd.yyyy') = 'false' AND dateCheck(inDate, 'mm.dd.yy') = 'false' AND dateCheck(inDate, 'yyyy.mm.dd') = 'false' AND dateCheck(inDate, 'yy.mm.dd') = 'false' then return 'false'; else return 'true'; end if; --exception --when others then return 'false'; END; 31 /42 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 D: Tabellen-übergreifende Prüfungen. Foreign Key Prüfung Beispieltabelle Prüfregel: Es darf keine Bestellung ohne zugehörigen Kunde geben. 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 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 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 Prüfungen Kategorie C: Satz-übergreifende Prüfungen. Eindeutigkeitsprüfungen Beispieltabelle select * from quarterly_sales 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 Document1 in ('Q1','Q2','Q3','Q4')) order by salesrep ; 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 Data Warehouse Technik im Fokus - Skripte in Kurzform (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 32 /42 25489743 25489743 25489743 25489743 25489743 select count(*) from x; -> 5 Flashback table x to scn 25489616; select count(*) from x; -> 1 MERGE INTO Kunde_TGT USING (SELECT KUNDEN_STAMM.KUNDENNR KUNDENNR, KUNDEN_STAMM.VORNAME VORNAME, KUNDEN_STAMM.NACHNAME NACHNAME, KUNDEN_STAMM.STATUS STATUS, KUNDEN_STAMM.STRASSE STRASSE, KUNDEN_STAMM.TELEFON TELEFON, KUNDEN_STAMM.TELEFAX TELEFAX FROM KUNDEN_STAMM KUNDEN_STAMM) MERGE_SUBQUERY ON ( Kunde_TGT.KUNDENNR = MERGE_SUBQUERY.KUNDENNR) WHEN NOT MATCHED THEN INSERT (Kunde_TGT.KUNDENNR, Kunde_TGT.VORNAME, Kunde_TGT.NACHNAME, Kunde_TGT.STATUS, Kunde_TGT.STRASSE, Kunde_TGT.TELEFON, Kunde_TGT.TELEFAX) VALUES (MERGE_SUBQUERY.KUNDENNR, MERGE_SUBQUERY.VORNAME, MERGE_SUBQUERY.NACHNAME, MERGE_SUBQUERY.STATUS, MERGE_SUBQUERY.STRASSE, MERGE_SUBQUERY.TELEFON, MERGE_SUBQUERY.TELEFAX) WHEN MATCHED THEN UPDATE SET VORNAME = MERGE_SUBQUERY.VORNAME, NACHNAME = MERGE_SUBQUERY.NACHNAME, STATUS = MERGE_SUBQUERY.STATUS, STRASSE = MERGE_SUBQUERY.STRASSE, TELEFON = MERGE_SUBQUERY.TELEFON, TELEFAX = MERGE_SUBQUERY.TELEFAX; Relevante Dictionary Views gv$flashback_database_log gv$parameter gv$database 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 ----- Flashback 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; ------------------------------------------------------------- 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 Table-Function-Definition Log-Modus / Archiv-Modus prüfen 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; 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 NUMBER NUMBER DATE NUMBER NUMBER Die Definition der Table-Function (in diesem Beispiel werden sehr einfache Aktinen in der Table Function gemacht, um das Prinzip aufzuzeigen) 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()); 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 ---------Document1 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; -- allow compilation RETURN NULL; EXCEPTION WHEN OTHERS THEN NULL; -- enter any exception code here RETURN NULL; Data Warehouse Technik im Fokus - Skripte in Kurzform END; Ergebnis: 'TAKTUM' Template für PL/SQL-Prozedur mit Cursor create or replace PROCEDURE ABC AS cursor Pos is select sysdate Tagedsdatum, '1' from DUAL connect by level < 10; V_Lieferkosten V_Tages_Datum V_Neues_Datum V_Konstante number; DATE; DATE; varchar2(5); BEGIN open Pos; loop exit when pos%notfound; fetch Pos into V_Tages_Datum, V_Konstante; V_Neues_Datum := V_Tages_Datum +10; dbms_output.put_line('Tagesdatum : ' ||V_Tages_Datum) ; dbms_output.put_line('Neues Datum: ' ||V_Neues_Datum) ; end loop; end; Erzeugen Universal Identifier select sys_GUID() from dual; SYS_GUID() -------------------------------BADF46B9AFD449FB8680A5A0751A7FD3 Aktivieren von Serveroutput set serveroutput on 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...) 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) Document1 33 /42 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. 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 months_between(to_date('25.12.2002', 'dd.mm.yyyy'), to_date('25.11.2002', 'dd.mm.yyyy')) Ergebnis: 1 Als Basis zur Berechnung werden immer 31 Tage je Monat zugrunde gelegt. Bedingte Abfragen (Decode / CASE) decode if then else greatest(e1[,e2] ...) größter Wert der Ausdrücke least(e1[,e2] ...) kleinster Wert der Ausdrücke nvl(e1, e2) ist e1 NULL dann e2 sonst e1 nvl2(e1, e2, e3) ist e1 NULL dann e3 sonst e2 (ab 8i) user aktueller Datenbankbenutzername userenv(s) Informationen zur Benutzerumgebung dump(e) interne Kodierung von e vsize(e) benötigter Speicherplatz in Bytes 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 vsize(sysdate) Ergebnis: 8 Konvertierungsfunktionen to_char(a[,fmt]) Umwandlung der Zahl in eine Zeichenkette je nach Format fmt. Data Warehouse Technik im Fokus - Skripte in Kurzform 34 /42 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 Beispiele insert into druckersteuerung (befehl, code) values ('6 Zeilen/Zoll', hextoraw('1B266C3644')); to_char(23012.9, '000G000D00') Ergebnis: '023.012,90' from auftrag_pos where auftrag_nr = 1; to_char(to_date('24.12.2002','dd.mm.yyyy'),'hh24:mi:ss') Ergebnis: '00:00:00' Relevante Fragestellungen 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: Lösung 1: select * from auftrag where datum = to_date ('21.09.1999', 'dd.mm.yyyy'); => Problem: Was ist mit der Uhrzeit? Lösung 2: select *from auftrag where to_char (datum, 'dd.mm.yyyy') = '21.09.1999'; 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) 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 Document1 select preis * to_number ('1,8', '9D9') from auftrag_pos where auftrag_nr = 1; Verwalten des Systems / Systembeobachtung 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 username format a15 Data Warehouse Technik im Fokus - Skripte in Kurzform column column column column column column column column Machine format a30 username format a10 SCHEMA format a10 machine format a18 sid format 9999 program format a15 process format a10 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; Document1 35 /42 Platzverbrauch AWR messen Select occupant_name, space_usage_kbytes from V$SYSAUX_OCCUPANTS where occupant_name = 'SM/AWR' Auflistung bestehender Snapshots 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 DBA_ALERT_HISTORY V$EVENT_NAME V$INSTANCE DBA_ADVISOR_TASKS 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); Data Warehouse Technik im Fokus - Skripte in Kurzform 36 /42 Beispiel-Trace-Session SQL> select count(*) from dwh.wh_transaktionen; 4216 SQL> execute dbms_monitor.session_trace_disable(135,181); -- Output in Trace-Datei *** 2011-09-05 08:08:53.468 ===================== PARSING IN CURSOR #1 len=62 dep=0 uid=0 oct=47 lid=0 tim=33718840968 hv=3081195784 ad='34ab52dc' sqlid='2bqy8r6vufn88' BEGIN dbms_monitor.session_trace_enable(135,181,false); END; END OF 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 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 = 'ETLDB' GROUP BY t.name, o.object_name order by o.object_name; Sessions schnell ‘killen’ select SID,serial#,schemaname,module from v$session where TYPE = 'USER'; alter system kill session 'sid,serial'; oder Session-Daten so herausfinden SET LINESIZE 100 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45 SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND' and s.username ='ABC' Schnelle Übersicht über aktuell laufende Sessions / wer / wo / was 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 Session-bezogene Informationen -- lock_SGA : SGA immer im Hauptspeicher ---belassen, kein Auslagern auf Platte alter system set lock_sga=TRUE scope=SPFILE; Session Daten abfragen Herausfinden Memory und Session 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 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; Document1 column USERNAME format a10 column name format a30 column machine format a40 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 SID USERNAME PROGRAM MACHINE NAME VALUE ------------------------------ ---------191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session pga memory 987364 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session pga memory max 987364 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session uga memory 438608 191 PETER sqlplus.exe ARBEITSGRUPPE\ALFRED session uga memory max 438608 Relevante Dictionary Views V$SESSTAT V$SESSION V$PGASTAT V$SYSTEM_EVENT V$STATNAME V$MEMORY_TARGET_ADVICE V$EVENT_NAME Data Warehouse Technik im Fokus - Skripte in Kurzform ASH (Active Session History), Session + User Informationen Größe ASH - Buffer SELECT * FROM gv$sgastat WHERE name = 'ASH buffers'; Desc gv$active_session_history, 37 /42 WHERE -- ash.sample_time > SYSDATE - 1/24 ash.session_state = 'WAITING' AND ash.event_id = evt.event_id AND evt.wait_class = 'User I/O' AND ash.sql_id = txt.SQL_ID and u.USER_ID = ash.user_id and u.username = 'TOMIS' GROUP BY u.username,ash.user_id,ash.sql_id , txt.SQL_TEXT ORDER BY COUNT(*) DESC; SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1; Session-Informationen Session Daten abfragen GV$ACTIVE_SESSION_HISTORY GV$SQL DBA_HIST_SNAPSHOT V$SESSION SQL> SELECT sid, serial# FROM gv$session WHERE username = 'MON'; SQL> desc v$session Relevante Dictionary Views -> 134 / 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 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; set linesize 121 SELECT * FROM TABLE(dbms_xplan.display_cursor('...sql_id_#...')); Sample-Time abfragen SELECT sample_time, event, wait_time FROM gv$active_session_history WHERE session_id = 134 AND session_serial# = 63 SAMPLE_TIME EVENT WAIT_TIME ---------------------------------- ---------------- 05-SEP-11 08.47.44.282 PM 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); Übersicht über SQL-Statements in der Vergangenheit 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; Aufbereitung Statements pro user mit SQL – Text column text format a100 SELECT u.username, ash.user_id,ash.sql_id, COUNT(*), txt.SQL_TEXT TEXT FROM gv$active_session_history ash, gv$event_name evt, GV$SQL txt, dba_users u Document1 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; 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 Data Warehouse Technik im Fokus - Skripte in Kurzform 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 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' / 38 /42 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'; 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 USERNAME ANZ_PROZ PARALLEL MILLISEK_RECH_ZEIT SQL_ID SQL_EXEC_ID YES YES NO 0 SELECT NAME LAUFZEIT SQL_TEXT NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1 ----------------- ------------- ------------------ ------------SYS_SQL_967d7c5636192728 SQL_PLAN_9czbwasv1k9t8499f732f MON 9 4 146. 742371 YES YES NO 0 select distinct VERTRIEBSKANAL from 9p3gpbg7sz1cr 16777216 22-08-2011 08:19:12 select distinct vertriebskanal from BESTELLUNG_PAR BESTELLUNG_PART_RANGE_4 MON 9 4 146.848209 9p3gpbg7sz1cr 16777217 22-08-2011 SYS_SQL_b96c99b551913735 SQL_PLAN_bkv4tqp8t2dtp6be2eac7 08:20:43 select distinct vertriebskanal from BESTELLUNG_PART_RANGE_4 YES YES NO 0 select * from tab MON 1 158.071063 9p3gpbg7sz1cr 16777218 22-08-2011 08:23:37 selectSYS_SQL_bff897b9dbcabe27 SQL_PLAN_bzy4rr7dwpgj7ed88afee distinct vertriebskanal from BESTELLUNG_PART_RANGE_4 0 SELECT PETER 1 158.029279 apzgjdwwcxb2j 16777217 22-08-2011 08:27:52 selectYES YES NO ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DA distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PETER 5 08:29:54 PETER 9 08:30:57 PETER 9 08:51:21 PETER 9 10:15:54 PETER 9 10:43:44 PETER 1 distinct 2 106.608017 apzgjdwwcxb2j 16777218 22-08-2011 Betrachten eines gespeicherten Plans select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 select * from table( 4 146.151807 apzgjdwwcxb2j 16777219 22-08-2011 dbms_xplan.Display_sql_Plan_BASELINE( select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 4 146.675706 apzgjdwwcxb2j 16777220 22-08-2011 SQL_HANDLE=> 'SYS_SQL_967d7c5636192728', select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 format=>'basic')); 4 146.563312 apzgjdwwcxb2j 16777222 22-08-2011 select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 PLAN_TABLE_OUTPUT 4 146.890659 apzgjdwwcxb2j 16777223 22-08-2011 -----------------------------------------------------------select distinct vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 158.497194 apzgjdwwcxb2j 16777224 22-08-2011 10:46:01 selectSQL handle: SYS_SQL_967d7c5636192728 vertriebskanal from mon.BESTELLUNG_PART_RANGE_4 SQL text: select distinct VERTRIEBSKANAL from 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 Document1 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 | Data Warehouse Technik im Fokus - Skripte in Kurzform 39 /42 | 3 | HASH UNIQUE| | | 4 | PX RECEIVE| | | 5 | PX SEND HASH | :TQ10000 | | 6 | PX BLOCK ITERATOR | | | 7 | TABLE ACCESS FULL| BESTELLUNG_PART_RANGE_4 | ----------------------------------------------------------- $ORACLE_HOME/rdbms/admin/spcreate.sql Evolve eines neuen Plans -- Snapshot stündlich ausführen lassen @?/rdbms/admin/spauto.sql --> stündlicher Aufruf 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 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 2 43~0,18 4 30~0,25 0,53/0,57 1,28/1,40/1,52/1,55 6 31~0,25 8 30~0,25 Relevante Dictionary Views V$STATNAME V$IOSTAT_FILE TRUE string Orion Infos über ORION -Help ORION: ORacle IO Numbers -- Version 11.2.0.1.0 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 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? IO Messung / calibrate IO disk_asynch_io boolean filesystemio_options -- Spapshot Bericht erstellen lassen D:\ora\product\11.2.0\dbhome_1\RDBMS\ADMIN>spreport.sql 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. DBA_SQL_PLAN_BASELINES V$SESSTAT V$SESSION DBA_DATA_FILES -- Snapshot erstellen (als PERFSTAT User) EXECUTE statspack.snap EXECUTE statspack.snap (i_snap_level => 7) SETALL Calibrate-Status abfragen [Kennt der Optimizer die Leistungsfähigkeit des Systems] select status from V$IO_CALIBRATION_STATUS; Calibrate IO abfragen set serveroutput on / DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat); dbms_resource_manager.calibrate_io (1, 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 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 -- 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 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 -- Perfstat User einrichten (als SYS User) select Perfstat Document1 ‘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 to_char(s.begin_interval_time,'mm-dd hh24') c1, Data Warehouse Technik im Fokus - Skripte in Kurzform 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 40 /42 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 V$BACKUP_REDOLOG V$LOG V$LOG_HISTORY RMAN RMAN starten Rman An Zieldatenbank einwählen Connect target orcl Welche Backups sind überflüssig Backup im Data Warehouse REPORT OBSOLETE; Welche Files benötigen ein Backup REPORT NEED BACKUP; Allgemeine Überlegungen Leeren Recover Area delete archivelog all; Argumente für ein DWH spezifisches Backup-Konzept Welche Files können nicht wiederhergestellt werden Welche Sicherungen liegen vor 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. REPORT UNRECOVERABLE; 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 SELECT * FROM x AS OF SCN 12555060; 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 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; Document1 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 SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG Anhang (DWH-Administrations-Checkliste - wird aktualisiert) Informationsbedarf planen Data Warehouse Technik im Fokus - Skripte in Kurzform 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? 41 /42 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 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 buch_id, ran_abc_mixed(15) Titel, ran_m_n(1,20) isbn, ran_m_n(1,100) preis, ran_abc_mixed(20) autor from dual connect by level < 2000 [Auf der Community-Web-Seite gibt es vorbereitete Skripte für umfangreichere Testdatenerzeugung: http://www.oracledwh.de/downloads/AutoIndex2.2.4/index.php?dir=downloads/DWH_Utilities/Testdatengenerator _V1/] Document1 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; Um 51,2 Millionen Sätze zu erhalten (das ist die Beispielmenge in dieser Skriptesammlung), muss man auf der Basis von 100000 Sätzen den Kopiervorgang 9 mal wiederholen. 6. Danach kann man die nötigen Constraints, Indexe usw. einrichten, so wie sie in der Skritesammelung dargestellt sind. Hilfsprozeduren zu den 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, Tag_des_Jahres, Woche_des_Jahres, Monats_Nummer, Monat_Desc, Quartals_Nummer, Jahr_Nummer) Values (incdate, recno, Data Warehouse Technik im Fokus - Skripte in Kurzform 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 42 /42
