Systemverwaltung: Optimierung - Universität Ulm · Inhaltsverzeichnis Teil 1. Einführung zur...

DB2®

Systemverwaltung: Optimierung

DB2 Version 9 für Linux, UNIX und Windows

SC12-3629-00

��

Hinweis Vor Verwendung dieser Informationen und des darin beschriebenen Produkts sollten die allgemeinen Informationen unter Bemerkungen gelesen werden.

Erste Ausgabe (August 2006)

Diese Veröffentlichung ist eine Übersetzung des Handbuchs IBM DB2 Version 9 for Linux, Unix, and Windows, Performance Guide, IBM Form SC10-4222-00, herausgegeben von International Business Machines Corporation, USA

© Copyright International Business Machines Corporation 2006 © Copyright IBM Deutschland GmbH 2006

Informationen, die nur für bestimmte Länder Gültigkeit haben und für Deutschland, Österreich und die Schweiz nicht zutreffen, wurden in dieser Veröffentlichung im Originaltext übernommen.

Möglicherweise sind nicht alle in dieser Übersetzung aufgeführten Produkte in Deutschland angekündigt und ver-fügbar; vor Entscheidungen empfiehlt sich der Kontakt mit der zuständigen IBM Geschäftsstelle.

Änderung des Textes bleibt vorbehalten.

Herausgegeben von: SW TSC Germany Kst. 2877 August 2006

Inhaltsverzeichnis

Teil 1. Einführung zur Leistung . . . 1

Kapitel 1. Leistungsprinzipien . . . . . 3 Elemente der Leistung . . . . . . . . . . . 3 Richtlinien zur Leistungsoptimierung . . . . . . 3 Entwickeln eines Leistungsverbesserungsprozesses . 5 Grenzen der Leistungsoptimierung . . . . . . . 6 Leistungsinformationen, die Benutzer liefern können 6 Einstiegstipps für die Leistungsoptimierung . . . . 7 Leistungsfaktoren für Plattenspeichern . . . . . . 8

Teil 2. Leistungsprobleme beim Datenbankentwurf . . . . . . . . . 9

Kapitel 2. Leistungsplanung beim Datenbankentwurf . . . . . . . . . . 11 Funktionen zur Leistungsverbesserung . . . . . 11

MQTs (MQT - Materialized Query Table, gespei-cherte Abfragetabelle) . . . . . . . . . . 11 Tabellenpartitionierung und MDC-Tabellen . . . 13 Optimierungsstrategien für MDC-Tabellen . . . 19 Optimierungsstrategien für partitionierte Tabellen 21

Kapitel 3. Indizes . . . . . . . . . . 29 Verwenden von relationalen Indizes zur Leistungs-verbesserung . . . . . . . . . . . . . . 29 Indexstruktur . . . . . . . . . . . . . . 31 Tipps zur Planung von relationalen Indizes . . . . 33 Tipps zur Leistung von relationalen Indizes . . . 35 Indexieren von XML-Daten - Übersicht . . . . . 39 Indizes in partitionierten Tabellen . . . . . . . 41

Indexverhalten bei partitionierten Tabellen . . . 41 Clusterindexverhalten bei partitionierten Tabellen 45 Asynchrone Indexbereinigung . . . . . . . 48

Kapitel 4. Designadvisor . . . . . . . 51 Designadvisor . . . . . . . . . . . . . 51

Ausgabe des Designadvisors . . . . . . . 52 Designadvisor-Funktionen . . . . . . . . . 53 Verwenden des Designadvisors . . . . . . . . 55 Definieren einer Auslastung für den Designadvisor 56 Verwenden des Designadvisors zur Migration von einer Datenbank mit einer einzelnen Partition auf eine Datenbank mit mehreren Partitionen . . . . 57 Begrenzungen und Einschränkungen des Designad-visors . . . . . . . . . . . . . . . . 58

Kapitel 5. Verwalten des gemeinsamen Zugriffs . . . . . . . . . . . . . . 61 Aspekte des gemeinsamen Zugriffs . . . . . . 61 Isolationsstufen . . . . . . . . . . . . . 62

Isolationsstufen und Leistung . . . . . . . 62 Angeben der Isolationsstufe . . . . . . . . 66

Sperren . . . . . . . . . . . . . . . . 69 Sperren und Steuerung des gemeinsamen Zugriffs . . . . . . . . . . . . . . . 69 Sperrenattribute . . . . . . . . . . . . 71 Sperren und Arten der Anwendungsverarbeitung 73 Sperrgranularität . . . . . . . . . . . 74 Sperrenumwandlung . . . . . . . . . . 75 Sperreneskalation . . . . . . . . . . . 76 Korrigieren von Problemen der Sperreneskalation 76 Deadlocks . . . . . . . . . . . . . . 78 Wartestatus und Zeitlimitüberschreitungen für Sperren . . . . . . . . . . . . . . . 80 Angabe einer Strategie für den Modus ’Warte-status für Sperre’ . . . . . . . . . . . 81 Verhindern von auf Sperren bezogenen Leistungsproblemen . . . . . . . . . . 82 Sperrentypenkompatibilität . . . . . . . . 84 Sperren und Datenzugriffsmethoden . . . . . 85 Sperrmodi und Zugriffspfade für Standard-tabellen . . . . . . . . . . . . . . . 87 Indextypen und Sperren des nächsten Schlüssels 91 Auswerten nicht festgeschriebener Daten durch Sperrenverzögerung . . . . . . . . . . 92 Option zum Ignorieren nicht festgeschriebener Einfügungen . . . . . . . . . . . . . 95 Sperrmodi für Tabellen- und Satz-ID-Indexsuchen für MDC-Tabellen . . . . . . . . . . . 96 Sperren für Blockindexsuchen für MDC-Tabellen 101 Sperrverhalten für partitionierte Tabellen . . . 104

Kapitel 6. DB2 Governor . . . . . . . 107 Das Dienstprogramm Governor . . . . . . . 107 Der Governor-Dämon . . . . . . . . . . 108 Starten und Stoppen von Governor . . . . . . 109 Konfigurieren von Governor . . . . . . . . 110 Die Konfigurationsdatei von Governor . . . . . 111 Elemente für Governor-Regeln . . . . . . . . 114 Beispiel für eine Governor-Konfigurationsdatei . . 119 Governor-Protokolldateien . . . . . . . . . 120 Abfragen von Governor-Protokolldateien . . . . 124

Kapitel 7. Maximieren der E/A-Effizi-enz . . . . . . . . . . . . . . . . 125 Reduzieren der E/A-Operationen durch Optimie-ren der SQL- und XQuery-Anweisungen . . . . 125

Angeben von Zeilenblockung zur Verringerung des Systemaufwands . . . . . . . . . . 125 Effiziente SELECT-Anweisungen . . . . . . 126 Stichprobendaten in SQL- und XQuery-Abfra-gen . . . . . . . . . . . . . . . . 128 Optimieren der Sortierleistung . . . . . . . 129

Pufferpools und Vorablesezugriff . . . . . . . 131 Pufferpoolverwaltung . . . . . . . . . 131 Verwaltung mehrerer Datenbankpufferpools . . 134 Pufferpoolverwaltung von Datenseiten . . . . 136

iii

Illustration der Datenseitenverwaltung in Pufferpools . . . . . . . . . . . . . 138 Aktualisierungsverarbeitung . . . . . . . 139 Proaktive Seitenbereinigung . . . . . . . 140 Vorablesen von Daten in den Pufferpool . . . 141 Sequenzielles Vorablesen . . . . . . . . 142 Blockorientierte Pufferpools für besseren sequenziellen Vorablesezugriff . . . . . . . 144 Vorablesezugriff über Listen . . . . . . . 145 Konfiguration von E/A-Servern für Vorablese-zugriff und Parallelität . . . . . . . . . 146 Illustration des Vorablesens mit paralleler E/A 147 Verwaltung der parallelen Ein-/Ausgabe . . . 148 Verbessern der Einfügeleistung . . . . . . 150 Verringern des Protokollierungsaufwands zur Verbesserung der Abfrageleistung . . . . . 151

Kapitel 8. Agentenverwaltung . . . . 153 Datenbankagenten . . . . . . . . . . . . 153 Übersicht über die Architektur und Prozesse von DB2 . . . . . . . . . . . . . . . . 155 Das DB2-Prozessmodell . . . . . . . . . . 158

Unterschiede zwischen Windows und UNIX . . 162 Verwaltung von Datenbankagenten . . . . . . 163 Agenten in einer partitionierten Datenbank . . . 163 Konfigurationsparameter mit Auswirkung auf die Anzahl von Agenten . . . . . . . . . . . 165 Client-/Serververarbeitungsmodell . . . . . . 166 Verbesserungen des Verbindungskonzentrators für Clientverbindungen . . . . . . . . . . . 171

Kapitel 9. Entwurf für den optimalen Datenzugriff . . . . . . . . . . . . 175 Der SQL- und XQuery-Compilerprozess . . . . 175 Auswählen von Optimierungsklassen . . . . . 179 Optimierungsklassen . . . . . . . . . . . 181 Einstellen der Optimierungsklasse . . . . . . 184 Konfigurationsparameter mit Einfluss auf die Abfrageoptimierung . . . . . . . . . . . 186 Auswirkung von Datenbankpartitionsgruppen auf die Abfrageoptimierung . . . . . . . . . . 189 Auswirkung von Tabellenbereichen auf die Abfrageoptimierung . . . . . . . . . . . 189 Optimieren von Zugriffsplänen . . . . . . . 192

Datenzugriffsmethoden . . . . . . . . . 192 Datenzugriff über Indexsuchen . . . . . . 193 Arten des Indexzugriffs . . . . . . . . . 196 Indexzugriff und Clusterverhältnisse . . . . 199 Vergleichselementtypologie und Zugriffspläne 200 Auswirkungen des Sortierens und Gruppierens 202 Berechnen von Gruppierungsschlüsselkardina-litäten mit Index- und Spaltengruppenstatistik-daten . . . . . . . . . . . . . . . 204 Verbessern der Leistung durch Binden mit REOPT . . . . . . . . . . . . . . 205 Replizierte MQTs in partitionierten Datenbank-umgebungen . . . . . . . . . . . . 206 Optimierungsstrategien für partitionsinterne Parallelität . . . . . . . . . . . . . 208

Statistische Sichten . . . . . . . . . . . 211

Statistische Sichten . . . . . . . . . . 211 Verwenden statistischer Sichten . . . . . . 212 Anzeigen der für die Optimierung relevanten Statistikdaten . . . . . . . . . . . . 213 Szenario: Verbessern der Kardinalitätsschätzung mit Hilfe statistischer Sichten . . . . . . . 214

Joins . . . . . . . . . . . . . . . . 220 Joins . . . . . . . . . . . . . . . 220 Joinmethoden . . . . . . . . . . . . 221 Strategien zur Auswahl optimaler Joins . . . . 224 Spaltenkorrelation für mehrere Vergleichs-elemente . . . . . . . . . . . . . . 227 Joinstrategien in partitionierten Datenbanken 229 Joinmethoden in Umgebungen mit partitionier-ten Datenbanken . . . . . . . . . . . 231

Umschreiben von Abfragen . . . . . . . . . 236 Methoden zum Umschreiben von Abfragen und Beispiele . . . . . . . . . . . . . . 236 Beispiel für das Umschreiben durch den Compi-ler: Zusammenfügen von Sichten . . . . . . 238 Beispiel für das Umschreiben durch den Compi-ler: Eliminierung von DISTINCT . . . . . . 240 Beispiel für das Umschreiben durch den Compi-ler: implizierte Vergleichselemente . . . . . 242

Zugriffspläne in föderierten Abfragen . . . . . 244 Pushdown-Analyse für föderierte Datenbanken 244 Richtlinien zur Analyse, wo eine Abfrage für föderierte Datenbanken ausgewertet wird . . . 249 Globale Analyse von Abfragen auf föderierte Datenbanken . . . . . . . . . . . . 250 Generierung von fernem SQL und globale Opti-mierung in föderierten Datenbanken . . . . 253 Serveroptionen mit Einfluss auf föderierte Datenbanken . . . . . . . . . . . . 256

Kapitel 10. Analysieren von Zugriffs-plänen mit der EXPLAIN-Funktion . . 257 EXPLAIN-Einrichtung . . . . . . . . . . 257 dynexpln . . . . . . . . . . . . . . . 258 EXPLAIN-Tools . . . . . . . . . . . . 258 Richtlinien zur Verwendung von EXPLAIN-Infor-mationen . . . . . . . . . . . . . . . 260 Die EXPLAIN-Tabellen und die Organisation von EXPLAIN-Informationen . . . . . . . . . 261 EXPLAIN-Informationen für Datenobjekte . . . . 264 EXPLAIN-Informationen für Instanzen . . . . . 264 EXPLAIN-Informationen für Datenoperatoren . . 268 Richtlinien zur Erfassung von EXPLAIN-Informati-onen . . . . . . . . . . . . . . . . 269 Richtlinien zur Analyse von EXPLAIN-Informatio-nen . . . . . . . . . . . . . . . . . 271 Hinweise zur Verwendung von dynexpln . . . . 273

Kapitel 11. Konfigurieren von DB2-Instanzen und -Datenbanken . . . . . 275 Konfigurationsparameter . . . . . . . . . 275 Konfigurieren von DB2 mit Konfigurations-parametern . . . . . . . . . . . . . . 277 Dynamische Konfiguration von Parametern . . . 281

iv Systemverwaltung: Optimierung

Generieren von Empfehlungen für die Datenbank-konfiguration . . . . . . . . . . . . . 284 Konfigurieren der DB2-Hauptspeicherzuordnung 286

Hauptspeicherzuordnung in DB2 . . . . . . 286 Gemeinsam genutzter Speicher des Datenbank-managers . . . . . . . . . . . . . . 289 Der FCM-Pufferpool und Speicheran-forderungen . . . . . . . . . . . . . 291 Optimieren der Parameter für die Hauptspeicherzuordnung . . . . . . . . 293

Automatisches Konfigurieren mit dem Speicher mit automatischer Leistungsoptimierung . . . . . 295

Speicher mit automatischer Leistungs-optimierung . . . . . . . . . . . . . 295 Aktivieren des Speichers mit automatischer Leistungsoptimierung . . . . . . . . . 296 Inaktivieren des Speichers mit automatischer Leistungsoptimierung . . . . . . . . . 297 Ermitteln der Speicherkonsumenten mit akti-vierter automatischer Leistungsoptimierung . . 299 Speicher mit automatischer Leistungs-optimierung in Umgebungen mit partitionierten Datenbanken . . . . . . . . . . . . 300 Verwenden von Speicher mit automatischer Leistungsoptimierung in Umgebungen mit par-titionierten Datenbanken . . . . . . . . 302 Betriebsmerkmale und Einschränkungen von Speicher mit automatischer Leistungs-optimierung . . . . . . . . . . . . . 304 Konfigurationsparameter - Übersicht . . . . 305

Teil 3. Erhalten der Systemleistung 317

Kapitel 12. Erfassen von Statistik-daten . . . . . . . . . . . . . . . 319 Katalogstatistiken . . . . . . . . . . . . 319 Erfassen von Katalogstatistiken . . . . . . . 322 Erfassen von Verteilungsstatistiken für bestimmte Spalten . . . . . . . . . . . . . . . 324 Erfassen von Indexstatistiken . . . . . . . . 325 Richtlinien für die Erfassung und Aktualisierung von Statistiken . . . . . . . . . . . . . 326 Erfassen von Statistiken an einer Stichprobe der Tabellendaten . . . . . . . . . . . . . 329 Katalogstatistiktabellen . . . . . . . . . . 330 Verteilungsstatistiken . . . . . . . . . . . 335 Detaillierte Indexstatistiken . . . . . . . . . 338 Unterelementstatistiken . . . . . . . . . . 340 Statistiken für benutzerdefinierte Funktionen . . . 341 Verwendung der Verteilungsstatistiken durch das Optimierungsprogramm . . . . . . . . . . 343 Erweiterte Beispiele zur Verwendung von Verteilungsstatistiken . . . . . . . . . . . 345 Katalogstatistiken zu Modellierung und Fallstudien 349 Statistiken zur Modellierung von Produktions-datenbanken . . . . . . . . . . . . . . 350 Manuelles Aktualisieren von Katalogstatistiken . . 353

Allgemeine Regeln zur manuellen Aktualisie-rung von Katalogstatistiken . . . . . . . 353

Regeln zur manuellen Aktualisierung von Spaltenstatistiken . . . . . . . . . . . 354 Regeln zur manuellen Aktualisierung von Verteilungsstatistiken . . . . . . . . . . 355 Regeln zur manuellen Aktualisierung von Tabel-len- und Kurznamenstatistiken . . . . . . 356 Regeln zur manuellen Aktualisierung von Indexstatistiken . . . . . . . . . . . . 357

Automatische Statistikerfassung . . . . . . . 358 Automatische Statistikerfassung . . . . . . 358 Verwenden der automatischen Statistikerfassung 359 Automatische Statistikprofilerstellung . . . . 360 Erfassen von Statistiken mit einem Statistikprofil 361

Kapitel 13. Tabellen- und Index-verwaltung . . . . . . . . . . . . 365 Tabellenreorganisation . . . . . . . . . . 365 Indexreorganisation . . . . . . . . . . . 366 Feststellen des Zeitpunkts zur Reorganisation von Tabellen . . . . . . . . . . . . . . . 369 Auswählen einer Methode zur Tabellen-reorganisation . . . . . . . . . . . . . 372 Aktivieren der automatischen Reorganisation von Tabellen und Indizes . . . . . . . . . . . 374 Verwenden von Snapshot Monitor-Daten zum Überwachen der Reorganisation einer partitionier-ten Tabelle . . . . . . . . . . . . . . 375 Tabellen- und Indexverwaltung für Standard-tabellen . . . . . . . . . . . . . . . 384 Tabellen- und Indexverwaltung für MDC-Tabellen 387 Indexbereinigung und Indexpflege . . . . . . 390 Online-Indexdefragmentierung . . . . . . . 392

Kapitel 14. Umverteilung von Daten 395 Umverteilung von Daten . . . . . . . . . 395 Feststellen der Erforderlichkeit einer Datenum-verteilung . . . . . . . . . . . . . . 397 Umverteilen von Daten auf Datenbankpartitionen 397 Protokollspeicheranforderungen für die Datenum-verteilung . . . . . . . . . . . . . . 400 Umverteilen von Daten unter Verwendung schritt-weiser Umverteilungsprozeduren . . . . . . . 401

Verwendungsbeispiel . . . . . . . . . . 401 Fehlerbehebung nach einer Umverteilung . . . . 403

Kapitel 15. Informationen des Datenbanksystemmonitors . . . . . 405

Kapitel 16. Messen der System-leistung mit Vergleichstests . . . . . 409 Durchführen von Vergleichstests . . . . . . . 409 Vorbereiten von Vergleichstests . . . . . . . 410 Erstellung von Vergleichstests . . . . . . . . 412 Beispiele für db2batch-Tests . . . . . . . . 414 Ausführung von Vergleichstests . . . . . . . 424 Vergleichstestanalyse - Beispiel . . . . . . . 426

Teil 4. Anhänge und Schlussteil 429

Inhaltsverzeichnis v

Anhang A. DB2-Konfigurations-parameter . . . . . . . . . . . . . 431 Einzelheiten zu Parametern nach Funktion . . . 431 Kapazitätsverwaltung . . . . . . . . . . 432

Gemeinsam benutzter Datenbankspeicher . . . 432 Gemeinsamer Anwendungsspeicher . . . . . 448 Privater Agentenspeicher . . . . . . . . 452 Agenten-/Anwendungskommunikationsspeicher . . . . 461 Speicher der Datenbankmanagerinstanz . . . 466 Sperren . . . . . . . . . . . . . . 471 Ein-/Ausgabe und Speicher . . . . . . . 475 Agenten . . . . . . . . . . . . . . 482 Gespeicherte Prozeduren und benutzerdefinierte Funktionen . . . . . . . . . . . . . 493

Protokollieren und Recovery . . . . . . . . 496 Datenbankprotokolldateien . . . . . . . . 497 Datenbankprotokollierung . . . . . . . . 507 Recovery . . . . . . . . . . . . . . 517 DUOW-Recovery . . . . . . . . . . . 529

Datenbankverwaltung . . . . . . . . . . 534 Query Enabler . . . . . . . . . . . . 534 Attribute . . . . . . . . . . . . . . 534 Status . . . . . . . . . . . . . . . 538 Compilereinstellungen . . . . . . . . . 540 Automatische Verwaltung . . . . . . . . 547

Kommunikation . . . . . . . . . . . . 550 Konfiguration der Kommunikationsprotokolle 550 DB2 Discovery . . . . . . . . . . . . 552

Umgebung mit partitionierter Datenbank . . . . 553 Kommunikation . . . . . . . . . . . 554 Parallelverarbeitung . . . . . . . . . . 559

Instanzverwaltung . . . . . . . . . . . . 561 Diagnose . . . . . . . . . . . . . . 561 Parameter für den Datenbanksystemmonitor 564 Systemverwaltung . . . . . . . . . . . 566 Instanzverwaltung . . . . . . . . . . . 574

DB2-Verwaltungsserver . . . . . . . . . . 589 authentication - DAS-Authentifizierungstyp . . 589 contact_host - Speicherposition der Liste mit Ansprechpartnern . . . . . . . . . . 590 das_codepage - DAS-Codepage . . . . . . 591 das_territory - DAS-Gebiet . . . . . . . . 591 dasadm_group - DASADM-Gruppenname . . 591 db2system - Name des DB2-Serversystems . . 592 discover - DAS-Discovery-Modus . . . . . 593 exec_exp_task - Verfallene Tasks ausführen . . 593 jdk_64_path - Installationspfad für Software Developer’s Kit (64 Bit) für Java auf DAS . . . 594 jdk_path - Installationspfad für Software Developer’s Kit für Java auf DAS . . . . . 595 sched_enable - Schedulermodus . . . . . . 595 sched_userid - Benutzer-ID für Scheduler . . . 596 smtp_server - SMTP-Server . . . . . . . 596 toolscat_db - Toolskatalogdatenbank . . . . 597 toolscat_inst - Instanz der Toolskatalogdaten-bank . . . . . . . . . . . . . . . 597 toolscat_schema - Schema der Toolskatalogda-tenbank . . . . . . . . . . . . . . 598

Anhang B. Registrierdatenbank- und Umgebungsvariablen von DB2 . . . . 601 Registrierdatenbank- und Umgebungsvariablen von DB2 . . . . . . . . . . . . . . . 601 Registrierdatenbank- und Umgebungsvariablen nach Kategorie . . . . . . . . . . . . . 602

Allgemeine Registrierdatenbankvariablen . . . 602 Systemumgebungsvariablen . . . . . . . 607 Kommunikationsvariablen . . . . . . . . 615 Befehlszeilenvariablen . . . . . . . . . 621 MPP-Konfigurationsvariablen . . . . . . . 623 Abfragecompilervariablen . . . . . . . . 624 Leistungsvariablen . . . . . . . . . . 630 Data Links-Variablen . . . . . . . . . . 647 Verschiedene Variablen . . . . . . . . . 649

Anhang C. EXPLAIN-Tabellen . . . . 663 EXPLAIN-Tabellen . . . . . . . . . . . 663 Die Tabelle EXPLAIN_ARGUMENT . . . . . . 665 Tabelle EXPLAIN_DIAGNOSTIC . . . . . . . 670 Tabelle EXPLAIN_DIAGNOSTIC_DATA . . . . 671 Die Tabelle EXPLAIN_INSTANCE . . . . . . 673 Die Tabelle EXPLAIN_OBJECT . . . . . . . 676 Die Tabelle EXPLAIN_OPERATOR . . . . . . 679 Die Tabelle EXPLAIN_PREDICATE . . . . . . 681 Die Tabelle EXPLAIN_STATEMENT . . . . . . 684 Die Tabelle EXPLAIN_STREAM . . . . . . . 686 Die Tabelle ADVISE_INDEX . . . . . . . . 688 Die Tabelle ADVISE_INSTANCE . . . . . . . 692 Die Tabelle ADVISE_MQT . . . . . . . . . 693 Die Tabelle ADVISE_PARTITION . . . . . . . 695 Die Tabelle ADVISE_TABLE . . . . . . . . 697 Die Tabelle ADVISE_WORKLOAD . . . . . . 698

Anhang D. EXPLAIN-Operatoren . . . 699 Operator CMPEXP . . . . . . . . . . . 699 Operator DELETE . . . . . . . . . . . . 699 Operator EISCAN . . . . . . . . . . . . 699 Operator FETCH . . . . . . . . . . . . 700 Operator FILTER . . . . . . . . . . . . 700 Operator GENROW . . . . . . . . . . . 701 Operator GRPBY . . . . . . . . . . . . 701 Operator HSJOIN . . . . . . . . . . . . 701 Operator INSERT . . . . . . . . . . . . 702 Operator IXAND . . . . . . . . . . . . 702 Operator IXSCAN . . . . . . . . . . . . 703 Operator MSJOIN . . . . . . . . . . . . 704 Operator NLJOIN . . . . . . . . . . . . 704 Operator PIPE . . . . . . . . . . . . . 705 Operator RETURN . . . . . . . . . . . 705 Operator RIDSCN . . . . . . . . . . . . 706 Operator RPD . . . . . . . . . . . . . 706 Operator SHIP . . . . . . . . . . . . . 706 Operator SORT . . . . . . . . . . . . . 707 Operator TBSCAN . . . . . . . . . . . 707 Operator TEMP . . . . . . . . . . . . 708 Operator TQUEUE . . . . . . . . . . . 709 Operator UNION . . . . . . . . . . . . 709 Operator UNIQUE . . . . . . . . . . . 709 Operator UPDATE . . . . . . . . . . . 710

vi Systemverwaltung: Optimierung

Anhang E. SQL- und XQuery-EXP-LAIN-Tools . . . . . . . . . . . . 711 SQL- und XQuery-EXPLAIN-Tools . . . . . . 711 db2expln . . . . . . . . . . . . . . . 712

db2expln - SQL- und XQuery-EXPLAIN . . . 712 Hinweise zur Verwendung von db2expln . . . 718

Informationen der EXPLAIN-Ausgabe . . . . . 719 Beschreibung der Ausgabe von db2expln und dynexpln . . . . . . . . . . . . . . 719 Tabellenzugriffsinformationen . . . . . . . 720 Informationen zu temporären Tabellen . . . . 725 Joininformationen . . . . . . . . . . . 728 Datenstrominformationen . . . . . . . . 730 Informationen zu INSERT, UPDATE und DELETE . . . . . . . . . . . . . . 731 Informationen zur Vorbereitung von Block- und Zeilen-IDs . . . . . . . . . . . . . 731 Informationen zu Spaltenberechnungen (Aggre-gation) . . . . . . . . . . . . . . 732 Informationen zur Parallelverarbeitung . . . . 733 Informationen zu Abfragen auf föderierte Datenbanken . . . . . . . . . . . . 736 Verschiedene EXPLAIN-Informationen . . . . 737

Beispiele für die Ausgabe von db2expln und dyn-expln . . . . . . . . . . . . . . . . 739

Beispiele für die Ausgabe von db2expln und dynexpln . . . . . . . . . . . . . . 739 Beispiel 1: keine Parallelität . . . . . . . . 739 Beispiel 2: Zugriffsplan für Einzelpartition mit partitionsinterner Parallelität . . . . . . . 741 Beispiel 3: Zugriffsplan für mehrere Partitionen mit partitionsübergreifender Parallelität . . . 743 Beispiel 4: Zugriffsplan für mehrere Partitionen mit partitionsübergreifender und partitions-interner Parallelität . . . . . . . . . . 745 Beispiel 5: Zugriffsplan für eine föderierte Datenbank . . . . . . . . . . . . . 747 Beispiel 6: Operatoren XANDOR und XISCAN 749 Beispiel 11: Operator XSCAN . . . . . . . 751 Beispiel 8: Operator XISCAN . . . . . . . 752

Anhang F. db2exfmt - EXPLAIN-Tabel-len formatieren . . . . . . . . . . 755

Anhang G. Verwenden des Windows-Systemmonitors . . . . . . . . . . 757 Windows-Systemmonitor - Einführung . . . . . 757 Registrieren von DB2 im Windows-Systemmonitor 757 Aktivieren des Fernzugriffs auf DB2-Leistungs-informationen . . . . . . . . . . . . . 758 Anzeigen von Leistungswerten der DB2-Datenbank und von DB2 Connect . . . . . . . . . . 759 Windows-Leistungsobjekte . . . . . . . . . 759 Zugreifen auf Leistungsinformationen ferner DB2-Datenbanken . . . . . . . . . . . . . 760 Zurücksetzen von DB2-Leistungswerten . . . . 761

Anhang H. Technische Informationen zu DB2-Datenbanken . . . . . . . . 763 Übersicht über technische Informationen zu DB2 763

Feedback zur Dokumentation . . . . . . . 763 Bibliothek mit technischen Informationen zu DB2 im PDF-Format . . . . . . . . . . . . . 764 Bestellen gedruckter DB2-Bücher . . . . . . . 767 Anzeigen der Hilfe für den SQL-Status über den Befehlszeilenprozessor . . . . . . . . . . 768 Zugriff auf verschiedene Versionen der DB2-Informationszentrale . . . . . . . . . . . 768 Anzeigen von Themen in der gewünschten Sprache in der DB2-Informationszentrale . . . . . . . 768 Aktualisieren der auf Ihrem Computer oder Intra-net-Server installierten DB2-Informationszentrale . 769 DB2-Lernprogramme . . . . . . . . . . . 772 Informationen zur Fehlerbehebung in DB2 . . . 772 Bedingungen . . . . . . . . . . . . . 773

Anhang I. Bemerkungen . . . . . . . 775 Marken . . . . . . . . . . . . . . . 777

Index . . . . . . . . . . . . . . . 779

Kontaktaufnahme mit IBM . . . . . . 797

Inhaltsverzeichnis vii

viii Systemverwaltung: Optimierung

Teil 1. Einführung zur Leistung

1

2 Systemverwaltung: Optimierung

Kapitel 1. Leistungsprinzipien

Elemente der Leistung

Als Leistung wird die Art und Weise bezeichnet, wie ein Computersystem unter einer bestimmten Auslastung arbeitet. Die Leistung wird an der Antwortzeit, am Durchsatz und an der Verfügbarkeit gemessen. Außerdem wird die Leistung von folgenden Faktoren beeinflusst: v Von den im System verfügbaren Ressourcen v Von der Auslastung und vom Ausmaß der gemeinsamen Nutzung dieser Res-

sourcen

Im Allgemeinen optimieren Sie Ihr System, um das Kosten-Nutzen-Verhältnis zu verbessern. Die folgenden speziellen Optimierungsziele können dabei verfolgt wer-den: v Verarbeiten einer größeren oder anspruchsvolleren Arbeitsbelastung ohne stei-

gende Verarbeitungskosten Zum Beispiel zur Steigerung der Auslastung ohne neue Hardware erwerben oder mehr Prozessorzeit in Kauf nehmen zu müssen.

v Erreichen schnellerer Systemantwortzeiten bzw. eines höheren Durchsatzes ohne Steigerung der Verarbeitungskosten

v Reduzieren von Verarbeitungskosten ohne negative Auswirkungen für Ihre Benutzer

Die Übersetzung von Leistung vom technischen Aspekt in den wirtschaftlichen Aspekt ist schwierig. Eine Leistungsoptimierung kostet natürlich Geld in Form von Zeitaufwand für Personal und Prozessorzeit, so dass vor einem Optimierungs-projekt die Kosten gegen die möglichen Vorteilsgewinne abgewogen werden müs-sen. Einige dieser Vorteile sind konkret messbar: v Effizientere Ressourcennutzung v Die Möglichkeit, weitere Benutzer dem System hinzuzufügen

Andere Vorteile, wie größere Zufriedenheit seitens der Benutzer aufgrund schnelle-rer Antwortzeiten, sind weniger fassbar. Jedoch sollten alle diese Vorteile in die Überlegungen mit einbezogen werden.

Zugehörige Konzepte:

v „Einstiegstipps für die Leistungsoptimierung” auf Seite 7 v „Richtlinien zur Leistungsoptimierung” auf Seite 3

Zugehörige Tasks:

v „Entwickeln eines Leistungsverbesserungsprozesses” auf Seite 5

Richtlinien zur Leistungsoptimierung

Die folgenden Richtlinien sind als Hilfe für die Entwicklung eines allgemeinen Ansatzes zur Leistungsoptimierung zu verstehen.

3

Behalten Sie das Gesetz der abnehmenden Ertragsgewinne im Hinterkopf: Die größten Leistungsvorteile werden in der Regel durch die ersten Maßnahmen erzielt. Weitere Änderungen erbringen im Allgemeinen immer kleinere Vorteile und erfordern immer höheren Aufwand.

Optimieren Sie nicht nur des Optimierens wegen: Optimieren Sie, um erkannten Engpässen abzuhelfen. Das Optimieren von Ressourcen, die nicht den Hauptgrund für Leistungsprobleme darstellen, hat wenig oder gar keine Wirkung auf Antwort-zeiten, solange Sie nicht die wichtigeren Probleme behoben haben, und kann tat-sächlich eine nachfolgende Optimierungsarbeit erschweren. Wenn es ein bedeuten-des Verbesserungspotenzial gibt, liegt es in der Verbesserung der Leistung von Ressourcen, die wichtige Faktoren in der Antwortzeit bilden.

Betrachten Sie das gesamte System: Ein Parameter bzw. System lässt sich nicht isoliert optimieren. Bevor Sie Anpassungen vornehmen, überlegen Sie, wie sich diese Anpassungen auf das System als Ganzes auswirken werden.

Ändern Sie jeweils nur einen Parameter gleichzeitig: Ändern Sie nicht mehrere Parameter zur Leistungsoptimierung in einem Schritt. Selbst wenn Sie sich sicher sind, dass alle Änderungen vorteilhaft sind, haben Sie hinterher keine Möglichkeit, den Beitrag jeder Änderung zu bewerten. Darüber hinaus können Sie bei gleichzei-tiger Änderung mehrerer Parameter den erzielten Vorteil nicht effektiv den mögli-cherweise in Kauf genommenen Einbußen gegenüberstellen. Von jeder Anpassung eines Parameters zur Optimierung eines Bereichs ist fast immer auch mindestens ein anderer Bereich betroffen, der vorher vielleicht nicht bedacht wurde. Wenn Sie jeweils nur einen Parameter ändern, haben Sie einen Vergleichspunkt und können feststellen, ob die Änderung die gewünschte Wirkung hat.

Führen Sie Messungen und Neukonfigurierungen nach Ebenen durch: Aus den-selben Gründen, aus denen nur jeweils ein Parameter geändert werden soll, emp-fiehlt sich auch, die einzelnen Ebenen des Systems getrennt zu optimieren. Die fol-gende Liste von Ebenen innerhalb eines Systems kann Ihnen dabei als Richtlinie dienen: v Hardware v Betriebssystem v Anwendungsserver und -requester v Datenbankmanager v SQL- und XQuery-Anweisungen v Anwendungsprogramme

Prüfen Sie auf Hardware- und Softwareprobleme: Einige Leistungsprobleme kön-nen vielleicht durch Wartung der Hardware oder Korrektur der Software oder durch beides behoben werden. Verwenden Sie nicht zu viel Zeit auf die Überwa-chung und Optimierung des Systems, wenn eine Hardwarewartung oder Software-korrektur dies unnötig machen könnte.

Ermitteln Sie die Ursache eines Problems, bevor Sie Ihre Hardware aufrüsten: Auch wenn es so aussieht, als könnten zusätzliche Speicher- und Prozessor-kapazitäten die Leistung sofort verbessern, sollten Sie sich die Zeit nehmen, die Engpässe zu lokalisieren und zu verstehen. Sie könnten ansonsten Geld für zusätz-lichen Plattenspeicher ausgeben und anschließend feststellen, dass Sie nicht über die Prozessorkapazitäten oder die Kanäle verfügen, um den Speicher vorteilhaft zu nutzen.


Bereiten Sie Zurücksetzungsprozeduren vor, bevor Sie mit der Optimierung beginnen: Wie bereits früher erwähnt, können einige Optimierungsmaßnahmen zu unerwarteten Leistungsergebnissen führen. Wenn sich daraus eine schlechtere Leis-tung ergibt, sollten die Maßnahmen rückgängig gemacht und alternative Optimie- rungsmaßnahmen versucht werden. Wenn der vorige Stand in einer Weise gesi-chert wurde, dass er einfach wiederhergestellt werden kann, ist die Rücknahme der nicht korrekten Informationen wesentlich einfacher.


v „Elemente der Leistung” auf Seite 3 v „Einstiegstipps für die Leistungsoptimierung” auf Seite 7

Zugehörige Tasks:


Entwickeln eines Leistungsverbesserungsprozesses

Ein Leistungsverbesserungsprozess ist ein iteratives und langfristig angelegtes Ver-fahren zur Überwachung und Optimierung von Leistungsbereichen. Abhängig von den Überwachungsergebnissen passen Sie und Ihr Optimierungsteam die Konfi-guration des Datenbankservers an und nehmen Änderungen an den Anwendun-gen vor, die den Datenbankserver verwenden.

Gehen Sie bei der Leistungsüberwachung und den Optimierungsentscheidungen von Ihren Kenntnissen über die Arten von Anwendungen, die mit den Daten arbei-ten, sowie von den Datenzugriffsmustern aus. Verschiedene Arten von Anwendun-gen haben unterschiedliche Leistungsanforderungen.

Betrachten Sie die folgende Verfahrensstruktur für den Leistungsverbesserungs-prozess als Richtlinie.

Vorgehensweise:

Gehen Sie zur Entwicklung eines Leistungsverbesserungsprozesses wie folgt vor: 1. Definieren Sie Leistungsziele. 2. Legen Sie Leistungsindikatoren für die wichtigsten Leistungsprobleme im Sys-

tem fest. 3. Entwickeln Sie einen Leistungsüberwachungsplan und führen Sie ihn aus. 4. Analysieren Sie die Ergebnisse der Überwachung fortlaufend, um zu ermitteln,

welche Ressourcen optimiert werden müssen. 5. Nehmen Sie jeweils nur eine Anpassung vor.

Selbst wenn Sie davon überzeugt sind, dass mehr als eine Ressource eine Opti-mierung erfordert, oder wenn mehrere Optimierungsoptionen für die zu opti-mierende Ressource möglich sind, sollten Sie nur eine Änderung pro Optimie- rungsschritt vornehmen, so dass Sie sicherstellen können, dass Ihre Optimie- rungsmaßnahmen die gewünschte Wirkung erzielen. An einem bestimmten Punkt lässt sich keine weitere Verbesserung der Leistung durch Optimieren des Datenbankservers und der Anwendungen mehr realisieren. Wenn dieser Punkt erreicht ist, bleibt Ihnen nur die Möglichkeit, Ihre Hardware aufzurüsten.

Zur tatsächlichen Leistungsoptimierung müssen Kompromisslösungen für verschie-dene Systemressourcen gefunden werden. Zum Beispiel könnten Sie zur Verbesse-

Kapitel 1. Leistungsprinzipien 5

rung der E/A-Leistung die Pufferpools vergrößern, jedoch benötigen größere Pufferpools mehr Speicher, was wiederum andere Bereiche der Leistung beein-trächtigen könnte.


v „Elemente der Leistung” auf Seite 3 v „Leistungsinformationen, die Benutzer liefern können” auf Seite 6 v „Einstiegstipps für die Leistungsoptimierung” auf Seite 7 v „Richtlinien zur Leistungsoptimierung” auf Seite 3 v „Grenzen der Leistungsoptimierung” auf Seite 6

Grenzen der Leistungsoptimierung

Eine Optimierung kann die Effizienz eines Systems nur um einen bestimmten Betrag ändern. Überlegen Sie, wie viel Zeit und Geld Sie in die Optimierung der Systemleistung investieren sollten, und wie viel Aufwand an Zeit und Geld den Benutzern des Systems tatsächlich hilft.

Zum Beispiel kann eine Optimierung häufig die Leistung verbessern, wenn das System auf einen Leistungsengpass stößt. Wenn Ihr System nahe an den Leistungs-grenzen arbeitet und sich die Anzahl von Benutzern am System um zehn Prozent erhöht, verlängern sich die Antwortzeiten wahrscheinlich um wesentlich mehr als zehn Prozent. In dieser Situation müssen Sie feststellen, wie Sie dieser Beeinträchti-gung der Leistung durch Optimieren des Systems entgegenwirken können.

Es gibt allerdings einen Punkt, ab dem das Optimieren keine weiteren Leistungs-gewinne erzielen kann. An diesem Punkt müssen Sie Ihre Ziele und Erwartungen innerhalb der Grenzen Ihrer Umgebung überprüfen. Wenn Sie bedeutsame Leistungsverbesserungen erreichen wollen, müssen Sie vielleicht mehr Platten-speicher, eine schnellere CPU, zusätzliche CPUs, mehr Arbeitsspeicher, schnellere Kommunikationsverbindungen oder eine Kombination aus diesen Möglichkeiten hinzufügen.


v „Verwalten der Datenbankserverkapazität” in Systemverwaltung: Implementierung

Zugehörige Tasks:


Leistungsinformationen, die Benutzer liefern können

Die ersten Anzeichen dafür, dass Ihr System optimiert werden müsste, könnten Klagen von Benutzern sein. Wenn Sie nicht genügend Zeit zur Definition von Leistungszielen sowie zur Überwachung und Optimierung in umfassender Weise haben, können Sie sich mit der Leistung auseinander setzen, indem Sie Ihren Benutzern zuhören. In der Regel können Sie bestimmen, wo mit der Untersuchung eines Problems zu beginnen ist, indem Sie einige einfache Fragen stellen. Sie könnten Ihre Benutzer zum Beispiel Folgendes fragen: v Was meinen sie mit „langsamer Reaktion”? Heißt dies, um zehn Prozent langsa-

mer, als Sie erwarten, oder um das Zigfache langsamer? v Wann haben Sie das Problem bemerkt? Tritt es erst seit kurzem auf oder war es

immer da?


v Haben andere Benutzer das gleiche Problem? Handelt es sich bei diesen Benut-zern um einen oder zwei Einzelpersonen oder um eine ganze Gruppe?

v Wenn eine Gruppe von Benutzern die gleichen Probleme hat, sind sie mit dem-selben lokalen Netzwerk (LAN) verbunden?

v Scheinen die Probleme mit einem bestimmten Transaktions- oder Anwendungs-programm zusammenzuhängen?

v Erkennen Sie ein Muster im Auftreten des Problems? Zum Beispiel: Tritt dieses Problem zu einer bestimmten Tageszeit, z. B. in der Mittagspause, auf oder ist es mehr oder weniger permanent spürbar?


v „Richtlinien zur Leistungsoptimierung” auf Seite 3

Zugehörige Tasks:


Einstiegstipps für die Leistungsoptimierung

Wenn Sie eine neue Instanz von DB2 starten, ziehen Sie die folgenden Empfehlun-gen für eine Basiskonfiguration in Betracht: v Verwenden Sie den Konfigurationsadvisor in der Steuerzentrale, um Empfehlun-

gen für sinnvolle Ausgangsstandardwerte für Ihr System zu erhalten. Die Stan-dardwerte, die für DB2 voreingestellt sind, sollten für Ihre eindeutige Hardware-umgebung optimiert werden. Stellen Sie Informationen zur Hardware an Ihrem Standort zusammen, so dass Sie auf die Fragen des Assistenten antworten können. Sie können die vorgeschla-genen Einstellungen für Konfigurationsparameter sofort anwenden oder den Assistenten ein Script erstellen lassen, das auf Ihren Antworten beruht, und die-ses Script später ausführen. Dieses Script stellt auch eine Liste der am häufigsten optimierten Parameter zur späteren Referenz bereit.

v Verwenden Sie andere Assistenten in der Steuerzentrale und in „Clientkonfigura-tion - Unterstützung” für leistungsrelevante Verwaltungsaufgaben. Solche Aufga-ben sind in der Regel diejenigen, bei denen Sie beträchtliche Leistungsverbes- serungen mit geringem Zeitaufwand und wenig Mühe erreichen. Andere Assistenten können Ihnen helfen, die Leistung einzelner Tabellen und des allgemeinen Datenzugriffs zu verbessern. Solche Assistenten sind: Daten-bank erstellen, Tabelle erstellen, Index erstellen und Aktualisierung auf mehreren Systemen konfigurieren. Die Diagnosezentrale stellt einen Satz von Überwa-chungs- und Optimierungstools bereit.

v Verwenden Sie das Designadvisor-Tool in der Steuerzentrale oder über den Befehl db2advis, um zu ermitteln, welche Indizes, MQTs (MQT - Materialized Query Table, gespeicherten Abfragetabellen), MDC-Tabellen und Datenbank-partitionen die Abfrageleistung verbessern würden.

v Verwenden Sie den Befehl ACTIVATE DATABASE, um Datenbanken zu starten. In einer partitionierten Datenbank aktiviert dieser Befehl die Datenbank in allen Datenbankpartitionen und vermeidet die Startzeit, die zur Initialisierung der Datenbank erforderlich ist, wenn die erste Anwendung eine Verbindung her-stellt.

Anmerkung: Wenn Sie den Befehl ACTIVATE DATABASE verwenden, müssen Sie die Datenbank mit dem Befehl DEACTIVATE DATABASE her-

Kapitel 1. Leistungsprinzipien 7

unterfahren. Die letzte Anwendung, die ihre Verbindung zur Datenbank trennt, bewirkt nicht, dass die Datenbank herunterge-fahren wird.

v Lesen Sie die Übersichtstabellen, die jeden für den Datenbankmanager und für jede Datenbank verfügbaren Konfigurationsparameter auflisten und kurz beschreiben. Diese Übersichtstabellen enthalten eine Spalte, die angibt, ob eine Optimierung des jeweiligen Parameters einen hohen, mittleren, niedrigen oder keinen Einfluss auf die Leistung in positivem oder negativem Sinn hat. Verwenden Sie diese Tabellen, um die Parameter zu ermitteln, die Sie optimieren können, um die größten Leistungsverbesserungen zu erzielen.


v Kapitel 15, „Informationen des Datenbanksystemmonitors”, auf Seite 405

Zugehörige Referenzen:

v „Konfigurationsparameter - Übersicht” auf Seite 305

Leistungsfaktoren für Plattenspeichern

Die Hardware, aus der sich Ihr System zusammensetzt, kann die Leistung Ihres Systems beeinflussen. Als Beispiel für den Einfluss, den die Hardware auf die Leis-tung hat, werden im Folgenden einige der Auswirkungen betrachtet, die mit dem Plattenspeicher zusammenhängen.

Vier Aspekte der Plattenspeicherverwaltung können sich auf die Leistung auswir-ken: v Speichereinteilung

Wie Sie eine begrenzte Speichergröße zwischen Indizes und Daten sowie unter Tabellenbereichen aufteilen, bestimmt in hohem Maße, welche Leistung die ein-zelnen Komponenten in verschiedenen Situationen erreichen.

v Speicherverschwendung Verschwendeter Speicher wirkt sich vielleicht als solcher nicht auf die Leistung des Systems aus, das ihn besitzt, aber er ist eine Ressource, die zur Verbesserung der Leistung an anderer Stelle genutzt werden könnte.

v Verteilung der Platten-E/A Wie ausgewogen Sie den Bedarf an Platten-E/A auf mehrere Plattenspeicher-einheiten und Controller verteilen, kann sich auf die Geschwindigkeit auswir-ken, mit der der Datenbankmanager Informationen von Platten abrufen kann.

v Mangel an verfügbarem Speicher Bei Erreichen der Grenze des verfügbaren Speichers kann die allgemeine Leis-tung beeinträchtigt werden.


v „Überlegungen zu DMS-Einheiten” in Systemverwaltung: Konzept v „Vom Datenbankmanager verwaltete Tabellenbereiche” in Systemverwaltung: Kon-

zept

v „Vom Betriebssystem verwaltete Tabellenbereiche” in Systemverwaltung: Konzept v „Verzeichnisse und Dateien einer Datenbank” in Systemverwaltung: Konzept v „Tabellen- und Indexverwaltung für MDC-Tabellen” auf Seite 387 v „Tabellen- und Indexverwaltung für Standardtabellen” auf Seite 384


Teil 2. Leistungsprobleme beim Datenbankentwurf

9

Kapitel 2. Leistungsplanung beim Datenbankentwurf

Funktionen zur Leistungsverbesserung

MQTs (MQT - Materialized Query Table, gespeicherte Abfrage-tabelle)

MQTs bieten eine leistungsstarke Möglichkeit, die Antwortzeit für komplexe Abfra-gen zu verbessern, insbesondere für Abfragen, für die einige der folgenden Opera-tionen erforderlich sind: v Erstellen von Ergebnisdaten für eine oder mehrere Dimensionen v Joins und Spaltenberechnungen für eine Gruppe von Tabellen v Daten aus einer häufig genutzten Untergruppe von Daten, d. h. einer sofort für

Verarbeitungsoperationen bereiten horizontalen oder vertikalen Datenbank-partition

v Erneutes Partitionieren von Daten aus einer Tabelle bzw. einem Teil einer Tabelle in einer partitionierten Datenbankumgebung

In den SQL- und XQuery-Compiler sind Kenntnisse über MQTs integriert. Im Compiler werden in der Phase des Umschreibens von Abfragen und durch das Optimierungsprogramm Abfragen mit MQTs verglichen, um zu ermitteln, ob an Stelle einer Abfrage, die auf die Basistabellen zugreift, eine MQT verwendet wer-den kann. Wenn eine MQT verwendet wird, kann die EXPLAIN-Einrichtung Infor-mationen darüber bereitstellen, welche MQT ausgewählt wurde.

Da sich MQTs in vielerlei Hinsicht wie reguläre Tabellen verhalten, treffen die Richtlinien zum Optimieren des Datenzugriffs unter Verwendung von Tabellen-bereichsdefinitionen, durch Erstellen von Indizes und Ausführen des Dienst-programms RUNSTATS auch auf MQTs zu.

Zur Veranschaulichung der Leistungsfähigkeit von MQTs wird im folgenden Bei-spiel eine mehrdimensionale Analyseabfrage dargestellt und erläutert, wie sie von MQTs profitiert.

Nehmen Sie für dieses Beispiel ein Datenbankschema an, in dem ein Data Ware-house eine Reihe von Kunden und eine Reihe von Kreditkartenkonten enthält. Das Data Warehouse zeichnet die Gruppe der Transaktionen auf, die mit den Kreditkar-ten durchgeführt wurden. Alle Transaktionen enthalten eine Anzahl von Artikeln, die gemeinsam gekauft wurden. Dieses Schema wird als Mehrfachsternschema (Multi-Star) eingestuft, da zwei große Tabellen, von denen die eine Transaktions-elemente enthält und die andere die Kauftransaktionen identifiziert, zusammen das Zentrum des Sterns bilden.

Die drei hierarchischen Dimensionen, die eine Transaktion beschreiben, sind Pro-dukt, Standort und Zeit. Die Produkthierarchie wird in zwei normalisierten Tabel-len gespeichert, die die Produktgruppe und die Produktlinie darstellen. Die Standorthierarchie enthält Informationen zu Ort, Bundesland, sowie Land oder Region, die in einer einzigen denormalisierten Tabelle dargestellt werden. Die Zeit-hierarchie enthält Informationen zu Tag, Monat und Jahr und ist in einem einzigen Datumsfeld codiert. Die Datumsdimensionen werden aus dem Datumsfeld der

11

Transaktion unter Verwendung integrierter Funktionen extrahiert. Andere Tabellen in diesem Schema stellen die Kontoinformationen für Kunden und Kunden-informationen dar.

Eine MQT wird mit der Summe und der Anzahl der Verkäufe für jede Stufe der folgenden Hierarchien erstellt: v Produkt v Standort v Zeit bestehend aus Jahr, Monat, Tag

Viele Abfragen können aus diesen zusammengefassten Ergebnisdaten erfüllt wer-den. Das folgende Beispiel zeigt, wie eine gespeicherte Abfrage erstellt wird, die die Summe und die Anzahl der Verkäufe für die Dimensionen ’Produktgruppe’ (Product Group) und ’Produktlinie (Product Line), für die Dimensionen ’Ort’ (City), ’Bundesland’ (State) und ’Land’ (Country) und für die Dimension ’Zeit’ (Time) berechnet. Das Beispiel enthält auch einige weitere Spalten in der Klausel GROUP BY. CREATE TABLE dba.PG_SALESSUM

AS ( SELECT l.id AS prodline, pg.id AS pgroup,

loc.country, loc.state, loc.city, l.name AS linename, pg.name AS pgname,

YEAR(pdate) AS year, MONTH(pdate) AS month, t.status,

SUM(ti.amount) AS amount, COUNT(*) AS count

FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg,

cube.prodline AS l WHERE ti.transid = t.id

AND ti.pgid = pg.id AND pg.lineid = l.id

AND t.locid = loc.id AND YEAR(pdate) > 1990

GROUP BY l.id, pg.id, loc.country, loc.state, loc.city, year(pdate), month(pdate), t.status, l.name, pg.name

) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE dba.SALESCUBE;

Abfragen, die solche vorberechneten Summen nutzen können, sind unter anderem: v Verkaufsdaten nach Monat und Produktgruppe v Gesamtvertrieb für Jahre nach 1990 v Verkauf für 1995 oder 1996 v Summe des Verkaufs für eine Produktgruppe oder Produktlinie v Summe des Verkaufs für eine bestimmte Produktgruppe oder Produktlinie UND

für 1995, 1996 v Summe des Verkaufs für ein bestimmtes Land

Obwohl die präzise Anwort für keine dieser Abfragen in der MQT enthalten ist, könnte der Aufwand zur Berechnung der Antwort mit Hilfe der MQT erheblich geringer ausfallen als bei Verwendung der umfangreichen Basistabelle, da ein Teil der für die Antwort benötigten Berechnungen bereits erfolgt ist. MQTs können die Notwendigkeit von aufwendigen Joins, Sortierungen und Spaltenberechnungen von Basisdaten verringern.


Die folgenden Beispielabfragen könnten beträchtliche Leistungsverbesserungen erfahren, da sie die bereits berechneten Ergebnisse der Beispiel-MQT nutzen könn-ten.

Das erste Beispiel liefert die Gesamtverkäufe für 1995 und 1996: SET CURRENT REFRESH AGE=ANY

SELECT YEAR(pdate) AS year, SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t,

cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l

WHERE ti.transid = t.id AND ti.pgid = pg.id

AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996)

GROUP BY year(pdate);

Das zweite Beispiel liefert die Gesamtverkäufe nach Produktgruppe für 1995 und 1996: SET CURRENT REFRESH AGE=ANY

SELECT pg.id AS "PRODUCT GROUP", SUM(ti.amount) AS amount

FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg,

cube.prodline AS l WHERE ti.transid = t.id

AND ti.pgid = pg.id AND pg.lineid = l.id

AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996)

GROUP BY pg.id;

Je größer die Basistabellen sind, desto höher können die Leistungsverbesserungen in Antwortzeiten ausfallen, weil die MQT langsamer anwächst als die Basistabelle. MQTs können sich überlappende Arbeitsschritte von Abfragen effektiv vermeiden helfen, indem sie die Berechnungen einmal bei ihrer Erstellung und Aktualisierung (REFRESH) durchführen und ihren Inhalt anschließend vielen Abfragen zur Verfü-gung stellen.


v „Replizierte MQTs in partitionierten Datenbankumgebungen” auf Seite 206 v „Designadvisor” auf Seite 51

Tabellenpartitionierung und MDC-Tabellen

Bei einer Tabelle kann es sich sowohl um eine MDC-Tabelle (Tabelle mit mehrdi-mensionalem Clustering) als auch um eine partitionierte Tabelle handeln. In einer Tabelle, die beide Merkmale aufweist, können Spalten sowohl in der Bereichs-partitionsspezifikation (Range-Partition-Spec) als auch im MDC-Schlüssel für die Tabellenpartitionierung verwendet werden. Diese Möglichkeit ermöglicht eine exaktere Differenzierung des Datenpartitions- und Blockausschlusses, als dies bei Verwendung nur einer dieser Funktionalitäten der Fall wäre. Es gibt auch zahlrei-che Anwendungen, in denen es sinnvoll ist, für den MDC-Schlüssel andere Spalten als die für die Tabellenpartitionierung genutzten Spalten anzugeben. Hierbei ist zu beachten, dass die Tabellenpartitionierung mit mehreren Spalten arbeitet, während beim MDC mit mehreren Dimensionen gearbeitet wird.

Kapitel 2. Leistungsplanung beim Datenbankentwurf 13

Merkmale eines normalen DB2 V9.1-Data Warehouses:

Die folgenden Empfehlungen beziehen sich auf typische, normale Data Ware-houses, die in DB2 V9.1 neu implementiert wurden. Die folgenden Merkmale wer-den angenommen: v Die Datenbank arbeitet auf mehreren Systemen oder in mehreren logischen AIX-

Partitionen. v Die Datenbankpartitionierungsfunktion (DPF) wird verwendet (Tabellen werden

mit Hilfe der Klausel DISTRIBUTE BY HASH erstellt). v Es wurden zwischen vier und 50 Datenpartitionen definiert. v Die Tabelle, für die die Möglichkeit zur Verwendung des MDC und der Tabel-

lenpartitionierung geprüft wird, ist eine der zentralen Fakttabellen. v Die Tabelle umfasst zwischen 100 Millionen und 100 Milliarden Zeilen. v Neue Daten werden in unterschiedlichen Zeitrahmen geladen: Jeweils über

Nacht, wöchentlich, monatlich. v Das tägliche Aufnahmevolumen liegt zwischen 10.000 und 10 Millionen Daten-

sätzen. v Die Datenvolumen schwanken: Hierbei liegt das Volumen des Spitzenmonats

um das Fünffache höher als das des Monats mit dem geringsten Datenauf-kommen. Entsprechend beläuft sich auch der Umfang der größten Dimensionen (Produktlinie, Bereich) auf das Fünffache des Umfangs der kleinsten Dimensio-nen.

v Die Datenbank enthält detaillierte Datenbestände der letzten 1 - 5 Jahre. v Abgelaufene Daten werden in monatlichen oder vierteljährlichen Abständen mit

einer Rollout-Operation ausgelagert. v Tabellen verwenden eine Vielzahl von Abfragetypen. Der Workload besteht

jedoch größtenteils aus analytischen Abfragen, die in Bezug auf OLTP-Workloads die folgenden Merkmale aufweisen: – Es gibt umfangreichere Ergebnismengen mit bis zu 2 Millionen Zeilen. – Die Mehrzahl oder alle Abfragen beziehen sich auf Sichten und nicht auf

Basistabellen.v SQL-Klauseln zur Auswahl von Daten nach Bereichen (Klausel BETWEEN), Ele-

menten in Listen etc.

Merkmale einer normalen Fakttabelle eines DB2 V9.1-Data Warehouses:

Eine normale Data Warehouse-Fakttabelle kann z. B. das folgende Design verwen-den: v Erstellung von Datenpartitionen über die Spalte ’Month’. v Definition einer Datenpartition für jeden Rollout-Zeitraum, z. B. 1 Monat, 3

Monate. v Erstellung von MDC-Dimensionen für ’Day’ und für 1 - 4 weitere Dimensionen.

Typische Dimensionen sind: Produktlinie und Bereich. v Alle Datenpartitionen und MDC-Cluster sind über alle Datenpartitionen verteilt.

Das MDC und die Tabellenpartitionierung bieten teilweise dieselben Vorteile. Die folgende Tabelle enthält mögliche Anforderungen innerhalb Ihres Unternehmens und Empfehlungen zu einem Organisationsschema, die auf der Basis der zuvor festgestellten Merkmale gegeben werden.


Tabelle 1. Verwendung der Tabellenpartitionierung mit MDC-Tabellen

Problemstellung Empfohlenes Schema Empfehlung

Datenverfügbarkeit während des Rollouts

Tabellenpartitionierung Sie können die Klausel DETACH PARTITION ver-wenden, um ein Rollout gro-ßer Datenmengen unter minimaler Beeinträchtigung durchzuführen.

Abfrageleistung Tabellenpartitionierung und MDC

MDC eignet sich am besten für die Abfrage mehrerer Dimensionen. Die Tabel- lenpartitionierung assistiert durch den Ausschluss von Datenpartitionen.

Minimale Reorganisation MDC MDC-Tabellen behalten das Clustering bei, so dass sich die Notwendigkeit von Reor-ganisationen verringert.

Rollout des Datenbestands eines Monats oder eines län-geren Zeitraums während eines traditionellen Offline-zeitfensters

Tabellenpartitionierung Die Datenpartitionierung kann diese Anforderung voll erfüllen. Durch das MDC würden sich keine zusätzli-chen Vorteile ergeben und dieses Verfahren wäre weni-ger geeignet.

Rollout des Datenbestands eines Monats oder eines län-geren Zeitraums während eines Mikro-Offlinezeit-fensters (weniger als 1 Minute)

Tabellenpartitionierung Die Datenpartitionierung kann diese Anforderung voll erfüllen. Durch das MDC würden sich keine zusätzli-chen Vorteile ergeben und dieses Verfahren wäre weni-ger geeignet.

Rollout des Datenbestands eines Monats oder eines län-geren Zeitraums bei gleich-zeitiger Erhaltung der Tabellenverfügbarkeit für Geschäftsbenutzer, die Abfra-gen ohne Serviceverluste weiterhin übergeben können

MDC MDC kann nicht alle in die-sem Zusammenhang gelten-den Anforderungen voll erfüllen. Die Tabellenparti- tionierung eignet sich hier nicht, da die Tabelle nur für kurze Zeit in den Offline-modus versetzt wird.

Tägliches Laden der Daten (Online oder Offline)

Tabellenpartitionierung und MDC

MDC bietet hier die meisten Vorteile. Die Tabellenparti- tionierung bietet Vorteile in Teilbereichen.

Kontinuierliches Laden der Daten (Online)


MDC bietet hier die meisten Vorteile. Die Tabellenparti- tionierung bietet Vorteile in Teilbereichen.

Leistung bei der Ausführung von Abfragen für traditio-nelle BI-Abfragen


MDC eignet sich besonders gut für Abfragen in Daten-kuben und mehreren Dimen-sionen. Die Tabellenpartiti- onierung bietet Unterstüt-zung über den Partitions-ausschluss.


Tabelle 1. Verwendung der Tabellenpartitionierung mit MDC-Tabellen (Forts.)

Problemstellung Empfohlenes Schema Empfehlung

Minimierung des Reorganisationsaufwandes durch Vermeidung des Reorganisationsbedarfs oder Reduzierung des Aufwands für die Ausführung der Task

MDC Beim MDC wird das Clustering beibehalten, so dass sich die Notwendigkeit von Reorganisationen verrin-gert. Wenn das MDC ver-wendet wird, dann bietet die Datenpartitionierung auch in Teilbereichen keine Vorteile. Allerdings ermöglicht die Bereichspartitionierung bei Nichtverwendung des MDC die Reduzierung des Reorganisationsbedarfs, indem auf Partitionsebene ein gewisses Maß an Course-Grain-Clustering beibehalten wird.

Beispiel 1:

Sie arbeiten mit einer Tabelle mit den Schlüsselspalten ’YearAndMonth’ und ’Pro-vince’. Bei der Planung dieser Tabelle wäre eine Partitionierung nach Datum mit einer Zeitspanne von zwei Monaten pro Datenpartition sinnvoll. Darüber hinaus können Sie die Daten auch nach der Spalte ’Province’ organisieren, so dass alle Zeilen für ein bestimmtes Gebiet innerhalb eines Datumsbereichs von zwei Mona-ten in einer Gruppe zusammengefasst werden. Diese Vorgehensweise ist in Abb. 1 auf Seite 17 dargestellt. CREATE TABLE orders (YearAndMonth INT, Province CHAR(2)) PARTITION BY RANGE (YearAndMonth) (STARTING 9901 ENDING 9904 EVERY 2) ORGANIZE BY (Province);


Beispiel 2:

Eine exaktere Differenzierung kann durch Hinzufügen von ’YearAndMonth’ zur Klausel ORGANIZE BY (siehe hierzu Abb. 2 auf Seite 18) erzielt werden. CREATE TABLE orders (YearAndMonth INT, Province CHAR(2)) PARTITION BY RANGE (YearAndMonth) (STARTING 9901 ENDING 9904 EVERY 2) ORGANIZE BY (YearAndMonth, Province);

Abbildung 1. Eine nach ’YearAndMonth’ partitionierte und nach ’Province’ organisierte Tabelle


Wenn die Partitionierung so festgelegt wurde, dass jeder Bereich nur einen einzi-gen Wert enthält, ergeben sich keine Vorteile, wenn die Tabellenpartitionierungs-spalte in den MDC-Schlüssel aufgenommen wird.

Wichtige Hinweise:

v Im Vergleich mit einer Basistabelle benötigen sowohl MDC-Tabellen als auch partitionierte Tabellen mehr Speicherplatz. Diese Speicherplatzanforderungen gelten zusätzlich zu den sonstigen Anforderungen, sind jedoch unter Berücksich-tigung der sich daraus ergebenden Vorteile sinnvoll.

v Wenn Sie die Tabellenpartitionierung und die MDC-Funktionalität in Ihrer parti-tionierten Datenbankumgebung nicht zusammen einsetzen wollen, dann sollten Sie die Tabellenpartitionierung in den Fällen einsetzen, in denen die Daten-verteilung verlässlich vorausgesagt werden kann. Dies ist normalerweise bei den hier erläuterten Systemtypen der Fall. Andernfalls sollten Sie die Verwendung von MDC in Betracht ziehen.

Abbildung 2. Eine nach ’YearAndMonth’ partitionierte und nach ’Province’ und ’YearAndMonth’ organisierte Tabelle



v „Datenpartitionen” in Systemverwaltung: Konzept v „Entwerfen von Tabellen mit mehrdimensionalem Clustering (MDC)” in System-

verwaltung: Konzept

v „Erstellung, Platzierung und Verwendung von Tabellen mit mehrdimensionalem Clustering (MDC)” in Systemverwaltung: Konzept

v „Optimierungsstrategien für MDC-Tabellen” auf Seite 19 v „Optimierungsstrategien für partitionierte Tabellen” auf Seite 21 v „Verhalten von partitionierten MQTs” in Systemverwaltung: Implementierung v „Partitionierte Tabellen” in Systemverwaltung: Konzept v „Tabellen- und Indexverwaltung für MDC-Tabellen” auf Seite 387 v „Tabellenpartitionierung” in Systemverwaltung: Konzept

Zugehörige Tasks:

v „Ändern partitionierter Tabellen” in Systemverwaltung: Implementierung v „Methoden zum Definieren von Bereichen für partitionierte Tabellen” in System-

verwaltung: Implementierung

v „Methoden zum Migrieren vorhandener Tabellen und Sichten in partitionierte Tabellen” in Systemverwaltung: Implementierung

v „Erstellen partitionierter Tabellen” in Systemverwaltung: Implementierung


v „Einschränkungen für den nativen XML-Datenspeicher” in XML-Handbuch

Optimierungsstrategien für MDC-Tabellen

Wenn Sie Tabellen mit mehrdimensionalem Clustering (MDC - Multidimensional Clustering) erstellen, kann sich die Leistung vieler Abfragen verbessern, weil das Optimierungsprogramm zusätzliche Optimierungsstrategien anwenden kann. Diese Strategien basieren in erster Linie auf der verbesserten Effizienz von Blockindizes, jedoch ermöglicht der Vorteil des mehrdimensionalen Clustering auch einen schnelleren Datenabruf.

Anmerkung: Die Optimierungsstrategien für MDC-Tabellen können auch die Leistungsvorteile der partitionsinternen und partitionsübergreifenden Parallelität implementieren.

MDC-Tabellen bieten die folgenden spezifischen Vorteile: v Dimensionsblockindexsuchen können die erforderlichen Teile der Tabelle ermit-

teln und schnell nur die angeforderten Blöcke durchsuchen. v Da Blockindizes kleiner als Satz-ID-Indizes sind, arbeiten Blockindexsuchen

schneller. v AND- und OR-Joins von Indizes (Index ANDing und Index ORing) können auf

Blockebene durchgeführt und mit Satz-IDs kombiniert werden. v Daten werden garantiert in EXTENTSIZE-Speicherbereichen in Clustern grup-

piert, was ein schnelleres Abrufen ermöglicht. v Zeilen können schneller gelöscht werden, wenn eine Auslagerung angewendet

werden kann.


Wenn die Bedingungen für eine Auslagerung gegeben sind, wird eine effizientere Lösung zum Löschen von Zeilen aus MDC-Tabellen verwendet. Die Bedingungen sind folgende: v Die Anweisung DELETE wird gesucht, nicht positioniert (d. h. verwendet nicht

die Klausel „WHERE CURRENT OF”). v Es gibt keine WHERE-Klauseln (alle Zeilen müssen gelöscht werden) oder die

einzigen Bedingungen in der Klausel WHERE gibt es für Dimensionen. v Es gibt keine Tabellen mit DATA CAPTURE CHANGES. v Die Tabelle ist hinsichtlich referenzieller Integrität nicht das übergeordnete Ele-

ment, verfügt über keine definierten Trigger zum Löschen und wird in keinerlei MQTs verwendet, die unverzüglich aktualisiert werden.

v Eine mehrstufige Löschoperation kommt für eine Auslagerung in Frage, wenn der Fremdschlüssel eine Untermenge der Dimensionsspalten ist.

v Die Anweisung DELETE darf nicht in einer Anweisung SELECT einer Klausel OLD TABLE vorhanden sein.

Bei einer Auslagerungslöschaktion werden die gelöschten Sätze nicht protokolliert. Stattdessen werden die Seiten mit den Sätzen durch eine Neuformatierung von Tei-len der Seiten optisch geleert. Die Änderungen an den neu formatierten Teilen wer-den protokolliert, die Sätze selbst werden allerdings nicht protokolliert. Es gibt keine Änderung hinsichtlich der Protokollierung von Indexaktualisierungen; die Leistungsverbesserung hängt also davon ab, wie viele Satz-ID-Indizes es gibt. Je weniger Satz-ID-Indizes es gibt, desto qualitativ höher ist die Verbesserung (als Prozentanteil der Gesamtzeit und des gesamten Protokollspeichers).

Mit der nachstehenden Formel können Sie eine ungefähre Angabe über die Menge des im Protokoll eingesparten Speichers machen: S + 38*N - 50*P

Dabei ist N die Anzahl der gelöschten Sätze, S die Gesamtgröße der gelöschten Sätze einschließlich Systemaufwand (z. B. Nullanzeiger und VARCHAR-Längen) und P die Anzahl der Seiten in dem Block mit den gelöschten Sätzen:

Diese Zahl entspricht der Menge von in dem permanenten Protokoll eingesparten Speicherbereich; sie muss verdoppelt werden, damit die Einsparungen im reser-vierten Speicherbereich im aktiven Protokoll einbezogen werden.

Setzen Sie die Registrierdatenbankvariable DB2_MDC_ROLLOUT auf ″OFF″, wenn Sie das Rolloutverhalten in Löschvorgängen inaktivieren möchten.

Betrachten Sie das folgende einfache Beispiel für eine MDC-Tabelle mit dem Namen sales, in der Dimensionen auf den Spalten region und month definiert sind: SELECT * FROM SALES

WHERE MONTH=’March’ AND REGION=’SE’

Für diese Abfrage kann das Optimierungsprogramm eine Dimensionsblockindex-suche durchführen, um die Blöcke zu finden, in denen der Monat März (March) und die Region SE vorkommen. Anschließend kann es nur die resultierenden Blö-cke der Tabelle durchsuchen, um die Ergebnismenge abzurufen.


v „Tabellen- und Indexverwaltung für MDC-Tabellen” auf Seite 387


Optimierungsstrategien für partitionierte Tabellen

Die Bezeichnung Ausschluss von Datenpartitionen bezieht sich auf die Fähigkeit des Datenbankservers, auf der Grundlage der Abfragevergleichselemente festzustellen, dass nur auf eine Untergruppe der Datenpartitionen einer Tabelle zugegriffen wer-den muss, um eine Abfrage zu erfüllen. Der Ausschluss von Datenpartitionen ist insbesondere vorteilhaft, wenn eine Entscheidungshilfeabfrage auf eine partitio-nierte Tabelle ausgeführt wird.

Eine partitionierte Tabelle arbeitet mit einem Datenorganisationsschema, bei dem Tabellendaten auf mehrere Speicherobjekte, die als Datenpartitionen oder Daten-bereiche (RANGE) bezeichnet werden, entsprechend den Werten einer oder mehre-rer Spalten der Tabelle, die den Tabellenpartitionierungsschlüssel bilden, verteilt werden. Daten aus einer gegebenen Tabelle werden in mehrere Speicherobjekte auf der Basis der in der Klausel PARTITION BY der Anweisung CREATE TABLE ange-gebenen Spezifikationen partitioniert. Diese Speicherobjekte können sich in ver-schiedenen Tabellenbereichen, in denselben Tabellenbereichen oder in einer Kombi-nation beider Arten von Tabellenbereichen befinden.

Das folgende Beispiel veranschaulicht die Leistungsvorteile des Ausschlusses von Datenpartitionen. Dazu wird die folgende Anweisung zur Erstellung einer partitio-nierten Tabelle ausführt: CREATE TABLE custlist(subsdate DATE, Province CHAR(2), AccountID INT) PARTITION BY RANGE(subsdate) (STARTING FROM ’1/1/1990’ IN ts1, STARTING FROM ’1/1/1991’ IN ts1, STARTING FROM ’1/1/1992’ IN ts1, STARTING FROM ’1/1/1993’ IN ts2, STARTING FROM ’1/1/1994’ IN ts2, STARTING FROM ’1/1/1995’ IN ts2, STARTING FROM ’1/1/1996’ IN ts3, STARTING FROM ’1/1/1997’ IN ts3, STARTING FROM ’1/1/1998’ IN ts3, STARTING FROM ’1/1/1999’ IN ts4, STARTING FROM ’1/1/2000’ IN ts4, STARTING FROM ’1/1/2001’ ENDING ’12/31/2001’ IN ts4);

Nehmen Sie an, Sie interessieren sich für Kundeninformationen des Jahres 2000. Sie führen die folgende Abfrage aus: SELECT * FROM custlist WHERE subsdate BETWEEN ’1/1/2000’ AND ’12/31/2000’;

Wie in Abb. 3 auf Seite 22 gezeigt, stellt der Datenbankserver fest, dass nur auf eine Datenpartition in Tabellenbereich 4 (’ts4’) zugegriffen werden muss, um diese Abfrage zu erfüllen.


Ein weiteres Beispiel für den Ausschluss von Datenpartitionen (siehe Abb. 4 auf Seite 23) ist eine Indexsuche über zwei Indizes, die auf folgendem Schema basie-ren: CREATE TABLE multi (sale_date date, region char(2)) PARTITION BY (sale_date) (STARTING ’01/01/2005’ ENDING ’12/31/2005’ EVERY 1 MONTH); CREATE INDEX sx ON multi(sale_date); CREATE INDEX rx ON multi(region);

Nun wird die folgende Abfrage ausgeführt: SELECT * FROM multi WHERE sale_date BETWEEN ’6/1/2005’ AND ’7/31/2005’ AND REGION = ’NW’;

Tabelle 'custlist'

Tabellen-bereich (ts1)




a=1990a=1991a=1992

a=1993a=1994a=1995

a=1996a=1997a=1998

a=1999a=2000a=2001

Legende

= Datenpartition

a = subsdate

Suche

Abbildung 3. Die Leistungsvorteile des Ausschlusses von Datenpartitionen für eine partitionierte Tabelle


Ohne Tabellenpartitionierung besteht ein wahrscheinlicher Plan in der logischen Verknüpfung von der Indizes über AND. Das Index ANDing führt die folgenden Aktionen aus: v Lesen aller relevanten Indexeinträge aus jedem Index v Speichern beider Gruppen von Zeilenkennungen (Satz-IDs, RIDs) v Abgleichen der RIDs, um zu ermitteln, welche in beiden Indizes vorkommen v Verwenden der RIDs zum Abrufen der Zeilen

Wie in Abb. 4 zu erkennen ist, wird mit der Tabellenpartitionierung der Index gele-sen, um Übereinstimmungen für beide Spalten, d. h. ’region’ und ’sale_date’, zu finden, so dass entsprechende Zeilen schnell abgerufen werden können.

DB2-Explain:

Sie können auch mit Hilfe von DB2 Explain den Ausschluss von Datenpartitionen ermitteln, der vom DB2-Optimierungsprogramm ausgewählt wurde. Die DP Elim Predicates-Informationen zeigen, welche Datenpartitionen durchsucht werden, um die folgende Abfrage zu erfüllen: SELECT * FROM custlist WHERE subsdate BETWEEN ’12/31/1999’ AND ’1/1/2001’

Arguments: --------- DPESTFLG: (Number of data partitions accessed are Estimated)

FALSE DPLSTPRT: (List of data partitions accessed)

9-11 DPNUMPRT: (Number of data partitions accessed)

3

DP Elim Predicates: ------------------ Range 1)

Stop Predicate: (Q1.A

Objects Used in Access Plan: ---------------------------

Schema: MRSRINI Name: CUSTLIST

Type: Data Partitioned Table Time of creation: 2005-11-30-14.21.33.857039

Last statistics update: 2005-11-30-14.21.34.339392 Number of columns: 3

Number of rows: 100000 Width of rows: 19

Number of buffer pool pages: 1200 Number of data partitions: 12

Distinct row values: No Tablespace name:

Unterstützung mehrerer Spalten:

Der Ausschluss von Datenpartitionen funktioniert auch in Fällen, in denen mehrere Spalten als Tabellenpartitionierungsschlüssel verwendet werden.

Es wird zum Beispiel eine Tabelle mit der folgenden Anweisung erstellt: CREATE TABLE sales(year INT, month INT)

PARTITION BY RANGE(year, month) (STARTING FROM (2001, 1) ENDING AT(2001,3) IN ts1,

ENDING AT(2001,6) IN ts2, ENDING AT(2001,9) IN ts3, ENDING AT(2001,12) IN ts4,

ENDING AT(2002,3) IN ts5, ENDING AT(2002,6) IN ts6, ENDING AT(2002,9) IN ts7, ENDING AT(2002,12) IN ts8)

Anschließend wird die folgende Abfrage ausgeführt: SELECT * FROM sales WHERE year = 2001 AND month < 8

Das Abfrageoptimierungsprogramm folgert, dass zur Erfüllung dieser Abfrage nur auf die Datenpartitionen in ’ts1’, ’ts2’ und ’ts3’ zugegriffen werden muss.

Anmerkung: Wenn der Tabellenpartitionierungsschlüssel aus mehreren Spalten gebildet wird, ist der Ausschluss von Datenpartitionen nur möglich, wenn Vergleichselemente für die führenden Spalten des zusammen-gesetzten Schlüssels verwendet werden, da nicht führende Spalten, die im Tabellenpartitionierungsschlüssel verwendet werden, nicht unabhängig sind.

Unterstützung mehrerer Bereiche:

Es ist möglich, einen Ausschluss von Datenpartitionen für mehrere Bereiche zu erzielen (d. h. durch logisches Verknüpfen von Bereichen über OR). An der im vorigen Beispiel erstellten Tabelle wird zum Beispiel die folgende Abfrage ausge-führt: SELECT * FROM sales

WHERE (year = 2001 AND month = 10)

Der Datenbankserver greift nur auf Daten für das erste Quartal von 2001 und das letzte Quartal von 2002 zu.


Generierte Spalten:

Sie können generierte Spalten als Tabellenpartitionierungsschlüssel verwenden.

Sie können zum Beispiel die folgende Anweisung ausführen: CREATE TABLE sales(a INT, b INT GENERATED ALWAYS AS (a / 5))

IN ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8,ts9,ts10 PARTITION BY RANGE(b)

(STARTING FROM (0) ENDING AT(1000) EVERY (50))

In diesem Fall werden Vergleichselemente für die generierte Spalte zum Ausschluss von Datenpartitionen verwendet. Wenn der Ausdruck zur Generierung der Spalten außerdem monoton ist, übersetzt der Datenbankserver Vergleichselemente für die Quellenspalten in Vergleichselemente für die generierten Spalten, so dass der Aus-schluss von Datenpartitionen über die generierten Spalten erfolgen kann.

Betrachten Sie zum Beispiel die folgende Abfrage: SELECT * FROM sales WHERE a > 35

In diesem Fall generiert der Datenbankserver aus dem Vergleichselement für a (a > 35) ein zusätzliches Vergleichselement für b (b > 7), um den Ausschluss von Daten-partitionen zu ermöglichen.

Joinvergleichselemente:

Joinvergleichselemente können ebenfalls beim Ausschluss von Datenpartitionen verwendet werden, wenn das Joinvergleichselement auf die Ebene des Tabellenzu-griffs verschoben wird (Pushdown). Das Joinvergleichselement wird nur für die innere Tabelle eines Joins mit Verschachtelungsschleife (NLJN, Nested Loop Join) auf die Tabellenzugriffsebene verschoben.

Betrachten Sie zum Beispiel die folgenden Tabellen: CREATE TABLE T1(A INT, B INT)

PARTITION BY RANGE(A, B) (STARTING FROM (1, 1)

ENDING (1,10) IN ts1, ENDING (1,20) IN ts2, ENDING (2,10) IN ts3, ENDING (2,20) IN ts4, ENDING (3,10) IN ts5, ENDING (3,20) IN ts6, ENDING (4,10) IN ts7, ENDING (4,20) IN ts8)

CREATE TABLE T2 (A INT, B INT)

Folgende Vergleichselemente werden verwendet: P1: T1.A = T2.A P2: T1.B > 15

In diesem Beispiel lassen sich die genauen Datenpartitionen, auf die zugegriffen wird, wegen unbekannter Werte der äußeren Tabelle des Joins beim Kompilieren nicht bestimmen. In diesem Fall und ebenso in Fällen, in denen Hostvariablen oder Parametermarken verwendet werden, erfolgt der Ausschluss von Datenpartitionen bei der Ausführung, wenn die erforderlichen Werte gebunden werden.

Wenn bei der Ausführung T1 die innere Tabelle eines NLJN-Joins ist, erfolgt der Ausschluss von Datenpartitionen entsprechend den Vergleichselementen für jeden Wert T2.A der äußeren Tabelle dynamisch. Bei der Ausführung werden die Vergleichselemente T1.A = 3 und T1.B > 15 für den Wert T2.A = 3 der äußeren


Tabelle angewendet. Dadurch werden die Datenpartitionen in den Tabellen-bereichen ’ts6’ und ’ts7’ für den Zugriff ermittelt.

Nehmen Sie an, dass die Spalten A in den Tabellen T1 und T2 folgende Werte ent-halten:

Äußere Tabelle T2: Spalte A

Innere Tabelle T1: Spalte A

Innere Tabelle T1: Spalte B

Innere Tabelle T1: Position der Daten-

partition 2 3 20 ts6 3 2 10 ts3 3 2 18 ts4

3 15 ts6 1 40 ts3

Für den Join mit Verschachtelungsschleife (unter Annahme einer Tabellensuche für die innere Tabelle) führt der Datenbankmanager die folgenden Schritte aus: 1. Er liest die erste Zeile aus T2. Der Wert für A ist 2. 2. Er bindet den Wert T2.A (d. h. 2) an die Spalte T2.A im Joinvergleichselement

T1.A = T2.A. Aus dem Vergleichselement wird T1.A = 2. 3. Er wendet den Ausschluss von Datenpartitionen unter Verwendung der

Vergleichselemente T1.A = 2 und T1.B > 15 an. Dies qualifiziert die Daten-partitionen in den Tabellenbereichen ’ts4’ und ’ts5’.

4. Er durchsucht die Datenpartitionen in den Tabellenbereichen ’ts4’ und ’ts5’ von Tabelle T1, bis eine Zeile nach Anwendung von T1.A = 2 und T1.B > 15 gefun-den wird. Die erste qualifizierte Zeile, die gefunden wird, ist die dritte Zeile von T1.

5. Er verknüpft die übereinstimmenden Zeilen. 6. Er durchsucht die Datenpartitionen in den Tabellenbereichen ’ts4’ und ’ts5’, bis

die nächste Übereinstimmung (mit T1.A = 2 und T1.B > 15) gefunden wird. In diesem Fall werden keine weiteren Zeilen gefunden.

7. Er wiederholt die Schritte 1 bis 6 für die nächste Zeile von T2 (d. h. er nimmt den Wert 3 aus Spalte A). Dieses Verfahren wird fortgesetzt, bis alle Zeilen von T2 durchlaufen sind.


v „Informationen zur Vorbereitung von Block- und Zeilen-IDs” auf Seite 731 v „Konfigurationsparameter mit Einfluss auf die Abfrageoptimierung” auf Seite

186 v „Datenorganisationsschemata in DB2- und Informix-Datenbanken” in System-

verwaltung: Konzept

v „EXPLAIN-Informationen für Datenobjekte” auf Seite 264 v „EXPLAIN-Tools” auf Seite 258 v „Richtlinien zur Analyse von EXPLAIN-Informationen” auf Seite 271 v „Richtlinien zur Erfassung von EXPLAIN-Informationen” auf Seite 269 v „Richtlinien zur Verwendung von EXPLAIN-Informationen” auf Seite 260 v „Verschiedene EXPLAIN-Informationen” auf Seite 737 v „Partitionierte Tabellen” in Systemverwaltung: Konzept v „EXPLAIN-Einrichtung” auf Seite 257 v „Tabellenpartitionierung” in Systemverwaltung: Konzept



v „EXPLAIN-Tabellen” auf Seite 663 v „CREATE TABLE statement” in SQL Reference, Volume 2


Kapitel 3. Indizes

Verwenden von relationalen Indizes zur Leistungsverbesserung

Indizes können zur Leistungsverbesserung beim Zugreifen auf Tabellendaten ver-wendet werden. Relationale Indizes werden zum Arbeiten mit relationale Daten verwendet. Beim Zugriff auf XML-Daten werden Indizes für XML-Daten verwen-det.

Obwohl das Optimierungsprogramm entscheidet, ob ein relationaler Index für den Zugriff auf Daten relationaler Tabellen verwendet werden soll, müssen Sie, abgese-hen von den folgenden Ausnahmen, ermitteln, welche Indizes die Leistung verbes-sern könnten, und diese Indizes erstellen. Ausnahmen sind die Dimensionsblock-indizes und die zusammengesetzten Blockindizes, die für jede Dimension automatisch erstellt werden, die Sie bei der Erstellung einer MDC-Tabelle (MDC - Multi-Dimensional Clustering) angeben.

Sie müssen außerdem in folgenden Situationen das Dienstprogramm RUNSTATS ausführen, um neue Statistikdaten zu den relationalen Indizes zu erfassen: v Nachdem Sie einen relationalen Index erstellt haben. v Nachdem Sie den Wert für PREFETCHSIZE geändert haben.Darüber hinaus sollten Sie das Dienstprogramm RUNSTATS in regelmäßigen Abständen ausführen, um die Statistikdaten auf aktuellem Stand zu halten. Ohne aktuelle Statistiken zu Indizes ist das Optimierungsprogramm nicht in der Lage, den besten Datenzugriffsplan für Abfragen zu ermitteln.

Anmerkung: Um festzustellen, ob ein relationaler Index in einem bestimmten Paket verwendet wird, verwenden Sie die EXPLAIN-Einrichtung. Zum Planen von relationalen Indizes können Sie mit dem Designad-visor in der Steuerzentrale oder mit dem Tool db2advis Hinweise zu relationalen Indizes abrufen, die von einer oder mehreren SQL-Anweisungen verwendet werden können.

Vorteile eines relationalen Index gegenüber keinem Index

Wenn für eine Tabelle kein Index vorhanden ist, muss eine Tabellensuche für jede Tabelle durchgeführt werden, auf die in einer SQL-Abfrage verwiesen wird. Je umfangreicher die Tabelle ist, desto länger dauert die Tabellensuche, weil eine Tabellensuche erfordert, dass auf jede Tabellenzeile sequenziell zugegriffen wird. Obwohl eine Tabellensuche für eine komplexe Abfrage, die die meisten der Zeilen in einer Tabelle abruft, effizienter sein kann, ist für eine Abfrage, die nur einige Tabellenzeilen zurückgibt, eine Indexsuche für den Zugriff auf Tabellenzeilen effizi-enter.

Das Optimierungsprogramm wählt eine Indexsuche aus, wenn in der SELECT-An-weisung auf die relationalen Indexspalten verwiesen wird und wenn aufgrund der Schätzungen zu erwarten ist, dass eine Indexsuche schneller als eine Tabellensuche durchgeführt werden kann. Indexdateien sind im Allgemeinen kleiner und erfor-dern weniger Zeit zum Lesen als eine ganze Tabelle, besonders wenn die Tabellen umfangreicher werden. Darüber hinaus braucht eventuell nicht der gesamte Index

29

durchsucht zu werden. Die Vergleichselemente, die auf einen Index angewandt werden, verringern die A

Date post:	24-Aug-2019
Category:	Documents
Upload:	nguyenkhanh
View:	212 times
Download:	0 times

Systemverwaltung: Optimierung - Universität Ulm · Inhaltsverzeichnis Teil 1. Einführung zur...

Documents