Mit Oracle 8i hat die Datenbankfirma einen wichtigen Schritt getan, um ihr Flaggschiff Internet-ready zu machen. Doch sobald eine Oracle-Datenbank in den Einsatz für Internet-Dienste geht, zeigt es sich, ob auch Entwickler und Administratoren ihr Handwerk gut genug verstehen, um ausreichende Geschwindigkeit auf Dauer zu gewährleisten.

Ein nahezu alltäglicher Fall: Bei einer Internetfirma X.COM erstellt die Programmiererin Paula eine Webseite basierend auf einer Oracle-Datenbank. Nachdem sie ihren SQL-Code und das Skript drumherum entwickelt hat, stellt sie mit Befriedigung fest, dass ihre Seite in nur 0,1 Sekunden aufgebaut ist, und meldet stolz die Fertigstellung. Die Anwendung geht online, aber nach nur drei Stunden melden sich enttäuschte Benutzer bei X.COM, dass die Seite unsagbar langsam aufgebaut wird. Was war passiert?

Paula hatte nicht damit gerechnet, dass wirklich 20 Benutzer pro Sekunde ihre Webseite besuchen und dass durch das stetige Füllen der Datenbank mit Benutzerdaten die einzelnen Operationen immer länger dauern.

Was lernen wir daraus? Datenbankanwendungen für das Internet müssen hoch performant sein und das eintretende Wachstum verkraften können. Damit Ihnen als Oracle-Programmierer oder -Administrator nicht das Gleiche passiert, möchte ich Ihnen einige Kochrezepte für das Orac-le-Performance-Tuning an die Hand geben. In der Tat sind die vorgestellten Techniken zum Teil auch auf andere Datenbanksysteme übertragbar, aber naturgemäß ist das High-End-Tuning von Datenbankanwendungen stark vom entsprechenden Produkt geprägt.

Abbildung 1: Die verschiedenen Tuning-Möglichkeiten sind unterschiedlich effizient.

The Name of the Game

Performance hat bei Servern immer zwei Aspekte: erstens die Antwortzeit – das ist die Zeit, die vergeht, bis ein einzelner Endanwender das vollständige Ergebnis einer Datenbankinteraktion zurückerhält, bis sich also eine Webseite oder eine Maske mit Daten gefüllt hat. Zweiter Aspekt ist der Durchsatz, der sich als Summe von Operationen pro Zeiteinheit über alle Benutzer versteht. Obwohl wir Fälle konstruieren können, in denen beide Ziele entgegengesetzte Maßnahmen erfordern, gilt doch in den meisten Fällen, dass eine gute Antwortzeit vieler Einzeloperationen gleichbedeutend mit einem hohen Gesamtdurchsatz ist. Ziel dieses Artikels ist es daher, Tuningmaßnahmen vorzuschlagen, die beide Aspekte der Datenbankperformance berücksichtigen.

Welche Faktoren bestimmen die Performance einer Datenbank mit all ihren Anwendungen? Wie so oft in der EDV wird auch bei Datenbankanwendungen die Gesamtleistung durch mehrere logische Schichten bestimmt:

• Anwendungsdesign und -code
• Datenbankdesign
• Konfiguration der Datenbankinstanz
• Konfiguration von Betriebssystem und Hardware

Die Praxis zeigt allerdings, dass diese vier Schichten ein stark unterschiedliches Verhältnis von Tuning-Effekt zu Tuning-Aufwand haben (Abbildung 1). Während wir durch optimale Betriebssystem- und Hardwareparameter oft nur Prozente an Leistung erzielen können, ist es möglich, durch wenige Regeln im Anwendungsdesign Faktoren von mehreren Größenordnungen in der Performance zu erzielen. Ich schlage deshalb vor, das Datenbanktuning immer in der obigen Reihenfolge durchzuführen, und gliedere so auch diesen Beitrag.

Wer kennt die Abkürzung?

Die Beschleunigung von Programmen wird dadurch erreicht, dass der so genannte Ausführungspfad von Operationen verkürzt wird. Doch wie sieht der Ausführungspfad einer SQL-Operation im Client-Server-Umfeld aus? Einen Überblick über die Schritte liefert dazu Abbildung 2 anhand eines einfachen »UPDATE«-Befehls, der von der Applikation abgesetzt wird:

1. Marshalling: Die Client-Bibliothek packt den SQL-Code mit Bind-Variablen und einer Reihe von Hilfsgrößen in eine Plattform-, Architektur- und Versions-unabhängige Form.

2. Dieses Datenpaket wird über einen Systemaufruf (»write()« oder Ähnliches) dem Betriebssystem zum Transport zum Server übergeben. Über die entsprechenden Protokollstapel und die Netzwerkhardware erreicht das Päckchen letztlich den zugeordneten Serverprozess.

3. Nach einem analogen De-Marshalling fängt aber erst die eigentliche Arbeit des Servers an:

4. Der SQL-Code wird auf syntaktische Richtigkeit geprüft. Das kann einige Zeit in Anspruch nehmen, bedenkt man die Größe der Syntaxgraphen moderner Oracle-Versionen.

5. Die Datenbank überprüft anhand des Repositorys, ob der angemeldete Benutzer diesen Code ausführen darf.

6. In der SQL-Area wird dann nachgeschaut, ob ein Ausführungsplan zu exakt diesem SQL-Statement bereits vorliegt. Wenn das nicht der Fall ist, muss der Optimierer (Optimizer) aus seinen verfügbaren Hintergrundinformationen einen neuen, den vermeintlich günstigsten Ausführungspfad berechnen.

7. Der Ausführungspfad wird abgearbeitet, das heißt, aus Tabellen und Indexen werden Datensätze geladen, modifiziert, in das Rollback-Segment geschrieben, alle Änderungen im Redo-Log manifestiert und ein Antwortpaket erzeugt.

8. Dieses Päckchen nimmt den umgekehrten Weg durch alle Instanzen zurück zur Client-Logik, wo lapidar festgestellt wird: »UPDATE« hat geklappt.

Abbildung 2: Der Weg der Information durch die verschiedenen Schichten der Datenbank.

Wege zu mehr Performance

Das klingt aufwändig und ist es auch. Aber mit der Kenntnis dieser Komplexität können wir einige Maßnahmen ableiten, um Performance zu gewinnen: Vermeiden Sie Client-Server-Roundtrips. Oft ist es sehr einfach möglich, mit einer SQL-Operation mehr als einen Datensatz zu lesen oder zu modifizieren. Schreiben Sie lieber

UPDATE emp SET gehalt=gehalt*1.1        WHERE id IN (45,46)

als

UPDATE emp SET gehalt=gehalt*1.1        WHERE id = 45
UPDATE emp SET gehalt=gehalt*1.1        WHERE id = 46

Verwenden Sie – wenn irgend möglich – Joins anstelle von verschachtelten SELECTs auf Einzeltabellen, auch wenn dabei ein paar Daten redundant übertragen werden: Unter dem Strich findet trotzdem weniger Netzwerkkommunikation statt. Verwenden Sie explizite Array- oder Batch-Ausführungen, die in manchen APIs angeboten werden.

Die konsequenteste Verfolgung dieses Ziels besteht bei Oracle in der serverseitigen Programmierung von Prozeduren, Funktionen und Packages in PL/SQL. Dieses Mittel sollte aber nur nach sorgfältiger Abwägung eingesetzt werden, denn dadurch muss die Datenbankinstanz besonders viel Logik abarbeiten, wodurch uns eine Skalierungsoption verloren geht.

Vermeiden Sie neue SQL-Statements: In der Regel setzt eine Programmlogik während der Laufzeit viele SQL-Statements ab, die sich gruppenweise nur in charakteristischen Parametern unterscheiden, zum Beispiel unterschiedliche Datumsbereiche in der »WHERE«-Klausel oder unterschiedliche »INSERT«-Daten. Oracle stattdessen bietet die Möglichkeit, diese im SQL-Text durch Platzhalter (:p1, :p2, … ) zu repräsentieren und die aktuellen Werte bei jeder Ausführung als so genannte Bind-Parameter mitzuliefern. Auf diese Weise wird die Generierung des Ausführungspfads nur bei der ersten Operation notwendig.

Verwenden Sie Prepared Statements: Viele Hostsprachen bieten im API die Möglichkeit, SQL-Anweisungen zu präparieren. So gibt der Datenbankserver nach dem ersten Generieren des Ausführungsplans ein Statement-Handle an den Client zurück, auf das – mit neuen Bind-Variablen – immer wieder Bezug genommen werden kann. Damit ersparen wir dem Server nicht nur die Generierung des Ausführungspfads, sondern zusätzlich die Schritte Syntaxanalyse und Rechteprüfung.

Design oder nicht sein

Wie viele Tonnen von Druckerschwärze wurden schon auf unschuldiges Papier gepresst, um das Thema Datenbankdesign und seine Auswirkungen auf die Performance erschöpfend zu behandeln?[1] Aber weshalb das alles? Zwei grundsätzliche Probleme treten bei SQL-Datenbanken auf.

Erstens: SQL ist eine imperative, mengenbasierte Sprache, mit der der Programmierer dem Server über mitunter sehr komplexe und mächtige Konstrukte mitteilt, welche Operationen (Lesen, Einfügen, Ändern, Löschen) er auf bestimmten Daten durchführen möchte. Er hat aber kein Sprachmittel, um zu spezifizieren, wie die Datenbank dies ausführen soll. Er begibt sich in die Abhängigkeit des Optimizers und hofft, dass dieser den besten Weg findet.

Zweitens: Viele Projekte werden nach der Systemanalyse aufgetrennt in Datenbankdesign und -implementierung sowie Applikationsdesign und -implementierung (siehe Abbildung 3). Wenn das physikalische Datenbank-Layout und der Anwendungscode am Ende wieder zusammentreffen, besteht die große Gefahr, dass das eine nicht zum anderen passt.

Wie können wir den Konflikt meistern? Eine wirksame Methode besteht darin, den jeweiligen Datenbankdesigner zu bitten, einen Code-Review der Datenbankapplikationen zu machen. Er wird schnell erkennen, ob sich seine Vorstellungen von der abzubildenden Welt mit denen des Entwickler-Kollegen decken.

Abbildung 3: Bei der Auftrennung des Projekts in Datenbank und Applikation besteht die Gefahr von Konflikten.

Entwicklungskonflikte

Ich ziehe hier aber einen gründlicheren Ansatz vor: Oracle erlaubt es uns, über so genannte Dynamic Performance Views auf alle SQL-Statements zuzugreifen, die die Datenbankinstanz in ihrer SQL-Area gespeichert hat, weil sie sie in der letzten Zeit verarbeiten musste. Weiterhin können wir über das »EXPLAIN PLAN«-Kommando den Ausführungspfad einer SQL-Operation ausgeben lassen. Werden diese beiden Techniken sinnvoll kombiniert, erhalten wir einen erschöpfenden Überblick darüber, was die Datenbank wie oft und mit welchem Aufwand macht und wie sie im Einzelfall vorgegangen ist (siehe Kasten “Analyse der SQL-Statements”).

Ich habe dazu in guter Unix-Manier zwei Perl-Programme erstellt (»lastanalyse.pl« und »do_explain.pl«), die diese beiden Teilaufgaben meistern und durch eine Pipe verknüpft werden können. Das Ergebnis ist eine hervorragende Grundlage, um das Wechselspiel von Datenbankprogrammen mit Datenbankobjekten und dem Optimierer auf Leistungsfähigkeit zu untersuchen. Leider bedarf es ein wenig der Übung, um die etwas kryptische Ausgabe des Oracle-Ausführungsplans komplett zu verstehen.

Das Feature, den Ausführungsplan eines SQL-Statements zu offenbaren, bietet auch das gute alte SQL*Plus mit dem Modus »SET AUTOTRACE ON«. Es ist in meiner Firma guter Brauch, dass kein Entwickler eine Datenbankoperation im Programmcode versenken darf, wenn er nicht in SQL*Plus den Ausführungsplan verstanden hat und weiß, dass das Statement optimal läuft. Der Kasten “Pläne für alle” erklärt, wie Sie vorgehen können, um »AUTOTRACE« und »EXPLAIN PLAN« für alle Benutzer verfügbar zu machen.

Mit Zeigefinger …

Aber wie bekommen wir ein Statement zu optimalem Performance-Verhalten? Das Zauberwort heißt hier auch im Zeitalter der hochgezüchteten Datenbanken: B*-Tree-Indexe. Indexe (Zeigefinger) sind baumartige Strukturen (siehe Abbildung 4), die es ermöglichen, eine oder eine Menge von Tabellenzeilen (Rows) mit einem Aufwand zu finden, der in der Regel logarithmisch mit der Gesamtzahl der Einträge skaliert. B* bedeutet hier soviel wie optimal balanciert. Ein solcher Index wirkt sich besonders in der Auswertung von »WHERE«-Klauseln, jedoch seltener bei »ORDER BY« oder Aggregationsfunktionen (»MIN()«, »MAX()«) aus.

Die naheliegende Vorgehensweise, sämtliche Spalten auf Verdacht zu indizieren, erreicht jedoch nicht das erhoffte Ziel: Indexe müssen bei Datenmanipulationen mit viel verborgenem Aufwand up to date gehalten werden, kosten also CPU- und I/O-Last und beanspruchen entsprechenden Plattenplatz. Daher gehört das sachkundige Indizieren einer Tabelle zu den Kernfähigkeiten erfolgreicher Datenbankdesigner.

Aber auch ein einfaches Kochrezept führt zu brauchbaren Ergebnissen:

• Primärschlüssel- und Unique-Constraints sorgen automatisch für einen geeigneten Index, müssen also nicht weiter betrachtet werden.
• Fremdschlüssel-Deklarationen erzeugen bei Oracle keinen eigenen Index, der aber oft für Joins oder Filterbedingungen benötigt wird. Daher sollte jeder Fremdschlüssel mit einem analogen Index versehen werden.
• Nachdem durch die beiden vorstehenden Punkte alle durch das Entity-Relationship-Konzept gerechtfertigten Indexe angelegt wurden, indizieren wir jetzt noch wenige charakteristische Suchattribute, die von den in der Konzeptphase erkannten Abfragevorgängen gebraucht werden. Gute Beispiele sind Mailadressen in Benutzertabellen oder das Datum in einer Terminverwaltung.
• In der Testphase bringen wir mit der obigen Inspektions-Technik SQL-Operationen und Indexe zur Deckung, so dass wir sicher sind keine Operationen programmiert zu haben, die für sich linear oder höher mit der Datenmenge skalieren.

Noch eine Anmerkung zu Indexen: Ein »CREATE INDEX« sollte immer mit der Klausel »NOLOGGING« abgesetzt werden. Es ist nämlich unnötig, dass die neu entstehenden Blöcke des Index durch den üblichen Redo-Mechanismus geschleust werden, da wir einen Index jederzeit wieder aus den Tabelleninformationen generieren können. Ein Faktor 2 ist mit diesem kleinen Zusatz leicht zu gewinnen.

Abbildung 4: Baumartige Indexe helfen dabei, den Suchaufwand erheblich zu reduzieren.

… und Physik

Indexe sind der wichtigste Punkt, um im Datenbankdesign Performance zu erzielen. Aber wir können noch mehr herausholen, wenn wir uns weiter in die Zugriffspfade der Datenbank hineindenken. Letztlich geht es immer darum, die I/O- und CPU-Ressourcen unseres Servers optimal zu nutzen.

Beim physikalischen Datenbankdesign haben erfahrene Entwickler noch eine Reihe von Möglichkeiten, das Speicherlayout ihrer Tabellen- und Indexsegmente zu bestimmen (»STORAGE«-Klausel des »CREATE«-Befehls). Was die einzelnen Freiheitsgrade bringen, hängt stark vom Anwendungsfall ab. Auf alle Fälle lohnt es sich, die Bedeutung der verschiedenen Optionen zu kennen, damit im Einzelfall eine spezielle Anforderung sachgerecht umgesetzt werden kann. Allzu viel Performance sollte man sich von der Variation der Storage-Parameter nicht versprechen. Das hat Oracle schon für die meisten Fälle optimiert.

Wichtig: Plattenaufteilung

Häufig ist es sowohl für die Performance als auch für die Speicherausnutzung wünschenswert, dass der freie Platz auf einem Tablespace nicht durch Fragmentierung zerrieben wird. Daher schlage ich grundsätzlich vor, dass alle Objekte innerhalb eines Tablespaces in Vielfachen einer einheitlichen Basisgröße (64 KByte, 1 MByte, 16 MByte) wachsen. Bei älteren, so genannten Dictionary managed Tablespaces erreichen wir dies, indem der Tablespace bei der Geburt eine entsprechende »DEFAULT STORAGE«-Klausel verpasst bekommt, die wir bei keinem »CREATE TABLE/INDEX«-Statement überschreiben.

Ab Version 8.1 kann die Datenbank auch die Freispeicherlisten in den Tablespaces selbst führen, was automatisch erzwingt, dass die Extents eine einheitliche Größe haben, Fragmentierung keine Chance hat und die Suche nach dem nächsten freien Extent sehr direkt ist.

Eine oft angewandte Technik zur Beschleunigung von I/O-Operationen besteht darin, Tabellen und Indexe explizit auf getrennte Festplatten oder Volumes zu legen, damit sich die parallelen Zugriffe nicht die Bandbreite streitig machen. Das ist grundsätzlich richtig, ich werde aber weiter unten eine wesentlich bequemere und allgemeinere Regel dem gegenüberstellen.

Partitionierte Tabellen

Oracle hat mit der Release 8.0 einige interessante Techniken in ihr Flaggschiff eingebaut und mit Release 8.1 noch praxistauglicher gemacht, die die physikalische Organisation sehr großer Tabellen optimieren. Zwei davon möchte ich hier erwähnen.

Zum einen sind das (endlich!) partitionierte Tabellen und Indexe, bei denen wir Teilmengen von Daten nach verschiedenen Gesichtspunkten auf physikalisch unterschiedliche Speicherorte aufteilen können, ohne dass die SQL-Programmierer davon beeinträchtigt werden. Partitionierung von Objekten erleichtert das Tuning und die Administration signifikant – wenn feststeht, dass eine Tabelle sehr groß werden kann.

Der offensichtlichste Anwendungsfall besteht bei Massendaten, die durch das fortlaufende Datum charakterisiert sind. Datenbankdesigner werden diese Tabelle (und die zugehörigen Indexe) nach diesem Datumskriterium partitionieren. Das führt dazu, dass Teile der Tabelle, die anhand der Datumsspalte als veraltet angesehen werden, mit einer sehr schnellen Operation offline gesetzt werden können. Weiterhin kann der Optimizer leicht erkennen, dass Operationen mit einer »WHERE Datum BETWEEN A AND B«-Klausel eventuell nur Blöcke einer Partition betreffen.

Die zweite Technik sind die überaus mächtigen Bitmap-Indexe. Das sind Variationen des B*-Tree-Prinzips. Alle vorkommenden Werte der indizierten Spalte werden nur einmal in einem B*-Tree abgelegt, aber an jeden Blattknoten des Baums wird jeweils eine (komprimierte) Bitmap abgelegt, in der ein Bit für jede Tabellenzeile bestimmt, ob der Wert darin gesetzt ist oder nicht. Bitmap-Indexe sind angebracht, wenn eine indizierte Spalte nur eine begrenzte Wertevielfalt (zum Beispiel 1000) hat. In diesen Fällen sind sowohl Zugriffsgeschwindigkeit als auch Plattenplatzverbrauch besser als bei B*-Indexen. Bei Primärschlüssel- oder eindeutigen Attributen ist ein Bitmap-Index natürlich widersinnig.

Noch ein kleiner Tipp: Das freie CGI-Programm Oracletool von [http://www.oracletool.com/] ist ein ausgezeichnetes Werkzeug, um sich sehr schnell einen Eindruck von allen logischen und physikalischen Eigenschaften aller Datenbankobjekte zu machen (Abbildung 5). Es schlägt in dieser Kategorie so manches kommerzielle Produkt und ist als Web-Interface natürlich auch plattformunabhängig.

Seine Installation auf einem beliebigen Webserver im Netz lohnt sich nicht nur wegen des schönen Drill-Downs in die Datenbankschemata, sondern auch wegen der vielen anderen Ansichten zu Sessions, Tablespaces, Instanz-Ressourcen, Rollback-Segmente und weiteren. Oracletool bedient sich wie alle derartigen Programme der exzellenten Diagnosemöglichkeiten, die durch die statischen und dynamischen Data Dictionary Views der laufenden Oracle-Instanz zur Verfügung gestellt werden.

Abbildung 5: Mit Oracletool steht ein freies, plattformunabhängiges Tool zur Administrierung von logischen und physikalischen Eigenschaaften aller Datenbankobjekte zur Verfügung.

Herztropfen

Die Datenbankinstanz ist die Gesamtheit aller aktiven Komponenten des Datenbankservers. Wenn man die Datenbank mit ihren Dateien als Gehirn des Servers versteht, dann ist die Instanz das Herz. Das komplexe Geflecht aus spezialisierten Prozessen, Shared Memory und Semaphoren spiegelt direkt die lange Geschichte des Softwareprodukts wieder: Einerseits ist zu erkennen, dass Oracle sehr viel Aufwand getrieben hat, damit das Räderwerk bei allen denkbaren Belastungssituationen nahe am Optimum funktioniert. Andererseits werden die Datenbankadministratoren mit unzähligen und ständig neuen »init.ora«-Parametern konfrontiert, die verstanden und beherrscht werden sollten[2].

Ich kann an dieser Stelle natürlich nicht alle diese Einflussgrößen und ihre Auswirkungen auf die Performance diskutieren, sondern möchte nur die wichtigsten Gesichtspunkte beleuchten.

Effiziente Speichernutzung

Der gemeinsame Hauptspeicherbereich aller Prozesse, der Datenbank-Cache oder System Global Area ist kritisch für eine gute Datenbankperformance. Grob lässt sich diese Speicherfläche unterteilen in den Cache für die Datenbankblöcke (Buffer Cache) und den Shared Pool, also den Zwischenspeicher für Meta-Daten. Zusätzlich brauchen wir noch kleinere Speicherbereiche für den Redo-Log-Puffer, eventuell den Java-Pool und diverse andere Speicherstrukturen.

Hier gilt die einfache Regel: Viel hilft viel. Es ist ungleich aufwändiger, einen Datenbankblock von Platte zu lesen oder einen Ausführungspfad einer SQL-Operation neu zu berechnen als diese Daten aus dem RAM zu holen. Je mehr Hauptspeicher zur Verfügung steht, desto größer ist die Wahrscheinlichkeit, dass die Instanz einen bestimmten Block dort findet. Aber aufgepasst: Nicht nur die Datenbankinstanz braucht Hauptspeicher, sondern auch noch die einzelnen Shadow-Prozesse, die den Client-Sessions zugeordnet sind, und eventuell noch eine ganze Reihe von Linux-Dienstprogrammen, die auch noch auf dem Server laufen.

Machen Sie die System Global Area daher gerade so groß, dass der Server als Ganzes nicht zum Paging genötigt wird, denn das bringt drastische Performance-Einbußen. Nach unserer Erfahrung ist folgende Regel ein guter Startpunkt für die Aufteilung des Hauptspeichers: Die Datenbank bekommt 50 Prozent, davon 80 Prozent für Buffer-Cache (Parameter »db_block_buffers«) und 20 Prozent für den Rest (»shared_pool_size«). Wenn im laufenden System noch etwas Hauptspeicher übrig ist, wird der Linux-Kernel ihn als zusätzlichen Cache für Leseoperationen der Datenbank auf den Dateisystemen anwenden, er ist also nicht verschenkt.

Der oben erwähnte Redo-Log-Puffer ist ein gutes Beispiel dafür, dass eine Default-Einstellung nicht optimal ist: Der unter Linux voreingestellte Wert von 160 KByte (»log_buffer«) ist für die meisten Anwendungsfälle zu klein und bremst anhaltende Schreibvorgänge merklich aus. Vergrößern Sie ihn – je nach Schreiblastigkeit auf 1 bis 4 MByte – und Ihre Anwender werden es Ihnen danken.

Daneben ist die Sort Area Size ein wichtiger Parameter, der an die Art der Anwendung angepasst sein sollte: Er bestimmt, ab welcher zu sortierenden Datenmenge die Datenbank den temporären Tablespace, also die Festplatte, zu Hilfe nimmt. Bei den typischen Internet-Applikationen sind die Zugriffe häufig sehr fein-granular und Sortieroperationen finden, wenn überhaupt, nur auf kleinen Datenbeständen statt. In diesem Fall ist der Standardwert von »sort _area_size« vollkommen ausreichend. Wenn die Programme jedoch umfangreichere Datenmengen sortieren müssen oder häufig Indexe angelegt werden, dann empfiehlt es sich, diesen Speicherbereich, der pro Session einmal alloziert wird, zu vergrößern.

Auch hier ist das Oracletool in der Lage zu berichten, wie oft ein Sortiervorgang auf Platte ausgelagert wurde. Apropos Diagnose: Ab Release 8.1.6 legt Oracle das so genannte Statspack als Werkzeug bei. Es handelt sich hierbei um ein Package, das Momentaufnahmen der Lastsituation erstellt und über diese Snapshots Reports erstellen kann. Statspack erweitert die Funktionalität der sehr primitiven SQL-Skripte »utlbstat.sql/utlestat.sql« wesentlich.

Sie sollten es auf jeden Fall auf dem Server installieren und dazu vorher sicherstellen, dass der »init.ora«-Parameter »timed_statistics = true« gesetzt ist. Erst dann sind die Reports besonders aussagekräftig. Sorgen Sie dafür, dass die Snapshots regelmäßig erstellt werden (mit dem Skript »spauto.sql«). Sie sind dadurch sehr gut in der Lage, die Lastsituation der Datenbank und die schuldigen SQL-Operationen über einen längeren Zeitraum zu verfolgen, können also Aussagen treffen, seit wann und warum Ihre Datenbank besonders belastet ist.

Nackte Tatsachen

Letztlich sind es aber doch Hardware und Betriebssystem, die die eigentliche Arbeit unseres Datenbankservers leisten müssen. Neben dem oben bereits angesprochenen Hauptspeicher sind zwei weitere Komponenten entscheidend für die Datenbankperformance: CPUs und Massenspeicher.

Wenn sich ein Benutzer, Entwickler oder Operator bei Ihnen als DBA beschwert, dass die Datenbank zu langsam ist, sollten Sie sich zunächst einen Eindruck verschaffen, welche Komponente Ihres Servers der Flaschenhals ist, bevor Sie entscheiden, dass Sie mehr CPUs oder schnellere Platten benötigen. Dazu können Sie sich unter Linux des bewährten Kommandos »vmstat« bedienen oder Sie setzen ein grafisches Tool wie Kload[3] ein (siehe Abbildung 6), um der Ursache der schlechten Performance näher zu kommen.

Abbildung 6: Kload erstellt über einen bestimmten Zeitraum ein Abbild der Serverlast. Auch kurzzeitige Spitzenbelastungen sind gut zu erkennen.

I/O ist wichtiger als CPU

CPU-Leistung ist heutzutage kein Problem mehr. Selbst die in Linux-PCs heimischen Intel- oder AMD-Prozessoren (Oracle gibt es nur für x86-Linux) sind mittlerweile so performant im Integer-Bereich, dass sie selten ein Engpass für die Gesamtleistung sind. Zwar können wir auf einem Linux-Server nicht die CPU-Leistung einer Sun E10000 oder einer IBM RS/6000 SP erzielen, aber für die meisten Internet-Datenbanken reicht ein Ein-, Zwei- oder Vier-Prozessor-System mit 700 bis 1000 MHz und 1 bis 4 GByte RAM völlig aus.

Deshalb müssen wir unbedingt darauf achten, dass die I/O-Bandbreite von und zu den Festplattensystemen nicht zu einem Engpass wird. Dabei ist anzumerken, dass die vom Linux-Kernel erzeugten Statistiken im »/proc«-Dateisystem zwar einfach auszuwerten sind, aber leider zu wenige Details der genauen Lastverteilung auf einzelne Plattensubsysteme geben, um gezielt zu analysieren und zu planen.

Beim Disk-I/O gilt auch wieder die einfache Regel: je mehr Controller und Platten (Spindles), desto besser. Sie werden deshalb vermutlich schon öfter auf die Aufforderung gestoßen sein: Legen Sie Tabellen auf ein Volume, Indexe auf ein zweites, Redo-Logs auf ein drittes, »TEMP«-Tablespace auf ein viertes und so weiter. Das ist zwar grundsätzlich richtig, aber zum einen mühsam, zum anderen birgt es die Gefahr, dass durch ungünstige Anordnung Teile der I/O-Bandbreite oder des zur Verfügung stehenden Gesamtspeichers nicht genutzt werden.

Viele Platten gleichzeitig

Daher schlage ich folgendes Kochrezept vor (siehe Abbildung 7): Bauen Sie Ihr Platten-Subsystem aus mehreren, gleich großen RAID-Systemen auf, die die Levels 1 (Mirroring) oder besser 10 (Striping über Mirroring) unterstützen. Über alle diese Hardware-Volumes setzen Sie mit dem Linux-LVM eine Volume Group auf, die das Striping quer zu den Volumes definiert. Aus dieser Volume Group schneiden sie einzelne Logical Volumes heraus, die als Grundlage für Dateisysteme oder Raw Devices dienen.

Es spielt dann keine Rolle mehr, wo Sie Ihre einzelnen Datenbankkomponenten ablegen, denn die Lastverteilung wird vom Betriebssystem und von den RAID-Controllern immer optimal vorgenommen. Hüten Sie sich aber auf jeden Fall davor, Datenbanken auf den RAID-Levels 3 oder 5 zu fahren. Festplatten sind heutzutage vergleichsweise billig und groß. Diese RAID-Levels haben zwar weniger Redundanz als reines Mirroring, aber sie sind auch langsamer und fehleranfälliger.

Die Wahl des Dateisystemtyps richtet sich nach der primären Anforderung: Wenn es nur auf I/O-Durchsatz ankommt, ist das bewährte und schlanke Ext2-Dateisystem immer noch erste Wahl. Wenn wir aber vor allem sicherstellen müssen, dass nach einem möglichen Absturz (so unwahrscheinlich der auch immer sein mag) der Server nicht Stunden im »fsck« hängt, dann ist ein Journaling-Filesystem (ReiserFS, IBM-JFS oder SGI-XFS) einfach Pflicht. Auf Raw Devices verzichten wir wegen der schlechten Administrierbarkeit jedoch lieber ganz.

Abbildung 7: Durch die geschickte Ausnutzung von RAID 0 und LVM lässt sich die Last gut auf die Platten verteilen.

Fazit

Es ist deutlich geworden, dass die Datenbankanwendungen sehr vielschichtig sind. Jede dieser Schichten stellt eine eigene Herausforderung für das Performance-Tuning dar, die gemeistert werden will.

Das Vorgehen ist aber trotzdem immer gleich: erst die Analyse, dann die Tuning-Maßnahmen. Und verzweifeln Sie nicht, wenn sie nicht auf Anhieb das Wunder an Performance aus Ihrem Server herausholen. Oracle ist ein mächtiges, aber auch sehr komplexes Produkt. Daher ist der Einsatz der Datenbank gleichbedeutend mit täglichem Erfahrungsgewinn. Wenn Sie aber doch einmal eine ruhige Minute haben, dann empfehle ich Ihnen die Lektüre der Performance- und Tuning-Seiten auf dem Oracle Technet[4]. ( hmi)

01 (us) ~> lastanalyse.pl system/manager@testdb |
02 >       do_explain.pl test/passtest@testdb |
03 >       do_explain.pl test2/passtest2@testdb
04 
05 User    N Exec  N Exec/sec  D.Reads  D.R./Exec.
06 
07 TEST    30276   0.008       0        0.000
08     UPDATE stat SET value=:a WHERE service=:b
09     AND name=:c AND datum=:d
10 > UPDATE STATEMENT
11 >   UPDATE  STAT (O:STAT)
12 >     INDEX UNIQUE SCAN PK_STAT (O:PK_STAT)
13 
14 TEST      427   0.000       0        0.000
15     SELECT DECODE('A','A','1','2')
16     FROM DUAL
17 > SELECT STATEMENT
18 >   TABLE ACCESS FULL DUAL (O:DUAL)
19 
20 TEST      257   0.000       0        0.000
21     SELECT p.id, p.name,p.beschreibung,
22     lp.email, lp.ftp_verzeichnis, cont_typ.name
23     FROM other p, lead_other lp,
24     cont,cont_typ WHERE p.id=lp.other_id
25     AND p.id=cont.other_id AND
26     cont.cont_typ_id= cont_typ.id
27 > SELECT STATEMENT
28 >   NESTED LOOPS
29 >     NESTED LOOPS
30 >       NESTED LOOPS
31 >         TABLE ACCESS FULL CONT_TYP (O:CONT_TYP)
32 >         TABLE ACCESS BY INDEX ROWID CONT (O:CONT)
33 >           INDEX RANGE SCAN IDX_CONT_CONT_TYP
34 >               (O:IDX_CONT_CONT_TYP)
35 >       TABLE ACCESS BY INDEX ROWID OTHER (O:OTHER)
36 >         INDEX UNIQUE SCAN PK_OTHER (O:PK_OTHER)
37 >     TABLE ACCESS BY INDEX ROWID LEAD_OTHER
38 >         (O:LEAD_OTHER)
39 
40 TEST      164   0.000       0        0.000
41     SELECT email,anrede,vorname,
42     nachname,strasse,hausnr,plz,ort,
43     land FROM transaktion
44 > SELECT STATEMENT
45 >   TABLE ACCESS FULL TRANSAKTION (O:TRANSAKTION)
46 
47 TEST       52   0.000       2        0.038
48     select ID,DATE_MODIFY into :b0,
49     :b1:b2 from ITEMS where
50     ((DBTYPE=:b3 and PATH=:b4)
51     and TITLE=:b5)
52 > SELECT STATEMENT
53 >   INDEX RANGE SCAN TYPE_PATH_TITLE_ID_DATE_IDX
54 >       (O:TYPE_PATH_TITLE_ID_DATE_IX)
55 
56 TEST       27   0.000       0        0.000
57     UPDATE transaktion SET status=:x
58     WHERE id=:x
59 > UPDATE STATEMENT
60 >   UPDATE  TRANSAKTION (O:TRANSAKTION)
61 >     INDEX UNIQUE SCAN PK_TRANSAKTION
62 >         (O:PK_TRANSAKTION)
63 
64 TEST2      12   0.000      845      70.417
65     DELETE FROM items WHERE path
66     LIKE :p0
67 > DELETE STATEMENT
68 >   DELETE  ITEMS (O:ITEMS)
69 >     TABLE ACCESS FULL ITEMS (O:ITEMS)
70 
71 
72 Anzahl der Statements/sec: 0.51

01 (oracle@server) ~ > sqlplus system/manager @?/rdbms/admin/utlxplan
02 [...]
03 Tabelle wurde angelegt.
04 
05 SQL> grant insert,update,delete,select on plan_table to public;
06 Benutzerzugriff (Grant) wurde erteilt.
07 SQL> create public synonym plan_table for system.plan_table;
08 Synonym wurde angelegt
09 SQL> quit
10 
11 (oracle@server) ~ > sqlplus internal @?/sqlplus/admin/plustrce
12 [...]
13 SQL> REM plustrce.sql legt die Rolle PLUSTRACE an und vergibt einige
14 SQL> REM Grants an sie. Jetzt noch PLUSTRACE an alle User weitergeben:
15 SQL> grant plustrace to public;
16 Benutzerzugriff (Grant) wurde erteilt.
17 SQL> quit