IBM DB2 9 and Windows XP

Database Tuning & Administration
von Andreas Engl und Dennis Stratmann
Vorbereitung
Wir haben uns in unserer Projektarbeit mit DB2 Express Edition von IBM beschäftigt. Unsere Aufgabe
bestand aus mehreren Teilen. Am Anfang mussten wir die CDDB Datenbank einbinden und
verschiedene Typen von Queries an der Datenbank testen und eine erste Optimierung vornehmen. Sie
diente dazu das System kennen zu lernen und sich mit den Funktionen vertraut zumachen. Die zweite
Aufgabe bestand in der eigentlich Aufgabe. Man musste ein durch den TPCH Benchmark erstellte
Datenbestand in das System einbinden und darauf ein paar der 22 von QGen erstellten Queries testen
und das System speziell für diese optimieren.
Zuerst haben wir Debian und Ubuntu installiert. Leider verlief die Installation von DB2 Express
Edition nicht so wie wir es erhofft haben. Das Problem waren die Zugriffsrechte auf die DB2 Daten.
Nachdem wir dieses Problem behoben hatten und DB2 installiert hatten, konnten wir keine neue
Datenbanken anlegen und auch sonst nichts erstellen oder ändern.
Frustriert entschieden wir uns für Windows XP Professional und diesmal verlief alles problemlos.
Beim ersten Start von DB2 Control Center haben wir eine Datenbank TEST angelegt und ein bisschen
mit den Einstellungen rumgespielt. allerdings nichts nennenswertes geändert.
Nun haben wir die cddb.sql analysiert und die einzelnen Tables in verschiedene Dateien kopiert.
Außerdem haben wir die cddb.sql auf die wesentlichen CREATE Statements reduziert.
Danach haben wir als erstes die Tables artist2album, artist2num_album, cds und cdtracks bearbeitet.
Hier konnte man sehr leicht die Daten so ändern, das man es leicht mit DB2 einlesen konnte. Sprich die
Zwischenräume mit ; auswechseln. Daraufhin haben wir die Tables in der Datenbank erstellt und die
geänderten Daten eingelesen.
Die restlichen Tables haben wir dann mit mit einen Python Skript geändert und dann eingefügt.
Um die 'Primary'- und 'Foreign Keys' richtig zu erstellen mussten einige Änderungen an
den Daten vorgenommen werden.
So sind z.B. die Daten in der Table 'songs' und 'song' identisch, bis auf das der Table
'songs' sehr viele Duplikate enthält. Daher haben wir den Table 'songs' weggelassen und
nur mit dem Table 'song' gearbeitet. Dieser hatte allerdings auch ein Duplikat, den
wir mit der Query
SELECT songid,
COUNT(songid) AS NumOccurrences
FROM DBTUNER.SONG
GROUP BY songid
HAVING ( COUNT(songid) > 1 );
gefunden haben. Das war auch gleichzeitig die erste Optimierung. Wir haben zwar die
Zeit vorher nicht gemessen, jedoch wird es durch die Keyerstellung eine signifikante
Verbesserung gegeben haben.
Um die Zeit genau zu messen kann man bei DB2 die Datei 'DB2Batch' benutzen. Diese ruft man
folgendermaßen auf:
C:\Program Files\IBM\SQLLIB\BIN\DB2Batch.exe -d Test -f C:\dbtuner\DB2Batch.sql
-d Um die Datenbank aufzurufen und -f um ein SQL-File zu laden.
Unser erster Test mit der Query
select artist, song, album, count(*) as num
from artists art1, song, albums alb, artist2album art2, cds, cdtracks cdt
where art2.artistid=art1.artistid and art2.albumid=alb.albumid and
art2.artist2albumid=cds.artist2albumid and song.songid=cdt.songid and cdt.cdid=cds.cdid
group by artist, song, album
having count(*) > 30;
ergab 125.535877 seconds
Die hier gemessenen Zeiten sind nicht die realen Zeiten. Denn wenn man die Zeit mehrmals
misst, kommen immer andere Zeiten raus, sogar mit sehr großen Unterschieden (40 sek.).
Wenn man dieselbe Query mit der Oberfläche und nicht db2batch laufen lässt, benötigt
diese ca. 16 sek.
Optimierung anhand des TPCH Benchmarks
Einführung
Als erstes haben wir versucht mit Cygwin die Daten zu erstellen. Doch hier gab der C Compiler immer
sehr viele Fehler aus. Wie Christian später in einer Mail geschrieben hat, lag das nur an der
Einstellung 'WIN32' unter 'Machine' in der 'makefile.suite'.
Nun haben wir die Tables erstellt. Die Create Befehle dazu sind in der 'dss.ddl' gespeichert
und anschließend die Tables einzeln importiert. Eigentlich gab es keine Probleme,
allerdings haben wir die 'dss.ddl' erst recht spät entdeckt, wodurch wir uns erst Gedanken
gemacht haben, ob wir jeden Table selbst erstellen müssten.
Um die von Qgen erzeugten Queries zu testen mussten sie teilweise umgeschrieben werden. So gab es
immer Probleme wenn in der Query der Ausdruck 'date' vor kam, da die Syntax in db2 anders ist.
Außerdem machte er Probleme, wenn man aus einem Datum speziell z.B. das Jahr auslesen möchte.
Die für das Seminar zur Verfügung gestellten Rechner stießen bei diesem Benchmark doch sehr schnell
an ihre Grenzen. Außerdem hatten wir große Probleme mit der Grafischen Oberfläche DB2 Control, da
diese bei DROP oder ALTER Befehlen sehr oft abstürzte. Wir entschieden uns also für einen anderen
Rechner, ein Laptop mit folgenden Spezifikationen:
●
Dual Core 2 Ghz mit 2 MB Cache
●
2 GB Ram
●
120 GB HDD mit 5400 rpm
Die Oberfläche stürzte zwar weniger ab, jedoch musste man auch hier sehr viel Zeit investieren, um
gewisse Queries zu testen. Nach einen ersten Durchlauf aller Queries mit der 1 GB Version der TPCH
Datenbank entschieden wir uns für eine kleinere Version, die 500 MB Version.
Alle Queries die bis 200 gehen brauchten eigentlich noch wesentlich länger (min. 10 Minuten), jedoch
haben wir leider die originalen Zeiten nicht mehr. Aber beispielsweise haben wir Query 16 ohne
Primary- und Foreignkeys und Indizes nach 4 Stunden abgebrochen. Mit Primary- und Foreignkeys
benötigte sie dann nur noch ca. 3 Sekunden. Das wäre natürlich interessant gewesen zu untersuchen
warum, aber ohne den Ausführungsplan beider Beispiele, konnte man dies nicht.
Allgemein zu unserem Vorgehen bei der Optimierung kann man folgendes sagen:
●
Eine Query wurde “am Stück“ getestet, das heißt alle Optimierungen iterative durchgeführt und,
wenn es eine zeitliche Verbesserung gab die Optimierung beibehalten, ansonsten wieder
verworfen
●
Alle Queries 11-mal durchlaufen lassen und den Durchschnitt gebildet ohne den ersten Lauf
●
Für die Zeitmessung haben wir “TOAD for DB2“ benutzt, da DB2batch die Zeiten nicht richtig
gemessen hat. Jedoch hatte auch TOAD seine Schwäche. So konnte man zum Beispiel Queries
nicht mehrmals durchlaufen lassen. Dazu musste man die Query mehrmals hintereinander
schreiben.. Somit konnte man auch nicht ohne Zuhilfenahme eine Taschenrechners den
Durchschnitt berechnen.
●
Problematisch war auch immer ein Systemneustart nach einem Absturz, da teilweise große
Zeitunterschiede nach dem Neustart an derselben Query zutage kamen – teilweise doppelt so
lang. Falls das der Fall war, mussten wir natürlich mit dem neuen Wert weitermachen und das
Gesamtergebnis wurde verfälscht. Leider haben wir dafür keine vernünftige Erklärung
gefunden.
Die grafische Oberfläche bot einige Optionen der Optimierung an. Zum Einen konnte man direkt alle
Parameter der Datenbank ändern. Jedoch brauchte man dazu schon einiges an Kenntnis der DB2
Datenbank. Es gab auch die Möglichkeit über einen Advisor und Optionen die Optimierungen
vorzunehmen. Hauptsächlich bestanden diese aus folgenden:
●
●
●
●
●
●
Indizes, aber keine verschiedenen Indizes, sondern nur eine Art mit verschiedenen Optionen
○ Index auf eine Spalte
○ Index über mehrere Spalten
○ Beide haben jeweils die drei Optionen: Cluster, Reverse Links, Page-Split-Verhalten
○ das Page-Split-Verhalten war nochmal unterteilt in: Symmetrisch, Hoch, Niedrig
Die Bufferpoolsize. Sie ist standardmäßig auf Self-Tuning (wahrscheinlich 250) eingestellt
○ Leider ist diese Self Tuning Option nicht optimal, da bei manuellem Umstellen bessere
Werte erzielt werden konnten
Tablespace Pagesize. Sie ist standardmäßig auf 4 KB Pages
Runstats zur Kosten Berechnung
Configuration Advisor, der eine Menge an Optionen bietet und am Schluss anzeigt welche
Parameter geändert wurden
○ Ramzuweisung, standardmäßig auf 25 % des RAMs für eine Datenbank
○ Workload - ob die Datenbank mehr für Data Warehousing oder Transactions benutzt wird
○ Recovery Options sind:
■ Schneller Transaction Perfomance (langsameres Recovery)
■ Langsamere Transaction Perfomance (schnelleres Recovery)
○ Wie viele Connections es durchschnittlich zur Datenbank gibt
○ Locking Optionen:
■ Repeatable Read (viele locks langer Dauer)
■ Read stability (wenige locks langer Dauer)
■ Cursor stability (viele locks kurzer Dauer)
■ Uncommited read (keine locks)
○ Liste der Parameter die verändert werden wird ausgegeben
Reorganisieren der Tabellen, entweder durch kopieren oder inkrementelles reorganisieren
Es gab aber auch Optimierungen von denen wir gerne gebraucht gemacht hätten, aber in der DB2
Express Edition nicht angeboten werden.
●
●
●
●
●
System logging. Man kann zwar Parameter umstellen, aber nicht komplett deaktivieren
Materialized View erstellen
Andere Datenstrukturen nicht wählbar
Andere Indizes erstellen, z.B. Textindex. Die Möglichkeit war vorhanden, jedoch ließ sie sich
nicht aktivieren und leider gab es auch keine Infos, ob es ein Volltextindex oder auf Attribute
ist, da nur eine Fehlermeldung kommt. Und die Website zur Fehlermeldung ist nicht informativ
Direktes eingreifen in die Ausführungspläne
Um eine Query zu optimieren muss man sich den Ausführungsplan anschauen. Dies ist in DB2 die
einzige Möglichkeit zur Analyse der Query. Hier kann man dann zum Beispiel anhand der Selectivität
feststellen, ob etwas optimiert werden kann.
Wie in den Vorträgen der anderen Datenbanken gezeigt wurde, haben diese wesentlich bessere
Möglichkeiten und es wird sogar angezeigt was man verbessern sollte. Hier besteht bei der DB2
Express Edition auf jeden Fall noch sehr viel Nachholbedarf.
Nun folgen drei Queries, die wir speziell zur Optimierung gewählt haben.
Query 5:
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as Einkommen
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and o_orderdate >= date ('1995-01-01')
and o_orderdate < date ('1996-01-01')
group by
n_name
order by
Einkommen desc;
Eigentlich hat Query 5 nicht viel besonderes, aber wir wollten ja auch mit einer “normalen“ Query mal
gearbeitet haben. Interessant ist noch, dass fast alle Tables gejoint werden und wir testen können, ob
mehr Cache vielleicht besser ist, und das “o_orderdate“ eine unterbrochene Reihe darstellt. Was
vielleicht auch noch interessant gewesen wäre, ist ein Textindex auf “r_name“, da dieser Table aber so
klein ist, nicht relevant.
Außerdem haben wir beim ersten Ausführen eine Differenz der Zeiten mit und ohne Primary- und
Foreignkeys festgestellt, wobei sie Ohne schneller war. Dem mussten wir auf den Grund gehen.
Als erstes der Ausführungsplan ohne Primary- und Foreignkeys.
Wie man sehen kann, unterscheidet DB2 die unterschiedlichen Operationen mit verschiedenen
Symbolen und Farben. Da alles beschriftet ist, ist die kein Problem zu verstehen. Beim nächsten
Ausführungsplan kommt dann noch das Symbol für Indizes dazu.
Interessant und traurig zugleich ist, das DB2 anscheinend nur Hashjoins und Nested-Loop Joins kennt.
Hashjoins werden bei unsortierten Tables angewendet und Nested-Loop Joins, wenn beide Tables
sortiert sind. Das kann natürlich nicht immer optimal sein. Ein Punkt den man in DB2 Express Edition
auch noch verbessern kann. Und hier wäre es auch interessant direkt zu sagen, welchen Join er wo
ausführen soll, oder wann er wo sortieren soll.
Die durchschnittliche Zeit zum Ausführen beträgt 4.384 Sekunden. Diese vergleichsweise lange Zeit
entsteht durch den linken Teil des Baumes. Wie man sieht sind die Kosten für den ersten Join zwischen
LINEITEM und SUPPLIER schon riesig. Und das wird natürlich an die nächsten weitergeben. Hier
besteht also ein großes Potenzial an Optimierung, denn von LINEITEM wird eigentlich nur l_suppkey
und l_orderkey benötigt.
Jetzt der Ausführungsplan mit Primary- und Foreignkeys
Die durchschnittliche Zeit zum Ausführen beträgt 1.802 Sekunden. Und wie man sieht wird hier der
Primary Key von LINEITEM benutzt und somit konnte auch mit dem Nested-Loop gejoint werden,
was die Kosten drastisch senkt.
Interessanterweise benötigte der erste Lauf 15.544 Sekunden. Leider können wir nicht sagen, warum
dieser Lauf soviel länger braucht.
Aber die durchschnittliche Zeit mit Keys ist auf jeden Fall besser, ca. 150 %. Nun kann man aber noch
versuchen die Zeit zu reduzieren, indem man sich die Joins mit hohen Kosten anschaut und Indizes
erstellt.
Der Ausführungsplan mit Indizes
Index auf ORDERS mit reverse links im Cluster : 00:01.250
Index auf SUPPLIER mit reverse links im Cluster : 00:01.250
Index auf CUSTOMER mit reverse links im Cluster : 00:00.617
Index auf LINEITEM mit reverse links im Cluster : 00:00.468
Index auf REGION mit reverse links im Cluster : 00:00.476 (wieder gelöscht)
Index auf NATION mit reverse links im Cluster : 00:00.484 (wieder gelöscht)
●
●
●
●
Iterative Indizes hinzugefügt. Wenn besser oder gleich geblieben gelassen, ansonsten gelöscht.
Andere Möglichkeit wäre die Verbesserung jedes Indizes einzeln zu testen, haben wir aber nicht
gemacht
Indizes sind im Cluster und reverse links, weil wir mehrere Versuche gemacht haben und alle
Optionen durchgegangen sind (also normal, nur Cluster, nur reverse links, Cluster und reverse
links) und sich diese Variante immer als die schnellste herausgestellt hat, allerdings liegt der
Unterschied im einstelligen Milliesekundenbereich selten im Zweistelligen
exakter Index auf r_name hat verlangsamt genauso wie ein Index über mehrere Attribute weil
die Kosten sehr niedrig sind
exakter Index o_orderdate war nicht so schnell wie ein Index über mehrere Attribute, in diesen
Fall o_custkey, o_orderkey und o_orderdate
●
●
●
allgemein war fast immer der exakte Index langsamer als ein Index über mehrere Attribute,
später kommt ein Beispiel bei dem es anders war.
Der Primary Key auf LINEITEM war langsamer als der Index über mehrere Attribute
REGION und NATION Index langsamer, weil niedrige Kosten
Nachdem wir nun die Daten selbst optimiert haben, versuchen wir nun noch durch die
Datenbankoptionen bzw. umstellen der Daten eine Verbesserung zu erzielen.
●
Bufferpoolsize (4 KB Pages) von 250 auf 20282
○ die 20282 kamen von einem vorherigen Test der hier nicht auf Folie ist. Da hat der
Configuration Advisor diesen Wert als optimal angegeben. Nach mehreren Test stellte sich
heraus das dieser Wert für diese Query am Besten geeignet war.
■ Durchschnittliche Zeit zum Ausführen: 00:00.468
●
Mit “Cofiguration Advisor“ optimiert
○ Configuration Advisor konnte keine Optimierung mehr erzielen, weil schon sehr kurze
Anfragezeit und im Ausführungsplan stand dass z.B. nicht mehr als 20 000 Bufferpages
benutzt wurden
■ Durchschnittliche Zeit zum Ausführen: 00:00.468
●
Reorganisieren der Tables
○ Wie uns Prof. Scholl nach dem Vortrag ja sagte, ist Reorganisieren ein bisschen anders zu
verstehen als wir es verstanden haben. Von daher wahrscheinlich der schlechtere Wert.
■ Durchschnittliche Zeit zum Ausführen: 00:00.500
●
Runstats auf Tables und Indizes
■ Durchschnittliche Zeit zum Ausführen: 00:00.414
●
Tablespace Page Size von 4 KB auf 8 KB und Bufferpoolsize auf 250
○ Die Tablespace Pagesize war nicht so leicht umzustellen. Man musste neuen Tablespace
anlegen und dann alle Tabellen neu erstellen oder alte kopieren. Dann alle Indizes neu
erstellen und alle vorherigen Optimierungen durchführen (runstats und Configuration
Advior)
■ Durchschnittliche Zeit zum Ausführen: 00:00.452
●
Bufferpoolsize (8 KB Pages) von 250 auf 20282
■ Durchschnittliche Zeit zum Ausführen: 00:00.390
●
Tablespace Page Size von 8 KB auf 16 KB und Bufferpoolsize auf 250
■ Durchschnittliche Zeit zum Ausführen: 00:00.437
●
Bufferpoolsize (16 KB Pages) von 250 auf 20282
■ Durchschnittliche Zeit zum Ausführen: 00:00.406
○
○
○
○
○
○
○
○
○
○
○
ohne keys: 4384 ms
mit keys: 1802 ms
indizes: 468 ms
buffer: 468 ms
config: 468 ms
reorg: 500 ms
runstats: 414 ms
tablespace 8KB: 452 ms
buffer: 390 ms
tablespace 16 KB: 437 ms
buffer: 406
(schlechteste)
(beste)
Wie man sieht konnte nochmal eine kleine Verbesserung nur durch die Datenbank erhalten werden.
Doch am Schluss wird noch mehr dazu gesagt.
Query 2
select
s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from
part, supplier, partsupp, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 5
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp, supplier, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
)
order by
s_acctbal desc, n_name, s_name, p_partkey;
Query 2 ist interessant, weil
● Subquery, wobei sich p_partkey aus der Subquery auf die Hauptquery bezieht
● %BRASS wird benutzt, und Wildcards sind immer schwer zu optimieren
● extremer Zeitunterschied nachdem Primary- und Foreignkeys hinzugefügt wurden
● exakte Indizes möglich oder sinnvoll ?
Als erstes wieder ohne Primary- und Foreignkeys
Durchschnittliche Zeit zum Ausführen beträgt 1:27.199 Minuten, was für eine “normale“ Query ohne
großer Besonderheiten eigentlich inakzeptabel ist. Aber wie man auf dem Plan sieht liegt das an dem
NLJoin von dem Table PART und dem ganzen Subtree unter dem Group By. Und diese Kosten ziehen
sich dann nach oben durch. Dort ist also ein sehr großes Potenzial zur Optimierung.
Und jetzt wieder mit Primary- und Foreignkeys
Durchschnittliche Zeit zum Ausführen beträgt jetzt nur noch 0.148 Sekunden. Das entspricht ca. einer
100 000 % Verbesserung. Das liegt zum Einen daran, dass der vorherige Subtree nun nicht mehr mit
PART gejoint wird, sondern erst ganz zum Schluss mit dem anderen Subtree gejoint wird. Und zum
Anderen daran, dass eben der Primary Key auf PARTSUPP benutzt wurde um mit PART zu joinen.
Und nun der Ausführungsplan mit Indizes
Index auf PART mit reverse links im Cluster : 00:00.192 (wieder gelöscht)
Exakter Index auf PART p.size mit reverse links im Cluster : 00:00.085
Index auf SUPPLIER mit reverse links im Cluster : 00:00.125 (wieder gelöscht)
Index auf PARTSUPP mit reverse links im Cluster : 00:00.078
Index auf NATION mit reverse links im Cluster : 00:00.093 (wieder gelöscht)
Index auf REGION mit reverse links im Cluster : 00:00.078
Auch hier haben wir die Indizes iterativ hinzugefügt. Interessanterweise war hier der exakte Index auf
PART.p_size besser, als ein Index ueber mehrere Attribute. Aber exakte Indizes von PART.p_type und
REGION.r_name waren langsamer.
Die Indizes waren also wieder ein Erfolg. Als nächstes werden dann wieder die Optionen der
Datenbank aufgeführt.
●
Bufferpoolsize (4 KB Pages) von 250 auf 5000
■ Durchschnittliche Zeit zum Ausführen: 00:00.078
●
Mit “Cofiguration Advisor“ optimiert
○ Bufferpoolsize und Configuration Advisor konnten keine Optimierung mehr erzielen, weil
schon sehr kurze Anfragezeit und im Ausführungsplan stand dass z.B. nicht mehr als 4 000
Bufferpages benutzt wurden
■ Durchschnittliche Zeit zum Ausführen: 00:00.078
●
Reorganisieren der Tables und Indizes
■ Durchschnittliche Zeit zum Ausführen: 00:00.084
●
Runstats auf Tables und Indizes
○ Runstats brachte diesmal keine Verbesserung. Und wir mussten Runstats auf den exakten
Indiex p.size nach 15 h abbrechen.
■ Durchschnittliche Zeit zum Ausführen: 00:00.078
●
Tablespace Page Size von 4 KB auf 8 KB und Bufferpoolsize auf 250
■ Durchschnittliche Zeit zum Ausführen: 00:00.100
●
Bufferpoolsize (8 KB Pages) von 250 auf 20282
■ Durchschnittliche Zeit zum Ausführen: 00:00.084
●
Tablespace Page Size von 8 KB auf 16 KB und Bufferpoolsize auf 250
■ Durchschnittliche Zeit zum Ausführen: 00:00.078
●
Bufferpoolsize (16 KB Pages) von 250 auf 20282
■ Durchschnittliche Zeit zum Ausführen: 00:00.078
Ein bisschen komisch ist, dass Tablespace Pagesize mit 4 KB Pages und 16 KB Pages gleich schnell
sind und mit 8 KB Pages langsamer. Sinnvoll ? Messfehler ? VIRUS !!! Wir haben keine gute
Erklärung, aber der Unterschied ist auch nicht so groß.
○
○
○
○
○
○
○
○
○
○
○
ohne keys: 87199 ms
mit keys: 148 ms
indizes: 78 ms
buffer: 78 ms
config: 78 ms
reorg: 84 ms
runstats: 78 ms
tablespace 8KB: 100 ms
buffer: 84 ms
tablespace 16 KB: 78 ms
buffer: 78 ms
(schlechteste)
(beste)
(beste)
(beste)
(beste)
(beste)
(beste)
Query 7
select
supp_nation, cust_nation, l_year, sum(volume) as revenue
from (
select
n1.n_name as supp_nation, n2.n_name as cust_nation,
year(l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume
from
supplier, lineitem, orders, customer, nation n1, nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'CHINA' and n2.n_name = 'JAPAN')
or (n1.n_name = 'JAPAN' and n2.n_name = 'CHINA')
)and l_shipdate between date ('1995-01-01') and date ('1996-12-31')
) as shipping
group by
supp_nation, cust_nation, l_year
order by
supp_nation, cust_nation, l_year;
Query 7 ist interessant, weil
● Subquery als Materialzed View möglich (zu spät gemerkt das bei uns doch nicht)
● range Index l_shipdate möglich oder sinnvoll ?
● exakte Indizes möglich oder sinnvoll ?
Zuerst der Plan ohne Primary- und Foreignkeys
Durchschnittliche Zeit zum Ausführen 8.637 Sekunden. Diese verhältnismäßig lange Zeit ergibt sich
aus dem linken Subtree, bei dem die zwei größten Tables gejoint werden und sich das natürlich wieder
bis nach ganz oben durchzieht.
Mit Primary- und Foreignkeys
Durchschnittliche Zeit zum Ausführen beträgt 3.340 Sekunden. Das ist immerhin ein Zeitunterschied
von ca. 5 Sekunden. Wie man sieht braucht zum Beispiel ORDERS durch den Primary Key nur noch
15,17 Timerons + 22,74 Timerons. Vorher brauchte dieser mehr als 23 000 Timerons.
Jedoch besteht auch hier noch sehr viel Potenzial zum Optimieren, denn wenn man sich zum Beispiel
LINEITEM anschaut, sieht man das man 1. ein Index brauchte und 2. man es vielleicht weiter nach
oben in dem Plan schieben könnte.
Wir haben auch hier mal versucht eine normale View der Subquery zu erstellen. Dies änderte allerdings
nichts an den Zeiten. Wenn man dabei an das Konzept denkt, wie wir die Queries hintereinander
getestet haben, (also die Query elf mal hintereinander geschrieben haben und dann ausgeführt), kann
man davon ausgehen, dass DB2 die Subquery automatisch im Cache behält.
Materialzed View wäre hier auch wieder interessant gewesen.
Und nun mit Indizes
Index auf LINEITEM mit reverse links im Cluster : 00:01.137
Index auf ORDERS mit reverse links im Cluster : 00:00.683
Index auf CUSTOMER mit reverse links im Cluster : 00:00.633
Index auf NATION mit reverse links im Cluster : 00:00.633
Index auf SUPPLIER mit reverse links im Cluster : 00:00.625
Die Indizes auf LINEITEM und ORDERS stechen hier heraus. Auch durch die veränderte Struktur im
Ausführungsplan ist diese Zeit dann erreicht worden, denn LINEITEM wird jetzt erst fast am Schluss
mit dem Rest gejoint. Dazu konnten wir zwar nicht wirklich beitragen, aber der Queryoptimizer :-)
Und jetzt wieder die Optimierung der Datenbank anhand von Einstellungen.
●
Bufferpoolsize (4 KB Pages) von 250 auf 20282
○ Durchschnittliche Zeit zum Ausführen: 00:00.625
●
Mit “Cofiguration Advisor“ optimiert
○ Durchschnittliche Zeit zum Ausführen: 00:00.605
●
Reorganisieren der Tables
○ Durchschnittliche Zeit zum Ausführen: 00:00.655
●
Runstats auf Tables und Indizes
○ Durchschnittliche Zeit zum Ausführen: 00:00.676
●
Tablespace Page Size von 4 KB auf 8 KB und Bufferpoolsize auf 250
○ Durchschnittliche Zeit zum Ausführen: 00:00.734
●
Bufferpoolsize (8 KB Pages) von 250 auf 20282
○ Durchschnittliche Zeit zum Ausführen: 00:00.694
●
Tablespace Page Size von 4 KB auf 8 KB und Bufferpoolsize auf 250
○ Durchschnittliche Zeit zum Ausführen: 00:00.708
●
Bufferpoolsize (8 KB Pages) von 250 auf 20282
○ Durchschnittliche Zeit zum Ausführen: 00:00.688
Tablespace Pagesizeerhoehung ist diesmal immer langsamer. Ansonsten ist eigentlich alles wie immer.
○
○
○
○
○
○
○
○
○
○
○
ohne keys: 8637 ms
mit keys: 3340 ms
indizes: 625 ms
buffer: 625 ms
config: 605 ms
reorg: 655 ms
runstats: 676 ms
tablespace 8KB: 734 ms
buffer: 694 ms
tablespace 16 KB: 708 ms
buffer: 688 ms
(schlechteste)
(beste)
Gesamtergebnis
●
Verbesserung von “mit Key“ zum Besten
○ Query 2: 89 %
○ Query 5: 362 %
○ Query 7: 452 %
Diese Werte sind eigentlich nicht schlecht, jedoch wenn man sich die gesamt Benötigte Zeit anschaut
und mit denen anderer Datenbanken vergleicht, ist es dann allgemein immer noch schlecht.
●
Verbesserung von “Indizes“ zum Besten
○ Query 2: 0 %
○ Query 5: 20 %
○ Query 7: 3 %
Diese Werte sind die Verbesserung, die nur durch Einstellungen der Datenbank erreicht wurden. Wie
man sieht, kann man teilweise doch recht gute Ergebnisse erzielen. Jedoch muss man im Auge
behalten, dass diese Einstellungen, teilweise speziell für die Query sind. Vor allem wenn man mit dem
Configuration Advisor arbeitet. Auch ob die Tablespacesize veränderung gut oder schlecht ist, ist
unterschiedlich.
●
Verbesserung insgesamt
○ Query 2: 111693 %
○ Query 5: 1024 %
○ Query 7: 1327 %
Diese Werte sind natürlich nur wegen der Vollständigkeit dabei. Normalerweise wird eine Datenbank
immer mit Primary- und Foreignkeys ausgestattet, damit die Konsistenz der Daten besser erhalten
bleiben kann.
Abschließend kann man sagen, dass durch Hinzufügen von Primary- und Foreignkeys sowie Indizes
immer eine Optimierung festgestellt werden konnte. Und die Verbesserung anhand von Einstellungen
und Parameter der Datenbank sind eher für spezielle Queries. Wenn also eine Query in einem System
besonders oft ausgeführt wird, könnte man sich überlegen, speziell für Diese das System zu optimieren.