Database Benchmark

Database Benchmark
Dozent: Prof. Bernhard Wyss
Student: Carla Santoro
Projektarbeit 2005/2006, Abteilung Informatik
Im Rahmen dieser Projektarbeit habe ich mich intensiv mit Linux-SUSE, Oracle 10g und
dem TPC-H Benchmark befasst. Dadurch konnte ich mich auf meinen bevorzugten Gebieten,
Linux-Systeme und Datenbanken, weiterentwickeln und darf auf eine interessante und lehrreiche Zeit zurückblicken.
Ich möchte mich an dieser Stelle bei Herrn Prof. Bernhard Wyss bedanken, dass ich diese
Projektarbeit durchführen durfte, er mich bei allen Problemen unterstütze und mir einiges über
Datenbanken beigebracht hat.
Inhaltsverzeichnis
1. Einleitung
1.1. Management Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2. Projektziel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2. Grundlagen
2.1. TPC-H Benchmark . . . . . . . . . . . . . .
2.1.1. Testumgebung . . . . . . . . . . . .
2.1.2. Queries . . . . . . . . . . . . . . . .
2.1.3. Ergebnis . . . . . . . . . . . . . . .
2.2. ORACLE-Datenbanksystem . . . . . . . . .
2.2.1. Aufbau . . . . . . . . . . . . . . . .
2.2.2. Werkzeuge . . . . . . . . . . . . . .
2.3. SUSE LINUX und Windows XP Client . . .
2.3.1. SUSE LINUX 10.0 . . . . . . . . . .
2.3.2. Windows XP Home Edition 2002 SP1
3. Einrichten der Testumgebung
3.1. Installation von SUSE . . . . . . . . . . .
3.2. Installation von Oracle . . . . . . . . . .
3.3. Datenbank erstellen . . . . . . . . . . . .
3.3.1. Benutzer und Tablespace anlegen
3.3.2. Tabellen erzeugen mit DBGEN .
3.3.3. Load . . . . . . . . . . . . . . .
3.3.4. Indizes erstellen . . . . . . . . .
4. Testen
4.1. Pro*C . . . . . . . . . . . . .
4.2. EmbeddedSQL . . . . . . . .
4.3. Programme erstellen . . . . .
4.4. Parameter erzeugen mit QGEN
4.5. Tests . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
2
2
.
.
.
.
.
.
.
.
.
.
3
3
4
5
5
6
6
11
13
13
13
.
.
.
.
.
.
.
14
14
16
18
25
28
30
31
.
.
.
.
.
32
32
37
38
38
39
5. Resultate
39
6. Tuning
6.1. Explain-Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2. Statistik . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.3. Optimizer Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
40
40
44
46
7. Schluss
7.1. Ausblick . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
48
48
Inhaltsverzeichnis
A. Resultate
A.1. Übersicht über alle Resultate . . . . . . . . . . . . . . . . . . . . . . . . . .
A.2. Einzelne Versionen mit Graphik . . . . . . . . . . . . . . . . . . . . . . . .
50
50
50
B. Datenbank
B.1. Tabellen und Indizes kreieren
B.2. Importieren der Daten . . . .
B.3. Logfiles der Tabellen . . . .
B.4. Parameter der Datenbank . .
.
.
.
.
51
51
51
51
51
C. Queries
C.1. Quellcode der Queries 1 bis 22 . . . . . . . . . . . . . . . . . . . . . . . . .
C.2. Buildprotokoll eines Programmes . . . . . . . . . . . . . . . . . . . . . . .
C.3. Einzelne Dateien mit Resultaten . . . . . . . . . . . . . . . . . . . . . . . .
52
52
52
52
D. Tuning
D.1. Explain-Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
53
53
E. TPC-H
E.1. Top Ten TPC-H . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
E.2. Readme zu DBGEN/QGEN . . . . . . . . . . . . . . . . . . . . . . . . . .
E.3. Generierte Parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
54
54
54
54
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1. Einleitung
1. Einleitung
Um die Leistung unterschiedlicher Systeme im Bezug auf Eigenschaften wie Anfragezeit und
Performance vergleichen zu können, müssen die zu vergleichenden Datenbank-Systeme standardisierten Tests unterzogen werden, was auch Benchmarking genannt wird.
Oracle ist ein mächtiges Datenbank-System und wird sehr oft eingesetzt. Aus diesem Grund
war es auch meine erste Wahl. Dasselbe gilt für den TPC-H Benchmark, er ist vor allem im
kaufmännischen Bereich von Bedeutung.
Da es beim Testen von Datenbank-Benchmarks vorallem um die Geschwindigkeit des Systems geht, habe ich mich entschieden, die Datenbank auf Linux aufzusetzen, und nicht auf
Windows. Zudem wollte ich mich stärke in das Thema Linux einarbeiten.
1
1. Einleitung
1.1. Management Summary
Aufgabenstellung:
Mit ausgesuchten Benchmarks (hauptsächlich Queries) sollen einige Datenbanksysteme getestet werden (ORACLE, PostgreSQL). Definition und Einrichten der Testumgebung, Benchmarks auswählen, Testwerkzeuge erstellen (CLI, OCI), Ablaufpläne verstehen und beeinflussen, Gezielt Hilfsstrukturen einsetzen.
Durchführung:
• Übersicht über die verschiedenen Benchmarks
• Auswahl und Anpassen einiger Benchmarks
• Aufsetzen der DB-Systeme auf einem System
• Einrichten der Datenbanken nach Vorgabe der Benchmarks
• Systemspezifische Tuningmassnahmen vergleichen
• Benchmarks durchführen und bewerten
Grenzen und Ausblick:
Benchmarks dienen dazu, die relative Performance von DB-Systemen abzuschätzen und Tuningmassnahmen einzusetzen zu bewerten, insbesondere
• erheben statistischer Grössen
• Ausführungspläne erstellen
• Zugriffsstrukturen erstellen
Resultate:
Die Resultate sollen interpretiert und ausgewertet werden.
1.2. Projektziel
Das Ziel der Projektarbeit musste ich im Verlauf der Arbeit anpassen. Da ich die Arbeit alleine
durchführte und mich auf einigermassen neues Territorium bewegte, wurde schnell klar, dass
nicht mehrere DB-Systeme getestet werden können. Das Aufsetzen von Oracle 10g auf LinuxSUSE 10.0 hat sehr viel Zeit beansprucht. Das Ziel wurde darauf reduziert, nur den TPC-H
Benchmark auf Oracle 10g zu testen.
2
2. Grundlagen
2. Grundlagen
In diesem Kapitel werde ich vor allem theoretisch auf den TPC-H Benchmark und das Oracle
Datenbank-System eingehen. Diejenigen, die sich bereits mit diesen Themen befasst haben,
können direkt zum Kapitel 2.3 gehen, wo ich auf die verwendete Hard- und Software eingehe.
2.1. TPC-H Benchmark
Ursprünglich kommt der Begriff Benchmark aus der Landvermessung und bezeichnet einen fixen Punkt in der Landschaft. Mit Benchmarking ist in diesem Zusammengang das Orientieren
an diesem Punkt gemeint. Im Falle der Datenbank-Systeme werden die Ergebnisse des Benchmarking eingesetzt, um die einzelnen Softwareprodukte in eine standartisierte Leistungsskala
einordnen zu können. Das Ergebnis des Benchmarking ergibt für jedes getestete Produkt einen
sogenannten Benchmark-Wert, der an einer imaginären Messlatte, z.B. dem Wert des auf dem
Markt führenden Softwareprodukts, gemessen werden kann. Je nach Höhe des ermittelten
Benchmark-Werts kann nun erkannt werden, ob das getestete Datenbank-System höher, niedriger oder gleichhoch wie die zu vergleichende Messlatte in die Leistungsskala eingeordnet
werden kann.
Das Transaction Performance Processing Council (TPC) hat es sich zur Aufgabe gemacht
verschiedene Benchmarks zur Verfügung zu stellen, um die Leistungen der unterschiedlichen
Datenbank-Systeme zu vergleichen. Die unterschiedlichen Benchmarks des TPC zielen auf
unterschiedliche Datenbank-Systeme ab und werden durch TPC- und einem einzelnen Buchstaben abgekürzt. Die Abkürzung TPC-C steht zum Beispiel für on-line transaction processing
benchmark, TPC-W steht für transactional web e-Commerce benchmrak. Die beiden Benchmarks TPC-A und TPC-B sind veraltet, sie wurden von der Technik eingeholt und sind nicht
mehr im Gebrauch.
Der TPC Benchmark H (TPC-H) [1] ist ein Entscheidungsunterstützungs-Benchmarksystem (decision support benchmarksystem). Er besteht aus einer Reihe von businessorientierten
ad-hoc Abfragen und parallelablaufenden Datenmanipulationen. Um auf veränderte Anforderungen im Datenbank-Bereich reagieren zu können, werden die Rahmenbedingungen des
Benchmarks an die laufende Entwicklung in der Industrie angepasst und weiterentwickelt, das
eigentliche Schema bleibt aber bestehen. Die aktuelle Versionsnummer des TPC-H ist 2.3.0.
Die Abfragen und die in der Datenbank enthaltenen Daten wurden vom TPC so ausgewählt,
dass sie eine möglichst breite branchenübergreifende Bedeutung haben. Es werden Abfragen
mit einem hohen Komplexitätsgrad ausgeführt und dadurch versucht Antworten auf kritische
Geschäftsfragen zu geben.
3
2. Grundlagen
2.1.1. Testumgebung
Die Datenbasis des Benchmarks besteht aus 8 eigenständigen Tabellen von zum Teil sehr
unterschiedlicher Grösse.
Abbildung 1: TPC-H Schema
Bei den kursivgedruckten Attributen handelt es sich um die Primärschlüssel der jeweiligen
Tabelle. Die Pfeilrichtung gibt die jeweilige Fremdschlüsselbeziehung an. Die Zahl unter dem
Tabellennamen gibt die Kardinalität (die Anzahl Zeilen) der jeweiligen Tabelle an. Die Kardinalitäten, die noch mit dem Faktor SF multipliziert werden, hängen vom Scaling Factor (dem
Skalierungswert) des Benchmarks ab. Dieser Wert bestimmt die Grösse der zu Grunde liebenden Datenbasis. Er kann die folgenden Werte annehmen:
SF = 1, 10, 30, 100, 300, 1000, 3000, 10000, 30000, 100000.
Bei einem SF = 1 beträgt die geschätzte Grösse der Datenbasis etwa 1 GB. Um Datenbanken mit einem grösseren Volumen zu simulieren, kann der SF entsprechend grösser gewählt
werden, und ergibt dadurch entsprechend eine Datenbasis von:
Ca. 10GB, 30GB, 100GB, usw...
Die Datenbestände müssen mit dem DBGEN Programm erstellt werden, welches von der
TCP-Organisation zur Verfügung gestellt wird.
Das Benchmarkschema von TPC-H bildet die Geschäftsprozesse eines imaginären Handelsunternehmen ab, das Waren (Part) von seinen Lieferanten (Supplier) bezieht. PartSupp gibt an
welche Waren von welchem Lieferanten geliefert werden können, so wie deren Anzahl und
Preis. Die Kunden (Cusomer) können Bestellungen (Order) aufgeben, welche wiederum aus
mehreren Bestellposten (Lineitem) bestehen können. Sowohl Kunden als auch Lieferanten
können unterschiedlicher Nationalität (Nation) sein und somit über die 5 unterschiedlichen
Kontinente (Region) aufgeteilt.
4
2. Grundlagen
2.1.2. Queries
Auf dieser Datenbasis werden 22 Decision-Support-Anfragen ausgeführt, die jeweils unterschiedliche geschäftsbezogene Anfragen für das zugrundeliegende Handelsunternehmen darstellen. Dies dient dazu, den betriebswirtschaftlichen Kontext zu verdeutlichen, in dessen Zusammenhang die Abfrage für ein beliebiges Unternehmen benutzt werden könnte.
Über die 22 Anfragen hinaus gibt es noch zwei Refresh-Funktionen. Diese beiden Funktionen fügen durch INSERT-Befehle Daten dem Datenbestand hinzu, bzw. nehmen mittels
DELETE-Befehlen Daten aus dem Datenbestand heraus. Es handelt sich bei all diesen Anfragen um sogenannte ad-hoc Anfragen. Es wird also davon ausgegangen, dass der Benutzer
nicht weiss, welche Anfragen an das Datenbanksystem gestellt werden und wie die Daten in
der Datenbank abgespeichert sind. Die Anfragen dürfen also nicht mit Hilfe von Kenntnissen
über den Datenbestand oder die Anfragereihenfolge optimiert werden.
2.1.3. Ergebnis
TPC-H definiert drei grundlegende Metriken, die dazu dienen, die getesteten Datenbanksysteme in 3 unterschiedlichen Bereichen in eine Leistungsskala einordnen zu können.
• Eine Anfragen-pro-Stunde (query-per-hour) Metrik (QphH@Size). Sie gibt an, wie viele Anfragen einer bestimmten Grösse (@Size) pro Stunde erfolgreich verarbeitet werden
können.
• Eine Preis-Leistungs (price-performance) Metrik ($/QphH@Size mit $ = Gesamtpreis
des Systems). Sie gibt das Preis-Leistungsverhältnis des Datenbanksystems an.
• Eine Systemverfügbarkeits (systems-availability-date) Metrik. Sie gibt an seit wann alle
am Test beteiligten Systemkomponenten auf dem Markt frei verfügbar sind.
Einen Auszug aus den Top Ten TPC-H by Price/Performane finden Sie im Anhang E. Für
weiterführende Informationen besuchen Sie die Homepage http://www.tpc.org.
5
2. Grundlagen
2.2. ORACLE-Datenbanksystem
Die folgende Zusammenfassung basiert auf dem Oracle Database 10g DBA-Handbuch [3].
Das Oracle-Datenbanksystem ist ein relationales Datenbank Management System, das sich
logisch in zwei Bereiche unterteilen lässt. Einerseits die eigentlichen Datenbankinstanzen und
andererseits der Oracle Database Server.
Während die Oracle-Datenbank auf Server-Laufwerken gespeichert ist, existiert die OracleInstanz im Arbeitsspeicher des Servers. Eine Oracle-Instanz besteht aus einem grossen Speicherblock, der in einem Bereich namens System Global Area (SGA) reserviert ist, und zahlreichen
Hintergrundprozessen, die zwischen der SGA und den Datenbankdateien auf den Laufwerken
interagieren.
Die Datendateien in einer Oracle-Datenbank sind in einem oder mehreren Tablespaces gruppiert. Innerhalb der Tablespaces, die zusammen die logische Datenbankstruktur aus Tabellen
und Indizies bilden, gibt es Segmente. Und diese Segmente sind nochmals in Extents und
Blöcke unterteilt.
2.2.1. Aufbau
Tablespaces:
Ein Oracle-Tablespace besteht aus einer oder mehreren Datendateien; eine Datendatei kann
nur zu einem einzigen Tablespace gehören. Beim Installieren von Oracle 10g werden mindestens zwei Tablespaces eingerichtet: Der SYSTEM- und der SYSAUX-Tablespace. Mit Bigfiles
(bis zu 1 Mio. Terabytes) wird die Tablespace-Verwaltung für den Datenbank-Administrator
(DBA) vollkommen transparent. Mit anderen Worten kann der DBA den Tablespace als Einheit verwalten, ohne sich um die Grösse und Struktur der darunter liegenden Datendateien
kümmern zu müssen. Noch einfacher ist die Verwaltung der Datendateien in den Tablespaces
mit den Oracle Managed Files (OMF). Mit OMF definiert der DBA einen oder mehrere Standorte im Dateisystem, an denen Datendateien, Steuerdatein und Redo Log-Dateien liegen sollen. Oracle übernimmt automatisch die Benennung und das Management dieser Dateien.
Bei lokal verwalteten Tablespaces unterhält Oracle für jede Datendatei im Tablespace ein
Bitmap, das die Verfügbarkeit des Speicherplatzes protokolliert. Nur die Platzkontingente werden weiterhin im Data Dictionary verwaltet, was potenzielle Engpässe bei Data DictionaryDateien drastisch reduziert.
Blöcke:
Ein Datenblock ist die kleinste Speichereinheit in der Oracle-Datenbank. Die Grösse eines
Blocks ist eine spezifische Anzahl Speicherbytes innerhalb eines Tablespaces in der Datenbank.
6
2. Grundlagen
Extents:
Ein Extent ist die nächste Ebene der logischen Gruppierung in der Datenbank. Ein Extent
besteht aus einem oder mehreren Datenbankblöcken. Vergrössert man ein Datenbankobjekt,
wird der zusätzliche Speicherplatz für das Objekt als Extent reserviert.
Segmente:
Die nachfolgende Ebene der logischen Gruppierung ist das Segment. Es ist eine Gruppe von
Extents mit einem Datenbankobjekt, das als Einheit behandelt wird. Bsp. Tabelle oder Index. Es ist die kleinste Speichereinheit, mit der ein Endanwender umgeht. In einer OracleDatenbank gibt es vier Segmenttypen: Datensegmente, Indexsegmente, temporäre Segmente
und Rollback-Segmente.
Tabellen:
In einer relationalen Datenbank werden alle Daten in zweidimensionale Tabellen abgespeichert, die sich aus Zeilen und Spalten zusammen setzen. In der Oracle-Datenbank gibt es
verschiedene Tabellentypen die man je nach Applikation auswählen kann.
Relationale Tabellen sind unsortiert, das heisst, die Zeilen in der Tabelle sind nicht in einer
bestimmten Abfolge hinterlegt. Jede Zeile einer Tabelle umfasst eine oder mehrere Spalten.
Eine Spalte kann einen benutzerdefinierten Objekttyp, eine verschachtelte Tabelle oder ein
VARRAY enthalten. Zusätzlich lässt sich eine Tabelle als Objekttabelle definieren.
Temporäre Tabellen sind hinsichtlich der Daten temporär, die in der Tabelle gespeichert
sind, nicht im Bezug auf die Tabellendefinition. In einer temporären Tabelle gibt es unterschiedliche Typen von temporären Daten: temporär für die Dauer der Transaktion, und temporär für die Dauer der Sitzung.
Indexorganisierte Tabellen haben den Vorteil, dass durch das Anlegen eines Index eine bestimmte Zeile innerhalb einer Tabelle leichter aufzufinden ist. Damit generiert man jedoch
einen kleinen Overhead, da die Datenbank neben den Datenzeilen auch die Indexeinträge für
die Tabelle verwalten muss.
Objekttabellen besitzen Zeilen, die ihrerseits Objekte sind, oder Instanzen von Typdefinitionen. Zeilen in einer Objekttabelle lassen sich über eine Objekt-ID referenzieren, im Gegensatz
zu einem Primärschlüssel in einer relationalen oder regulären Tabelle. Dennoch können Objekttabellen, genau wie relationale Tabellen, sowohl primäre als auch eindeutige Schlüssel
besitzen.
7
2. Grundlagen
Externe Tabellen erlauben dem Benutzer den Zugriff auf eine Datenquelle, wie eine Textdatei, als handle es sich um eine Tabelle in der Datenbank. Die Metadaten für die Tabelle
sind im Oracle Data Dictionary, die Inhalte der Tabelle hingegen sind extern gespeichert.
Auf externe Tabellen lassen sich keine Indizes anlege und keine inserts, updates oder deletes ausführen. Vorteile sind dagegen, dass sich zum Beispiel eine native Datenbanktabelle in
eine Data Warehouse-Umgebung laden lässt.
Geclusterte Tabellen sind eine Möglichkeit zur Performanceverbesserung wenn häufig auf
eine oder zwei Tabellen zugegriffen wird. Ein spezieller Typ für geclusterte Tabellen ist ein
Hash-Cluster, welcher zum Abholen und Speichern der Daten in einer Tabelle eine HashingFunktion nutzt. Ein Hash-Cluster kann jedoch kein Cluster-Index verwenden.
Neu bei Oracle 10g sind die sortierten Hash-Cluster. Diese ermöglichen zusätzlich, dass die
Zeilen in der Tabelle über eine oder mehrere Spalten in aufsteigender Reihenfolge gespeichert
werden. Damit lassen sich die Daten jener Applikationen wesentlich schneller verarbeiten, die
nach dem FIFO-Prinzip arbeiten.
Grosse Tabellen lassen sich durch Partitionieren einfacher verwalten. Oracle empfiehlt, alle
Tabellen grösser als 2 GB zu partitionieren.
Constraints:
Ein Oracle-Contraint besteht aus einer oder mehreren Regeln, die sich zur Durchsetzung der
Geschäftsregeln für eine oder mehrere Spalten definieren lassen. So kann ein Constraint beispielsweise die Geschäftsregel erzwingen, dass das Anfangsgehalt eines Angestellten mindestens CHF 25’000 beträgt. Auf Spalten lassen sich sechs Typen von Datenintegritätsregeln
anwenden:
• NULL-Regel -> NOT NULL
• eindeutige Spaltenwerte -> UNIQUE
• Primärschlüsselwerte -> PRIMARY KEY
• referenzielle Integirtätswerte -> FOREIGN KEY
• komplexe In-Line-Integrität -> CHECK
• Trigger-basierte Integrität -> mit create trigger-Befehl und PL/SQL-Block (Geschäftsregel)
8
2. Grundlagen
Indizes:
Ein Oracle-Index ermöglicht einen schnelleren Zugriff auf Zeilen in einer Tabelle, wenn nur
ein kleines Subset der Zeile aus der Tabelle abzuholen ist. Indizes legt man auf einzelne oder
mehrere Spalten an. Der eindeutige Index beschleunigt nicht nur die Suchgeschwindigkeit,
sondern erzwingt auf die indizierte Spalte zusätzlich einen UNIQUE- oder PrimärschlüsselConstraint. Die Indexeinträge werden in dem Moment akutalisiert, wo man Inhalte einfügt,
aktualisiert oder löscht. Wird die Tabelle gelöscht, werden automatisch alle Indizes gelöscht.
Views:
Mit Hilfe von Views sehen Anwender eine aufbereitete Darstellung der Daten in einer Tabelle oder von Joins zwischen mehreren Tabellen. Ein View ist auch eine gespeicherte Abfrage,
die Abfragedetails bleiben aber dem Benutzer einer View verborgen. Eine reguläre View speichert keine Daten, sondern nur die Definition, und die darunter liegende Abfrage wird bei
jedem Zugriff auf die View ausgeführt. Die materialisierte View speichert neben der Abfrage
auch die Abfrageergebnisse, was unter anderem die Verarbeitung beschleunigt. Objekt-Views
verbergen die Details der darunter liegenden Tabellen-Joins. Zudem ermöglichen sie die objektorientierte Verarbeitung in Datenbanken, in denen die darunter liegenden Tabellen noch
im relationalen Format vorliegen.
Physische Speicherstrukturen in Oracle:
Die Oracle-Datenbank nutzt zum Halten und Verwalten der Benutzertransaktionen auf dem
Laufwerk zahlreiche physische Speicherstrukturen. Datendateien, Redo Log-Dateien und archivierte Redo Log-Dateien halten aktuelle Benutzerdaten vor. Steuerdateien verwalten den
Status der Datenbankobjekte. Textbasierte Alert- und Trace-Dateien enthalten Logging-Informationen über Routine-Events und Fehlerbedingungen in der Datenbank.
Oracle-Arbeitsspeicherstrukturen:
Der für eine Oracle-Instanz reservierte Datenbereich heisst System Global Area (SGA) und
liegt im Arbeitsspeicher des Servers. Darin enthalten sind:
• der Programmcode für Oracle selbst
• Sitzungsinformationen
• individuelle Prozesse für die Datenbank
• Sitzungsinformationen, die von allen Prozessen benötigt werden
• SQL-Anweisungen für Benutzer
• das Data Dictionary
• gecachte Informationen wie Datenblöcke von Datenbanksegmenten
• Informationen über beendete Transaktionen in der Datenbank
9
2. Grundlagen
Die Programme liegen im Software-Codebereich. Zusätzlich gibt es die Program Global
Area (PGA), die privat für jeden Server- und Hintergrundprozess ist. Für jeden Prozess wird
eine PGA reserviert. Die Konfiguration der PGA hängt von der Verbindungskonfiguration der
Oracle-Datebank ab: entweder Shared Server oder dediziert.
In einer Shared Server-Konfiguration nutzen mehrere Benutzer eine Verbindung zur Datenbank, was zwar die Belastung des Arbeitsspeichers auf dem Server minimiert, aber die
Antwortzeiten für Benutzeranforderungen verlängern kann. In Die Sitzungsinformationen für
den Benutzer enthält nicht die PGA, sondern die SGA. In einer dedizierten Serverumgebung
erhält jeder Benutzerprozess eine eigene Verbindung zur Datenbank; die PGA enthält die Sitzungsinformationen.
Hintergrundprozesse:
Beim Start einer Oracle-Instanz starten auch zahlreiche Hintergrundprozesse. Zwei Beispiele
sind SMON und PMON.
SMON ist der System Monitor-Prozess. Im Falle eines Systemzusammenbruchs oder Instanzfehlers führt der SMON-Prozess ein Crash-Recovery aus. Dazu werden die Einträge in
den Online Redo Log-Dateien auf die Datendateien angewendet. Zusätzlich werden die temporären Segmente in allen Tablespaces gelöscht. Falls eine Benutzersitzung oder ein Benutzerprozess scheitert, übernimmt der Prozessmonitor (PMON) die Aufräumarbeiten. Er räumt
den Datenbank-Puffercache und alle anderen Ressourcen auf, die von der Benutzersitzung
verwendet wurden.
Backup und Recovery:
Oracle unterstützt verschiedene Formen von Backups und Recoveries. Manche Varianten lassen sich über Benutzerebenen verwalten, wie Export und Import, doch die Mehrzahl sind strikt
dem DBA vorbehalten. Dazu zählen Online- und Offline-Backups, der Einsatz von Betriebssystembefehlen oder das Dienstprogramm RMAN.
Eine Möglichkeit für ein physisches Backup der Datenbank ist das Ausführen eines OfflineBackups. Dazu wird die Datenbank heruntergefahren und alle datenbankbezogenen Dateien
werden aneinen zweiten Standort kopiert. Befindet sich die Datenbank im archivelog-Modus,
lässt sich die Datenbank mit Online-Backups sichern. Die Datenbank kann offen bleiben und
ist damit während des Backup-Prozesses für alle Benutzer verfügbar. Zuerst überführt man
den Tablespace in den Backup-Status, sicher die Datendateien im Tablespace mit Betriebssystembefehlen und setzt den Tablespace anschliessend wieder in den normalen Status zurück.
10
2. Grundlagen
Sicherheitsoptionen:
Objektberechtigungen steuern den Zugriff auf Daten und andere Objekte. Mit Systemberechtigungen steuert man den Zugriff auf Aktionen in der Datenbank.
Um den Zugriff auf die Objekte in der Datenbank durch Benutzer zu prüfen, lässt sich mit
Hilfe des audit-Befehls auf spezifische Objekte oder Aktionen ein Audit-Trail oder Prüfpfad
einrichten. Dabei kann man sowohl die SQL-Anweisungen als auch die Zugriffe auf ein
bestimmtes Datenbankobjekt überwachen. Das übermässige Auditing von Datenbankobjekten kann sich jedoch auf die Performance auswirken. Deshalb sollte man mit einem BasisAuditing auf die zentralen Berechtigungen und Objekte beginnen und das Auditing erst erweitern, wenn mit dem Basis-Auditing potenzielle Problemfelder aufgedeckt wurden.
Die Virtual Private Database verbindet eine hoch auflösende Zugriffssteuerung mit einem
sicheren Applikationskontext. Die Sicherheitspolitik ist an die Daten und nicht an die Applikation gebunden. Dadurch wird sichergestellt, dass die Sicherheitsregeln unabhängig vom
Datenzugriff erzwungen werden.
Die Oracle Label Security bietet eine vordefinierte Lösung, um den Zugriff auf die Zeilen
in allen Tabellen einzuschränken. Dies erlaubt beispielsweise dem DBA eines Application
Service provider, nur eine Instanz mit zulässigen Applikationen einzurichten, und mit Hilfe
der Label Security in allen Tabellen die Zeilen einzuschränken, die den einzelnen Accounts
einer Firma als zulässige Informationen zur Verfügung stehen.
2.2.2. Werkzeuge
Oracle Enterprise Manager:
Der Oracle Enterprise Manager (OEM) ist ein Toolset für die umfassende Verwaltung aller Komponenten einer Oracle-Infrastruktur, inklusive der Oracle-Datenbankinstanzen, Oracle
Applikation Server und Webserver. Der OEM ist via Netscape oder Internet Explorer voll
webfähig. Damit lässt sich die OEM-Konsole von jeder Plattform starten, die Netscape oder
den IE unterstützt. OEM bietet zudem den Vorteil, dass sich E-Mail-Benachrichtigungen für
kritische Bedingungen an den SYSMAN oder andere E-Mail-Accounts versenden lassen.
11
2. Grundlagen
Die folgende Liste gibt einen Überblick über verschiedene Werkzeuge, die Oracle anbietet
um mit der Datenbank zu arbeiten:
• Oracle Universal Installer (OUI)
• Database Configuration Assistant (DBCA)
• Administration Assistant for Windows
• Database Upgrade Assistant
• Oracle Net Manager
• Ultra Search und Ultra Search Administration Tool
• Oracle Enterprise Manager 10g Database Control
• SQL*Plus und iSQL*Plus
Mit Hilfe des Oracle Universal Installers kann das ganze Oracle-System, sowie Zusatzsoftware, installiert und deinstalliert werden. Der Database Configuration Assistant ist das
Werkzeug zum Erstellen, Löschen und Modifizieren der eigentlichen Datenbanken. Eine Datenbank kann auch manuell, ohne DBCA, erstellt werden. Der Oracle Administartion Assistant
for Windows dient dazu, administrative Aufgaben mit Hilfe eines grafischen Benutzerinterfaces im Windows Stil zu erleichtern. Darüber hinaus bietet er die Möglichkeit verschiedene
Parameter des Oracle-Systems zu modifizieren.
Oracle Net Manager:
Oracle Net dient als Schnittstelle zwischen einer Clientanwendung und einem Oracle-Datenbankserver und ist für den Verbindungsaufbau und die Überwachung einer Netzwerksession,
genauso wie für den Nachrichtenaustausch zwischen den beiden Instanzen verantwortlich.
Oracle Net muss auf jedem Client und Server im betreffenden Netzwerk installiert sein. Der
Oracle Net Manager kann dann dazu eingesetzt werden die jeweiligen Computer zu konfigurieren. Dies kann sowohl vom Client wie auch vom Server aus geschehen. Der Oracle Net
Manager ist auch im Oracle Enterprise Manager integriert und kann durch diesen aufgerufen
werden.
12
2. Grundlagen
2.3. SUSE LINUX und Windows XP Client
Der Oracle-Datenbank-Server wurde auf SUSE LINUX installiert. Der Oracle-DatenbankClient wurde auf Windows XP installiert.
2.3.1. SUSE LINUX 10.0
Hardware:
• Intel Pentium IV mit 3 GHz
• 1 GB RAM
• 59 GB Festplatte
Software:
• SUSE LINUX 10.0
• Oracle Database 10 g
2.3.2. Windows XP Home Edition 2002 SP1
Hardware:
• Intel Pentium IV mit 2 GHz
• 256 MB RAM
• 40 GB Festplatte
Software:
• Windows XP Home Edition 2002 SP1
• Oracle Databse Client
• Microsoft Visual Studio .NET 2003
13
3. Einrichten der Testumgebung
3. Einrichten der Testumgebung
Bevor irgendetwas getestet werden kann, muss einige Vorarbeit geleistet werden. Dieses Kapitel beschreibt zunächst die Installation des SUSE 10.0 Linux Betriebssystem. Danach wird das
Oracle 10g Datenbanksystem auf SUSE 10.0 installiert und zum Schluss wird die Datenbank
erstellt.
3.1. Installation von SUSE
Da Oracle 10g nur SUSE 9 unterstützt, habe ich zuerst versucht SUSE 9 zu installieren. Doch
die Fehlermeldung, es könne keine Festplatte gefunden werden hat mich dazu veranlasst SUSE 10.0 zu installieren. Mittlerweile gibt es SUSE 10.1 Final und SUSE 10.2. Alpha-Version.
Die Installation des SUSE Linux Systems wird durch den Systemassistenten YaST ausgeführt. Nach einlegen der Installations-CD muss man den Computer neu starten und folgt
den Anweisungen. Genaue Installations-Handbücher findet man auf der Homepage von Novell http://www.novell.com.
An dieser Stelle sei erwähnt, dass YaST “stärker“ ist, als die Konsole. Das heisst, wenn
Änderungen am System über die Konsole gemacht werden, kann es sein, dass YaST diese
beim nächsten Neustart überschreibt.
Als Oberfläche kann man zwischen KDE Desktop und GNOME Desktop wählen. Ich habe
mich für KDE entschieden.
Das Installieren und Löschen von Software erfolgt über den YaST Package Manager. Diesen wird man am Anfang öfter brauchen, denn um Oracle 10g zu installieren, muss man zuvor
noch einige Pakete für SUSE installieren. Das wird im nächsten Unterkapitel beschrieben.
Abbildung 2: Yast Package Manager
14
3. Einrichten der Testumgebung
Um später vom Windows Client auf den SUSE-Rechner zugreifen zu können, muss ein
Programm für die Datenübertragung installiert werden. Neben Samba (bestehend aus einer
Sammlung von Tools mit denen UNIX-Rechner Drucker und Dateien für Windowsrechner
über das Netzwerk freibgeben kann) gibt es das FTP (File Transfer Protocol), welches auf
SUSE 10.0 leider nicht läuft. Dafür geht es mit SFTP (Simple File Transfer Protocol) umso
einfacher. Samba habe ich installiert, um auf dem Drucker der Schule drucken zu können. Ein
Drucker kann auf Linux auch mit ”CUPS”(= Common UNIX Printing System) eingerichtet
und verwaltet werden. Gehen Sie dazu auf die Homepage http://www.cups.org.
Während der Installation wird man aufgefordert, das root-Passwort zu setzen. Hier sei angemerkt, dass man bei Linux-Systemen allgemein nur als root angemeldet sein sollte, wenn
es unbedingt nötig ist. Ist das Passwort gesetzt, kommt man zu den Netzwerk-Einstellungen.
Die Adresskonfiguration erfolgt automatisch mit DHCP. Auch hier gibt es bei der Installation
von Oracle 10g einige Probleme, die im nächsten Unterkapitel beschrieben werden.
Arbeitet man in einem Netzwerk, sollte man nicht vergessen, die Firewall zu aktivieren.
Um vom Windows-Client auf den SUSE-Rechner zugreifen zu können, habe ich den Port
1521 (OracleNet) geöffnet.
Zu guter Letzt erstellen wir einen separaten User für die Arbeit mit der Oracle Datenbank.
Ich habe meinen User “oracle“ genannt und mit dem Passwort “test“ versehen.
15
3. Einrichten der Testumgebung
3.2. Installation von Oracle
Für die Installation von Oracle 10g habe ich das Online-Handbuch “Oracle Database Quick
Installation Guide“ [4] verwendet. Nehmen Sie bitte dieses Handbuch zur Unterstützung, ich
werde in diesem Kapitel hauptsächlich auf die Probleme die auftreten können eingehen.
Zuerst muss man sich in einer Konsole als root anmelden um einige Hardware-Anforderungen zu überprüfen. Es muss auf jeden fall genügend Speicherplatz vorhanden sein. Genaue
Angaben dazu findet man im genannten Handbuch [4].
Oracle 10g unterstütz die Linux-Distributionen RedHat und SUSE, aber bei SUSE nur die
Version 9.0. Ich musste also einen kleinen Trick anwenden und habe die Datei /etc/SUSErelease so abgeändert, dass jetzt “SUSE LINUX 9.0“ darin steht. Das System denkt nun, es
sei Version 9.0 obwohl es Version 10.0 ist. Die unterstützten Kernel-Versionen sind von 2.6.5
bis 7.97. Mein System hat die Kernel-Version 2.6.13.
Bevor man nun mit der Installation beginnen kann, müssen einige zusätzlichen Pakete installiert werden. Welche das genau sind, inklusive Versionsnummer, findet man im Handbuch
[4].
Beim Erstellen des oracle-Benutzers sollte man nicht vergessen, ihm ein home-Verzeichnis
zu geben. Sonst könnte bei der späteren Installation ein seltsamer Fehler (”DISPLAY”) auftreten, aus dem nicht ersichtlich ist, dass es um das home-Verzeichnis geht.
Der nächste spannende Punkt sind die Kernel Parameter. Die Änderungen sind in der Datei
/etc/sysctl.conf vorzunehmen. Hier kommt nun das Problem, dass YaST gewisse Änderungen
überschreibt. Es werden zumindest nicht alle Änderungen auf Anhieb angenommen. Es wird
empfohlen, das Paket “orarun“ zu installieren. Welches dann ein weiteres Paket namens “libaio“
benötigt. Mit “orarun“ werden die meisten Änderungen dann angenommen. Genaue Angaben
zum Vorgehen mit “orarun’”findet man im Handbuch “Oracle 10g R1 (10.1.0.3) on SUSE LINUX Enterprise Server 9“ [5]. Die einzige Änderung die jetzt noch nicht akzeptiert wird, ist
”net.ipv4.ip local port range”. Als Nothilfe kann man vor jedem Versuch der Intallation von
Oracle in der Konsole den Befehl “echo 1024 65000 > /proc/sys/net/ipv4/ip local port range“
eingeben.
Wenn alles gemäss Handbuch [4] vorbereitet ist, kann man mit der Installation beginnen.
Das Standardverzeichnis ist /home/oracle/oracle/product/10.2.0/db 1
16
3. Einrichten der Testumgebung
Legt man die Oracle 10g CD ein um die Installation zu starten, werden zunächst die Anforderungen geprüft. Ich hatte an dieser Stelle einen Fehler mit der anschliessenden Empfehlung: “Oracle unterstützt Installationen in Systemen mit DHCP-zugewiesenen öffentlichen
IP-Adressen. Die primäre Netzwerkschnittstelle in dem System muss jedoch mit einer statischen IP-Adresse konfiguriert werden, damit die Oracle-Software richtig ausgeführt werden
kann.“ Ich habe meinen Rechner dann so konfiguriert, doch der Fehler blieb weiterhin bestehen. Ich habe die Installation dann trotzdem ausgeführt. Das heisst, dieser Fehler kann
ignoriert werden.
Abbildung 3: Oracle Universal Installer
Als nächstes muss der Oracle Client auf Windows installiert werden. Das geht problemlos.
Man wählt einfach die Installation als “Administrator“ aus und folgt den Anweisungen.
Sobald die Datenbank auf dem SUSE-Rechner erstellt ist, können praktisch alle Arbeiten
über die Enterprise Manager Konsole vom Client aus durchgeführt werden.
17
3. Einrichten der Testumgebung
3.3. Datenbank erstellen
Der Installationsort von Oracle auf meinem SUSE-Rechner ist;
/home/oracle/oracle/product/10.2.0/db 16 (db 16 weil 15 Versuche Fehl schlugen)
• unter db 16 welchseln wir ins Verzeichnis /bin und setzten den Pfad und das Home;
• geben Sie “. oraenv“ ein, dann fragt er;
• “ORACLE SID = [oracle] ?“ worauf Sie “test“ eingeben (oder das Passwort, welches
Sie gesetzt haben).
• Nun können Sie die Datenbank starten indem Sie ”dbcaëingeben und mit Return bestätigen.
Es erscheint folgendes Bild
Abbildung 4: Datenbank Konfigurationsassistent
18
3. Einrichten der Testumgebung
Abbildung 5: Schritt 1
Abbildung 6: Schritt 2
Wir kreieren eine benutzerdefinierte Datenbank.
19
3. Einrichten der Testumgebung
Abbildung 7: Schritt 3
Der Name der Datenbank ist hier ”test”, die SID ist automatisch auch ”test”.
Abbildung 8: Schritt 4
20
3. Einrichten der Testumgebung
Abbildung 9: Schritt 5
Abbildung 10: Schritt 6
21
3. Einrichten der Testumgebung
Abbildung 11: Schritt 7
Abbildung 12: Schritt 8
22
3. Einrichten der Testumgebung
Abbildung 13: Schritt 9
Abbildung 14: Schritt 10
23
3. Einrichten der Testumgebung
Abbildung 15: Schritt 11
Abbildung 16: Schritt 12
24
3. Einrichten der Testumgebung
3.3.1. Benutzer und Tablespace anlegen
Den Benutzer und die Tablespace anlegen kann man bequem über die Oracle Enterprise
Manager-Konsole auf dem Windows Client. Möchte man sich nun über die Manager-Konsole
mit dem Server (SUSE-Rechner) verbinden, muss man den Listener auf dem Linux-Rechner
starten:
• ../oracle/product/10.2.0/db 16/bin (enter)
• . oraenv (enter)
• test (enter)
• sqlplus (enter)
• / as sysdba (enter)
• SQL> startup (enter)
“Datenbank geöffnet“
• exit (enter)
• ../oracle/product/10.2.0/db 16/bin> lsnrctl start (enter)
Um überhaupt mit der Oracle Enterprise Manager-Konsole arbeiten zu können, gehen Sie
ins Verzeichnis;
D:\oracle\product\10.2.0\client 1\BIN
und erstellen eine Verknüpfung von “oemapp.bat“ und schreiben bei den Eigenschaften
“console“ dahinter, so wie auf folgendem Bild
Abbildung 17: Eigenschaften oemapp.bat
25
3. Einrichten der Testumgebung
Zuerst muss man den Tablespace kreieren. In der Oracle Enterprise Manager-Konsole klickt
man auf “Speicher“, “Tablespace“ und rechtsklick auf “USERS“, dann auf “ ähnliche erstellen“. Der Name ist TPCH (TPCH1.dbf) und die Grösse 500MB.
Abbildung 18: Oracle Enterprise Manager-Konsole
Die wichtigsten Benutzer sind SYS und SYSTEM. Nun müssen wir noch einen separaten Benutzer kreieren. Das kann man auch über die Enterprise Manager-Konsole erledigen,
indem man auf Schema klickt und dann auf Erstellen (man kann auch über “Sicherheit”, ”Benutzer”gehen).
Ich habe den Benutzer ”tpchtest”wie folgt erstellt:
Abbildung 19: Benutzer - Allgemein
26
3. Einrichten der Testumgebung
Abbildung 20: Benutzer - Rolle
Abbildung 21: Benutzer - Quota
27
3. Einrichten der Testumgebung
3.3.2. Tabellen erzeugen mit DBGEN
Um den TPC-H Benchmark zu testen, muss man die Datenbank nach den Vorgaben des ”TPC
BENCHMARK H (Decision Support) Standard Specification Revision 2.3.0“ erstellen. Auf
der Homepage http://www.tcp.org unter TPC H kann man das Programm DBGEN
herunter laden. Das Programm erstellt die Tabellen. Meine Version ist; tpch 20051026.
Es ist einfacher das Programm auf Linux zu starten, die Dateien dann auf den WindowsClient zu kopieren und von dort mit der Manager-Konsole zu laden. Das DBGEN-Programm
direkt vom Windows-Client zu starten, kann zu grösseren Problemen führen, da es ursprünglich
für Linux-Betriebssysteme geschrieben wurde.
Ich habe also DBGEN auf dem SUSE-Rechner installiert. Gemäss dem ”Readme“ von DBGEN muss man das ”makefile.suite“ ändern und dann starten. Der default scale factor ist 1 GB.
Die Änderungen sehen wie folgt aus:
# Current values for WORKLOAD are: TPCH, TPCR
DATABASE= SQLSERVER
MACHINE = SUN
WORKLOAD = TPCH
Dann kann man DBGEN starten, indem man in einer Konsole in das Verzeichnis wechselt,
wo sich DBGEN befindet, “./dbgen“ eingibt und mit Return bestätigt.
Die Dateien
• customer.tbl
• lineitem.tbl
• nation.tbl
• orders.tbl
• partsupp.tbl
• part.tbl
• region.tbl
• supplier.tbl
werden erstellt. Das dauert ca. 2 Minuten.
28
3. Einrichten der Testumgebung
Nun können diese Dateien mit dem WinSCP auf den Windows-Rechner herunter geladen
werden. Man sollte unbedingt beachten, dass man die Dateien als ”Text“ kopiert.
Jetzt muss man die Tables kreieren. Dazu verwende ich die Datei ”create.txt“ welche man
im Anhang findet. Man speichert dieses als “sql.sql“ ab und kann es dann über die ManagerKonsole im SQL*Plus Worksheet starten, indem man auf “Arbeitsblatt“ klickt und dann auf
”Lokales Skript ausführen...”. Die Tabellen erscheinen;
Abbildung 22: Tabellen
29
3. Einrichten der Testumgebung
3.3.3. Load
Die Tabellen werden mit dem SQL-Loader mit Daten gefüllt. Dazu ist es empfehlenswert, die
im Anhang befindlichen Dateien zu verwenden. Diese Dateien müssen wie folgt angepasst
werden:
In der Datei “importdaten.cmd“ muss der Pfad angepasst werden, je nachdem wo sicher der
SQL-Loader befindet.
In der Datei “importdaten.par“ muss der “userid“ angepasst werden. Die Datei
“importdaten.cmd“ ruft mit jedem Start die Datei “importdaten.par“ auf, also für jede Tabelle
einzeln. Das heisst, in der Datei “importdaten.par“ muss man vor jedem Start die Datei der zu
ladenden Tabelle angeben, also “control = ¡tabellenname¿.ctl“.
Die Dateien <tabellenname>.ctl laden dann die Daten in die entsprechenden Tabellen. Das
heisst, dass man die <tabellenname>.tbl Dateien, die DBGEN erzeugt hat, nacht <tabellenname>.dat umbenennen muss und in den gleichen Ordner kopieren muss.
Ist das gemacht, kann man wie gesagt für jede Tabelle die Datei “importdaten.cmd“ starten.
Bei diesem Vorgang muss die Reihenfolge stimmen, sonst bekommt man Probleme mit den
Schlüsselbeziehungen.
Reihenfolge der Tabellen mit der benötigten Lade-Zeit in Minuten und Sekunden:
1.
2.
3.
4.
5.
6.
7.
8.
region:
nation:
supplier:
part:
partsupp:
customer:
orders:
lineitem:
0:02
0:01
0:04
0:22
1:20
0:17
2:03
16.57
In Oracle 10g müssen Nummer mit ”TO NUMBER(:...)”versehen werden.
30
3. Einrichten der Testumgebung
3.3.4. Indizes erstellen
Nun kann man die Indizes kreieren. Dazu habe ich die Datei “index.txt“ verwendet. Das Vorgehen ist gleich wie beim Kreieren der Tables, man ändert die Datei zu “sql.sql“ ab und führt
sie mit dem SQL-Worksheet aus. Die Datei befindet sich ebenfalls im Anhang.
Sollte beim Laden der Daten in die Tabellen ein Problem auftreten, zum Beispiel ein Fehler
“ora-02291“, dann kann auf der Homepage http://ora-02291.ora-code.com nachsehen, was der Fehler bedeutet, warum es ihn gegeben hat, und wie man ihn behebt.
Im Anhang B befinden sich die verschiedenen Files die in diesem Kapitel angesprochen
wurden. Zusätzliche Informationen finden Sie im “Oracle Database Utilities“ [6].
31
4. Testen
4. Testen
Um die Queries testen zu können, muss man ein Programm schreiben, entweder mit OracleOCI oder mit embeddedSQL und pro*c. Ich habe mich für embeddedSQL entschieden.
4.1. Pro*C
Pro*C ist ein Precompiler von Oracle für die Programmiersprache C und C++. Mittels des
Precompilers ist es möglich, SQL-Ausdrücke und normale C/C++ Quellcode-Elemente miteinander zu vermischen. Die daraus resultierende Datei wird schliesslich in gültigen Quellcode
übersetzt, um diesen mit einem herkömmlichen Compiler übersetzen zu können. Intern werden die im Quellcode eingebetteten SQL-Ausdrücke durch den Precompiler in normale Oracle
Funktionsaufrufe umgewandelt. Mittels Pro*C ist es möglich, mit einer Datenbank innerhalb
eines C-Programmes zu kommunizieren.
Abbildung 23: Pro*C Ablauf
Um Pro*c verwenden zu können, müssen einige Änderungen im Visual Studio vorgenommen werden. Die Änderungen sind im Visual Studio .NET anders, als im früheren Visual
Studio C++. Da es dafür leider noch kein offizielles Handbuch gibt, gehe ich hier detailliert
darauf ein. Das Vorgehen ist wie folgt:
32
4. Testen
1. neues Projekt erstellen, Win32-Konsoleprojekt
2. vorkompilierte Header nicht verwenden, stdafx.cpp und stdafx.h löschen
Abbildung 24: Eigenschaftenseiten
3. .pc-Datei kreieren; Hinzufügen, Neues Element hinzufügen
Abbildung 25: Neues Element hinzufügen
33
4. Testen
4. Rechtsklick auf .pc-Datei, Eigenschaften, Benutzerdef. Buildschritt, Befehlszeile
eingeben:
D:\oracle\product\10.2.0\client 1\BIN\proc C\ARBEIT\dotnet\doku\doku.pc
include=”C:\Programme\Microsoft Visual Studio .NET 2003\Vc7\include”
Der Pfad muss natürlich angepasst werden.
Abbildung 26: Befehlszeile
Im Feld Ausgaben muss folgendes eingetragen werden:
C:\ARBEIT\dotnet\doku\doku.cpp
Auch hier muss der Pfad angepasst werden.
Abbildung 27: Ausgaben
Abbildung 28: Eigenschaften
34
4. Testen
5. Rechtsklick auf doku (Projekt), Hinzufügen, Vorhandenes Element hinzufügen:
D:\oracle\product\10.2.0\client 1\precomp\LIB\msvc ->orasqx10.lib
Abbildung 29: orasqx10.lib
6. Rechtsklick auf Quelldateien, Hinzufügen, Vorhandenes Element hinzufügen:
C:\ARBEIT\dotnet\doku\doku.cpp
Abbildung 30: doku.cpp
35
4. Testen
Dann müssen noch zwei Änderungen vorgenommen werden, die nur einmal gemacht werden
müssen, und dann für alle Projekte gelten:
1. Klicken Sie im Menu auf Extras, Optionen, Projekte, VC++-Verzeichnisse,
Bibliothekdateien und fügen das Verzeichnis hinzu:
D:\oracle\product\10.2.0\client 1\precomp\public
Abbildung 31: Optionen
2. Im Verzeichneis D:\oracle\product\10.2.0\client 1\precomp\admin befindet sich die
Datei: pcscfg.cfg Öffnen Sie diese mit einem Editor und fügen die Zeilen hinzu:
CODE=CPP
PARSE=PARTIAL
Abbildung 32: pcscfg.cfg
Das wars auch schon. Weiterführende Informationen zu Pro*C finden Sie im “Pro*C/C++
Programmers Guide“ [2].
36
4. Testen
4.2. EmbeddedSQL
Embedded SQL ist eine Spracherweiterung von SQL, mit der es möglich ist, SQL-Anweisungen
innerhalb einer strukturierten oder objektorientierten Programmiersprache auszuführen.
Durch Queries ermittelte Informationen müssen in Variable gespeichert werden. Diese Variable sind in der SQL declare section zu definieren.
Bsp.:
EXEC SQL BEGIN DECLARE SECTION
char username[9] = ”TPCHTEST”;
char password[9] = ”TEST”;
char service[9] = ”TEST”;
EXEC SQL END DECLARE SECTION;
Dann kann man den Connect zur Datenbank machen:
EXEC SQL CONNECT: username
IDENTIFIED BY: password
USING: service;
Das select-Statement macht man am besten, indem man einen cursor deklariert:
EXEC SQL DECLARE mein cursor CURSOR FOR select <select-Statement>;
Dann öffnet man den cursor:
EXEC SQL OPEN mein cursor;
und macht einen Fetch:
EXEC SQL FETCH mein cursor INTO :nr, :name...
und schliesst ihn wieder:
EXEC SQL CLOSE mein cursor;
EXEC SQL COMMIT;
37
4. Testen
4.3. Programme erstellen
Der Quellcode der Programme Query1 bis Query22 befindet sich im Anhang C.
Ein solches Programm macht zuerst einen Connect auf die Datenbank, führt die SQLAbfage durch, schreibt das Erbegnis je nach Test-Version auf Konsole oder in eine Datei und
misst die benötigte Zeit.
Zuerst müssen die Programme so gestartet werden, dass man die Ergebnisse mit den Daten im TPC Benchmark H Decision Support vergleichen kann. Das heisst, man verwendet die
gleichen Parameter in den SQL-Abfragen.
4.4. Parameter erzeugen mit QGEN
Für die eigentlichen Test, generiert man die Parameter mit dem Programm QGEN. Dafür startet man auf dem SUSE-Rechner im Verzeichnis wo das Programm ist (bei mir; tpch 20051026)
QGEN indem man in der Konsole “./qgen“ eingibt und mit Return bestätigt.
Wenn man .QGEN ohne nachfolgenden Parameter startet, werden die Parameter für alle
Queries generiert. Man könnte sie auch einzeln generieren mit ./ qgen 1 (für Query1), das
macht jedoch wenig Sinn. Mit jedem Starten von QGEN werden andere Parameter erzeugt.
Das File mit den erstellten Parametern befindet sich im Anhang E.
38
5. Resultate
4.5. Tests
Ich habe verschiedene Tests durchgeführt, mit und ohne Indizes, mit und ohne Ausgabe auf die
Konsole und in die Datei, mit begrenzter Anzahl Zeilen und habe zwischendurch den Server
neu gestartet. Als erstes habe ich natürlich die Validierung gemäss TPC [1] gemacht um die
Ergebnisse vergleichen zu können, damit meine Programme nicht fehlerhaft sind. Hier eine
Übersicht:
Versions-Nr.
V01
V02
V03
V04
V05
V06
V07
V08
V09
V11
V12
V13
V14
V15
V16
valid
mit Indizes
ja
ja
ja
ja
ja
nein
nein
nein
nein
ja
ja
ja
ja
nein
ja
Ausg. auf Konsole
ja
ja
ja
nein
nein
ja
ja
ja
ja
ja
ja
nein
nein
nein
Ausg. in Datei Bemerkungen
ja
ja
bis 15 Zeilen
nein
nein
alle gleichzeitig
nein
nein
ja
bis 15 Zeilen
ja
ja
ja
bis 15 Zeilen
nein
nein
nein
Server neu gestartet
nein
Server neu gestartet
validierung gem. TPC
Die Versionen V01 - V05 habe ich an einem Donnerstag gemacht, alle anderen jeweils am
Wochenende. Die Netzwerkbelastung macht einen kleinen Unterschied. Ich habe noch einige
Tests direkt mit dem SQL-Scrachpad durchgeführt, darauf komme ich im Kapitel Tuning zu
sprechen.
5. Resultate
Die Resultate sind in Excel-Tabellen festgehalten, jeweils mit Graphiken. Sie sind im Anhang
A abgelegt. Interessant ist vorallem das Query 20, welches ohne Indizes bedeutend schneller
läuft, als mit Indizes. Die restlichen Queries laufen eher schneller mit Indizes. Das Query 10
ist dann besonders schnell, wenn es keine Resultate auf die Konsole oder in die Datei schreiben
muss. Ähnlich wie bei Query 10 ist es auch bei Query 16. Die restlichen Queries zeigen nur
kleinere Differenzen. Einige Dateien in denen die Resultate rausgeschrieben wurden, finden
Sie im Anhang C.
39
6. Tuning
6. Tuning
Das Handbuch “Oracle Database Performance Tuning Guide“ [7] enthält ausführliche Informationen zum Tunen und Optimieren der Datenbank und der SQL-Abfragen.
6.1. Explain-Plan
Für jeden SQL-Befehl erstellt der Optimierer einen Ausführungsplan vor der Ausführung. Der
Ausführungsplan besteht aus einer Abfolge von Operationen die Oracle durchführt, wenn ein
SQL-Befehl ausgeführt wird.
Im Ausführungsplan sind enthalten:
• alle referenzierten Tabellen in der verwendeten Reihenfolge
• sämtliche verwendete Zugriffsmethoden auf die Tabellen
• die Join-Methode für die in eine Join-Operation einbezogenen Tabellen
• Operationen wie filter, sort oder aggregation
Zusätzlich zu den Reihen im Quellcode-Baum enthält der Ausführungsplan folgende Informationen:
• Optimierung, wie Kosten und Kardinalität jeder Operation
• Unterteilung der Zugriffe
• Parallele Ausführung, zum Beispiel bei join-Methoden
Der Ausführungsplan hilft zu verstehen, was der Optimierer macht, warum er beispielsweise eine verschachtelte Schlaufe von joins wählt, anstatt ein hash join.
40
6. Tuning
Die üblichen Verfahren für den Tabellenzugriff sind:
• Vollständiger Tablescan: wird üblicherweise verwendet, wenn es bei einer Tabelle keine
oder mehrere ungeeignete Indizes hat. Oder falls beim Zugriff mehr als 20 Prozent der
Tabellendaten gelesen werden müssen.
• Per RowID: Üblicherweise als Ergebnis eines Indexscans. Dies ist die schnellste Zugriffsmethode auf Daten, die in einer Tabelle enthalten sind. Eine RowID verweist direkt
auf eine bestimmte Zeile in einer Tabelle.
• Clusterscan: Nur im Zusammenhang mit Clustern einer Datenbank. Cluster werden (mit
Ausnahme des Data Dictionary) nicht sehr häufig eingesetzt, sodass diese Zugriffsmethode im Allgemeinen nicht zur Anwendung kommt.
• Hash-Scan: Üblicherweise beim Fehlen von Indices, bei ungeeigneten Indices oder beim
Zugriff auf mehr als 20 Prozent der Tabellendaten.
Wenn man im SQL-Scrachpad ein select durchführt, kann man sich anschliessend den
explain-plan anzeigen lassen. Dazu klicken Sie links auf das zweite Symbol von oben.
Abbildung 33: SQL-Scrachpad
Es erscheint das bild des explain-plan. Für detailliertere Angaben, kann man sich den Bericht ansehen. Ich habe dies für die Queries 2, 4, 7, 10 und 20 gemacht und im Anhang abgelegt. Die Queries 7 und 20 sind ohne Indizes schneller, als mit.
Man sieht nicht nur die Operation, die ausgeführt wird, sondern auch die geschätzten Kosten für den jeweiligen Zugriff.
41
6. Tuning
Die Statistik sagt außerdem noch etwas über die Anfragegröße und die Menge der übertragenen
Daten aus.
Abbildung 34: explain-plan
In diesem Bild sieht man, dass der 1. Schritt des Plans, TPCHTEST.PART TABLE ACCESS
ist, d.h. er ruft alle Zeilen aus der Tabelle PART ab. Durch anklicken der einzelnen Reihen,
kann man sich noch weitere Details anzeigen lassen.
Der 2. Schritt des Plans ruft eine oder mehrere ROWIDs in aufsteigender Reihenfolge ab,
indem der B*-Baum-Index mit Index PARTSUPPPARTFK, ohne Index PARTSUPPPK durchsucht wird. Die genauen Pläne sind wie gesagt im Anhang zu finden.
Interessant ist das Query 20, welches ohne Index schneller läuft als mit. Das select-Statement
sieht wie folgt aus:
select sname, saddress from supplier, nation where ssuppkey in ( select pssuppkey from
partsupp where pspartkey in ( select ppartkey from part where pname like ’metallic ’)and
psavailqty > ( select 0.5 * sum(lquantity) from lineitem where lpartkey = pspartkey and lsuppkey = pssuppkey and lshipdate >= date ’1997-01-01’ and lshipdate < date ’1997-01-01’ +
interval ’1’ year))and snationkey = nnationkey and nname = ’MOROCCO’ order by sname;
42
6. Tuning
Im Fall mit Index, werden mehrere NESTED LOOPS (ab Schritt 4) durchgeführt. Die
entsprechenden Zeilenpaare werden gegen die Join-Bedingung getestet, die in der WHEREKlausel der Abfrage angegeben werden.
Im Fall ohne Index, wird einmal weniger NESTED LOOPS (ab Schritt 3) durchgeführt und
dafür ein HASH JOIN (Schritt 6). Eine Hash-Tabelle wird mit den Zeilen erstellt, die aus der
ersten untergeordnetetn Menge zurückgegeben werden. Jede von der zweiten untergeordneten Menge zurückgegebene Zeile wird dann benutzt, um die Hash-Tabelle auf Zeilenpaare zu
überprüfen, die einer Bedingung entsprechen, die in der WHERE-Klausel der Abfrage angegeben wird.
Eine andere Variante zur Analyse von Ausführungsplänen wäre SQL Trace und TKPROF.
Dabei müsste SQL Trace vor der Ausführung des zu protokollierenden Statements aktiviert
werden und während der Ausführung im Hintergrund laufen. SQL Trac kann für eine Session oder eine Instanz aktiviert werden. Duch die Aktivierung von SQL Trace entsteht ein
gewisser Overhead, der die Ergebnisse der Leistungsprotokolle aber in den meisten Fällen
nicht verfälscht, aber bei einer stark ausgelasteten Datenbank zu Problemen führen kann. Das
TKPROF Werkzeug ist ein Kommandozeilenprogramm, das dazu dient, die abgespeicherten
Protokolldateien in ein gewünschtes, aussagekräftiges Format zu wandeln und in einer getrennten Datei abzuspeichern.
Weitere Informationen dazu finden Sie im “Oracle Database Performance Tuning“ [7]. Die
Explain-Plans der Queries 2, 4, 7, 10 und 20 finden Sie im Anhang D.
43
6. Tuning
6.2. Statistik
Wenn wir die Tabellen und Indizes analysieren, erstellt Oracle eine Statistik und speichert sie
im Data Dictionary. Diese Statistiken sollten in regelmässigen Abständen oder nach grösseren
Änderungen aktualisiert werden.
Klicken Sie in der Oracle Enterprise Manager-Konsole auf Extras, Datenbank-Werkzeuge,
Analysieren.
Abbildung 35: Analyse-Assisten
Klicken Sie auf Weiter und wählen Sie drei Tabellen aus.
Abbildung 36: Objektauswahl
44
6. Tuning
Abbildung 37: Tabellen
Klicken Sie nun auf Weiter und dann auf Beenden.
Abbildung 38: Zusammenfassung
Dieser Vorgang dauert ein paar Sekunden.
45
6. Tuning
Abbildung 39: Fertig
Ich habe nun das Query 20 nochmals mit dem SQL-Scrachpad gestartet. Vorher hat es ca.
390 Sekunden gedauert, jetzt dauert es noch 28 Sekunden.
6.3. Optimizer Hints
Die Optimierung läuft vollautomatisch ab und basiert auf Systemstatistiken und Schätzungen
der Ausführungskosten für eine SQL-Abfrage. Diese Optimierungen können die Ausführungsdauer einer SQL-Anfrage stark beschleunigen. Da diese Kostenschätzverfahren nicht perfekt
sind, kann es aber auch zu Fehloptimierungen kommen, die eine unnötig lange Ausführungsdauer zur Folge haben können. Ein wichtiges Merkmal von Oracle besteht in der Möglichkeit
zum Angeben von Optimizer Hints (Hinweisen an den Optimierer). Mit diesen Hinweisen
können Sie den Optimierer veranlassen, einen von Ihnen bestimmten Ausführungspfad für die
Abfrage zu verwenden. So können diese Fehloptimierungen vermeiden werden.
Die Hinweise werden in den Kommentaren zu den SQL-Befehlen SELECT, INSERT und
UPDATE angegeben: Ein Hint beginnt mi /*+ und endet mit */
SELECT /*+ Hint */
<Anweisung>
In Oracle gibt es Hints für die Wahl von:
• Zugriffsmethoden
• Join-Reihenfolgen und Umwandlungen
• Parallelen Operationen (nur auf Mehrprozessor-Rechnern verfügbar)
• Sonstige
46
6. Tuning
Hints für Zugriffsmethoden sind beispielsweise:
• FULL(TABLE): teilt dem Optimierer mit, die angegebene Tabelle vollständig zu durchsuchen (full table-scan)
• HASH(TABLE): teilt dem Optimierer mit, für den Zugriff auf die Tabelle explizit das
Hash-Verfahren anzuwenden.
• ROWID(TABLE): erzwingt das Durchsuchen der RowID für die angegebene Tabelle.
Hints für Join-Operationen sind beispielsweise:
• USE HASH(TABLE): veranlasst Oracle dazu, jede angegebene Tabelle über einen HashJoin mit einer anderen Zeilenquelle zu verknüpfen.
• USE NL(TABLE): zwingt eine verschachtelte Schleife dazu, die angegebene Tabelle
als äussere Tabelle zu verwenden.
Abbildung 40: SQL-Scratchpad
Dieses Beispiel dauert dann noch 20 Sekunden.
47
7. Schluss
7. Schluss
Im Verlauf dieser Arbeit hat sich gezeigt, dass das Testen von Datenbanken mit Benchmarks
zu einigen Problemen führen kann. Es müssen einige Hindernisse überwunden werden, bis
man überhaupt soweit ist, die Tests durchführen zu können. Wenn man sich dann noch den
jeweils neuesten Versionen der einzelnen Software bedient, ist es schwierig genügend Unterstützung in Büchern oder im Internet zu finden. Ich hoffe, dass diese Dokumentation hilft
schneller voran zu kommen und genügend Zeit bleibt, um andere Benchmarks und andere
Datenbank-Systeme zu testen, damit auch ein Vergleich möglich ist.
Zusammenfassend hier nochmals die zentralen Punkte und Probleme der Projektarbeit:
• Installation von Linux -> für Oracle nur SUSE und RedHat unterstüzt
• Installation von Oracle -> Kerneländerungen
• Datenbank erstellen
• Tabellen erzeugen mit DBGEN -> Änderungen im makfile.suite
• Load -> Reihenfolge beachten
• Testprogramme erstellen, Parameter mit QGEN erstellen
• Tests durchführen
• Resultate auswerten
• Tuning
7.1. Ausblick
Man könnte noch einen Refresh machen, das heisst, den Inhalt der Datenbank ändern und
dann die Tests durchführen, wieder den Inhalt ändern, Tests durchführen etc.
Sehr interessant wäre es nun, andere Datenbank-Systeme auf Linux SUSE 10.0 aufzusetzen
und mit dem TPC-H Benchmark zu testen und dann zu vergleichen, ob Oracle schneller oder
langsamer ist. Zudem wäre es sinnvoll, andere Benchmarks zu testen. Ich bin davon überzeugt,
dass die Tests auf Linux-Systemen schneller sind, als auf Windows-Systemen. Es wäre aber
noch interessant zu wissen, ob zum Beispiel RedHat schneller ist als SUSE.
48
Literatur
Literatur
[1] TPC BENCHMARKTM H (Decision Support) Standard Specification Revision 2.3.0
1993 - 2005
[2] Pro*C/C++ Programmers Guide 10g Release 2 (10.2) B14407-01, June 2005
[3] Kevin Loney, Bob Bryla: Oracle Database 10g DBA-Handbuch, Eine High-Performance
Oracle-Datenbank verwalten, Hanser Fachbuchverlag, Oktober 2005
c Database Quick Installation Guide 10g Release 2 (10.2) for Linux x86
[4] Oracle
B15661-01, June 2005
[5] Oracle 10g Release 1(10.1.0.3) on SUSE LINUX Enterprise Server 9 ( How to Install ),
Novell
c Database Utilities 10g Release 2 (10.2) B14215-01, June 2005
[6] Oracle
c Database Performance Tuning 10g Release 2 (10.2) B14211-01, June 2005
[7] Oracle
49
A. Resultate
A. Resultate
A.1. Übersicht über alle Resultate
A.2. Einzelne Versionen mit Graphik
50
B. Datenbank
B. Datenbank
B.1. Tabellen und Indizes kreieren
B.2. Importieren der Daten
B.3. Logfiles der Tabellen
B.4. Parameter der Datenbank
51
C. Queries
C. Queries
C.1. Quellcode der Queries 1 bis 22
C.2. Buildprotokoll eines Programmes
C.3. Einzelne Dateien mit Resultaten
52
D. Tuning
D. Tuning
D.1. Explain-Plans
53
E. TPC-H
E. TPC-H
E.1. Top Ten TPC-H
E.2. Readme zu DBGEN/QGEN
E.3. Generierte Parameter
54