Bevor Sie mit dem Anlegen von Indexes beginnen, sollten Sie sich zuvor Gedanken über den Aufwand zur Pflege der Indexes machen, als da wären ...

• Indexes benötigen Speicherplatz - große Indexes viel Speicherplatz.
• Jeder Index benötigt einen Index Space und darunterliegende VSAM Datasets und es existieren Einschränkungen in der Anzahl der offenen Datasets des MVS Betriebssystems (10.000 je Address Space).
• Ein Index muß bei jeder Datenänderungen mitgepflegt werden, um Änderungen in seiner Basistabelle zu reflektieren. Wenn eine UPDATE SQL-Anweisung eine Spalte verändert, die Bestandteil eines Index ist, muß der Index ebenfalls verändert werden. Die Gesamtzeit für die Pflege steigt somit entsprechend.
• Indexes müssen während des Laden eines Tables erstellt werden - das kostet Zeit. Sie müssen aus ihrer Basis-Tabelle wiederhergestellt werden, wenn der Tablespace wiederhergestellt wird - diese verbrauch zusätzlich Zeit.

Das Design der Indexes sollte Bestandteil des Database Design sein und nicht vernachlässigt werden. Treten bei SQL-Anweisungen Performance-Probleme auf, stellen Sie sich folgende Fragen:

• Würde das Hinzufügen einer Spalte zu einem Index der Anweisung erlauben Index-Only-Zugriff zu nutzen?
• Benötigen Sie einen neuen Index?
• Ist die Wahl des Aufbaus des Index korrekt?

Empfehlungen für Sortierungen

Man kann häufig Sortierungen vermeiden, wenn Index Keys in der Reihenfolge vorliegen, die von ORDER BY, GROUP BY, einer Join-Ooperation, oder DISTINCT in einer Column-Function benötigt werden. In anderen Fällen, beispielsweise bei Einsatz des List Sequential Prefetch, stellt ein Index keine sinnvolle Sortierung zur Verfügung und die selektieren Daten müssen zwangsläufig sortiert werden.

Wenn es zwingend erforderlich ist Sortierungen zu vehindern, erwägen Sie die Anlage eines Index auf die erforderlichen Spalten und setzen Sie die OPTIMIZE FOR n ROW Anweisung. Lassen Sie uns folgende Query betrachten:

SELECT C1,C2,C3 FROM T1
WHERE C1 > 1
ORDER BY C1 OPTIMIZE FOR 1 ROW;

Ein aufsteigender Index auf der Spalte C1 kann eine Sortierung verhindern. Ein Index auf C1+C2+C3 erfüllt den gleichen Zweck, ermöglicht aber gleichzeitig Index-Only-Zugriff.

Beachten Sie auch die Hinweise im Abschnitt Aufwand und Kosten von Indexes, bevor Sie versuchen mit neuen Indexes versuchen Sort-Operationen zu vermeiden, denn nicht alle Sorts sind ineffizient. Beispielsweise, wenn Indexes nicht effizient genug ist und sehr viele Rows qualifiziert, kann der Optimizer einen anderen Zugriffspfad wählen und stattdessen die Daten ohne Indexzugriff selektieren und anschließend sortieren, was erheblich kostengünstiger sein kann.

Faktoren, die die Sort-Performance beeinflussen und Techniken, die Sorts verbessern können:

• Vergewissern Sie sich, daß die eingesetzen Prädikaten die Daten liefern, die Sie benötigen. Die Einschränkung eines Auswahlergebnisses, die Reduzierung des Result Sets, reduziert natürlich den Sort-Aufwand.
• Wenn Sort durchgeführt werden hängt die Row-Länge von der Anzahl der selektierten Ergebnisspalten ab. Die Reduzierung der Spalten erhöht die Performances des Sort, weil der Umfang der Daten und des Workspaces fällt. Generelle Vorschläge zur Reduzierung der Sort-Row-Länge:

1. Wenn VARCHAR-Spalten nicht benötigt werden, dann verzichten Sie auch auf diese. VARCHARs werden mit Blanks auf ihre maximale Länge aufgefüllt.
2. Minimieren Sie die Anzahl der Sort Key Columns.
3. Minimieren Sie die Anzahl der Sort Data columns.

• Workfiles verfügen über ein vielfältiges Einsatzspektrum und haben Wechselwirkung auf die Sort-Performance. Denken Sie an Global Temporary Tables und Materialized Views. Der Systemadministrator sollte ausreichend physischen Platz bereitstellen und diese Workfiles in einen eigenen Bufferpool legen. Die Isolierung von anderen Objekten vereinfacht das Monitoring und Tuning der Sort-Performance

Anwendungen die Global Temporary Tables (GTT) nutzen, belegen Workfile-Space bis zum einem COMMIT oder ROLLBACK. Wenn Sorts und GTT-Nutzung gleichzeitig auftreten, werden Sie sehr wahrscheinlich mehr
Workfile-Space benötigen.
• Der Systemadministrator sollte die Bufferpool-Größe für Workfile Buffers erhöhen, wenn die Prefetch Rate 4 Page oder weniger beträgt.

• Bei der Nutzung von STOGROUP's nur jeweils ein Volume je Storage Group. Zusätzlich Volumes werden erste genutzt, wenn das erste Volume ausgenutzt wurde.

• Nie mehr als ein physisches Workfile Dataset je DASD Volume anlegen.
• Die Größe des Sort Bufferpool beeinflußt die Sort-Performance. Je größer der Buffer, desto größer die Effizienz von Sorts.
• Planen Sie Ihre Konfiguration um minimale I/O Contention auf den I/O Paths zu den physischen Workfiles sicherzustellen. Verteilen Sie die Workfiles auf unterschiedliche I/O Paths.
• Sind Statistiken nicht aktuell, bringen Sie sie mit dem RUNSTATS Utility auf den aktuellen Stand.

SQL-Abfragen mit Subqueries

Obgleich es keine allgemeinen Regeln für die Entscheidung gibt, ob und wie Subqueries einzusetzen sind, hier eine generelle Hilfestellung:

• Wenn möglich, besser einen Join als eine Subquery nutzen, die die Reihenfolge, in der auf die Tables zugegriffen wird, festlegt. Mit Join kann die jeweils bessere Table-Zugriffsreihenfolge gewählt werden.
• Wenn wirksame Indexes auf Tables in der Subquery zur Verfügung stehen, ist eine Correlated Subquery die wirksamste Art einer möglichen Subquery.
• Wenn keine wirksamen Indexes auf Tables in der Subquery vorhanden sind, dürfte eine Non-Correlated Subquery der wirksamste Zugriff sein.
• Wenn in einer Query mehrere Subqueries vorkommen, stellen Sie sicher, daß die Subqueries in einer sinnvolle Reihenfolge erscheinen. Betrachten wir folgende Query - nehmen wir 1000 Rows in der MAIN_TABLE an:

Unter der Voraussetzung, daß Subquery 1 und Subquery 2 vom selben Typ von Subquery (entweder correlated oder non-correlated) sind, wird DB2 die Subquery-Prädikate in der Reihenfolge ausführen, in der Sie in der WHERE-Bedingung vorkommen. Subquery 1 schließt 10% aller Rows aus, und Subquery 2 schließt 80% aller Rows aus.
Das Prädikat in Subquery 1 (P1) wird 1000 mal ausgeführt, und das Prädikat in Subquery 2 (P2) wird im Anschluß 900 mal ausgeführt, was eine Summe von 1900 Prädikat-Prüfungen ergibt. cks. Wenn man die Reihenfolge der Subqueries umdreht wird P2 zwar ebenfalls 1000 mal ausgeführt, qaulifiziert jedoch nur noch 20% aus der Gesamtmenge, was sch in lediglich 200 Prüfungen in P1 niederschlägt. Die Anzahl der Prädikat-Prüfungen sinkt auf 1200.

Es scheint, die Reihenfolge P2 dann P1 ist günstiger als die Reihenfolge P1 dann P2, sofern der Zeitaufwand je Prädikat gleichartig ist. Ist der Zeitaufwand der Prädikate jedoch sehr unterschiedlich, müssen die Alternativen im Einzelnen geprüft werden.

Wenn Sie sich nicht sicher sind, wenden Sie EXPLAIN auf die Query auf beide Variationen an, mit Correlated und einer Non-Correlated Subquery. Nach Prüfung des EXPLAIN-Ergebnisses und Untersuchung der vorliegenden Struktur Ihrer Daten sollte Sie in der Lage sein, das günstiger SQL Statement zu bestimmen.

Diese Vorgehen kann für alle Typen von Prädikaten angewand werden, da Subquery Prädikate über das Potential verfügen ein Vielfaches von Prozessor- und I/O-Zeit gegenüber anderen Prädikaten zu verschlingen. Prüfen Sie deshalb immer deren Reihenfolge.

DB2 führt alle Non-Correlated Subquery-Prädikate vor Correlated Subquery-Prädikaten aus, unabhängig von deren Reihenfolge in der Query.
 
 

Nutzen Sie Input (Host) Variablen in Prädikaten Ihrer Static SQL Query? Variablen wie Parameter Marker erlauben keine Auskunft über mögliche Werte zur Bind- und Ausführungszeit - vorher auch?! DB2 nutzt deshalb den s.g. Filterfaktor um den besten Zugriffspfad für ein SQL-Statement zu bestimmen.

• Wenn sich dieser Zugriffspfad als ineffizient herausstellen sollte, können Sie erneute Überprüfung (REOPT) für langlaufende Queries (>10 secs Elapsed Time) zur Laufzeit veranlassen.
• Das Binden mit EXPLAIN-Option veranlaßt ein Static Explain. Wollen Sie bereits vor dem Bind den voraussichtlichen Zugriffspfad von Explain ermitteln lassen, also einen Dynamic Bind ausführen, extrahieren Sie das jeweilige Statement und ersetzen die Host-Variablen durch ?. Der Dynamic Explain wird sich dann wie en Static Explain verhalten.

Stellen Sie sicher, daß Ihre SQL Query so einfach und effizient wie möglich formuliert ist. Vermeiden Sie die Auswahl nicht benötigter Spalten, vermeiden Sie unnötige ORDER BY oder GROUP BY Anweisungen.
 
 

Wenn Sie Column Functions einsetzen, sollte diese so einfach wie möglich gestaltet sein - dies erhöht die Wahrscheinlichkeit, daß sie bereits aufgeüfhrt werden, wenn die Daten beschafft werden, nicht erst im Anschluß daran. Grundsätzlich kann man sagen, daß Column Functions am effizientesten sind, wenn sie nicht erste während der Sort-Phase des Statements ausgeführt werden.

Um Column Functions bereits während des Datenzugriffs zu ermöglich, müssen folgende Bedingungen vorliegen:

• GROUP BY benötigt keinen Sort (EXPLAIN-Output prüfen).
• Kein Stage-2 (residual) Prädikat (dies in der Anwendung orüfen).
• Keine Distinct Set Funktion (wie COUNT(DISTINCT C1)).
• Besteht die Query aus einem Join, dann beziehen sich alle Set Functions auf die letzte Tablle des Joins (EXPLAIN-Output prüfen).

• Alle Column Functions beziehen sich auf eine einzige Columns ohne arithmetische Asudrücke, ausgenommen die Column Functions VARIANCE and STTVEV, die niemals während Data Retrieval asugeführt werden.

• Prädikate die Indexes nutzen könnten: Auf Formulierung achten, die Index-Einsatz erlaubt.

• Unabsichtlich redundante oder unnötige Prädikate vermeiden

• Deklarierte Länge von Host Variablen: Stellen Sie sicher, daß die Länge deklarierter Host Variablen (HV) nicht länger ist als das Attribute der Datenspalte, mit der die Host-Variable korrespondiert. Wenn die HV-Länge größer ist, wird das Prädikat 'Stage-2' und kann kein passendes Prädikat für einen Index Scan werden.


Nehmen wird folgende Host Variable und SQL Tabellenspalte an:
Assembler Declaration SQL definition
MYHOSTV DS PLn 'value' COL1 DECIMAL(6,3)

WHERE COL1 = :MYHOSTV

Eine Möglichkeit solche ineffizienten Prädikate zu vermeiden besteht darin Host-Variblen ohn 'Ln' Option zu versehen, also ...
MYHOSTV DS P'123.123'
Dies Garantiert die identische Attributdefinition wie die der SQL Spalte.
 
 

Zurück zum Seitenkopf