Oracle 10G and Linux (Ubuntu)

Seminar Database Tuning & Administration“
”
Ausarbeitung
Oracle Datenbank / Ubuntu
Sebastian Gath und Hannes Schwarz
Universität Konstanz
1
Einleitung
Heutige Datenbanken sind in der Lage, riesige Datenmengen zu speichern und
zu verwalten. Im Allgemeinen erhöhen sich jedoch die Antwortzeiten von Abfragen, Einträgen und Updates mit zunehmender Größe der Datenbank. Diese
Performanceeinbußen gilt es durch verschiedene Optimierungsmaßnahmen zu
minimieren. Da heutige Datenbanken einen immer größer werdenden Funktionsumfang bieten, gibt es zahlreiche Optimierungsmöglichkeiten, die nicht immer
das gewünschte Ergebnis liefern, da bei einer Anfrage sehr viele Faktoren eine
Rolle spielen.
Die vorliegende Seminararbeit befasst sich mit der Administration und der
Optimierung einer Oracle Datenbank auf einem Linux System. Das Ziel ist es,
eine praktische Darstellung der getätigten Arbeitschritte, die während des Seminars Database Tuning & Administration“ erledigt wurden, zu geben. Nach
”
der administrativen Vorbereitung, die die Installation des Betriebssystems, der
Datenbank und der Benchmark umfasst, wird auf die verschiedenen Optimierungen eingegangen. Dabei liegt der Schwerpunkt auf vier Anfragen des TPC-H
Benchmarks, an Hand derer verschiedene Techniken zur Optimierung gezeigt
und bewertet werden.
2
2.1
Administration
Vorbereitung
Als Ausgangssituation stand uns ein HP Compaq dc5750 Microtower mit einem
AMD Athlon 64 x2 Dual Core Prozessor 4600+ und 1 Gigabyte DDR2 RAM
zur Verfügung. Wir entschlossen uns bezüglich des Betriebssystems auf die freie
Linux-Distribution Ubuntu zurückzugreifen und installierten die 64-Bit Version
6.10 Edgy Eft“ mit Hilfe einer Installations-CD. Danach kam die Installati”
on der Oracle 10g Express-Version. Dazu nutzten wir das Debian Package, das
auf der Webseite von Oracle [Oracle] zur Verfügung stand. Mit dem Programm
dpkg werden die Dateien automatisch in die nötigen Verzeichnisse kopiert und es
müssen nur noch der Umgebungsvariablen hinzugefügt werden. Dies wird durch
die folgenden Befehle erreicht:
2
Sebastian Gath & Hannes Schwarz
# dkpg oracle-xe 10.2.0.1-1.0 i386.deb
# export
PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
Als Benchmark wurde die Decision Support Benchmark TPC-H in der Version 2.6.0 vorgegeben. Die Benchmark nutzt für den Datenbanktest Anfragen und
Daten, die eine weitreichende Relevanz für die Industrie haben. Dabei handelt
es sich um ein Decision Support System, das große Datenmengen verarbeitet,
Anfragen mit einem hohen Grad an Komplexität ausführt und Antworten zu
kritischen Geschäftsfragen liefert.
Nach dem Download der nötigen Dateien von der TPC-H Webseite [TPC]
muss die Benchmark vor der Installation auf das System eingestellt werden.
Hierzu wird ein Makefile mitgeliefert, in dem drei Parameter angepasst werden
müssen, welche wir wie folgt wählten:
CC = gcc
DATABASE = DB2
MACHINE = LINUX
Da der Datenbanktyp Oracle nicht zur Verfügung stand, wählten wir DB2.
Nach dem Speichern der Datei musste der C-Compiler gcc und dessen C-Bibliotheken
installiert werden. Dazu dienten diese Befehle:
# apt-get install gcc
# apt-get install gcc-lib
Anschließend konnten die TPC-H Quelldateien mit Hilfe des Makefiles kompiliert werden und die Programme dbgen und qgen wurden erstellt. Als nächstes
war die Aufgabe, die Daten zu erzeugen und diese dann in der Datenbank zu speichern. Dazu muss das Tool dbgen genutzt und ein Skalierungsfaktor angegeben
werden, mit dessen Hilfe die Datenmenge angeben wird. Ein Skalierungsfaktor
von 1.0 erzeugt Daten von etwa einem Gigabyte. Als Grundlage für unsere Tests
erzeugten wir mit folgendem Befehl Daten von einem Gigabyte, da die Express
Version nur maximal 5 Gigabyte unterstützt.
# dbgen -s 1.0
Dbgen erstellt durch diesen Befehl zehn Dateien, acht tbl Dateien, die die
Daten beinhalten, eine dss.ddl Datei, mit den Spezifikation der Tabellen und
eine dss.ri Datei, in der die Schlüssel der Tabellen definiert sind. Da die Syntax
nicht ganz Oraclekonform war und um später nicht immer alle Tabellen neu
einspielen zu müssen, erstellten wir passend zu den acht Tabellen einzelne SQLSkripte mit dem Namen ctTABELLE.sql, die die Befehle aus der dss.ddl und
dss.ri vereinten. Folgend als Beispiel für die Tabelle part:
Um diese Tabellen zu erstellen, nutzten wir die Shell und das kommandozeilenbasierte Werkzeug von Oracle sqlplus mit folgendem Befehl:
# for i in ct*.sql do sqlplus benutzername/passwort @$i done
Database Tuning & Administration: Oracle/Ubuntu“
”
3
drop table part;
CREATE TABLE PART ( P PARTKEY NUMBER(7,0) NOT NULL,
P NAME VARCHAR2(55) NOT NULL,
P MFGR varCHAR2(25) NOT NULL,
P BRAND varCHAR2(10) NOT NULL,
P TYPE VARCHAR2(25) NOT NULL,
P SIZE NUMBER (3,0) NOT NULL,
P CONTAINER varCHAR2(10) NOT NULL,
P RETAILPRICE NUMBER(15,2) NOT NULL,
P COMMENT VARCHAR2(23) NOT NULL,
constraint part pk PRIMARY KEY (p partkey));
Abbildung 1. Beispiel für das SQL-Skript zum Erstellen der Tabelle Part
Dadurch wurden alle Tabellendefinitionen in der Datenbank erstellt.
Bevor nun die Daten in die Tabellen geladen werden konnten, musste noch
das Datumsformat der Datenbank an die Daten angepasst werden. Dies konnte
dauerhaft in sqlplus mit dem unten stehenden Befehl erreicht werden.
> alter system set nls date format=‘YYYY-MM-DD
HH24:MI:SS‘scope=spfile;
Anschließend konnten die Daten in die Datenbank b̈ertragen werden. Dies
wurde durch den Bulk Loader sglldr von Oracle erreicht. Dazu nutzen wir dieses
Skript, welches mit Hilfe des Bulk Loaders aufgerufen und ausgefhrt wurde.
LOAD DATA
INFILE ’part.tbl’
INSERT INTO TABLE part
FIELDS TERMINATED BY ‘|‘
(p partkey, p name, p mfgr, p brand, p type, p size, p container,
p retailprice, p comment)
Abbildung 2. Beispiel für das SQL-Skript zum Füllen der Tabelle Part
Als Ergebnis stand das in Abbildung (3) ersichtliche Schema in der Datenbank zur Verfügung. Die fett gedruckten Attribute nutzen wir als Primärschlüssel.
Wir nahmen von der Idee abstand, Tabellen ohne Primärschlüssel anzulegen, da
dies in der Praxis nicht üblich ist.
Desweiteren sind die Verbindungen der Tabellen untereinander durch die
Pfeile dargestellt. Diese Attribute bieten sich als Fremdschlüssel an, die wir jedoch nicht genutzt haben. Letztendlich bildet dieses Schema die Grundlage des
TPC-H Benchmarks, die wir zur Leistunsmessung herangezogen haben.
4
Sebastian Gath & Hannes Schwarz
PART
PARTSUPP
LINEITEM
ORDERS
200.000 Einträge
P_PARTKEY
P_NAME
P_MFGR
P_BRAND
P_TYPE
P_SIZE
P_CONTAINER
P_RETAILPRICE
P_COMMENT
800.000 Einträge
PS_PARTKEY
PS_SUPPKEY
PS_AVAILQTY
PS_SUPPLYCOST
PS_COMMENT
6.000.000 Einträge
L_ORDERKEY
L_PARTKEY
L_SUPPKEY
L_LINENUMBER
L_QUANTITY
L_EXTENDPRICE
L_DISCOUNT
L_TAX
L_RETURNFLAG
L_LINESTATUS
L_SHIPDATE
L_COMMITDATE
L_RECIEPTDATE
L_SHIPINSTRUCT
L_SHIPMODE
L_COMMENT
1.500.000 Einträge
O_ORDERKEY
O_CUSTKEY
O_ORDERSTATUS
O_TOTALPRICE
O_ORDERDATE
O_ORDERPRIORITY
O_CLERK
O_SHIPPRIORITY
O_COMMENT
SUPPLIER
10.000 Einträge
S_SUPPKEY
S_NAME
S_ADDRESS
S_NATIONKEY
S_PHONE
S_ACCTBAL
S_COMMENT
CUSTOMER
150.000 Einträge
C_CUSTKEY
C_NAME
C_ADDRESS
C_NATIONKEY
C_PHONE
C_ACCTBAL
C_MKTSEGMENT
C_COMMENT
NATION
25 Einträge
N_NATIONKEY
N_NAME
N_REGIONKEY
N_COMMENT
REGION
5 Einträge
R_REGIONKEY
R_NAME
R_COMMENT
Abbildung 3. TPC-H Schema mit einem Scaling-Factor von 1.0
2.2
Leistungsmessungen
Zur Zeitmessung wurde ein Shellskript genutzt, das die Anfragen in zwei Varianten ausführte. In der ersten Variante wurde jede Anfrage des Benchmarks
mehrfach hintereinander ausgeführt. In der zweiten Variante wurden alle Anfragen des Benchmarks einmal ausgeführt und dies mehrfach hintereinander. Das
Ergebnis wurde in zwei Dateien, eine für jede Variante, gespeichert, um diese
später weiterzuverarbeiten. Im folgendem Beispiel ist das Shellskript zur Leistungsmessung nach Variante zwei zu sehen.
for x in ‘seq 1 10‘
do
echo XXXXXXX $x XXXXXXXXX >> Var2.txt
for i in *.sql
do
echo -n $i: >> Var2.txt
sqlplus Benutzername/Passwort @$i | grep Elapsed >> Var2.txt
echo
done
done
Database Tuning & Administration: Oracle/Ubuntu“
”
5
Wir erhofften uns durch diese zwei Varianten, den Caching-Effekt auszuschalten und gingen davon aus, dass die zweite Variante eine längere Laufzeit
aufweisen würde. Wie später im Abschnitt (4) Datenbankoptimierungen zu sehen sein wird, war dies nicht der Fall. Eine weitere Möglichkeit der Analyse der
Anfragen waren die Ausführungspläne, die von Oralce zur Verfügung gestellt
werden. Diese können in sqlplus mit dem Befehl SET AUTOTRACE ON vor der
Abfrage verlangt werden und werden nach der Ausführung der Anfrage angezeigt.
2.3
Erste Zeitmessung
Abbildung 4. Eine erste Zeitmessung der Anfragen ohne Optimierung
Eine erste Zeitmessung aller Anfragen des Benchmarks ist in Abbildung (4)
zu sehen. Die Anfragen wurden vorab nach vorkommenden SQL Statements
gruppiert und entsprechend ihrer Zugehörigkeit eingefärbt, um einen Überblick
über die Art und Laufzeit der Anfrage zu geben. So stehen z.B. die roten Punkte
für eine select Anfrage, in dessen from Teil ein weiteres select ausgeführt wird.
Anhand der X-Achse ist die Zeit logarithmisch abgetragen, da die Zeitunterschie-
6
Sebastian Gath & Hannes Schwarz
de zwischen den Abfragen sehr groß ist. Die langsamsten Anfragen befinden sich
daher auf der rechten Seite.
Unter Zuhilfenahme dieser Zeitmessung haben wir uns für vier Anfragen entschieden, die zur Optimierung herangezogen wurden und dessen Optimierungsmaßnahmen im Folgenden beschrieben werden. Die Entscheidung fiel auf diese
Anfragen, da hier unserer Meinung Veränderungen schneller sichtbar werden und
sie eine heterogene Mischung der Anfragen darstellen.
3
Anfragenoptimierungen
Zu Beginn unserer Optimierungsmaßnahmen beschäftigten wir uns mit den zuvor ausgewählten Anfragen 2, 3, 13 und 22. Im Folgenden werden auf die einzelnen Ideen zur Optimierung und dessen Auswirkungen auf die jeweiligen Anfragen
eingegangen.
3.1
Anfrage 13
select c count, count(*) as custdist
from (
select c custkey, count(o orderkey) c count
from customer left outer join orders on
c custkey = o custkey
and o comment not like ’%special%requests%’
group by c custkey
) c orders
group c count
order by custdist desc, c count desc;
Abbildung 5. Anfrage13
In Anfrage 13 (Abbildung (5)) handelt es sich bei den zeitintensiven Berechnungen um einen left outer join und eine Substringsuche mit mehreren
Platzhaltern (not like). Der von Oracle zur Verfügung gestellte Ausführungsplan ist in Abbildung (6) zu sehen. Dort bestätigt sich die Annahme, dass es
sich bei diesen Stellen um die Flaschenhälse der Anfrage handelt. Wie hier sehr
gut zu sehen ist, benötigt die Ausführung des Joins und die Substringsuche ca.
50% der Gesamtlaufzeit und sind daher Ziel unserer Optimierungen.
Idee 1 - Vermeiden von Substringsuche auf Attribut ’o comment’
Um die Substringsuche zu vermeiden, legten wir eine Kodierung für normale
Database Tuning & Administration: Oracle/Ubuntu“
”
7
Abbildung 6. Ausführungsplan für Anfrage 13
Aufträge und Special Requests fest. Das Attribut o nonspecial wurde zu diesem
Zweck angelegt. Alle Anfragen, die einen Special Request darstellen, werden somit mit einer 1 im Attribut o nonspecial markiert. Dadurch wird die Suche auf
dem Textfeld vermieden und stattdessen durch eine einfache Abfrage des Attributs ersetzt. Die Anfrage ändert sich daraufhin nur geringfügig, wie in Abbildung
(7) zu sehen ist. Die Laufzeit der Anfrage sank daraufhin von 3.05 Sekunden
select c count, count(*) as custdist
from (
select c custkey, count(o orderkey) c count
from customer left outer join orders on
c custkey = o custkey
and o nonspecial=1
group by c custkey
) c orders
group c count
order by custdist desc, c count desc;
Abbildung 7. Anfrage 13 optimiert - Tuningidee I
auf 2.48 Sekunden im Schnitt, wobei es sich um eine Verbesserung von 20%
handelt. Durch das Einfügen und Füllen des Attributs o nonspecial steigt der
Speicherverbrauch und die Anwendungsprogramme bzw. Anfragen, die die Optimierung nutzen sollen, müssen angepasst werden. Weitere Optimierungsschritte,
die durchgeführt werden könnten, allerdings in unserer Oracle Datenbankversion
nicht freigeschaltet sind, wären zum einen das Anlegen eines Index auf das Attribut o nonspecial. Allerdings würden wir dadurch eher eine Verschlechterung
erwarten, da wir im Laufe dieser Arbeit feststellen konnten, dass bei der Benutzung von Indizes weniger oft mehr ist. Zum anderen könnte die Tabelle Orders
8
Sebastian Gath & Hannes Schwarz
nach dem Attribut o nonspecial partitioniert werden, was die Anfrage sicherlich beschleunigen würde, dies jedoch wahrscheinlich einen negativen Effekt auf
andere Anfragen hätte, der in keinem Verhältnis steht.
Idee 2 - Optimieren des left outer join orders on c custkey = o custkey
Um den left outer join zu optimieren, wurden die Tabellen Orders und Customer mit dem Primärschlüssel custkey geclustert gespeichert. Dazu erstellten
wir die neuen Tabellen Ordersc und Cumstomerc, um zunächst keine negativen
Effekte auf andere Anfragen hervorzurufen, mit denen wohl gerechnet werden
muss. Durch ein Cluster wird unter anderem der Speicher effizienter genutzt,
allerdings ist das Einfügen und Verändern der geclusterten Tabellen langsamer.
Nach Anpassung der Anfrage traten drastische Performanzeinbußen auf und wir
verfolgten die Idee nicht weiter.
3.2
Anfrage 22
select cntrycode, count(*) numcust, sum(c acctbal) totacctbal
from (select substr(c phone, 1, 2) cntrycode, c acctbal
from customer
where substr(c phone, 1, 2)
in (’13’, ’31’, ’23’, ’29’, ’30’, ’18’, ’17’)
and c acctbal > (select avg(c acctbal)
from customer
where c acctbal > 0.00
and substr(c phone, 1, 2) in
(’13’, ’31’, ’23’, ’29’, ’30’, ’18’, ’17’))
and not exists (select *
from orders
where o custkey = c custkey)) custsale
group by cntrycode
order by cntrycode;
Abbildung 8. Anfrage22
Anfrage 22 (Abbildung (8)) stellt eine typische Anfrage aus der Industrie dar.
Hierbei werden Kunden aus bestimmten Gebieten gesucht, indem diese durch
ihre Telefonvorwahl identifiziert werden. Dazu wird in der Anfrage und der Unteranfrage eine Substringsuche auf den ersten beiden Stellen der Telefonnummer
ausgeführt. Wie auch in Abbildung (9) zu sehen ist, handelt es sich bei der
Substringsuche ähnlich zu Anfrage 13 um den zeitintensivsten Teil der Anfrage.
Database Tuning & Administration: Oracle/Ubuntu“
”
9
Abbildung 9. Ausführungsplan für Anfrage 22
Idee 1 - Vermeiden von Substringsuche auf Attribut o comment
Die erste Optimierung orientiert sich an den Ideen der Anfrage 13. Da es sich bei
der Substringsuche um die Vorwahl, genauer gesagt die Ortskennzahl, handelt,
lag die Idee nahe, die Telefonnummer aufzuteilen. Dazu wurde das neue Attribut area code in die Tabelle eingefügt. Anschließend wurden die ersten beiden
Stellen der Telefonnummer (c phone) ausgeschnitten und in dem neuen Attribut
gespeichert. Der Vorteil liegt auf der Hand. Nun muss keine teure Substringsuche mehr ausgeführt werden, sondern es reicht das Attribut area code auszulesen
und es kommt, wie zuvor in Anfrage 13, zu einem Zahlenvergleich. Durch diese Optimierung wurde eine Laufzeitverkürzung von ca. 25% (Vorher: 1,98 sek;
Nachher: 1,51 sek) ohne Änderung des Speicherverbrauchs erreicht. Nachteil ist
jedoch, dass jede Anwendung, die die Tabelle verwendet, angepasst werden muss,
da diese sonst die Telefonnummer ohne Vorwahl anzeigen würde.
Idee 2 - Optimierung des Operators not exists
Eine weitere Optimierung versuchten wir durch Umschreiben der Anfrage zu
erreichen. Dies ist in Abbildung (10) zu erkennen. Idee war es, den Operator not
exists durch den Mengenoperator minus zu ersetzen. Die Laufzeit der Anfrage
erhöhte sich von den ursprünglich 1,98 Sekunden auf 3,61 Sekunden, was fast
eine Verdoppelung der Laufzeit bedeutet.
3.3
Anfrage 2
Zunächst einmal fällt bei der in Abblidung (11) gezeigten Anfrage auf, dass der
Join über die Tabellen partsupp, supplier, nation und region in der äußeren,
10
Sebastian Gath & Hannes Schwarz
select...
from customerAC,
(select c custkey from customerAC
minus select o custkey from orders ) ominu
where (c areacode = 13 or c areacode = 31 ...)
and c acctbal > (select ...
where c acctbal > 0.00
and (c areacode = 13 or c areacode = 31 ...))
group by cntrycode
...
Abbildung 10. Anfrage 22 optimiert - Tuningidee 2 (Anfrage gekürzt)
select ...
from part, supplier, partsupp, nation, region
where p partkey = ps partkey and s suppkey = ps suppkey
and p size = 15 and p type like ’%BRASS’
and s nationkey = n nationkey and n regionkey = r regionkey
and r name = ’EUROPE’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 = ’EUROPE’ )
order by s acctbal desc, n name, s name, p partkey;
Abbildung 11. Anfrage2 - gekürzt
wie auch in der Subanfrage, vorkommt. Jedoch findet bei der äußeren Anfrage noch eine Selektion auf p size = 15 and p type like ’%BRASS’ statt. Wie
dem Ausführungsplan (Abbildung (12)) zu entnehmen ist, macht die Berechnung dieser Selektion ca. die Hälfte der gesamten Anfrageverarbeitungszeit aus.
Die verbleibende Anfrageverarbeitungszeit wird von dem Nested Loops Join der
Tabellen partsupp und part ausgeschöpft. Somit sind hier Tuningmaßnahmen
anzusetzen.
Idee 1 - Selektion auf p size = 15 and p type like ’%BRASS’ beschleunigen
Durch die Definition eines Bitmapindexes auf das Attribut p size könnte eine
Beschleunigung der Anfragebearbeitung erreicht werden, da so der Full Table
Database Tuning & Administration: Oracle/Ubuntu“
”
11
Abbildung 12. Ausführungsplan für Anfrage 2
Scan entfällt. Ein Bitmapindex ist notwendig, da das Attribut p size nicht eindeutig ist. Dies ist in der Expressversion nicht möglich, aber es ist zu vermuten,
dass die Beschleunigung eher gering ausfallen würde, da die Selektivität des Attributes p type gering ist.
Eine weitere Möglichkeit wäre die Definition eines Textindexes auf dem Attribut p type. Textindexe sind jedoch sehr speicherintensiv und führen nicht
zwangsläufig zu Performanceverbesserungen. Da hier eine führende Wildcard
in like ’%BRASS’ verwendet wird, ist ein Full Table Scan notwendig und so
die einfache Suche in dem Textfeld effizienter, da der von Oracle eingesetzte
Textindizes solche Anfragen nicht effizient unterstützt.
Idee 2 - Tabellen nation/region geclustered speichern
Um das Joinen der Tabellen nation/region zu beschleunigen, würde es sich
anbieten, beide Tabellen geclustert zu speichern. Das Clustern hätte zur Folge,
dass Tupel aus beiden Tabellen, die den gleichen Schlüsselwert haben, zusammenhängend auf der Festplatte gespeichert werden und so das Lesen effizienter
möglich ist. Da dieser Join in vielen Anfragen des TPC-Benchmarks vorkommt,
würde diese Tuningmaßnahme auch andere Anfragen beschleunigen. Allerdings
enthalten die beiden Tabellen so wenige Tupel, dass kein messbarer Effekt durch
die Maßnahme erzielt werden kann.
Idee 3 - Unteranfrage durch Materialized View und Partitionierung
beschleunigen
12
Sebastian Gath & Hannes Schwarz
Eine andere Optimierungsmöglichkeit wäre das Anlegen einer Materialized
View mit r name, p partkey und min(ps supplycost) as min supplycost als Attribute. Diese sollte nach dem Attribut r name partitioniert werden, da so nicht nur
eine Beschleunigung für Selektion auf r name = ’EUROPE’, sondern auch auf
alle anderen Ausprägungen des Attributs p name erreicht werden kann. Je Partition müsste noch ein Index auf p partkey definiert werden und die min supplycost
könnten aus den Materialized View Statistiken ermittelt werden.
Da diese Definition der Materialized View so in der Expressversion nicht möglich
ist, haben wir eine Tabelle mit einer Struktur erzeugt, die der Partition r name =
’EUROPE’ entspricht. Das Erzeugen einer Materialized View geht immer auch
mit einem steigenden Speicherverbrauch einher und führt zu erhöhtem Overhead bei Änderungen oder Einfügungen neuer Tupel in eine der Basistabellen,
da die Materialized View aktualisiert werden muss. Dies kann in Oracle durch
Setzen einer Refresh-Clause auf ON COMMIT automatisiert werden. Es werden so Änderungen der Basistabelle nach dem Commit auch in der Materialized
View übernommen.
Das Einbinden der Tabelle in die Anfrage führt zu einer Verkürzung der Laufzeit
von ca. 20% (Vorher: 0.39 sek; Nachher: 0.31 sek). Der Einsatz einer Materialized View, wie oben beschrieben, würde zu einer ähnlichen Laufzeit führen, da
der Overhead kaum größer ist als der bei einem Tabellenzugriff.
3.4
Anfrage 3
select l orderkey, sum(l extendedprice * (1 - l discount)) revenue,
o orderdate, o shippriority
from customer, orders, lineitem
where c mktsegment = ’BUILDING’ and c custkey = o custkey
and l orderkey = o orderkey
and o orderdate < date ’1995-03-15’
and l shipdate > date ’1995-03-15’
group by l orderkey, o orderdate, o shippriority
order by revenue desc, o orderdate;
Abbildung 13. Anfrage 3- gekürzt
In Anfrage 3 (Abbildung (13)) sind zwei Selektion über Datumsbereiche enthalten, deren Perfomance oft problematisch ist. Der von Oracle generierte Anfrageplan, zu sehen in Abbildung (14), zeigt sehr deutlich, dass die Auswertung
der Selektion o orderdate < date ’1995-03-15’ rund 90% der Laufzeit ausmacht.
Die verbleibenden zehn Prozent werden genutzt, um die Selektion c mktsegment
= ’BUILDING’ zu berechnen. Bei beiden Selektionen ist jeweils ein Full Table
Database Tuning & Administration: Oracle/Ubuntu“
”
13
Access notwending. Hier ist durch die Wahl geeigneter Tuningmaßnahmen eine
effizientere Verarbeitung sicherzustellen.
Abbildung 14. Ausführungsplan für Anfrage 3
Idee 1 - Selektion o orderdate < date ’1995-03-15’ durch Materialized
View beschleunigen
Ohne eine Tuningmaßnahme erzielt die Anfrage 2 eine Laufzeit von 0.95 Sekunden. Da im Durchschnitt 623 o orderkeys je o orderdate vorhanden sind,
wäre die Definition einer Materialized View mit den Attributen o orderdate,
o orderkey, o custkey und o shippriority zweckmäßig. Diese Attribute werden
für die weitere Verarbeitung der Anfrage benötigt. Die Materialized View könnte nach o orderdate je Jahr/Monat partitioniert werden, d.h. es werden alle
o orderdate die dem gleichen Jahr/Monat angehören in der gleichen Partition
gespeichert. Die Partitionen könnten wiederum mit einem Bitmapindex auf dem
Attribut o orderdate versehen werden. Ein Bitmapindex ist zu wählen, da das
Attribut o orderdate nicht eindeutig ist.
Da weder das Definieren eines Indexes auf Materialized Views, noch das Partitionieren in der Expressversion erlaubt ist, haben wir eine Tabelle erstellt, die
jedoch unpartitioniert, alle Daten die die Selektionsbedingung erfüllen, fasst. So
kann eine Reduzierung der Laufzeit um ca. 73 Prozent (0.26 Sekunden) erreicht
werden. Dieser Wert kann durch den Einsatz einer wie oben gezeigten Materialized View nahezu erreicht werden, da der Overhead durch die Partitionierung
sehr gering ist.
Idee 2 - Selektion c mktsegment = ’BUILDING’ beschleunigen
Bei der Auswertung der Selektion c mktsegment = ’BUILDING’ führt Oracle
14
Sebastian Gath & Hannes Schwarz
eine Full Table Access durch, der über 150.000 Datensätze in der Tabelle customer geht. Von diesen 150.000 Datensätzen erfüllen 30.142 (ca. 20 Prozent) die
Selektionsbedingung, d.h. die Selektivität des Attributs c mktsegment ist nicht
sehr hoch. Die Definition eines Textindexes auf diesem Attribut wäre somit wenig zweckmäßig. Die Nachteile der geringen Selektivität kann die Definition eines Bitmapindexes nicht verbessern, jedoch kann hierdurch erreicht werden, dass
Datensätze mit gleicher Ausprägung im Attribut c mktsegment zusammenliegen
auf der Festplatte gespeichert werden und so effizient gelesen werden können.
Dies konnten wir leider aufgrund fehlender Funktionalität nicht testen.
3.5
Ergebnisse im Überblick
3.0
Abbildung (15) zeigt noch einmal die Tuningergebnisse für die Anfragen 2, 3,
13 und 22 im Überblick. Vorallem bei Anfrage 3 konnte durch den Einsatz einer
Materialized View die Laufzeit erheblich verbessert werden.
1.5
0.0
0.5
1.0
Laufzeit (sek)
2.0
2.5
Nicht optimiert
Optimiert
2
3
13
22
Queryname
Abbildung 15. Tuningergebnisse für Anfragen 2, 3, 13 und 22 im berblick
4
4.1
Datenbankoptimierungen
Allgemeine Anmerkungen
Neben dem Definieren von Indexstrukturen, Einsetzten von Materialized Views
oder dem Clustern von Tabellen, kann versucht werden über Hints Einfluss auf
den vom Optimierer erzeugten Ausführungsplan zu nehmen. So kann z.B. dem
Database Tuning & Administration: Oracle/Ubuntu“
”
15
Optimierer die Verwendung eines Indexes oder eines bestimmtes Joinalgorithmuses nahegelegt werden, ohne das sichergestellt ist, dass dieser auch so verfährt.
Die Möglichkeiten von Hints sind bei den beschriebenen Optimierungen unberücksichtigt geblieben, da wir uns auf andere Tuningmaßnahmen konzentriert
haben.
Hints, genauso wie die in Kaptiel (3) gezeigten Methoden, beeinflussen zunächst
einmal nur die Laufzeit der gezeigten Anfragen, können jedoch durch Anpassung
anderer Anfragen auch deren Laufzeiten beeinflussen. Oracle bietet darüberhinaus die Möglichkeit, Datenbankparameter je Tablespace oder auch für die gesamte Datenbank zu ändern. Da wir alle Tabellen in keinem speziellen Tablespace
angelegt haben, beziehen sich die im Folgenden beschriebenen Änderungen auf
die gesamte Datenbank. Die geänderten Parameter können auch für jeden Tablespace individuell angepasst werden.
4.2
Ausgangssituation & Möglichkeiten
Das den Messungen zugrundeliegen System besitzt 1 GB RAM, von dem jedoch
nur ca. 944 MB zur Verfügung stehen. Oracle reserviert sich standardmäßig 41
des gesamten Arbeitsspeichers, im konkreten Fall waren dies 236 MB. Dieser
reservierte Bereich wird auch SGA - System Global Area genannt und lässt sich
über die Systemvariable sga max size setzten. So setzt z.B. der Befehl Alter system set sga max size = 512M die SGA auf 512 MB. Die SGA lässt sich grob
in den Buffer Cache, also der klassische Datenbankcache für Datenbanksegmente wie Tabellen oder Indizes und in die Shared Pools einteilen. Diese speichern
Ausführungspläne oder auch Data Dictionary Daten.
Durch das Setzten der sga max size übernimmt Oracle die genau Verteilung des
Arbeitsspeichers auf die einzelnen Komponenten. Auf der anderen Seite ist es
natürlich auch möglich, die Komponenten einzeln zu setzten. Zusätzlich gibt es
noch eine Reihe weiterer Cachegrößen, die gesetzt werden können, wie z.B. Caches die genutzt werden, wenn Oracle aus Anwendungen heraus angefragt wird.
Oracle bietet mit der Systemtabelle v$shared pool advice eine Vorhersage, wie
sich Veränderungen der Cachegröße auf die Laufzeit von Anfragen auswirken.
Im Folgenden wird dieser Aspekt näher erläutert.
Als Startkonfiguration sind alle Tabellen mit Primärschlüsselen und Index auf
dem Primärschlüsselattribut angelegt worden, da das Erstellen einer Tabelle ohne Primärschlüssel und passendem Index den Einsatz eines Datenbanksystems
überflüssig macht. In dieser Konfiguration könnten die Daten auch in einer einfachen Datei gespeichert und über Ubuntu-Boardmittel verarbeitet werden. Auch
wurde die Möglichkeit, Tabellen dauerhaft im Cache zu halten, zunächst außer
Acht gelassen. Der Befehl alter table tab storage (buffer pool keep) erzwingt das
Halten der Tabelle tab in dem Bufferpool keep, d.h. die Tabelle tab steht im
Hauptspeicher zur Verfügung und muss nicht von der Festplatte gelesen werden.
16
4.3
Sebastian Gath & Hannes Schwarz
Ergebnisse
Das in Kapitel (2.2) eingeführte Skript ermittelt die durchschnittliche Laufzeit
einer Anfrage bei einer 1000fachen Ausführung. Eine solche Art zu messen ist
jedoch sehr unrealistisch, da mit sehr hoher Wahrscheinlichkeit im realen Betrieb exakt die gleiche Anfrage nicht mehrfach hintereinander ausgeführt wird.
Jedoch macht es das Auswerten der Zeiten sehr viel einfacher.
Ein etwas realistischeres Anfrageverhalten kann durch einmaliges Ausführen jeder Anfrage nacheinander und vielfache Wiederholung dieses Schrittes erreicht
werden, wobei die Auswertung der Zeiten noch einfach zu handhaben ist. Durch
zufällige Wiederholung der Anfragen wären die Ergebnisse wahrscheinlich am
Realistischsten, jedoch gestaltet sich hier die Auswertung weit aus schwieriger.
Abbildung (16) zeigt die Messergebnisse der beiden beschriebenen Varianten, auf
allen Anfragen angewendet, im ÜberblickS. Anfragen mit einer Laufzeit unter
0.01 Sekunden wurden aus Gründen der Übersichtlichkeit nicht visualisiert.
Abbildung 16. Laufzeiten unter verschiedenen Einstellungen
Die hellroten und hellblauen Balken zeigen jeweils die Anfragezeiten aufgrund von Messung, bei denen die gleiche Anfragen 100 mal hintereinander ausgeführt wurde. Blaue Balken stehen für einen sga max size von 236 MB, rote
Balken hingegen für 512 MB. Die dunkelroten und dunkelblauen Balken stehen
für Anfragezeiten denen eine einmalige Ausführung jeder Anfrage und 100 fache
Database Tuning & Administration: Oracle/Ubuntu“
”
17
Wiederholung dieses Schrittes zu Grunde liegt.
Es lässt sich anmerken, dass die hellblaue 236 MB sga max size Variante tendenziell am Langsamsten ist. Vermutlich ist Oracle standardmäßig auf den Mehrbenutzerbetrieb optimiert und erwartet nicht, dass die gleiche Anfrage vielfach
hintereinander ausgeführt wird, wie auch der teils deutliche kürzere dunkelblaue
Balken zeigt. Anfrage 15 stellt hier eine Ausnahme dar. Ursächlich hierfür ist
das temporäre Anlegen einer View in dieser Anfrage, welche bei mehrfachem
Ausführen der Anfrage sehr wahrscheinlich im Cache gehalten wird und so zu
einer Performancesteigerung führt.
Sehr auffällig ist, dass das hinzufgen von mehr Cache nicht zwangsläufig auch
zu einer Performancesteigerung führen muss. Anfragen, die den vorhandenen
Cache nicht voll ausnutzen, können nicht durch Bereitstellung weiterem Caches
beschleunigt werden, da sie diesen nicht nutzen. Tuningeffekte durch gesteigerten
Cache hängt also immer von der Speicherauslastung vor der Tuningmaßnahme
ab.
Der gelbe Balken zeigt den Effekt des Haltens der Tabelle orders im Cache. Anfragen, die diese Tabelle nutzen, erfahren so eine Performancesteigerung (z.B.
Anfrage 22). Allerdings wird so der für Anfragen zur Verfügung stehende Hauptspeicher reduziert und kann gegebenenfalls dazu führen, dass Tabellen nicht mehr
gecached werden können und so aufwendige Leseoperationen von der Festplatte
notwendig sind, was von Performanceeinbußen zur Folge hat.
5
Fazit
Abschließend lässt sich festhalten, dass Operationen wie das Definieren von Partitionierungen, Bitmapindexe, Indexe auf Materialized Views in der Expressversion nicht möglich sind und so die Möglichkeiten doch erheblich einschränken.
Hinzu kommt eine Begrenzung der maximalen Datenbankgröße auf 5 GB. Somit
war das Erstellen einer 10 GB Instanz der TPC-Daten leider nicht möglich. Auch
standen der SQL Tuning Advisor und Access Advisor, beides Tools die automatisch Tuningvorschläge generieren, nicht zur Verfügung und es ist zu vermuten,
dass noch eine Reihe weiterer Features in der Expressversion nicht unterstützt
werden.
Festzuhalten ist, dass der von Oracle eingesetzte Optimierter bereits ohne Eingriffe sehr gut arbeitet. Der allgemeine Einsatz von Indexen führt nicht zwangsläufig
zu einer Performancesteigerung. Dennoch sind Performancesteigerungen mit akzeptablen Kosten und Auswirkungen auf andere Anfragen möglich, welche jedoch
bei jeder Maßnahme genau zu prüfen sind.
Literatur
1.
2.
3.
4.
Loney, Kevin. Oracle Database 10g. Die umfassende Referenz; Hanser 2005
Haas, Frank. Oracle-Tuning in der Praxis München; Hanser 2005
http://www.oracle.com/pls/db102/homepage
http://www.tpc.org/tpch/