DB2-SQL - SK Consulting Services GmbH

SQL- Performance - Tips
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 1 -
Inhaltsverzeichnis
I.
Aufwand und Kosten von Indexes
II.
Empfehlungen zum Anlegen von Indexes
III.
Empfehlungen zu Sortierungen
IV.
Abfragen mit "subqueries"
V.
Die Verwendung von „scalar functions“
VI.
Tips und Hinweise zu SQL
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 2 -
I.
Aufwand und Kosten von Indexes
DB2 kennt 2 Arten von Zugriffen:
•
Indexbasiert und
•
Tablespace-Scan
Der indexbasierte Zugriff ist dem TS-Scan
grundsätzlich vorzuziehen !
ABER:
Bevor man mit dem Anlegen von Indexes beginnt, sollte man sich
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. Es existiert eine Einschränkungen
in der Anzahl der offenen Datasets des MVS Betriebssystems (1000 Address Spaces(=default)).
! Ein Index muß bei jeder Datenänderungen mitgepflegt
werden, um die Änderungen in seinen Basistabellen zu reflektieren. Wenn eine UPDATE-Anweisung eine Spalte verändert und die Spalte 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 einer Table erstellt
werden - das kostet Zeit. Sie müssen und können aus ihrer
Basis-Tabelle wiederhergestellt werden, wenn der
Tablespace wiederhergestellt werden muss, dies verbraucht
zusätzlich Zeit.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 3 -
II. Empfehlungen zum Anlegen von Indexes
Das Design der Indexes sollte Bestandteil des Database Design sein und nicht vernachlässigt werden.
Treten bei SQL-Anweisungen Performance-Probleme auf, stellt
man sich folgende Fragen:
1 . W rde das Hinzuf gen einer S palte zu ei nem Inde x einer
A nweisung erla uben Index-On ly-Zugriff zu nutzen?
2.
W ird ein neuer Index be n tigt?
3.
Is t der Index-A ufbau korrek t?
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 4 -
III. Empfehlungen zu Sortierungen
Man kann Sortierungen häufig vermeiden, wenn Index Keys in
der Reihenfolge vorliegen, die in ORDER BY, GROUP BY, einer
Join-Operation, oder bei einem DISTINCT in einer ColumnFunktion benötigt werden.
In anderen Fällen, beispielsweise bei Einsatz des List /
Sequential Prefetch stehen im Index keine sinnvolle
Sortierungen zur Verfügung und die selektieren Daten müssen
zwangsläufig sortiert werden.
Wenn es zwingend erforderlich ist, Sortierungen zu verhindern,
sollte man die Anlage eines passenden Index für die erforderlichen
Spalten erwägen und die OPTIMIZE FOR n ROW- Anweisung
einsetzen.
Beispiel:
SELEC T
C 1, C 2, C 3
FR OM
T1
W H ERE
C1 > 1
OR D ER B Y C 1
OPTIMIZE F OR 1 R OW ;
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.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 5 -
III. Empfehlungen zu Sortierungen
Desweiteren gilt es zur Reduktion der SORTs folgende Faktoren
zu beachten:
! Vergewissern Sie sich, daß die eingesetzten Prädikate die Daten liefern, die benötigt werden: Jede Einschränkung eines Auswahlergebnisses, die Reduzierung des Result Sets, usw. reduzieren den
Sort-Aufwand.
! Werden Sorts durchgeführt, hängt die Row-Länge von der Anzahl der
selektierten Ergebnisspalten ab. Die Reduzierung der Spalten erhöht
die Performance eines Sort, wobei der Umfang der Daten und des
Workspaces vor allem eine Rolle spielen. Generelle Vorschläge zur
Reduzierung der Sort-Row-Länge:
1.
2.
3.
Wenn VARCHAR-Spalten nicht benötigt werden, dann verzichte man
auf sie. VARCHARs im Index werden mit Blanks auf ihre maximale
Länge aufgefüllt.
Minimieren der Anzahl von Sort Key Columns,
Minimieren der Anzahl der Sort Data columns.
! Workfiles verfügen über ein vielfältiges Einsatzspektrum und haben
eine Wechselwirkung zur Sort-Performance; z.B. Global Temporary
Tables und Materialized Views.
Der Systemadministrator sollte ausreichend physischen Platz bereitstellen und diese Workspaces in einen eigenen Bufferpool legen. Die
Isolierung von anderen Objekten vereinfacht das Monitoring und Tuning der Sort-Performance.
Anwendungen die Global Temporary Tables (GT‘J) nutzen, belegen
Workfile-Space bei einem COMMIT oder ROLLBACK.
Wenn Sorts und GTT-Nutzung gleichzeitig erfolgen, dann benötigt man
sehr wahrscheinlich mehr Workfile-Space.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 6 -
III. Empfehlungen zu Sortierungen
! Der Systemadministrator sollte die Bufferpool-Grösse für Workfile
Buffers erhöhen, wenn die Prefetch Rate 4 Pages oder weniger beträgt.
! Bei der Nutzung von STOGROUP‘s sollte nur jeweils ein Volume je
Storage Group genutzt werden. Zusätzliche Volumes werden erst
genutzt, wenn das erste Volume vollständig belegt 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.
! Die Planung der Konfiguration sollte so erfolgen, dass minimale I/O
Contention auf den I/O Paths zu den physischen Workfiles sicherzustellen. Eine Verteilung der Workfiles auf unterschiedliche Disk
Paths hilft meist.
Sind Statistiken nic ht aktuell, sollte man diese mit dem
RUNSTATS Utility a uf de n aktue lle n Sta nd b ringen.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 7 -
IV. Abfragen mit "subqueries"
4.1 Nutzung von Input-Variablen in Prädikaten einer Static SQL 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. 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önnte man eine 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“.
Um bereits vor dem Bind den voraussichtlichen Zugriffspfad von
Explain ermitteln zu lassen, also einen „Dynamic EXPLAIN“ auszuführen, extrahiert man das jeweilige Statement aus dem Programm und
ersetzt die Host-Variablen durch Konstante. Der „Dynamic Explain“
wird sich dann wie ein „Static Explain“ verhalten.
4.2 Komplexität von Queries
! Man sollte sicherstellen, dass die Query so einfach und effizient wie
möglich formuliert ist.
! Die Auswahl nicht benötigter Spalten und unnötige ORDER BY
oder GROUP BY Anweisungen sind zu vermeiden
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 8 -
IV. Abfragen mit "subqueries"
4.3 Spalten-Funktionen
Werden Column Functions eingesetzt, sollten diese so einfach wie möglich gestaltet sein, damit die Wahrscheinlichkeit, dass sie bereits aufgeführt
werden, wenn die Daten beschafft werden, und nicht erst im Anschluss
daran, möglichst hoch ist. Grundsätzlich kann man sagen, daß Column
Functions am effizientesten sind, wenn sie nicht erst während der SortPhase des Statements ausgeführt werden.
Um Column Functions bereits während des Datenzugriffs zu ermöglichen,
müssen folgende Bedingungen vorliegen:
" GROUP BY benötigt keinen Sort (EXPLAIN-Output prüfen).
" Kein Stage-2-Prädikat verwenden. Dies kann man auch in der Anwendung formulieren.
" Keine Distinct-Set Funktion (wie z.B. COUNT(DISTINCT C1)).
" Beinhaltet die Query einen Join, dann beziehen sich alle Set
Functions auf die letzte Tabelle des Joins (EXPLAIN-Output prüfen).
" Alle Column Functions beziehen sich auf eine einzige Column ohne
arithmetischen Ausdruck, ausgenommen die Column Functions z. B.
VARIANCE und STDDEV, die niemals während Retrieval ausgeführt
werden können.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 9 -
IV. Abfragen mit "subqueries"
4.4 Formulieren von Prädikaten
Manche Formulierungen in den Prädikaten schliessen eine Indexnutzung bei
DB2 aus. Deshalb folgende Empfehlungen:
" Prädikate die Indexes nutzen könnten bevorzugen
" Unabsichtlich redundante oder unnötige Prädikate vermeiden
" Deklarierte Länge von Host Variablen darstellen: Die Länge deklarierter Host-Variablen (HV) darf nicht länger sein als das Attribut der
Datenspalte, mit der die Host-Variable korrespondiert. Wenn die HVLänge größer ist, wird das Prädikat ‘Stage-2‘ und kann niemals passendes Prädikat für einen Index Scan werden.
" Folgende Host Variable und SQL Tabellenspalte sei angenommen:
Assembler D eclaration
MYH OSTV
DS
S QL D efinition
P Ln value
C OL1
D EC IMAL(6,3)
Die Präzision der Host-Variablen beträgt 2n-1. Bei n = 4 und Wert = ‘123.123‘ würde das Prädikat,
kein passendes sein, weil die Präzisionen (7,0 und 6,3) unterschiedlich sind: ... WHERE COL1 =
:MYHOSTV
Eine Möglichkeit solche ineffizienten Prädikate zu vermeiden besteht darin, Host-Variablen ohne
Längenoption zu versehen, also
MYHOSTV
DS P‘l23.l23‘
Dies garantiert die Gleichheit der Attributdefinition
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 10 -
V. Die Verwendung von „scalar functions“
Die Verwendung von skalaren Funktionen, wie SUM, MAX, MIN, AVG,
COUNT, LENGTH, VALUE, CHAR, DATE, DECIMAL, DIGITS usw., sollte
immer mit Vorsicht erfolgen.
Es ist genauestens zu prüfen, ob Programmfunktionen in Anwendungsprogrammen nicht dieselbe Wirkung und Funktionalität besitzen, ohne den
DB2-Kernel unnötig zu belasten..
D B 2 ist ein M eister i n relationa ler F unktio nalit t
aber kein K onvertier- oder R echenprogramm
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 11 -
VI. Tips und Hinweise zu SQL
6.1. Nutzen der Funktionen des "relational calculus"
Beispiel:
... WHERE
PERSNR BETWEEN
"4711" AND "4729"
PERSNR >=
PERSNR <=
AND
ist besser als
... WHERE
6.2
"4711"
"4720"
Vermeiden von Konvertierungen
Beispiel:
Ausbildungslevel ist als SMALLINT definiert
... WHERE
AUSB-STA < 11 AND
AUSB-STA >= 2
ist folgender Formulierung in jedem Fall vorzuziehen
... WHERE
copyright S.K. Consulting GmbH, München
AUSB-STA < 1.1E1 AND
AUSB-STA > 1.3
DB2_SQL_PERF - 12 -
VI. Tips und Hinweise zu SQL
6.3
Benutzung von LIKE-Prädikaten
Vermeiden Sie LIKE-Prädikate die mit "%" oder "_" beginnen.
Beispiel:
... WHERE
NACHNAME LIKE "K%U%"
ist effizienter als
... WHERE
NACHNAME LIKE "%AUS"
6.4. Aritmethische Ausdrücke im Prädikat
Vermeiden arithmetischer Ausdrücke in einem Prädikat
Beispiel:
Addition auf die eigene Hostvariable:
: VAR1 = :VAR1 + 1
...
EXEC
SQL
...
WHERE AUSB-STA
END-EXEC.
>
:VAR1
>
:VAR 1 + 1
ist besser als
EXEC
SQL
...
WHERE AUSB-STA
END-EXEC.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 13 -