DB2-SQL

Allgemeine Tips zur Steigerung der SQL
Performance
Tips für Einsteiger und fortgeschrittene SQL-Benutze zu den Themen:
ALLGEMEINE TIPS ZUR STEIGERUNG DER SQL PERFORMANCE...................................... 1
1
DB2 SQL UND PERFORMANCE................................................................................................ 2
1.1
SQL-TUNING UND DIE LOGIK VON ABFRAGEN .......................................................................... 2
1.1.1
“constant propagation”.................................................................................................... 2
1.1.2
Eliminieren von „totem Code“ ......................................................................................... 3
1.1.3
Zusammenfassen von Konstanten („constant folding“).................................................... 4
1.1.4
“case-insensitive” Suchen ................................................................................................ 5
1.1.5
„Sargability“ .................................................................................................................... 6
1.1.6
"Join transitive closure".................................................................................................... 7
1.2
GRUNDSÄTZLICHE EMPFEHLUNGEN ZU DB2-SQL .................................................................... 8
1.2.1
Suche die kleinste „row“-Menge ...................................................................................... 9
1.2.2
Lies nur die Spalten, die wirklich benötigt werden ......................................................... 10
1.3
AUFWAND UND KOSTEN VON INDEXES.................................................................................... 12
1.4
EMPFEHLUNGEN FÜR SORTIERUNGEN ...................................................................................... 13
2
SQL-ABFRAGEN MIT SUBQUERIES ..................................................................................... 15
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
2.10
2.11
2.12
2.13
3
SUBQUERIES SIND ZU „TUNEN“ ................................................................................................ 15
NUTZUNG VON INPUT-VARIABLEN IN PRÄDIKATEN EINER STATIC SQL QUERY ..................... 16
KOMPLEXITÄT VON QUERIES ................................................................................................... 16
SPALTEN-FUNKTIONEN ............................................................................................................ 16
FORMULIERUNG VON PRÄDIKATEN ......................................................................................... 17
DIE VERWENDUNG VON „SCALAR FUNCTIONS“ ....................................................................... 17
NEUORDNEN DER TABELLENFOLGE IN DER FROM KLAUSEL .................................................. 18
„LIST PREFETCH“ ..................................................................................................................... 18
UNCOMMITTED READ“............................................................................................................. 18
“ROW LEVEL LOCKS” ............................................................................................................... 18
FREIGABE VON LOCKS ............................................................................................................. 19
“LOCK ESCALATION” ............................................................................................................... 19
"MATERIALIZED QUERY TABLES"(MQT'S) UND "AUTOMATIC QUERY REWRITE"(AQR)........... 20
DER DB2-KATALOG.................................................................................................................. 21
Allgemeine Tips zur Steigerung der SQL Performance
1 DB2 SQL und Performance
Folgende Empfehlungen zur Konstruktion von SQL-Queries gelten immer dann, wenn eine
der Forderungen Performance heißt. Die Aufzählung ist nicht unbedingt komplett, weist aber
den Weg und die Methode zum Tuning von SQL.
1.1 SQL-Tuning und die Logik von Abfragen
1.1.1 “constant propagation”
Das Transitivitätsgesetz in der mathematischen Logik stellt folgendes fest:
IF
AND
THEN
AND NOT
( A <vergleichsoperator> B ) IS true
( B <vergleichsoperator> C ) IS true
( A <vergleichsoperator> C ) IS true
( A <vergleichsoperator> C ) IS false
<vergleichsoperator> kann dabei sein: =, >, >=, <, <=
NICHT aber: <> oder LIKE (!)
Das Transitivitätsgesetz führt zur Erkenntnis, dass man z. B. C mit B ersetzen kann, ohne eine
Änderung der Bedeutung eines Ausdrucks zu verursachen. Diese Sub- stitution einer
Konstanten nennt man „constant propagation“.
Die folgenden zwei Ausdrücke meinen dasselbe, aber die zweite Formulierung ist besser, da
ein „column“-Name durch ein Literal(5) ersetzt wird:
Fall-1:
SELECT ….
FROM
WHERE
AND
AND
tab_1
t1.col1
t1.col2
t1.col1
t1
<
=
=
t1.col2
t1.col3
5
SELECT ….
FROM
WHERE
AND
AND
tab_1
5
t1.col2
t1.col1
t1
<
=
=
t1.col2
t1.col3
5
Fall-2:
Der Ausdruck in Fall-2 wird auch Transformation des Ausdrucks in Fall1 genannt.
Einige gute DBMS-Systeme führen diese Transformation eigenständig und automatisch durch
(siehe Pkt. Fehler! Verweisquelle konnte nicht gefunden werden.).
Manche DBMS versuchen keine Transformationen, wenn die Gefahr besteht, eine Query
langsamer zu machen, z. B. eine Transformation von Ausdrücken mit NOT:
2 / 21
Allgemeine Tips zur Steigerung der SQL Performance
Fall3:
SELECT ….
FROM
WHERE
AND NOT
Manuelle Lösung:
SELECT ….
FROM
WHERE
AND
AND
tab_1
t1.col1
( t1.col3
t1.col2 )
t1
=
=
tab_1
t1.col1
t1.col3
t1.col2
t1
=
5
<> 7
<> 5
5
7
OR
t1.col1 =
Das transformierte Statement ist ca 50% schneller als das Original. In anderen Worten:
Manchmal zahlt es sich aus Transformationen selbst vorzunehmen.
„constant propagation“ funktioniert manchmal nicht mit „float“ Daten: Sie können sowohl
„größer als“ und „gleich“ sein, wenn numerische Vergleiche gemacht werden. Wenn die
Methode möglich ist, kann man von einem Performance-Gewinn von ca. 5/8 ausgehen.
Andererseits funktioniert die „constant propagation“ oft nicht bei CHAR-Ausdrücken. Wenn
sie möglich ist, ist auch eine Performance-Verbesserung von 4/8 drin.
1.1.2 Eliminieren von „totem Code“
In manchen alten SQL-Programmen findet man auf beiden Seiten des Operators
Literale, wie im folgenden Beispiel:
SELECT
FROM
WHERE
AND
*
tab_1
0
t1.col1
t1
=
=
1
‘I hope we never execute this’
Die Query liefert immer 0 “rows” zurück. DBMS-Systeme könne die gesamte WHEREKlausel ignorieren. Aber nicht alle tun dies. - Ein Test ergab, dass die Query-Formulierung
SELECT
FROM
*
tab_1
t1
ohne WHERE-Klausel einen Performancegewinn von ca. 5/8 brachte.Nun sind die beiden
Queries ja nicht identisch. Dennoch sollte eine Query, die 0 „rows“ liefert schneller sein, als
eine, die ein Resultat mit mehreren „rows“ abzugeben hat und zudem einen TS Scan
durchführen muss.
Das Beispiel zeigt, dass nicht alle DBMS „false“-Bedingungen und ihre abhängigen
Konditionen in der PREPARE-Phase eliminieren.
Dennoch sind die DBMS ziemlich hartnäckig im Eliminieren von „always-true“ Konditionen.
Beispielsweise kann man die Rückgabe von Werten mit geringer Präzision steuern, indem
man eine zusätzliche Bedingung in die WHERE-Klausel mit aufnimmt.
3 / 21
Allgemeine Tips zur Steigerung der SQL Performance
Beispiel:
SELECT
FROM
WHERE
OR
*
tab_1
t1
( 77 / 10 = 7.7 AND t1.col1 / 10 = 7.7 )
( 77 / 10 = 7 AND t1.col1 * 10 = 77 )
Der Performancegewinn kann bis zu 5/8 betragen.
Natürlich kann es zunächst keine gute Idee sein, redundanten Code in ein SQL-Statement
einzubauen.
Aber:
Angenommen eine Spalte ist eine „indizierte NOT NULL“-Spalte. Dann könnte
man das folgende Statement wie folgt transformieren:
SELECT
*
SELECT
FROM
WHERE
*
FROM tab_1
t1
In:
tab_1 t1
indexed_column > 0
Dies wiederum veranlasst DB2 beispielsweise zu einer Suche über Index.
1.1.3 Zusammenfassen von Konstanten („constant folding“)
Jeder, der schon einmal C programmiert hat weiß, dass der Ausdruck x = 1+1-1-1 zu x=0 zum
Compile-Zeitpunkt umformuliert wird. Da mag es überraschen, dass die SQL DBMS Systeme
folgende Ausdrücke nicht ebenfalls auf die gleiche Weise behandeln:
… WHERE
col1
… WHERE
5
… WHERE
col1
… CAST ( 1 AS INTEGER )
… WHERE
‚a’
+ 0
+ 0.0
IN ( 1, 3, 3 )
|| ‚b’
Findet man in altem SQL-Code solche Formulierungen, dann lasse man sie so wie sie sind.
Sie sind geschrieben, um Indexzugriffe zu verhindern, den Datentyp einer „result column“ zu
ändern, eine Limitierung in der Zeilengröße zu überwinden oder – na ja.
Wie auch immer, die Empfehlung für Transformation lautet hier:
… WHERE
a - 3
= 5
… WHERE
a
= 8
nach
umzuwandeln.
4 / 21
Allgemeine Tips zur Steigerung der SQL Performance
1.1.4 “case-insensitive” Suchen
Die meisten Datenbanken unterscheiden bei der Suche nach Objekten und Daten zwischen
Groß- und Kleinschreibung (MS-Access beispielsweise nicht). Die meisten User verwenden
für die sogenannte „case-insensitive“ Suche die Funktion UPPER.
Dies kann jedoch schon ein Fehler sein, in dem Augenblick, wo mit anderen Buchstaben als
denen aus der reinen Lateinschrift gearbeitet wurde.
Beispiel:
… WHERE
… UPPER( ‘résumé’ )
Bei der Transformation geht Information verloren. Die obige Anweisung liefert RESUME als
Ergebnis. Und das ist die Bedeutung des Wortes von „curriculum vitae“(engl.) zu „starte
erneut“. So scheint es besser, die Funktion LOWER zu verwenden, da so keine Information
verloren gehen kann.
Beispiel:
… WHERE
LOWER(col1) = ‘résumé’
Ist man unschlüssig, ob Daten in Groß- oder Kleinschreibung bzw. sogar gemischt auf der
Datenbank abgespeichert sind, so sollte man grundsätzlich Funktionen auf Spalten in der
WHERE-Klausel vermeiden.
Beispiel:
WHERE
col1 = ‘SMITH’
OR col1 = ‘Smith’
Diese Formulierung ist immer noch langsam. Die Empfehlung ist, das DBMS nur dann zum
„case-sensitive search“ zu veranlassen, wenn dies erforderlich ist.
WHERE
col1 = ‘SMITH’
OR ( ‘SMITH’ <> ‘Smith’ AND
Dies bringt eine ungefähre Verbesserung von ca. 3/8.
5 / 21
col1 = ‘Smith’ )
Allgemeine Tips zur Steigerung der SQL Performance
1.1.5 „Sargability“
Die ideale Form eines SQL-Prädikats hat die Form:
<column> <vergleichsoperator> <literal>
IBM nennt solche Prädikate „sargable“. SARG ist eine Abkürzung für „Search ARGument.
Später wurde die Bedeutung auf „kann im Index gesucht werden“ erweitert. Wie auch immer:
Es ergibt sich daraus eine Empfehlung die heißt:
Die linke Seite eines Such-Prädikates sollte möglichst ein einfacher Spaltenname sein;
die rechte Seite sollte einen einfachen Suchbegriff enthalten!
Dem folgend transformieren alle DBMS die folgenden Ausdrücke:
5
=
<col1> - 3 =
<col1>
- <col2>
nach:
nach:
6 / 21
<col1> =
<col1> =
5;
- <col2> + 3; (4/8)
Allgemeine Tips zur Steigerung der SQL Performance
1.1.6 "Join transitive closure"
Auch die Methode des "transitive closure" zum Verbinden von Spalten, stellt eine von SQL
unabhängige Methode der Umformung von Queries dar. Sie ist eine der Methoden der
mathematischen Logik, logische Ausdrücke aufzulösen und zu berechnen, ohne die Logik zu
verändern. Sie könnte auf der Optimizer-Ebene jederzeit implementiert werden
Die folgende Query spezifiziert einen "equi-join" auf t1.c11 und t2.c21, und einen "equi-join"
auf t2.c21 und t3.c31:
select *
from t1, t2, t3
where t1.c11 = t2.c21
and
t2.c21 = t3.c31
and
t3.c31 = 1
Ohne "join transitive closure" wäre die einzige richtige Reihenfolge der Tabellen (t1, t2, t3),
(t2, t1, t3), (t2, t3, t1) und (t3, t2, t1). Fügt man die Joins auf t1.c11 = t3.31 hinzu, wird der
Optimizer versuchen, die Liste der Möglichkeiten zum Join um folgende Aspekte zu
erweitern: (t1, t3, t2) und (t3, t1, t2).
Über die "transitive closure" Regel kann die Bedingung t3.c31 = 1 auch auf die "join
columns" von t1 und t2 angewendet werden.
"Transitive closure" wird normalerweise ausschließlich auf "equ-joins" angewendet. "Join
transitive closure" wird nicht verwendet für:
•
"Non-equi-joins"; beispielsweise, t1.c1 > t2.c2
•
Equi-joins die einen Ausdruck beinhalten; z.B. t1.c1 = t2.c1 + 5
•
Equi-joins mit einer or Klausel
•
Outer joins, z.B. t1.c11 <> t2.c2 oder left join oderr right join
•
Joins über "subquery" Grenzen hinweg
•
Joins zum Prüfen von "referential integrity" oder der with check option auf
"views"
•
Spalten mit "incompatible datatypes"
7 / 21
Allgemeine Tips zur Steigerung der SQL Performance
1.2 Grundsätzliche Empfehlungen zu DB2-SQL
1. Holen Sie das absolute Minimum an Daten in die DB2 „Engine“.
2. Filtern Sie alle „non-qualifying“ Daten aus der Resultatsmenge
3. Geben Sie nur das absolute Minimum an Daten an den „requestor“
zurück
4. Erledigen Sie die Verarbeitung in der kleinsten Menge funktionaler
SQL-Statements
5. Erledigen Sie soviel Funktionalität wie möglich im SQL-Statement
(nicht im Programm)
Alle diese Maßnahmen führen dazu, dass SQL letztlich mit einem Minimum an RessourcenVerbrauch ablaufen kann.
Beispiel:
DECLARE
testcurs1 CURSOR
SELECT
abtnr
,
abtname
,
abtltnr
FROM
abteilung
.....
FETCH testcurs1
INTO :hvn, :hvm :hvo
IF
sqlcode = 0 THEN
DO
LOOP
DECLARE
testcurs2 CURSOR
SELECT
persnr
FROM
mitarbeiter
WHERE
persnr = :hvo
AND
ausbst > 20
...........
FETCH testcurs2
INTO :hv3
...........
IF
sqlcode = 0
THEN <fetch erneut>
ELSE
<loop erneut>
endif
endloop
endif
.........
8 / 21
Allgemeine Tips zur Steigerung der SQL Performance
… das ist so nicht optimal !!!!
Subselect:
SELECT
,
FROM
WHERE
abtnr
abtname
abteilung
abtltnr IN
( SELECT
FROM
WHERE
persnr,
mitarbeiter
ausbst > 20 )
JOIN:
SELECT
,
FROM
,
WHERE
AND
abtnr
abtname
abteilung
mitarbeiter
abtltnr =
ausbst > 20
persnr,
1.2.1 Suche die kleinste „row“-Menge
Nur die Daten sollten den „requestor“/Programm erreichen, die unbedingt gebraucht werden.
Selten kommt es vor, dass alle Daten in ein Programm zu übergeben sind. Häufig ist dies ein
Problem des physischen Design, weil Indexes fehlen oder aus anderen Gründen, die in der
Implementierung liegen. Ein einfaches Beispiel wird im Pkt. 1.2.2 ff geliefert. Oft wird
ähnliches SQL von Code-Generatoren erzeugt.
9 / 21
Allgemeine Tips zur Steigerung der SQL Performance
1.2.2 Lies nur die Spalten, die wirklich benötigt werden
Beispiel:
SELECT
,
,
FROM
WHERE
kd#
name
betrag
kunde
kd#
= :hv
Genügen würde:
SELECT
,
FROM
WHERE
name
betrag
kunde
kd#
= :hv
Es gibt schlichtweg keinen Grund, die „kd#“ zu selektieren, wenn sie bereits bekannt ist –
und das muss sie in diesem Fall ja wohl sein, sonst wäre die „hostvariable“ :hv nicht zu
besetzen gewesen(!) Der Zugriffspfad basiert auf diesen Prädikaten. Um diese zusätzliche
Spalte im Resultat unnötigerweise trotzdem darzustellen, geschieht folgendes:
1.
Lesen aus der „page“ in den Bufferpool
2.
Übertragen aus dem Bufferpool in eine „User Work Area“
3.
Übergabe an Stage2
4.
Übergabe über XMS an das Programm/QMF/SPUFI
Dies bedeutet in jedem Fall unnötige Beschäftigung der CPU.
Hier ist es nur eine „row“, aber wie viele Transaktionen und „rows“ und wie häufig passiert
dies pro Tag ? – Nicht das einzelne SQL-Statement verursacht Schäden im DB2-Umfeld,
vielmehr der Effekt der multiplen Nutzung lässt die Ressourcen knapp werden. Sollte das
vorgenannte SQL in einem AP vorkommen, das mehrere „rows“ zurückgibt und dies noch im
Falle einer „child table“ zur Kundentabelle, dann wird die Situation bedenklich.
Oft tritt der Fall ein, dass das Resultat sortiert angeboten werden muss und die Spalte ist
erforderlich zur Nutzung bestimmter Indexes:
Beipiel:
SELECT ac.kd#
,
ar.rechn#
,
iv.betrag
,
it.teile#
,
it.teile_beschreibung
,
it.teile_kosten
FROM kunde
ac
,
auftrag
ar
,
rechnung
iv
,
teile
it
WHERE ac.kd#
=
:hv1
AND
ac.kd#
=
ar.kd#
AND
ar.rechn#
=
iv.rechn#
AND
iv.teile#
=
it.teile#
ORDER BY
ac.kd#, ar.rechn#, iv.teile#
10 / 21
Allgemeine Tips zur Steigerung der SQL Performance
Es gibt in diesem Fall keine eindeutige Interpretation dessen, was passiert. Die Query bezieht
sich auf eine einzelne „kd#“ und so kann es sein, dass die Spalte „kd#“ im SELECT nicht
erforderlich ist.
Es kann auch sein, dass die „kd#“ im ORDER BY SORT-Probleme verursacht. Dies ist ein
Join über 4 Tabellen und die Chance, dass die „kd#‘ im ORDER BY für die Auswahl
bestimmter Indexes zur Vermeidung von SORTs nötig ist, ist ziemlich unwahrscheinlich.
Diese Query könnte besser wie folgt geschrieben sein:
SELECT ar.kd#
,
ar.rechn#
,
iv.betrag
,
it.teile#
,
it.teile_beschreibung
,
it.teile_kosten
FROM auftrag
ar
,
rechnung
iv
,
teile
it
WHERE ar.kd#
=
:hv1
AND
ar.rechn#
=
iv.rechn#
AND
iv.teile#
=
it.teile#
ORDER BY
ar.kd#, ar.rechn#, iv.teile#
Das Ergebnis ist identisch mit der ersten Query. Die Kundentabelle wird allerdings nicht mehr
verwendet und folglich auch nicht im JOIN eingebunden. Der JOIN ist ein Join über 3
Tabellen anstatt über vier.
Trotz der vielfältigen Möglichkeiten, die aufgrund der vielfältigen Variablen für diese Query
bestehen, könnte man eine weitere Variante für ein besseres SQL wie folgt vermuten:
SELECT iv.rechn#
,
iv.betrag
,
it.teile#
,
it.teile_beschreibung
,
it.teile_kosten
FROM auftrag
ar
,
rechnung
iv
,
teile
it
WHERE ar.kd#
=
:hv1
AND
ar.rechn#
=
iv.rechn#
AND
iv.teile#
=
it.teile#
ORDER BY
iv.rechn#, iv.teile#
Hier wurde die „kd#“ aus dem SELECT-Statement genommen, da der Wert bereits in der
Hostvariablen „hv1“ enthalten ist. Es werden viele „rows“ zurückgegeben und so reduziert die
Wegnahme der „kd#“ den Aufwand für die Query enorm.
Zudem befinden sich nur noch zwei Spalten im ORDER BY anstatt drei und sortiert werden
nur noch fünf Spalten anstatt sechs (siehe SELECT-Statement).
Dies wiederum verkleinert die Anforderungen für SORT im Bufferpool und die I/O
Aktivitäten dort. Es könnte jetzt auch der Fall eintreten, dass der SORT-Pool für das
Sortieren ausreicht, während dies beim ersten Beispiel unwahrscheinlich(er) ist.
11 / 21
Allgemeine Tips zur Steigerung der SQL Performance
1.3 Aufwand und Kosten von Indexes
Bevor Sie mit dem Anlegen von Indexes beginnen, sollten Sie sich zuvor Gedanken
über dc 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 existiert eine Einschränkungen in der Anzahl der offenen Datasets des MVS
Betriebssystems (1000 pro Address Space).
•
Ein Index muß bei jeder Datenänderungen mitgepflegt werden, um Änderungen in
seinen Basistabellen zu reflektieren.
•
Wenn eine UPDATE SQL-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 Tabelle erstellt werden - das kostet Zeit.
•
Indexes müssen und können aus ihrer Basis-Tabelle wiederhergestellt werden,
wenn der Tablespace wiederhergestellt werden muss, dies verbraucht zusätzlich
Zeit.
Empfehlungen:
Das Design der Indexes sollte Bestandteil des Database Design sein und nicht
vernachlässigt Treten bei SQL-Anweisungen Performance-Probleme auf, stellen Sie
sich folgende Fragen:
1. Würde das Hinzufügen einer Spalte zu einem Index einer Anweisung erlauben
Index-Only-Zugriff zu nutzen?
2. Benötigen Sie einen neuen Index?
3. Ist die Wahl des Index-Aufbaus korrekt?
12 / 21
Allgemeine Tips zur Steigerung der SQL Performance
1.4 Empfehlungen für Sortierungen
Sortierungen kann man häufig dann vermeiden, wenn Index Keys in der Reihenfolge
vorliegen, die in
•
ORDER BY,
•
GROUP BY,
•
einer Join-Operation, oder
•
DISTINCT in einer Column-Funktion
benötigt werden. In anderen Fällen, beispielsweise bei Einsatz des List Sequential Prefetch
stehen im Index keine sinnvollen Sortierfolgen zur Verfügung und die selektieren Daten
müssen zwangsläufig sortiert werden.
Ist es zwingend erforderlich, Sortierungen zu verhindern, erwägt man im allgemeinen die
Anlage eines passenden Index für die erforderlichen Spalten und setzt die OPTIMIZE FOR n
RQWS Anweisung.
Beispiel:
Folgende Query:
SELECT
FROM
WHERE
ORDER
C1,C2,C3
T1
C1 > 1
BY
C1
OPTIMIZE FOR 1 ROW;
Ein aufsteigender Index auf der Spalte Cl kann eine Sortierung verhindern. Ein
Index auf Cl + C2 + C3 erfüllt den gleichen Zweck, ermöglicht aber gleichzeitig
Index-Only-Zugriff.
Man beachte in diesem Zusammenhang auch die Hinweise im Abschnitt Aufwand und
Kosten von Indexes, bevor man mit neuen Indexes versucht Sort-Operationen zu
vermeiden, denn nicht alle Sorts sind eine Behinderung. Beispielsweise, wenn ein
Index nicht effizient genug ist und sehr viele Rows qualifiziert werden, kann der
Optimizer einen anderen Zugriffspfad wählen und stattdessen die Daten ohne
Indexzugriff selektieren, anschließend sortieren, und damit u. U. erheblich
kostengünstiger arbeiten.
13 / 21
Allgemeine Tips zur Steigerung der SQL Performance
Empfehlungen:
Faktoren, die die Sort-Performance beeinflussen und Techniken, die Sorts verbessern
können:
•
Die eingesetzen Prädikate sollten die Daten liefern, die benötigt werden: Die
Einschränkung eines Auswahlergebnisses, die Reduzierung des Result Sets, usw.
reduziert den Sort-Aufwand.
•
Wenn Sorts durchgeführt werden hängt die Row-Länge von der Anzahl der
selektierten Ergebnisspalten ab. Die Reduzierung der Spalten erhöht die
Performance des Sort, wobei der Umfang der Daten und des Workspaces ins
Gewicht fallen.
•
Generelle Vorschläge zur Reduzierung der Sort-Row-Längesind:
1. Wenn VARCHAR-Spalten nicht benötigt werden, dann verzichten Sie auch
auf sie. VARCHARs im Index 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 besitzen eine
Wechselwirkung zur Sort-Performance. Man denke an 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 SortPerformance.
•
Anwendungen die Global Temporary Tables (GTT) nutzen, belegen WorkfileSpace bei COMMIT oder ROLLBACK. Wenn Sorts und GTT-Nutzung
gleichzeitig erfolgen, dann wird sehr wahrscheinlich mehr Workfile-Space
benötigen.
•
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 DB2-Umgebung sollte zum Ziel haben, minimale I/O Contention
auf den I/O Paths zu den physischen Workfiles sicherzustellen. Also sollte man die
Workfiles auf unterschiedliche Disk Paths verteilen.
•
Sind Statistiken nicht aktuell, sollte man sie mit dem RUNSTATS Utility auf den
aktuellen Stand bringen.
14 / 21
Allgemeine Tips zur Steigerung der SQL Performance
2 SQL-Abfragen mit Subqueries
2.1 Subqueries sind zu „tunen“
Obgleich es keine allgemeinen Regeln für die Entscheidung gibt, ob und wie Subqueries
einzusetzen sind, hier eine generelle Hilfestellung:
Wenn möglich, ist es besser einen Join als eine Subquery nutzen, da eine
Subquery die Reihenfolge, in der auf Tables zugegriffen wird, streng festlegt. Mit
einem Join wählt DB2 die jeweils bessere Table-Zugriffsreihenfolge.
Wenn wirksame Indexes auf Tables in einer Subquery zur Verfügung stehen, ist
eine Correlated Subquery die wirksamste Art einer möglichen Subquery.
Wenn keine passenden 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, stellt man sicher, dass die
Subqueries in einer sinnvollen Reihenfolge angelegt sind.
Betrachten wir folgende Query auf die Tabelle MY_TABLE:
SELECT
FROM
WHERE
AND
*
MY_TABLE
TYPE
PARTS
IN (subquery 1)
IN (subquery 2);
Unter der Voraussetzung, dass Subquery 1 und Subquery 2 vom selben Typ von Subquery
(correlated bzw. non-correlated) sind, wird DB2 die Subqueryprädikate in der Reihenfolge
auflösen, in der Sie in der WHERE-Bedingung vorkommen.
15 / 21
Allg_Tips zur Steigerung der SQL Performance.doc
2.2 Nutzung von Input-Variablen in Prädikaten einer
Static SQL Query
Werden Input (Host) Variablen in Prädikaten der Static SQL Query genutzt? Variablen wie
Page-Marker erlauben keine Auskunft über mögliche Werte zur Bind- und Ausführungszeit.
DB2 nutzt deshalb vor allem den sogenannten Filterfaktor um den besten Zugriffspfad für
ein SQL-Statement zu bestimmen.
–
•
Wenn sich dieser Zugriffspfad als ineffizient herausstellen sollte, kann man eine
erneute Überprüfung (REOPT VARS) für langlaufende Queries (>10 secs Elapsed
Time) zur Laufzeit veranlassen.
•
Das Binden mit EXPLAIN-Option veranlaßt ein Static Explain. Soll der
voraussichtliche Zugrriffspfad bereits vor dem Bind von Explain ermittelt werden,
also wenn ein Dynamic EXPLAIN gewünscht wird, extrahiert man das jeweilige
Statement und ersetzt die Host-Variablen durch Konstante. Der Dynamic Explain
wird sich dann wie ein Static Explain verhalten.
2.3 Komplexität von Queries
Man sollte sicherstellen, daß eine SQL 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 sollten vermieden werden.
2.4 Spalten-Funktionen
Werden Column Functions eingesetzt, sollten diese so einfach wie möglich gestaltet sein,
damit die Wahrscheinlichkeit, daß sie bereits aufgeführt werden, wenn die Daten beschafft
werden und nicht erst danach, möglichst hoch ist.
Grundsätzlich kann man davon ausgehen, daß Column Functions am effizientesten sind,
wenn sie nicht erst während der Sort-Phase eines SQL-Statements ausgeführt werden.
Um die Ausführung von 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 (residual) Prädikat verwenden (dies in der Anwendung prüfen).
Keine Distinct-Set Funktion (wie.COUNT(DISTINCT Cl)).
Besteht die Query aus einem 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 VARIANCE and STDDEV, die niemals
während Retrieval ausgeführt werden können.
16 / 21
Allg_Tips zur Steigerung der SQL Performance.doc
2.5 Formulierung von Prädikaten
Bei der Formulierung von Query-Prädikaten, gilt es:
•
Prädikate die Indexes nutzen könnten zu verwenden: Auf die Formulierungen achten, die
eine Index-Nutzung ermöglichen
•
Unabsichtlich redundante oder unnötige Prädikate zu vermeiden
•
Deklarierte Längen und Datentypen von Host Variablen exakt beachten: Es muss
sichergestellt werden, daß die Länge und der Datentyp deklarierter Host-Variablen (HV)
nicht ungleich ist dem Attribut der Datenspalte, mit der die Host-Variable korrespondiert.
Wenn diese Werte differieren, wird das Prädikat ‘Stage-2‘ und kann niemals Prädikat für
einen Index-Zugriff werden.
Beispiel:
Nehmen wird folgende Host Variable und SQL Tabellenspalte an:
Assembler Deklaration
SQL definition
MYHOSTV DS PLn ‘value‘
COL1
DECIMAL(6,3)
Die Präzision der Host-Variablen beträgt (2 * n)-1. Bei n = 4 und Wert = ‘123.123‘ würde das
Prädikat, wie nachfolgend dargestellt, kein passendes sein, weil die Präzisionen (7,0 und 6,3)
unterschiedlich sind:
WHERE COLl
=
: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 eine identische Attributdefinition wie die der SQL Spalte.
2.6 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 zu 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.
DB2 ist ein Meister in relationaler Funktionalität aber kein Konvertier- oder
Rechenprogramm.
17 / 21
Allg_Tips zur Steigerung der SQL Performance.doc
2.7 Neuordnen der Tabellenfolge in der FROM Klausel
Die Reihenfolge der Tabellen oder Views in der FROM Klausel kann die Auswahl des Zugriffspfads
beeinflussen. Wenn die Query langsam läuft kann dies deshalb sein, weil die "join sequence"
ineffizient ist. Man kann die "join sequence" innerhgalb eines Query-Blocks aus der Spalte PLANNO
in der PLAN_TABLE .
Eine Neuanordnung der Tabellen oder Views in der FROM Klausel kann zu einer besseren
Performance der Query führen.
Es sollte Dabei darauf geachtetet werden, dass in den verschiedenen JOIN-Verfahren immer die
Tabelle/View mit den kleineren Join-Resultaten als äussere Tabelle beim Join verwendet wird.
2.8 „list prefetch“
“Index screening” ist genau die richtige Medizin für exzessive “list prefetch”-Operationen. „List
prefetch“ erfolgt immer, wenn Indizes nicht genau zur WHERE-Klausel passen.
Beispiel:
Index: c1, c2, c3, c4
WHERE c1 = xx AND c3 = yy AND c4 = zz
Bisher wurde nur die c1 Spalte genutzt, um die RID’s für den „list prefetch“ zu finden. Jetzt werden
auch die Spalten c3 und c4 überprüft, um die entsprechenden RIDs vor dem „list prefetch“ zu
eliminieren. Damit wird die Last für den RID Pool reduziert, über die ansonsten „RID Pool failures“
und andere Probleme hervorgerufen werden.
2.9 Uncommitted read“
Wenn irgend möglich sollte das “isolation level” uncommitted read”(UR) gesetzt werden. UR
vermeidet unnötigen „lock overhead“. Am einfachsten wird die Nutzung von UR bei „read-only“
Daten. Jede Tabelle, die als „read-only“ erkannt und analysiert ist sollte im SELECT-Statement die
Klausel WITH UR enthalten.
Bei langlaufenden Queries auf den Clients können nach Erfahrungen so bis zu 30% CPU-Zeit
gespart werden.
2.10 “row level locks”
Man sollte “row-level locking“ vermeiden wo immer es geht. Oft wird RLL („row level locking“)
genutzt, um Probleme bei der Parallelverarbeitung zu vermeiden. Meist jedoch erzeugt diese
Vorgehensweise mehr Probleme als sie löst.
Besonders, da diese Verfahrensweise zu einer potentiellen Steigerung von „deadlock“-Situationen
führt, indem sie mehr als einen Prozess pro page zulässt.
18 / 21
Allg_Tips zur Steigerung der SQL Performance.doc
2.11 Freigabe von Locks
In n-tier Umgebungen verursachen “locks” die Belegung von aktiven oder inaktiven „threads“ nach
einem Commit. In der Komponente DB2-Connect kann man den Parameter cursorhold auf 0 (kein
“default”) setzen, was dazu führt, dass Sperren nach einem „commit“ aufgehoben werden.
Zusätzlich dazu sollte man den Parameter autocommit überprüfen, der unterschiedlich, z.B. in ERPSystemen, eingesetzt werden kann. So setzen manche ERP Systeme den Parameter autocommit auf 0,
um „commits“ zwischen den SQL-Statements zu unterbinden, da alle „commits“ in der Applikation
ausgelöst werden.
In anderen Fällen ist der autocommit unerlässlich, z.B. in Tuxedo-Umgebungen.
2.12 “lock escalation”
Bei bestimmten TS ist es sinnvoll “lock escalation” auszuschalten. „Lock escalation“ kann in
einigen Situation durchaus zu einer echten Performance-Bremse werden. Wenn man LOCKMAX in
der TS-Definition auf 0 setzt, schaltet man „lock escalation“ für diesen Tablespace aus.
Zudem sollte LOCKSIZE auf PAGE oder ROW gesetzt sein.
Bei "partitioned table spaces" (PTS) sollte das „selective partition locking“(SPL) eingeschaltet
sein (LOCKPART YES). Dies verursacht nur bei den benutzten Partitions Sperrungen, nicht aber auf
allen anderen – auch den nicht verwendeten. Bedingungen, die SPL verhindern, sind:
• Type 1 Index wird im „access path“ verwendet
• Der Plan wurde mit ACQUIRE(ALLOCATE) gebunden
• Der TS wurde mit LOCKSIZE TABLESPACE erstellt
• LOCK TABLE IN EXCLUSIVE MODE wurde ohne PART Angabe gesetzt.
19 / 21
Allg_Tips zur Steigerung der SQL Performance.doc
2.13 "materialized query tables"(MQT's) und "automatic
query rewrite"(AQR)
Materialized query tables sind Tabelle, die Informationen enthalten, die aus anderen Tabellen
gewonnen werden. MQT's speichern Resultate aus vorangehenden Queries , die aufwendige Joins und
Aggregationsoperationen durchführen.
Indem die gewonnene, zusammengefasste Information aufbewahrt und vorgehalten wird, können
MQT's folgende Query-Verarbeitung vereinfachen und die Performance von "dynamic SQL queries"
erheblich verbessern. MQT's sind deshalb insbesonders in "data warehousing applications" zu finden.
Automatic query rewrite ist der Prozess bei DB2, der zur Verarbeitung von Daten aus einer MQT
führt. Wird AQR zugelassen, dann entscheidet DB2 selbst, ob es eine "dynamic query" oder einen
Teil daraus über die Nutzung einer "materialized query table" schneller erledigen kann. Wenn ja, wird
DB2 die Query so umformulieren ("rewrite"), dass anstatt der originalen Tabelle(n) die MQT
verwendet werden kann. Dabei ist zu beachten, dass eine MQT Query-Resultate enthalten kann, die
nicht "ad hoc"-aktuell sind – vor allem, wenn die betroffenen "base tables" nach der Erstellung der
MQT öfter geändert wurden.
20 / 21
Allg_Tips zur Steigerung der SQL Performance.doc
3 Der DB2-Katalog
Es ist wichtig, den Katalog sauber und ordentlich zu verwalten. Normalerweise nämlich ist die Anzahl
„packages“ und DB2-Objekte in einer Produktionsumgebung nicht unerheblich.
Der Katalog sollte keine unnötigen Objekte enthalten und ebenfalls von Zeit zu Zeit reorganisiert
werden. Dies ist Aufgabe einer leistungsfähigen DBA.
Erfahrungen zeigen eine Reduktion von I/O’s um ca. 10 bis 12 Prozent und eine Reduktion von
„elapsed time“ um ca. 11 bis 14 Prozent nach einer Reorganisation.
In einigen Fällen, z.B. bei starker Fragmentierung wurden sogar bis zu 50% Reduktion der I/O
Tätigkeit nach einem REORG des Katalogs erzielt.
21 / 21