Voraussetzungen

Um das Tuning mit EM Grid Control durchführen zu können, ist es erforderlich, das Database Tuning Pack für das betroffene Zielsystem zu aktivieren. Bei diesem Pack handelt es sich um eine so genannte Option der Oracle Enterprise Edition, die lizenzpflichtig ist. Die dafür erforderliche Vorgehensweise haben wir bereits in Teil 2 [2] dieser Artikelserie beschrieben.

Die für das Tuning zur Verfügung gestellten Daten basieren teilweise auf dem Automatic Workload Repository (AWR) von Oracle, das mit dem Release 10g neu eingeführt wurde. Um die darin gespeicherten Informationen zu benutzen, muss das Database Diagnostic Pack als Option der Enterprise Edition ebenfalls lizensiert sein.

Verfügbare Werkzeuge

Oracle stellt als zentrales Werkzeug den SQL Tuning Advisor bereit. Als zweites Tool steht der SQL Access Advisor zur Verfügung, der zum einen durch den SQL Tuning Advisor automatisch verwendet wird, der aber zum anderen auch separat genutzt werden kann. Zur Verwendung dieser beiden Advisor muss der dynamisch per alter system bzw. alter session änderbare Initialisierungsparameter statistics_level auf typical (default) oder all eingestellt werden. Anderenfalls werden die erforderlichen Daten nicht im AWR gesammelt.

SQL Tuning Advisor

Mit dem SQL Tuning Advisor steht ein mächtiges Werkzeug zum Optimieren von SQL-Befehlen zur Verfügung. Die Basis für das Tuning der SQL-Befehle ist der Optimizer, der in zwei unterschiedlichen Modi betrieben werden kann:

Normaler Modus: Dies ist der bisher bekannte Modus des Optimizers. Er erstellt innerhalb sehr kurzer Zeit einen Ausführungsplan für ein SQL Statement. Dieser Ausführungsplan ist für die überwiegende Anzahl von SQL-Befehlen ausreichend.

Tuning-Modus: Dieser ist insbesondere für aufwändigere SQL-Befehle konzipiert. In diesem Modus überprüft der Optimizer eine Vielzahl von Alternativen, sucht nach fehlenden oder veralteten Statistiken und testet, ob alternative Formulierungen eines SQL-Befehls dessen schnellere Ausführung erlauben. Ein Lauf des Optimizers im Tuning-Modus kann mehrere Minuten dauern.

Als Eingabe akzeptiert der Tuning Advisor Eingaben aus folgenden Quellen:

• Automatic Database Diagnostic Monitor (ADDM)
• High Load SQL aus dem Automatic Workload Repository (AWR)
• Cursor Cache
• SQL Tuning Set

Der Tuning Advisor bedient sich dabei wiederum der Arbeit des SQL Access Advisors.

SQL Access Advisor

Der SQL Access Advisor überprüft anhand zahlreicher SQL-Befehle, ob diese durch den Einsatz bestimmter Indizes oder Materialized Views zu optimieren sind. Als Eingabe dient ein Workload oder ein einzelner SQL-Befehl im so genannten "Quick Tune". Der Advisor analysiert den Workload oder den SQL-Befehl und gibt Empfehlungen über die Anlage oder Löschung von Indizes sowie die Anlage oder Änderung von Materialized Views. Das Erstellen von SQL Access Advisor Tasks wird im weiteren Verlauf des Artikels separat erläutert.

Tuning mit dem SQL Tuning Advisor

Grundsätzlich ist die folgende Vorgehensweise für das SQL Tuning vorgesehen:

Abb. 1: Auswahl des Tuning-Zielsystems, also der betroffenen Datenbankinstanz. vergrößern

Abb. 2: Übersicht über die Navigation von den jeweiligen Einstiegspunkten zur Auswahl des bzw. der zu tunenden SQL Statements. vergrößern

Abb. 3: Möglichkeiten zur Planung einer Advisor Task. vergrößern

Abb. 4: Beispiel einer Empfehlung vom Tuning Advisor. vergrößern

1. Ziel aussuchen

Zunächst ist das Zielsystem für das Tuning auszuwählen. Dazu steht auf der Startseite in der rechten Fensterhälfte der Bereich "Zielsuche" zur Verfügung (siehe Abbildung 1). Um zu den Informationen der gewünschten Datenbank zu gelangen, ist aus der Drop-Down-Liste "Datenbank-Instance" auszuwählen oder in das Textfeld der Name der Instanz einzutragen. Ein Klick auf "Los" führt zur Überblicksseite der entsprechenden Datenbank.

2. Zu tunende SQL Statements selektieren bzw. gegebenenfalls Tuning Set erstellen

Um das zu tunende SQL Statement zu selektieren, stellt Oracle nun verschiedene Einstiegspunkte zur Verfügung. Das sind im Wesentlichen die Bereiche "Zentrales Advisory", "Performance Analyse" und "SQL-Anwortzeit" (siehe Abbildung 2, hellgrüne Felder).

3. Advisor Task erstellen, Parameter setzen, SQL Tuning Advisor planen

Um Parameter für den SQL Tuning Advisor zu setzen und dessen Ausführung zu planen, wählt man den zu optimierenden SQL-Befehl bzw. SQL Tuning Set aus. Anschließend empfehlen wir, den recht kryptischen, von Oracle vorgeschlagenen Task-Namen auf der Seite "SQL Tuning Advisor planen" (siehe Abbildung 3) durch eine eigene Bezeichnung zu ersetzen. Die Angabe einer Beschreibung ist optional.

4. Task ausführen

Wichtig ist der "Geltungsbereich" der Task. Wählt der User hier "Begrenzt" aus, wird der Optimizer im Standardmodus ausgeführt, was recht schnell abläuft. Für viele SQL-Befehle ist dies auch ausreichend.

Liegt jedoch ein Statement oder Tuning Set vor, das sich massiv auf die Gesamt-Performance auswirkt, ist es sinnvoll, einen umfassenden Geltungsbereich festzulegen. Dadurch wird der Optimizer im so genannten Tuning-Modus gestartet, welcher im Abschnitt "SQL Tuning Advisor" bereits kurz beschrieben wurde.

Die maximale Laufzeit der Task kann durch Angabe einer maximalen Minutenzahl begrenzt werden. Soll die Tuning Task nicht sofort ausgeführt werden (Default), stehen noch Parameter für ein Scheduling zur Verfügung. Dabei wird als Teil des Optimizers der so genannte "Automatische Tuning Optimizer" ausgeführt. Dieser führt zu erheblich längeren Optimierungszyklen. Diese sind im Wesentlichen durch folgende Aktionen bedingt:

• Sampling von Daten
• Teilausführung von SELECT-Komponenten (z. B. Subselects)
• Vergleich des Ausführungsplans mit echten Ausführungen
• Statistikanalyse: Fehlen Statistiken oder sind sie veraltet, erfolgt eine Sammlung während der Optimierung.
• SQL Profiling
• Zugriffspfadanalyse: Hier wird durch Aufruf des SQL Access Advisors geprüft, ob die Befehlsausführung durch die Anlage zusätzlicher Indizes und/oder Materialized Views beschleunigt werden kann.
• SQL Struktur Analyse: Dabei werden SQL-Befehle auf syntaktische und semantische Änderungsmöglichkeiten geprüft, deren Ausführungsplan nicht optimiert werden kann.

5. Empfehlungen testen

Ist die Ausführung der Tuning Task abgeschlossen, sollte man sich die Ergebnisse anzeigen lassen (siehe Abbildung 4). Die darin aufgeführten Empfehlungen sollte man genau lesen und dann mit Bedacht handeln. Sie liefern eine "Was-wäre-wenn?"-Aussage. Es kann auch vorkommen, dass Grid Control keine Empfehlungen ausgibt, weil kein Optimierungspotential gefunden wurde.

Die Empfehlungen versprechen oftmals deutliche Performance-Verbesserungen bei deren Implementierung. Unter Umständen wird auch ein Vorschlag für ein Umschreiben von SQL-Befehlen gemacht. Es empfiehlt sich unbedingt, die Auswirkungen einer implementierten Empfehlung vor Produktivsetzung in einer geeigneten Umgebung zu testen!

Vor der Implementierung der Empfehlungen kann man sich die potentiellen Auswirkungen in den Ausführungsplänen anzeigen lassen.

• "Original-Explain-Plan" zeigt den mit Anmerkungen des Tuning Advisors versehenen originalen Ausführungsplan.
• "Neuer Explain-Plan" liefert den Ausführungsplan, den der Optimizer bei Ausführung des Statements nach der Empfehlungsimplementierung generieren und benutzen würde.
• "Explain-Pläne vergleichen" ermöglicht einen direkten Vergleich der beiden oben genannten Ausführungspläne.

6. Implementieren und damit Erzeugen eines SQL-Profils

Wird ein besserer Ausführungsplan für einen SQL-Befehl gefunden, kann man diesen implementieren. Dabei wird ein so genanntes SQL-Profil erstellt, das im Data Dictionary gespeichert wird. Dieses Profil wird automatisch der Kategorie "DEFAULT" zugeordnet, womit es durch sämtliche Sessions benutzt werden kann. Für die Zuordnung eines Profils nur zu bestimmten Anwendern, kann die Kategorie geändert und diesen Benutzern die neue Kategorie zugeordnet werden. Ein im Einsatz befindliches SQL-Profil kann auch deaktiviert werden, so dass der ursprüngliche Ausführungsplan wieder zur Anwendung kommt.

Tuning mit dem SQL Access Advisor

Die zweite Möglichkeit des Tunings besteht in der Nutzung des SQL Access Advisors. Im Gegensatz zum SQL Tuning Advisor, der immer einzelne SQL Statements oder ganze Tuning Sets optimiert, werden hier eine Menge von SQL Statements daraufhin untersucht, ob deren Ausführung durch den Einsatz bestimmter Indizes oder Materialized Views optimiert werden kann.

Der SQL Access Advisor kann über das "zentrale Advisory" bzw. die Seite "SQL Tuning Sets" gestartet werden. Die wesentlichen Funktionalitäten wurden zu Beginn des Artikels bereits beschrieben. Bei der Erstellung einer Advisor Task kann als Grundlage entweder das vorhandene Default Template oder eine vorhandene Task bzw. eine andere Vorlage benutzt werden. Als wesentliche Grundlage für die Analyse dient ein Workload, der aus verschiedenen Quellen stammen kann:

• Aktuelle und letzte SQL-Aktivität; die Informationen werden dem Library Cache entnommen.
• Import aus dem SQL Repository; hier wird auf gespeicherte Tuning Sets zurückgegriffen.
• Benutzerdefiniertes Workload; SQL wird aus einer Tabelle oder View importiert, die mindestens die Spalten SQL_TEXT und Username enthalten muss.
• Hypothetisches Workload aus Schemata und Tabellen erstellen; setzt voraus, dass die Tabellen Dimensionen oder Primary/Foreign-Key-Beziehungen enthalten.

Task definieren

Eine Task wird in vier Schritten definiert:

Schritt 1: Es sind zunächst diverse Filterungen, wie beispielsweise über Benutzer, Tabellen, SQL-Text, Modul-IDs, Aktionen und Ressourcennutzung möglich.

Schritt 2: Jetzt können Empfehlungstypen (Index/Materialized Views) sowie der Advisor Modus festgelegt werden. Im so genannten begrenzten Modus werden nur die SQL-Anweisungen mit den höchsten Kosten berücksichtigt, im umfassenden Modus alle Befehle des Workloads. Des Weiteren sind Einstellungen zu Volatilität, Workload-Geltungsbereich, Speicherplatz, Priorität von Optimierungsoptionen, Erstellungskosten der zu erzeugenden Empfehlung sowie Schemata und Tablespaces für die Speicherung von Indizes und Materialized Views möglich. Die Bedeutung und Auswirkungen der einzelnen Optionen sind auf der entsprechenden Seite detailliert und verständlich erläutert.

Schritt 3: Festlegung des Task-Namens einschließlich der Beschreibung für die Protokollierungsebene, die Speicherdauer der Tasks im Repository und für das Scheduling. Außerdem stehen durch die Task noch verschiedene Typen von Ausführungsplänen zur Verfügung.

Schritt 4: Anzeige einer umfangreichen Zusammenfassung mit der Möglichkeit, vorgenommene Einstellungen zu modifizieren, bevor die Task erzeugt bzw. ausgeführt wird. Der Enterprise Manager Grid Control benutzt für das Scheduling und die Ausführung von sämtlichen Tuning Tasks sein internes Jobsystem.

Nutzung der Advisor außerhalb des Grid Control

Alles, was im Kontext des SQL Tuning mit der Grid Control möglich ist, kann alternativ auch mit der Database Control durchgeführt werden. Ebenso können Tuning-Maßnahmen auf Kommandozeilenebene unter Nutzung der mitgelieferten Packages DBMS_ADVISOR und DBMS_SQLTUNE durchgeführt werden.

Fazit

Oracle stellt mit dem Enterprise Manager Grid Control, Release 10g, ein mächtiges, aber auch komplexes Framework für die Optimierung von SQL-Befehlen zur Verfügung. Server-Komponenten analysieren Ressourcenverbrauch sowie Performance und speichern diese Informationen im Automatic Workload Repository.

Die Advisor liefern mit ihren Performance-Tipps, Reports zur Analyse sowie "Was-wäre-wenn?"-Szenarien eine wertvolle Unterstützung für den Administrator. Dennoch sind weiterhin tiefgehendes Know-how, viel Erfahrung und besondere Aufmerksamkeit des Anwenders gefragt, um die Empfehlungen richtig beurteilen zu können. Dies gilt insbesondere für mögliche, falsche Resultate der SQL-Strukturanalyse. Die mitgelieferte Online-Hilfe ist zwar umfangreich, jedoch manchmal nicht detailliert genug, um das jeweilige Thema vollständig zu erfassen.

In einem weiteren Teil der Reihe werden wir uns mit der Administration des Oracle 10g EM Grid Control befassen.

Wilhelm Breßer (info@ordix.de).