Bei der Kompilierung einer SQL-Abfrage kann eine Reihe von Optimierungstechniken verwendet werden, um den effizientesten Zugriffsplan für diese Abfrage zu ermitteln. Die Verwendung weiterer Optimierungsverfahren hat folgende Konsequenzen:
Aus diesem Grund kann es sinnvoll sein, die Anzahl der Techniken, die zur Optimierung der Abfrage verwendet werden, durch Festlegung der Optimierungsklasse zu beschränken. Dies ist besonders unter folgenden Umständen hilfreich:
Sie können jede der unten beschriebenen Abfrageoptimierungsklassen auswählen, die Klassen 0 und 9 sollten jedoch nur in speziellen Situationen verwendet werden. Klasse 5 ist der Standardwert. Die Klassen 0, 1 und 2 verwenden den Algorithmus für schnelle Verknüpfungsaufzählung. Bei komplexen Abfragen berücksichtigt dieser Algorithmus wesentlich weniger alternative Pläne und benötigt deutlich weniger Kompilierungszeit als die Klassen ab Klasse 3. Die Klassen ab 3 aufwärts verwenden den Algorithmus für dynamisch programmierte Verknüpfungsaufzählung. Dieser Algorithmus berücksichtigt wesentlich mehr alternative Pläne und benötigt deutlich mehr Kompilierungszeit als die Klassen 0, 1 und 2 bei steigender Tabellenzahl.
Diese Klasse sollte nur unter speziellen Umständen verwendet werden, wenn der Systemaufwand zur Kompilierung der Abfrage so gering wie möglich gehalten werden muß. Eine Anwendung, die insgesamt aus sehr einfachen dynamischen SQL-Anweisungen besteht, die auf Tabellen mit sehr zweckmäßigen Indizes zugreifen, ist für die Kompilierung mit der Optimierungsklasse 0 zum Beispiel geeignet.
Anmerkung: | Logisches Verknüpfen von Indizes über AND (Index ANDing) wird noch immer bei der Arbeit mit einfachen Gleichheitsverknüpfungen (Semi-Joins) verwendet, die bei Sternverknüpfungen (Star Joins) vorgefunden werden. |
Die Optimierungsklasse 1 ist der Klasse 0 sehr ähnlich, abgesehen davon, daß Mischverknüpfungen und Tabellensuchen ebenfalls verfügbar sind.
Die Optimierungsklasse 2 ist der Klasse 5 sehr ähnlich, sie verwendet jedoch schnelle Verknüpfungsaufzählung und nicht dynamische programmierte Verknüpfungsaufzählung. Diese Klasse hat den höchsten Optimierungsgrad aller Klassen, die mit dem Algorithmus für schnelle Verknüpfungsaufzählung arbeiten, der für komplexe Abfragen weniger Alternativen berücksichtigt und dadurch einen geringeren Kompilierungsaufwand erfordert als die Klassen ab 3 aufwärts. Sie ist daher empfehlenswert für sehr komplexe Abfragen in einer Umgebung für Entscheidungshilfe oder analytische Online-Verarbeitung (OLAP). In solchen Fällen besteht eine hohe Wahrscheinlichkeit, daß dieselbe Abfrage nur selten ausgeführt wird, d. h. der dazugehörige Zugriffsplan bleibt sehr wahrscheinlich nicht bis zur nächsten Ausführung der Abfrage im Cache erhalten.
Diese Klasse eignet sich für eine große Bandbreite von Anwendungen. Durch Verwendung dieser Klasse erhält das Optimierungsprogramm bessere Möglichkeiten, einen günstigen Zugriffsplan für Abfragen mit vier oder mehr Verknüpfungen auszuwählen. Es ist jedoch möglich, daß der vom Optimierungsprogramm gewählte Plan nicht so gut ist wie einer, der mit der standardmäßigen Optimierungsklasse gewählt worden wäre.
Stellt das Optimierungsprogramm fest, daß die zusätzlichen Ressourcen und die Verarbeitungszeit für komplexe dynamische SQL-Abfragen nicht gewährleistet sind, wird die Optimierung reduziert. Das Ausmaß oder der Umfang der Reduzierung hängt von der Maschinengröße und der Anzahl der Vergleichselemente ab.
Reduziert das Abfrageoptimierungsprogramm den Grad an ausgeführter Abfrageoptimierung, verwendet es weiterhin alle Regeln für das Umschreiben von Abfragen, die normalerweise angewandt würden. Es verwendet jedoch die schnelle Verknüpfungszählung und reduziert die Anzahl der Zugriffsplankombinationen, die in Erwägung gezogen werden.
Die Abfrageoptimierungsklasse 5 ist hervorragend für eine gemischte Umgebung geeignet, in der sowohl Transaktionen als auch komplexe Abfragen ausgeführt werden. Diese Optimierungsklasse wurde zur Verwendung der meisten wertvollen Abfragetransformationen und anderer Optimierungstechniken für Abfragen in einer effizienten Weise entwickelt.
Diese Klasse kann die Anzahl der möglichen Zugriffspläne, die vom Optimierungsprogramm ausgewertet werden, erheblich vergrößern. Diese Klasse sollte verwendet werden, um festzustellen, ob eine umfassendere Optimierung zur Generierung eines besseren Zugriffsplans für sehr komplexe und zeitintensive Abfragen auf große Tabellen führt. Die Einrichtung EXPLAIN sowie Leistungsmessungen sollten zur Überprüfung, ob ein besserer Plan gefunden wurde, herangezogen werden.
Die Art, wie eine bestimmte Optimierungsklasse für eine Abfrage angefordert wird, hängt davon ab, ob statisches oder dynamisches SQL verwendet wird.
SET CURRENT QUERY OPTIMIZATION = 1
Um sicherzustellen, daß eine dynamische SQL-Anweisung immer dieselbe Optimierungsklasse verwendet, kann diese Anweisung SET in das Anwendungsprogramm mit aufgenommen werden. Weitere Informationen finden Sie im Handbuch SQL Reference.
Wenn das Register CURRENT QUERY OPTIMIZATION nicht gesetzt wird, werden die dynamischen Anweisungen mit der Standardoptimierungsklasse für Abfragen gebunden. Der Standardwert für dynamisches und statisches SQL wird durch den Wert des konfigurierbaren Datenbankparameters DFT_QUERYOPT festgelegt. Klasse 5 ist die standardmäßige Optimierungsklasse für Abfragen, wenn Sie den Standardwert nicht geändert haben. (Weitere Informationen zu diesem Parameter finden Sie in Standardabfrageoptimierungsklasse (dft_queryopt).) Die Standardwerte für die Bindeoption und die Sonderregister werden dem Konfigurationsparameter DFT_QUERYOPT entnommen.
Die meisten Anweisungen werden in angemessener Weise bei einem sinnvollen Einsatz von Ressourcen unter Verwendung der Standardoptimierungsklasse optimiert. Die Kompilierzeit und der Bedarf an Ressourcen für eine Abfrage werden bei einer bestimmten Optimierungsklasse hauptsächlich durch die Komplexität der Abfrage, besonders durch die Anzahl der Verknüpfungen und Unterabfragen, beeinflußt. Allerdings werden die Kompilierzeit und der Ressourcenbedarf auch vom Grad der durchgeführten Optimierung für verschiedene Optimierungsklassen beeinflußt. Für jede Optimierungsklasse können bei einer sehr komplexen Abfrage größere Unterschiede in der Kompilierzeit und im Ressourcenbedarf erwartet werden als bei einer einfachen Abfrage.
Die folgenden Regeln können Ihnen beim Auswählen der geeigneten Optimierungsklasse behilflich sein:
Beachten Sie, daß die Abfrageoptimierungsklassen 1, 3, 5 und 7 alle für allgemeine Zwecke geeignet sind.
Nur wenn Sie die Dauer der Abfragekompilierung weiter verringern müssen und Sie die Art von SQL (z. B. extrem einfache Anweisungen) kennen, die ausgeführt wird, sollten Sie die Klasse 0 in Betracht ziehen. Dieses SQL hat normalerweise die folgenden Kenndaten:
Transaktionen der Online-Transaktionserarbeitung (OLTP) sind gute Beispiele für diese Art von SQL.
Für komplexe Abfragen können unterschiedliche Grade an Optimierung erforderlich sein, um den besten Zugriffsplan auszuwählen. Für Abfragen mit folgenden Merkmalen kommt möglicherweise eine höhere Optimierungsklasse in Frage:
Abfragen zur Entscheidungshilfe oder Abfragen für Monatsberichte aus vollständig normalisierten Datenbanken sind gute Beispiele für komplexe Abfragen, für die zumindest die Standardoptimierungsklasse verwendet werden sollte.
Ein anderer Grund für die Verwendung höherer Optimierungsklassen ist dann gegeben, wenn das SQL von einem Abfragegenerator erstellt wurde. Viele Abfragegeneratoren erstellen SQL, das nicht effizient ist. Ineffizient geschriebene Abfragen, einschließlich der von einem Abfragegenerator erstellten, können eine zusätzliche Optimierung erforderlich machen, um einen guten Zugriffsplan auswählen zu können. Durch Verwendung der Abfrageoptimierungsklasse 2 oder höher können schlecht geschriebene SQL-Abfragen verbessert werden.
Ein weiterer wichtiger Aspekt ist die Frage, ob statisches oder dynamisches SQL verwendet wird und ob dasselbe dynamische SQL wiederholt ausgeführt wird oder nicht. Für statisches SQL tritt der Aufwand an Kompilierzeit und Ressourcen nur einmal auf, und der ausgewählte Zugriffsplan kann mehrfach verwendet werden. Im allgemeinen gilt, daß für statisches SQL stets die Standardoptimierungsklasse verwendet werden sollte. Dynamische Anweisungen werden zur Laufzeit gebunden und ausgeführt. Daher ist hier zu überlegen, ob der Aufwand für eine zusätzliche Optimierung der dynamischen Anweisungen die allgemeine Leistung verbessert. Wenn dieselbe dynamische SQL-Anweisung jedoch wiederholt ausgeführt wird, wird der ausgewählte Zugriffsplan im Cache zwischengespeichert. Zum Auswählen einer Abfrageoptimierungsklasse kann die Anweisung wie eine statische SQL-Anweisung behandelt werden.
(Weitere Informationen zur Verwendung von statischem und dynamischem SQL finden Sie in Application Development Guide.)
Wenn Sie annehmen, daß für eine Abfrage eine weitere Optimierung von Vorteil wäre, Sie aber nicht sicher sind oder Bedenken hinsichtlich der Kompilierzeit oder des Ressourcenbedarfs haben, können Sie einige Vergleichstests (Benchmarktests) durchführen. Diese Tests können Anhaltspunkte über den Nutzen liefern, der sich durch verschiedene Optimierungsklassen erzielen läßt. Informationen zu allgemeinen Verfahren und zur spezifischen Verwendung des Tools db2batch finden Sie in Kapitel 31, Durchführen von Vergleichstests. Beim Entwerfen und Durchführen des Vergleichstests ist zu berücksichtigen, ob die SQL-Anweisungen in Ihrer Anwendung statisch oder dynamisch sind:
Kompilierzeit + Summe der Ausführungszeiten aller Iterationen -------------------------------------------------------- Anzahl der Iterationen
Die Anzahl der Iterationen ist die Häufigkeit, mit der die SQL-Anweisung Ihrer Schätzung nach jedesmal, wenn sie kompiliert wird, ausgeführt wird.
Anmerkung: | Nach der Erstkompilierung werden dynamische SQL-Anweisungen erneut kompiliert, wenn eine Änderung an der Umgebung eine erneute Kompilierung der Anweisung erforderlich macht. Eine zwischengespeicherte SQL-Anweisung braucht nicht erneut kompiliert zu werden, da nachfolgende PREPARE-Anweisungen (in der Annahme, daß sich die Umgebung nicht ändert) auf die zwischengespeicherte Anweisung zugreifen. (Informationen zu einem Cache, der die Leistung bei der Verarbeitung dynamischer SQL-Anweisungen verbessern kann, finden Sie in Katalog-Cache-Größe (catalogcache_sz) und in Größe des Paket-Cache (pckcachesz).) |
Anmerkung: | Obwohl es vielleicht auch interessant ist, die Kompilierzeit statischer SQL-Anweisungen zu kennen, ist die Gesamtzeit (Kompilier- und Laufzeit) der Anweisung in einem sinnvollen Kontext nur wenig aussagekräftig. Beim Vergleich der Gesamtzeit wird die Tatsache außer acht gelassen, daß eine statische SQL-Anweisung nach jedem Binden viele Male ausgeführt werden kann und daß sie in der Regel nicht während der Laufzeit gebunden wird. |