Index Advisor ist ein Verwaltungsprogramm, das Sie teilweise von der Notwendigkeit befreit, selbst geeignete Indizes für die Daten entwerfen und definieren zu müssen.
Index Advisor dient folgenden Zwecken:
Es gibt einige Begriffe, die mit der SQL-Advise-Einrichtung verbunden sind. Da ist zunächst der Begriff der Auslastung (Workload). Eine Auslastung ist eine Gruppe von SQL-Anweisungen, die der Datenbankmanager über einen bestimmten Zeitraum hinweg verarbeiten muß. Die SQL-Anweisungen können folgende sein: SELECT, INSERT, UPDATE und DELETE. Zum Beispiel könnte der Datenbankmanager über einen Monat hinweg 1 000 INSERT-, 10 000 UPDATE-, 10 000 SELECT- und 1 000 DELETE-Operationen verarbeiten müssen. Die Informationen in der Auslastung betreffen die Art und Häufigkeit der SQL-Anweisungen über einen bestimmten Zeitraum hinweg. Die Advisor-Steuerkomponente verwendet diese Informationen zur Auslastung in Verbindung mit den Datenbankinformationen, um Indizes zu empfehlen. Der Zweck der Advisor-Steuerkomponente besteht darin, den Gesamtaufwand für die Auslastung zu minimieren.
Sodann gibt es den Begriff eines virtuellen Index. Virtuelle Indizes sind Indizes, die im aktuellen Datenbankschema nicht vorhanden sind. Diese Indizes können entweder von der Advise-Einrichtung empfohlene Indizes sein oder Indizes, die Sie von der Advise-Einrichtung ausgewertet haben wollen. Diese Indizes können außerdem solche sein, die von der Advise-Einrichtung als Teil des Prozesses in Betracht gezogen werden, aber anschließend wieder verworfen werden, weil sie nicht zu empfehlen sind. Virtuelle Indizes werden von Ihnen an die Advise-Einrichtung und von der Advise-Einrichtung an Sie mit Hilfe der Tabelle ADVISE_INDEX übergeben.
Die Advise-Einrichtung verwendet eine Auslastung und Statistikdaten aus der Datenbank, um empfohlene Indizes zu generieren.
Die Advise-Einrichtung verwendet zwei EXPLAIN-Tabellen:
In dieser Tabelle wird die zu berücksichtigende Auslastung beschrieben. Jede Zeile in der Tabelle stellt eine SQL-Anweisung dar und wird durch eine zugeordnete Häufigkeit beschrieben. Es gibt eine Kennung für jede Auslastung, die in einem Feld namens "WORKLOAD_NAME" der Tabelle gespeichert wird. Alle SQL-Anweisungen, die zur selben Auslastung gehören, müssen denselben Namen für WORKLOAD_NAME haben.
Der Assistent: Index und das Programm db2advis verwenden die Tabelle zum Abrufen und Speichern von Informationen zu Auslastungen.
In dieser Tabelle werden Informationen über empfohlene Indizes gespeichert. Informationen werden in diese Tabelle vom SQL-Compiler, vom Assistent: Index, vom Programm db2advis oder von Ihnen eingefügt.
Diese Tabelle wird zu zwei Zwecken verwendet:
Anmerkung: | Zur Erstellung dieser Tabelle führen Sie die Prozedur EXPLAIN.DDL aus, die sich im Unterverzeichnis misc des Unterverzeichnisses sqllib befindet. Wenn sie nicht bereits erstellt ist, kann der Assistent: Index diese Tabelle ebenfalls erstellen. |
Das Verfahren zur Verwendung von Index Advisor umfaßt Eingaben, das Aufrufen von Advisor, Ausgaben und einige Sonderfälle, die betrachtet werden sollten.
Die Eingabe für Index Advisor kann auf drei Arten erstellt werden:
Das heißt, Verwenden einer der folgenden Methoden, um das auszuwertende SQL zu erstellen:
Es gibt vier Methoden, Index Advisor aufzurufen:
Dies ist die empfohlene Methode, Index Advisor zu verwenden. Erweitern Sie von der Steuerzentrale aus die Objektbaumstruktur, bis Sie den Ordner Indizes finden. Klicken Sie mit der Maustaste 2 den Ordner Indizes an, und wählen Sie Erstellen->Index mit Assistent im Kontextmenü aus. Daraufhin wird der Assistent: Index geöffnet. Zum Assistent: Index ist eine umfassende Hilfe verfügbar, und er ist einfach zu verwenden. Der Assistent enthält außerdem Einrichtungen zur Bildung einer Auslastung, indem er nach kürzlich ausgeführtem SQL sucht bzw. kürzlich verwendete Pakete durchsucht oder indem ihm manuell SQL-Anweisungen hinzugefügt werden.
Geben Sie in die Befehlszeile den Befehl db2advis ein. Der Befehl db2advis beginnt, indem er eine Auslastung aus einer von drei Positionen liest:
Das Programm verwendet dann das Register CURRENT EXPLAIN MODE, um die empfohlenen Indizes zusammen mit einem internen Optimierungsalgorithmus zur Auswahl der besten Indizes zu erhalten. Die Ausgabe erfolgt auf dem Terminalbildschirm, in die Tabelle ADVISE_INDEX und in eine Ausgabedatei, falls erwünscht.
Im folgenden Beispiel soll das Programm Indizes für eine einfache Abfrage "select count(*) from sales where region = 'Quebec'" empfehlen:
$ db2advis -d sample \ -s "select count(*) from sales where region = 'Quebec'" \ -t 1 performing auto-bind Bind is successful. Used bindfile: /home3/valentin/sqllib/bnd/db2advis.bnd Calculating initial cost (without recommended indexes) [31.198040] timerons Initial set of proposed indexes is ready. Found maximum set of [1] recommended indexes Cost of workload with all indexes included [2,177133] timerons cost without index [0] is [31,198040] timerons. Derived benefit is [29,020907] total disk space needed for initial set [1] MB total disk space constrained to [-1] MB 1 indexes in current solution [31,198040] timerons (without indexes) [2,177133] timerons (with current solution) [%93,02] improvement Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ689 ON VALENTIN.SALES (REGION DESC) =========================== Index Advisor tool is finished.
Das Programm db2advis darüber hinaus zur Empfehlung von Indizes für eine Auslastung verwendet werden. Sie können eine Eingabedatei mit dem Namen "sample.sql" erstellen:
--#SET FREQUENCY 100 select count(*) from sales where region = ?; --#SET FREQUENCY 3 select projno, sum(comm) tot_comm from employee, emp_act where employee.empno = emp_act.empno and employee.job='DESIGNER' group by projno order by tot_comm desc; --#SET FREQUENCY 50 select * from sales where sales_date = ?;
Führen Sie anschließend den folgenden Befehl aus:
$ db2advis -d sample -i sample.sql -t 0 found [3] SQL statements from the input file Calculating initial cost (without recommmended indexes) [62,331280] timerons Initial set of proposed indexes is ready. Found maximum set of [2] recommended indexes Cost of workload with all indexes included [29,795755] timerons cost without index [0] is [58,816662] timerons. Derived benefit is [29,020907] cost without index [1] is [33,310373] timerons. Derived benefit is [3,514618] total disk space needed for initial set [2] MB total disk space constrained to [-1] MB 2 indexes in current solution [62,331280] timerons (without indexes) [29,795755] timerons (with current solution) [%52,20] improvement Trying variations of the solution set. Time elapsed. LIST OF RECOMMENDED INDEXES =========================== index[1], 1MB CREATE INDEX WIZ119 ON VALENTIN.SALES (SALES_DATE DESC, SALES_PERSON DESC) index[2], 1MB CREATE INDEX WIZ63 ON VALENTIN.SALES (REGION DESC) =========================== Index Advisor tool is finished.
Zum Beispiel wird das Sonderregister CURRENT EXPLAIN MODE auf RECOMMEND INDEXES gesetzt. Diese Einstellung weist den SQL-Compiler an, EXPLAIN-Daten zu erfassen, und sorgt dafür, daß empfohlene Indizes in die Tabelle ADVISE_INDEX eingefügt werden. Die SQL-Anweisung wird jedoch nicht ausgeführt.
Oder das Sonderregister CURRENT EXPLAIN MODE wird auf EVALUATE INDEXES gesetzt. Diese Einstellung bewirkt, daß der SQL-Compiler die vom Benutzer in die Tabelle ADVISE_INDEX eingefügten Indizes verwendet. Der Benutzer fügt eine neue Zeile für jeden Index ein, der ausgewertet werden soll. Die für jeden Index erforderlichen Informationen sind: Indexname, Tabellenname und die Spaltennamen, aus denen der auszuwertende Index besteht. Wenn das Sonderregister CURRENT EXPLAIN MODE eingegeben wurde, sollte es auf EVALUATE INDEXES gesetzt werden. Der SQL-Compiler durchsucht dann die Tabelle ADVISE_INDEX, nach Indizes, bei denen das Feld USE_INDEX="Y" ist (diese Indizes werden als virtuelle Indizes bezeichnet). Alle im Modus EVALUATE INDEXES ausgeführten dynamischen Anweisungen werden von EXPLAIN so bearbeitet, als wären diese virtuellen Indizes verfügbar. Der SQL-Compiler wählt in diesem Fall die virtuellen Indizes, wenn sie die Leistung der Anweisungen verbessern. Ansonsten werden die Indizes ignoriert. Durch Prüfen der EXPLAIN-Ergebnisse können Sie feststellen, ob die vom Benutzer vorgeschlagenen Indizes vom SQL-Compiler verwendet wurden. Die verwendeten Indizes sollten zur Verbesserung des Zugriffs implementiert werden.
Wenn Sie diese Schnittstelle zum Schreiben von Anwendungen verwenden, können Sie Index Advisor ebenfalls verwenden.
Die Ergebnisse aus Index Advisor bieten drei Verwendungsmöglichkeiten:
Mit Hilfe der folgenden Abfrage können Sie ermitteln, welche Indizes von der Advise-Einrichtung empfohlen wurden:
SELECT CAST(CREATION_TEXT as CHAR(200)) FROM ADVISE_INDEX
Um bessere Empfehlungen für eine bestimmte Abfrage zu erhalten, ist es zu empfehlen, die Advise-Einrichtung nur für diese Abfrage zu verwenden. Sie können sich mit Hilfe des Assistent: Index Indizes für eine einzelne Abfrage empfehlen lassen, indem Sie eine Auslastung erstellen, die nur diese Abfrage enthält.
Eine Beispielauslastung kann der Ausgabe von Event Monitor entnommen werden. Mit Event Monitor können Ausführungen von dynamischen SQL erfaßt werden. Diese Anweisungen können anschließend als Eingabe für die Advise-Einrichtung verwendet werden.
Der Assistent: Index ist eine einfache, benutzerfreundliche visuelle Schnittstelle, die einen hervorragenden Zugriff auf die Advise-Einrichtung bietet.