vorheriges KapitelInhaltsverzeichnisIndexInfoseitenächstes Kapitel



15


SQL-Anweisungen optimieren

Ziele

Die Lesbarkeit von SQL-Anweisungen verbessern

Vollständige Suche

Einen neuen Index hinzufügen

Anordnung von Elementen in einer Abfrage

OLAP vs. OLTP

Stapelverarbeitung vs. Transaktionsverarbeitung

Datenverarbeitung durch Löschen von Indizes optimieren

Ein häufiges COMMIT entlastet den DBA

Tabellen und Indizes in einer dynamischen Umgebung neu aufbauen

Feinabstimmung der Datenbank

Leistungsbremsen

Werkzeuge zur Feinabstimmung

Zusammenfassung

Fragen & Antworten

Workshop


Neben dem Entwurf und der Feinabstimmung einer Datenbank beeinflußt der rationelle Aufbau einer SQL-Anweisung entscheidend die Leistungsbilanz einer Anwendung. Wenn Sie nicht ein paar grundlegende Regeln beachten, erhalten Sie die Ergebnisse einer Abfrage kaum innerhalb akzeptabler Zeiten, unabhängig davon, wie ausgeklügelt die Datenbank aufgebaut ist oder deren Struktur aussieht. Und wenn der Kunde nicht zufrieden ist, wird es Ihr Chef erst recht nicht sein.



Ziele

Die Hauptkomponenten der relationalen Datenbanksprache von SQL sind Ihnen nun bekannt, und Sie wissen, wie man mit der Datenbank kommuniziert. Jetzt ist es an der Zeit, daß Sie Ihre Kenntnisse in die Praxis umsetzen. Die heutige Lektion empfiehlt unter anderem Methoden zur Leistungsverbesserung und zur Optimierung einer SQL-Anweisung. Insbesondere geht es heute um ...


Um eine SQL-Anweisung optimieren zu können, sollte man immer genau wissen, was man erreichen will, und dann bestrebt sein, den Weg des geringsten Widerstands zu gehen. Je mehr Zeit man in die Planung investiert, desto weniger Zeit muß man später mit der Überarbeitung verbringen. Das Ziel sollte immer darin bestehen, Daten gezielt und in möglichst kurzer Zeit abzufragen. Ein Benutzer ist schnell frustriert, wenn er auf eine langsame Abfrage warten muß. Obwohl man die meisten Abfragen auf verschiedene Arten schreiben kann, stellt die Anordnung der Komponenten innerhalb der Abfrage den Faktor dar, der bei der Ausführung den Unterschied in Sekunden, Minuten und manchmal auch Stunden ausmacht. Mit einer Optimierung von SQL versucht man, die bestmögliche Anordnung der Elemente innerhalb der Abfrage zu finden.


Neben der Optimierung der SQL-Anweisung spielen weitere Faktoren eine Rolle, wenn man die Gesamtleistungsbilanz der Datenbank verbessern will. Dazu gehören die Koordinierung gleichzeitig ablaufender Transaktionen, das Indizieren von Tabellen und die Feinabstimmung der Datenbank insgesamt.


Die heutigen Beispiele arbeiten mit Personal Oracle8 und Werkzeugen, die zum relationalen Datenbank-Managementsystem Oracle8 gehören. Die behandelten Konzepte sind allerdings nicht auf Oracle beschränkt. und lassen sich durchaus auf andere Datenbank-Managementsysteme übertragen.


Die Lesbarkeit von SQL-Anweisungen verbessern

Selbst wenn die Lesbarkeit nicht die eigentliche Leistung von SQL-Anweisungen beeinflußt, gehört es zum guten Programmierstil, übersichtlichen Code zu schreiben. Die Lesbarkeit ist insbesondere wichtig, wenn man mehrere Bedingungen in der WHERE-Klausel stehen hat. Es sollte auf einen Blick erkennbar sein, ob die Tabellen in geeigneter Weise verknüpft sind und in welcher Reihenfolge die Bedingungen ausgewertet werden.


Lesen Sie einmal diese Anweisung:


SQL> SELECT MITARBEITER.MITARBEITER_ID, MITARBEITER.NAME, GEHALTSLISTE.GEHALT,GEHALTSLISTE.EINST_DATUM
2 FROM MITARBEITER, GEHALTSLISTE
3 WHERE MITARBEITER.MITARBEITER_ID = GEHALTSLISTE.MITARBEITER_ID AND
4 GEHALTSLISTE.GEHALT > 30000 OR (GEHALTSLISTE.GEHALT BETWEEN 25000
5 AND 30000 AND GEHALTSLISTE.EINST_DATUM < SYSDATE - 365);


Die gleiche Abfrage wurde neu formatiert, um die Lesbarkeit zu verbessern:


SQL> SELECT M.MITARBEITER, M.NAME, G.GEHALT, G.EINST_DATUM
2 FROM MITARBEITER M,
3 GEHALTSLISTE G
4 WHERE M.MITARBEITER_ID = G.MITARBEITER_ID
5 AND P.GEHALT > 30000
6 OR (G.GEHALT BETWEEN 25000 AND 30000
7 AND G.EINST_DATUM < SYSDATE - 365);


Beachten Sie die Aliasnamen für die Tabellen in der obigen Abfrage. MITARBEITER in Zeile 2 wurde der Alias M zugewiesen, und GEHALTSLISTE in Zeile hat den Alias G bekommen. In den Zeilen 4 bis 7 ist zu erkennen, daß M und G für die vollständigen Tabellennamen stehen. Aliase erfordern weniger Tipparbeit als das Ausschreiben des vollen Tabellennamens. Vor allem aber sind Abfragen mit Aliasnamen übersichtlicher als Abfragen, die mit unnötig langen Tabellennamen überladen sind.

Beide Abfragen sind identisch. Die zweite ist aber aufgrund der besseren Struktur wesentlich leichter zu erfassen. Die logischen Komponenten der Abfrage wurden hier durch Zeilenwechsel und einheitliche Abstandsgestaltung getrennt. Man kann schnell erkennen, was ausgewählt wird (SELECT-Klausel), auf welche Tabellen der Zugriff erfolgt (FROM-Klausel) und welche Bedingungen erfüllt sein müssen (WHERE-Klausel).



Vollständige Suche

Das Absuchen ganzer Tabellen tritt auf, wenn der Datenbankserver jeden Datensatz in einer Tabelle liest, um eine SQL-Anweisung auszuführen. Normalerweise ist das der Fall, wenn man mit Abfragen oder der SELECT-Anweisung arbeitet. Allerdings kann das Absuchen ganzer Tabellen auch beim Aktualisieren und Löschen ins Spiel kommen. Vollständige Suchläufe sind erforderlich, wenn die Spalten in der WHERE-Klausel nicht indiziert sind. Man kann das praktisch mit dem Lesen eines Buches von der ersten bis zur letzten Seite vergleichen, wenn man auf der Suche nach einem bestimmten Wort ist. In der Regel greift man aber auf den Index am Ende des Buches zurück.


Die vollständige Suche läßt sich vermeiden, wenn man auf den Spalten, die als Bedingungen in der WHERE-Klausel einer SQL-Anweisung vorkommen, einen Index aufbaut. Genau wie der Index in einem Buch den Leser auf eine bestimmte Seitennummer führt, bieten Indizes in einer Datenbank den direkten Weg zu den Daten und beschleunigen damit den Datenzugriff. Die vollständige Suchmethode ist zwar bei den Programmierern verpönt, in den folgenden Fällen aber durchaus angebracht:


In den beiden ersten Fällen wäre ein Index wenig effizient. Der Datenbank-Server müßte sich auf den Index beziehen, die Tabelle lesen, wieder im Index nachsehen, die Tabelle erneut lesen usw. Indizes sind am effizientesten, wenn man nur auf etwa 10 bis 15 Prozent aller in der Tabelle enthaltenen Daten zugreift.


Darüber hinaus eignen sich Indizes am besten für große Tabellen. Bei der Planung von Tabellen und Indizes sollte man immer die Tabellengröße berücksichtigen. Eine geeignete Indizierung der Tabellen setzt voraus, daß man die Datenbestände genau kennt und weiß, auf welche Spalten die häufigsten Zugriffe erfolgen. Außerdem gehören einige Experimente dazu, um herauszufinden, welcher Index am besten arbeitet.


»Groß« ist immer ein relativer Begriff. Eine Tabelle, die für den einen extrem groß sein mag, ist für den anderen nur ein Klacks. Die Größe einer Tabelle ist relativ zur Größe anderer Tabellen in der Datenbank, zum verfügbaren Plattenplatz und der Anzahl der verfügbaren Datenträger zu sehen. Offensichtlich ist eine Tabelle mit 2 Gbyte groß, während eine Tabelle von 16 Kbyte als klein zu bezeichnen ist. In einer Datenbankumgebung, wo die durchschnittliche Größe der Tabellen 100 Mbyte beträgt, wird man eine Tabelle von 500 Mbyte als umfangreich ansehen.


Einen neuen Index hinzufügen

Es kommt gelegentlich vor, daß eine SQL-Anweisung unverhältnismäßig lange für die Ausführung braucht, obwohl die Leistung bei anderen Anweisungen akzeptabel zu sein scheint. Beispielsweise ist das der Fall, wenn sich die Bedingungen für das Abrufen von Daten ändern oder die Tabellenstruktur modifiziert wird.


Dieser Bremseffekt ist auch festzustellen, wenn man einen neuen Bildschirm oder ein neues Fenster in einer Frontend-Anwendung öffnet. Als erstes sollten Sie bei der Fehlersuche ermitteln, ob die Zieltabelle über einen Index verfügt. Oftmals ist die Zieltabelle zwar indiziert, aber für eine der neuen Bedingungen in der WHERE-Klausel wurde noch kein Index angelegt. Bei der Analyse der WHERE-Klausel in der SQL-Anweisung stellt sich dann die Frage, ob man einen weiteren Index aufbauen sollte. Die Antwort kann Ja lauten, wenn ...


Es lassen sich auch zusammengesetzte Indizes verwenden. Man versteht darunter einen Index auf zwei oder mehr Spalten einer Tabelle. Diese Indizes können effizienter sein als einspaltige Indizes, wenn die indizierten Spalten oft zusammen als Bedingungen in der WHERE-Klausel einer SQL-Anweisung vorkommen. Wenn die indizierten Spalten sowohl einzeln als auch zusammen - insbesondere in anderen Abfragen - verwendet werden, sind einspaltige Indizes geeigneter. Entscheiden Sie selbst anhand von Tests mit Ihren Daten, um den am besten geeigneten Index für Ihre Datenbank herauszufinden.



Anordnung von Elementen in einer Abfrage

Die günstigste Anordnung der Elemente innerhalb einer Abfrage, insbesondere in der WHERE-Klausel, hängt prinzipiell von der Reihenfolge der Verarbeitungsschritte in der konkreten Implementierung ab. Die Anordnung der Bedingungen richtet sich nach den indizierten Spalten sowie danach, welche Bedingung die wenigsten Datensätze abruft.


In der WHERE-Klausel muß man nicht unbedingt eine indizierte Spalte verwenden, offensichtlich ist es aber günstiger, mit einem Index zu arbeiten. Die Ergebnisse der SQL-Anweisung sollte man nach Möglichkeit mit einem Index einengen, der die wenigsten Zeilen zurückgibt. Eine entsprechend formulierte Bedingung bezeichnet man als restriktivste Bedingung. Allgemein gilt, daß die restriktivsten Bedingungen zuletzt in der WHERE-Klausel stehen sollten. (Der Abfrageoptimierer von Oracle liest eine WHERE-Klausel von unten nach oben. Die als letztes angegebene Bedingung steht damit praktisch an erster Stelle.)


Der Optimierer liest also die restriktivste Bedingung zuerst und kann die erste Ergebnismenge einschränken, bevor er zur nächsten Bedingung übergeht. Die nächste Bedingung muß nun nicht mehr auf die gesamte Tabelle, sondern nur noch auf die von der selektivsten Bedingung ausgewählte Untermenge angewendet werden. Letztlich läuft das Abrufen der Daten schneller ab. In komplexen Abfragen mit mehreren Bedingungen, Unterabfragen, Berechnungen und verschiedenen Kombinationen von AND, OR und LIKE läßt sich die selektivste Bedingung allerdings nicht immer auf den ersten Blick erkennen.


Vergewissern Sie sich anhand der Dokumentation Ihrer Datenbank, wie SQL-Anweisungen in der konkreten Implementierung verarbeitet werden.

Der folgende Test ist einer von vielen, mit denen wir in einer Oracle-Datenbank die Laufzeitunterschiede von zwei Abfragen mit demselben Inhalt, aber unterschiedlicher Anordnung der Bedingungen gemessen haben. Denken Sie daran, daß der Optimierer in dieser Implementierung die WHERE-Klausel von unten nach oben liest.


Vor dem Erstellen der SELECT-Anweisung haben wir zunächst für jede der vorgesehenen Bedingungen die Anzahl der voneinander verschiedenen Zeilen ermittelt. Die folgende Übersicht zeigt die entsprechenden Werte:


Bedingung

Anzahl der unterschiedlichen Werte

CALC_YTD = '-2109490.8'

mehr als 13000

DT_STMP = '01-SEP-96'

15

OUTPUT_CD = '001'

13

ACTIVITY_CD = 'IN'

10

STATUS_CD = 'A'

4

FUNCTION_CD = '060'

6

Die restriktivste Bedingung ist auch die Bedingung mit den meisten unterschiedlichen Werten.

Das erste Beispiel plaziert die restriktivsten Bedingungen an erster Stelle in der WHERE-Klausel:


SQL> SET TIMING ON
2 SELECT COUNT(*)
3 FROM FACT_TABLE
4 WHERE CALC_YTD = '-2109490.8'
5 AND DT_STMP = '01-SEP-96'
6 AND OUTPUT_CD = '001'
7 AND ACTIVITY_CD = 'IN'
8 AND STATUS_CD = 'A'
9 AND FUNCTION_CD = '060';


COUNT(*)
--------
8

1 Zeile wurde ausgewählt.
real: 00:00:15.37


Im folgenden Beispiel stehen die restriktivsten Bedingungen an letzter Stelle in der WHERE-Klausel:


SQL> SET TIMING ON
2 SELECT COUNT(*)
3 FROM FACT_TABLE
4 WHERE FUNCTION_CD = '060'
5 AND STATUS_CD = 'A'
6 AND ACTIVITY_CD = 'IN'
7 AND OUTPUT_CD = '001'
8 AND DT_STMP = '01-SEP-96'
9 AND CALC_YTD = '-2109490.8';

COUNT(*)
--------
8

1 Zeile wurde ausgewählt.
real: 00:00:01.80


Beachten Sie die unterschiedlichen Ausführungszeiten. Allein durch die geänderte Reihenfolge der Bedingungen entsprechend der Tabellenstatistik läuft die zweite Abfrage fast 14 Sekunden schneller als die erste. Stellen Sie sich den Unterschied bei einer schlecht strukturierten Abfrage vor, die zum Beispiel drei Stunden läuft!


Prozeduren

Bei regelmäßig ausgeführten Abfragen sollten Sie Prozeduren einsetzen. Eine Prozedur faßt mehrere - möglicherweise sehr viele - SQL-Anweisungen als Gruppe zusammen. (Siehe dazu Tag 13.)


Prozeduren werden von der Datenbank-Engine kompiliert und dann ausgeführt. Die Datenbank-Engine muß die Prozedur nicht wie eine SQL-Anweisung vor der Ausführung optimieren. Verglichen mit zahlreichen Einzelabfragen sind Prozeduren für den Benutzer einfacher zu handhaben und erhöhen die Effizienz der Datenbank.



OR vermeiden

Auf den logischen Operator OR sollte man in einer Abfrage nach Möglichkeit verzichten. Bei Tabellen ab einer gewissen Größe verringert dieser Operator unvermeidlich die Geschwindigkeit nahezu jeder Abfrage. Wir haben festgestellt, daß IN im allgemeinen schneller als OR arbeitet, auch wenn die Dokumentationen behaupten, daß Optimierer die IN-Argumente in OR-Bedingungen konvertieren.


Das folgende Beispiel arbeitet mit mehreren OR-Operatoren:


SQL> SELECT *
2 FROM FACT_TABLE
3 WHERE STATUS_CD = 'A'
4 OR STATUS_CD = 'B'
5 OR STATUS_CD = 'C'
6 OR STATUS_CD = 'D'
7 OR STATUS_CD = 'E'
8 OR STATUS_CD = 'F'
9 ORDER BY STATUS_CD;


Die gleiche Abfrage unter Verwendung von SUBSTR und IN sieht folgendermaßen aus:


SQL> SELECT *
2 FROM FACT_TABLE
3 WHERE STATUS_CD IN ('A', 'B', 'C', 'D', 'E', 'F')
4 ORDER BY STATUS_CD


Probieren Sie selbst ähnliche Anweisungen aus. Bücher sind zwar ausgezeichnete Quellen für Standards und die allgemeine Richtung, spezielle Dinge - wie eben die Leistungsbilanz - müssen Sie aber selbst untersuchen.

Hier noch ein Beispiel mit SUBSTR und IN. Beachten Sie, daß die erste Abfrage die Operatoren LIKE und OR kombiniert.


SQL> SELECT *
2 FROM FACT_TABLE
3 WHERE PROD_CD LIKE 'AB%'
4 OR PROD_CD LIKE 'AC%'
5 OR PROD_CD LIKE 'BB%'
6 OR PROD_CD LIKE 'BC%'
7 OR PROD_CD LIKE 'CC%'
8 ORDER BY PROD_CD;
SQL> SELECT *
2 FROM FACT_TABLE
3 WHERE SUBSTR(PROD_CD,1,2) IN ('AB','AC','BB','BC','CC')
4 ORDER BY PROD_CD;


Das zweite Beispiel vermeidet nicht nur das OR, sondern eliminiert auch die Kombination der Operatoren OR und LIKE. Anhand derartiger Beispiele sollten Sie die Laufzeitunterschiede bei Ihren Datenbeständen messen.


OLAP vs. OLTP

Bei der Feinabstimmung einer Datenbank ist zuerst der Einsatzzweck der Datenbank zu bestimmen. Eine Datenbank für Online-Analysen (OLAP - Online Analytical Processing) ist ein System, das dem Endbenutzer Abfragemöglichkeiten für statistische und allgemeine Informationen liefern soll. In derartigen Umgebungen werden oft Daten für statistische Berichte abgerufen und im Entscheidungsprozeß eines Unternehmens herangezogen. Daher spricht man auch von Systemen zur Entscheidungsunterstützung (DSS - Decision Support Systems). Eine Datenbank für die Online-Transaktionsverarbeitung (OLTP - Online Transactional Processing) stellt in erster Linie die Umgebung für die Eingaben von Endbenutzern bereit und kann gegebenenfalls Abfragen bezüglich aktueller Informationen realisieren. OLTP-Systeme werden für die Manipulation der Datenbankinformationen auf täglicher Basis eingesetzt. Data Warehouses und Systeme zur Entscheidungsunterstützung erhalten ihre Daten aus OLTP-Datenbanken und manchmal von anderen OLAP-Systemen.



OLTP-Feinabstimmung

Eine transaktionale Datenbank ist ein empfindliches System, auf das häufige Zugriffe in Form von Transaktionen und Abfragen bezüglich aktueller Informationen stattfinden. Gewöhnlich erfordert ein OLTP keinen großen Sortierbereich, wie er etwa in einer OLAP-Umgebung benötigt wird. Die meisten OLTP-Transaktionen sind schnell und umfassen kaum Sortieroperationen.


Zu den größten Problemen bei transaktionalen Datenbanken gehören die Rollback-Segmente. Deren Anzahl und Größe hängen in starkem Maße davon ab, wieviel Benutzer gleichzeitig auf die Datenbank zugreifen und wie umfangreich die einzelnen Transaktionen sind. In einer Transaktionsumgebung empfiehlt sich der Einsatz mehrerer Rollback-Segmente.


Ein weiteres Thema in einer Transaktionsumgebung ist die Integrität der Transaktionsprotokolle (oder Transaktions-Logs), die nach jeder Transaktion geschrieben werden. Diese Protokolle dienen einzig und allein der Wiederherstellung. Demzufolge muß jede SQL-Implementierung die Protokolle für »den Zeitpunkt der Wiederherstellung« sichern können. SQL Server bedient sich sogenannter Dump-Geräte. Oracle verwendet einen als ARCHIVELOG bezeichneten Datenbankmodus. Transaktionsprotokolle sind ebenfalls Gegenstand einer Leistungsbetrachtung, da die Sicherung der Protokolle zusätzliche Verwaltungsarbeit bedeutet.



OLAP-Feinabstimmung

Die Feinabstimmung von OLAP-Systemen wie bei Data Warehouses oder Systemen zur Entscheidungsunterstützung unterscheidet sich wesentlich von der Feinabstimmung einer Transaktionsdatenbank.


Da diese Systeme dem Abrufen von Daten für die Entscheidungsfindung dienen, ist mit komplexen Abfragen zu rechnen, in denen normalerweise Daten zu gruppieren und zu sortieren sind. Verglichen mit einer Transaktionsdatenbank benötigen OLAP-Systeme in der Regel mehr Platz für den Sortierbereich, aber weniger Platz für den Rollback-Bereich.


Die meisten Transaktionen in einem OLAP-System laufen als Teil einer Stapelverarbeitung ab. Statt verschiedene Rollback-Bereich für die Benutzereingaben vorzusehen, greift man auf einen großen Rollback-Bereiche für die routinemäßig durchgeführten Stapelprozesse zurück, um die zusätzlichen Verwaltungsarbeiten in Grenzen zu halten.



Stapelverarbeitung vs. Transaktionsverarbeitung

Ein Hauptfaktor bei der Leistungsbilanz von Datenbanken und SQL-Anweisungen ist der Typ der Verarbeitung, der in einer Datenbank stattfindet. Zu diesen Typen gehört die oben behandelte OLTP. Bei der Transaktionsverarbeitung unterscheidet man Benutzereingaben und Stapelprozesse.


Normale Benutzereingaben bestehen gewöhnlich aus SQL-Anweisungen wie INSERT, UPDATE und DELETE. Diese Arten der Transaktionen werden oft vom Endbenutzer - oder dem Kunden - ausgeführt. Endbenutzer arbeiten normalerweise mit einer Frontend-Anwendung wie PowerBuilder, um mit der Datenbank in den Dialog zu treten. Da die Frontend-Anwendung den SQL-Code generiert, kommt diese Benutzergruppe mit den eigentlichen SQL-Anweisungen nur selten in Berührung.


Bei der Optimierung der Leistung einer Datenbank sollte man sich hauptsächlich auf die Transaktionen des Endbenutzers konzentrieren. Denn »kein Kunde« ist gleichbedeutend mit »keine Datenbank«. Das heißt wiederum, daß Ihre Arbeit überflüssig wäre. Versuchen Sie immer, Ihre Kunden zufriedenzustellen, selbst wenn deren Erwartungen an die Leistung des Systems bzw. der Datenbank manchmal unvernünftig scheint. Einen Faktor der Benutzereingaben stellt die Anzahl der gleichzeitig arbeitenden - konkurrierenden - Benutzer dar. Die Wahrscheinlichkeit für ein Absinken der Datenbankleistung wächst mit einer größeren Zahl von konkurrierenden Benutzern.


Stapelverarbeitung bedeutet, daß mehrere Transaktionen bezüglich der Datenbank auf einmal ausgeführt werden. Nehmen wir zum Beispiel an, daß Sie die Daten des letzten Jahres in einer umfangreichen Archivdatenbank ablegen möchten. Vielleicht müssen Sie Tausende oder sogar Millionen von Datenzeilen in die Archivtabelle einfügen. Diese Aufgabe wollen Sie sicherlich nicht manuell erledigen, so daß Sie höchstwahrscheinlich einen Stapeljob oder ein Skript zur Automatisierung des Prozesses erstellen. (Für das Laden von Daten in einem Stapel sind zahlreiche Verfahren verfügbar.) Stapelverarbeitungen sind berüchtigt dafür, daß sie System- und Datenbankressourcen in starkem Maße beanspruchen. Zu den Datenbankressourcen gehören Zugriffe auf Tabellen und den Systemkatalog, Rollback-Segmente und Sortierbereiche. Systemressourcen umfassen die verfügbare CPU und den gemeinsam genutzten Speicher. Je nach Betriebssystem und Datenbank-Server sind viele weitere Faktoren zu beachten.


Sowohl Endbenutzer-Transaktionen als auch Stapelprozesse müssen bei den meisten Datenbanken erfolgreich ablaufen. Leistungsprobleme können aber auftreten, wenn sich die beiden Verarbeitungstypen gegenseitig blockieren. Demzufolge sollte man den Unterschied zwischen ihnen kennen und sie möglichst getrennt halten. Beispielsweise wird man bei hoher Benutzeraktivität keine Unmengen von Daten in die Datenbank laden. Aufgrund der vielen gleichzeitig arbeitenden Benutzer kann die Reaktionszeit der Datenbank bereits relativ groß sein. Stapelprozesse sollten vorzugsweise ablaufen, wenn die Benutzeraktivitäten ein Minimum erreicht haben. Viele Shops reservieren Zeiten in den Abend- oder frühen Morgenstunden, um Daten im Stapel zu laden und Konflikte mit den Routinearbeiten zu vermeiden.


Bei der Planung der Datenbankauslastung sollte man eine Überschneidung von massiven Stapeloperationen mit der normalen Datenbanknutzung ausschließen. Abbildung 15.1 zeigt, wie ein Block von massiven Stapelaktualisierungen und mehrere gleichzeitig ablaufende Benutzerprozesse um die Systemressourcen streiten.


Abbildung 15.1:
Kampf um die Systemressourcen

Es wird deutlich, daß viele Prozesse um die Systemressourcen kämpfen und die im Stapel ablaufenden Aktualisierungen der Sand im Getriebe sind. Die Systemressourcen sind nicht etwa gleichmäßig unter den Benutzern aufgeteilt, sondern werden scheinbar von den Stapelprozessen verschlungen. Das ist nur der Anfang des Ressourcenstreits. Im weiteren Verlauf der Stapeltransaktionen können die Benutzerprozesse schließlich ganz verdrängt werden. Dieser Zustand ist nicht gerade erstrebenswert. Selbst wenn nur ein Benutzer im System angemeldet ist, kann dieser nicht vernünftig arbeiten.


Bei Stapelprozessen tritt weiterhin das Problem auf, daß Tabellen gesperrt sind, auf die ein Benutzer gerade zugreifen möchte. Erst wenn der Stapelprozeß die Sperre der Tabellen aufhebt, ist ein Benutzerzugriff möglich - bis dahin können aber mehrere Stunden vergehen. Stapelprozesse plant man für Zeiten ein, wenn die Systemressourcen nur zu einem geringen Teil ausgelastet sind. Die Transaktionen des Benutzers sollten nicht als Konkurrenz zum Stapel ablaufen. Dieses Spiel kennt keine Gewinner.



Datenverarbeitung durch Löschen von Indizes optimieren

Stapelaktualisierungen lassen sich beschleunigen, wenn man Indizes löscht. Die oben erwähnte Archivtabelle enthält sicherlich mehrere tausend Zeilen und verfügt wahrscheinlich über einen oder mehrere Indizes. Zunächst verbindet man mit einem Index die Vorstellung von schnelleren Tabellenzugriffen. Bei Stapelprozessen kann aber das Löschen von Indizes vorteilhafter sein.


Lädt man Daten in eine indizierte Tabelle, ist mit häufigen Zugriffen auf den Index zu rechnen, insbesondere wenn man einen hohen Prozentsatz von Zeilen in der Tabelle aktualisiert. Sucht man zum Beispiel in einem Buch viele Schlüsselstellen für einen späteren Bezug, kommt man wahrscheinlich schneller voran, wenn man das Buch von Anfang bis Ende durcharbeitet, ohne auf den Index zurückzugreifen. (Die Benutzung des Index wäre effizient, wenn man nur einen Bruchteil als Schlüsselstellen markieren möchte.)


Die Geschwindigkeit von Stapelprozessen, die einen großen Teil der Tabellenzeilen betreffen, kann man erhöhen, indem man die Indizes nach den folgenden Schritten behandelt:


1. Löschen der in Frage kommenden Indizes.


2. Laden/Aktualisieren der Daten in der Tabelle.


3. Index auf der Tabelle neu aufbauen.



Ein häufiges COMMIT entlastet den DBA

Bei der Ausführung von Stapeltransaktionen muß man wissen, wie oft COMMIT-Operationen auszuführen sind. Wie Sie in Lektion 11 gelernt haben, schließt eine COMMIT-Anweisung eine Transaktion ab - das heißt, sichert eine Transaktion oder schreibt alle Änderungen in die betreffende(n) Tabelle(n). Hinter den Kulissen läuft aber wesentlich mehr ab. Einige Bereiche in der Datenbank sind für die Speicherung der abgeschlossenen Transaktionen reserviert, bevor die Änderungen tatsächlich in die Zieltabelle übernommen werden. Oracle nennt diese Bereiche Rollback-Segmente. Führt man eine COMMIT-Anweisung aus, werden die in den Rollback-Segmenten der betreffenden SQL-Sitzung gespeicherten Transaktionen in die Zieltabelle geschrieben. Nach abgeschlossener Aktualisierung wird der Inhalt des Rollback-Segments entfernt. Ein ROLLBACK-Befehl löscht dagegen den Inhalt des Rollback-Segments, ohne die Zieltabelle zu aktualisieren.


Es liegt auf der Hand, daß sich die Transaktionen in den Rollback-Segmenten anhäufen, wenn man keine COMMIT- oder ROLLBACK-Befehle auslöst. Reicht der Platz in den Rollback-Segmenten nicht mehr aus, um weitere Daten aufzunehmen, kommt die Datenbank praktisch zum Stillstand und verweigert alle weiteren Transaktionen. Die Unterlassung von COMMIT-Befehlen ist ein häufiger Stolperstein in der Programmierung. Regelmäßig ausgelöste COMMITs sichern eine stabile Arbeitsweise des gesamten Datenbanksystems.


Die Verwaltung von Rollback-Segmenten ist eine komplexe und wichtige Aufgabe des Datenbank-Administrators (DBA), da Transaktionen dynamisch die Rollback-Segmente beeinflussen und wiederum sowohl die Gesamtleistung der Datenbank als auch einzelne SQL-Anweisungen betreffen. Wenn man daher große Datenmengen lädt, sollte man die regelmäßige Ausführung des COMMIT-Befehls sicherstellen. Fragen Sie Ihren DBA um Rat, wie oft man bei Stapeltransaktionen COMMITs verwenden sollte. (Siehe dazu Abbildung 15.2.)


Abbildung 15.2:
Der Rollback-Bereich

Wie man Abbildung 15.2 entnehmen kann, verbleiben die Änderungen im Rollback-Bereich, wenn ein Benutzer eine Transaktion ausführt.



Tabellen und Indizes in einer dynamischen Umgebung neu aufbauen

Der Begriff dynamische Datenbankumgebung bezeichnet eine große Datenbank, die einem ständigen Wechsel unterworfen ist. Dabei geht es hier vor allem um häufige Stapelaktualisierungen und routinemäßig ablaufende Transaktionen. Dynamische Datenbanken erfordern gewöhnlich starke OLTP-Systeme, können sich aber auch auf Systeme der Entscheidungsunterstützung oder Data Warehouses beziehen - je nach Umfang und Häufigkeit der Ladeoperationen von Daten.


Massive und häufige Änderungen führen zu einem Wachstum der Datenbank, was mit einer Fragmentierung verbunden ist. Wenn man auf das Wachstum einer Datenbank nicht in der richtigen Weise Einfluß nimmt, kann die Fragmentierung leicht zu unkontrollierbaren Zuständen führen. Oracle weist den Tabellen bei der Erzeugung eine anfängliche Größe zu. Wenn die geladenen Daten den Umfang der anfänglichen Tabellengröße erreichen, wird ein Erweiterungsbereich zugewiesen, dessen Größe man ebenfalls beim Erstellen der Tabelle festlegt.


Die Größenfestlegung von Tabellen und Indizes ist im wesentlichen eine Aufgabe des DBA und kann die Leistung von SQL-Anweisungen drastisch beeinflussen. Der erste Schritt ist ein vorausschauendes Arbeiten. Schaffen Sie vernünftigen Platz für Tabellen, damit diese vom ersten Tag an wachsen können. Planen Sie auch die regelmäßige Defragmentierung der Datenbank ein, selbst wenn das wöchentliche Wartungsmaßnahmen bedeuten sollte. Die grundlegenden Schritte bei der Defragmentierung von Tabellen und Indizes in einem relationalen Datenbank-Managementsystem sehen folgendermaßen aus:


1. Legen Sie eine Datensicherung der Tabellen und/oder Indizes an.


2. Löschen Sie die Tabellen und/oder Indizes.


3. Bauen Sie die Tabellen und/oder Indizes mit neuer Platzzuweisung wieder auf.


4. Stellen Sie die Daten in den neu aufgebauten Tabellen wieder her.


5. Erstellen Sie bei Bedarf die Indizes neu.


6. Richten Sie bei Bedarf die Benutzer-/Rollenberechtigungen auf der Tabelle neu ein.


7.Heben Sie die Sicherungskopie Ihrer Tabelle solange auf, bis Sie sich von der Fehlerfreiheit der neuen Tabelle überzeugt haben. Wenn Sie die Sicherungskopie der Originaltabelle löschen, sollten Sie zuerst eine Sicherung der neuen Tabelle anlegen, nachdem die Daten vollständig wiederhergestellt wurden.


Das folgende Beispiel demonstriert den Einsatz einer Adressentabelle in einer Oracle-Datenbankumgebung. (Für NEW_SIZE ist ein geeigneter Wert einzusetzen. TABLESPACE_NAME ist durch den Namen des entsprechenden Tabellenbereichs zu ersetzen.)


Verwerfen Sie die Sicherungskopie Ihrer Tabelle erst, wenn Sie sich vom erfolgreichen Aufbau der neuen Tabelle überzeugt haben.

CREATE TABLE ADRESSEN_SICH AS
SELECT * FROM ADRESSEN;


Tabelle wurde angelegt.


drop table ADRESSEN;

Tabelle wurde gelöscht.

SQL> CREATE TABLE ADRESSEN (
2 PERSONAL_NR VARCHAR2(12) NOT NULL,
3 NAME VARCHAR2(30) NOT NULL,
4 ADRESSE VARCHAR(40) NOT NULL,
5 STADT VARCHAR(25) NOT NULL,
6 STAAT VARCHAR(2) NOT NULL,
7 ZIP VARCHAR(9) NOT NULL
8 )
9 TABLESPACE MIST
10 STORAGE ( INITIAL NEW_SIZE
11 NEXT NEW_SIZE );

Tabelle wurde angelegt.

SQL> INSERT INTO ADRESSEN
2 SELECT * FROM ADRESSEN_SICH;

93.451 Zeilen wurden erstellt.
SQL> CREATE INDEX ADRESSEN_IDX ON ADRESSEN
2 (
3 PERSONAL_NR
4 )
5 TABLESPACE TABLESPACE_NAME
6 STORAGE ( INITIAL NEW_SIZE
7 NEXT NEW_SIZE );

Index wurde angelegt.

SQL> grant select on adressen to public;

Benutzerzugriff (Grant) wurde erteilt.

SQL> drop table adressen_sich;

Tabelle wurde gelöscht.


Wenn man größer gewordene Tabellen und Indizes neu aufbaut, läßt sich gleichzeitig die Speicherung optimieren und dadurch die Gesamtleistung verbessern. Denken Sie daran, die Sicherungskopie der Tabelle erst dann zu löschen, wenn Sie sich vom erfolgreichen Aufbau der neuen Tabelle überzeugt haben. Beachten Sie auch, daß man die gleichen Ergebnisse mit anderen Methoden erreichen kann. Prüfen Sie die verfügbaren Möglichkeiten anhand der Dokumentation zur Datenbank.


Feinabstimmung der Datenbank

Bei der Feinabstimmung einer Datenbank optimiert man die Leistung des Datenbank-Servers. Als Neueinsteiger in SQL werden Sie wahrscheinlich noch nichts mit der Abstimmung der Datenbank zu tun haben, außer wenn Sie als Datenbank-Administrator neu beginnen oder in dieser Position auf eine relationale Datenbankumgebung umsteigen. Ob Sie nun eine Datenbank verwalten oder SQL in Anwendungen oder der Programmierung einsetzen, es ist für Sie auf jeden Fall von Nutzen, wenn Sie die Methoden zur Feinabstimmung von Datenbanken kennen. Der Schlüssel zum Erfolg für alle Datenbanken liegt in der Zusammenarbeit aller beteiligten Parteien. Die folgenden Tips gelten allgemein für die Feinabstimmung einer Datenbank:


Abbildung 15.3 zeigt ein einfaches Beispiel für die Aufteilung der Hauptbereiche einer Datenbank.


Abbildung 15.3:
Verfügbare Laufwerke zur Leistungssteigerung nutzen

Das Szenario in Abbildung 15.3 arbeitet mit vier Platten: Disk01 bis Disk04. Das Ziel bei der Aufteilung der Datenbankbereiche und Objekte besteht darin, Bereiche mit starker Nutzung möglichst voneinander getrennt zu halten.


Beachten Sie auch die Anordnung der Tabellen und Indizes einer Anwendung auf den Festplatten. Tabellen und Indizes sollten möglichst getrennt vorliegen.

Wie Abbildung 15.3 zeigt, sind die Tabellen und Indizes auf unterschiedlichen Geräten gespeichert. Insbesondere ist zu sehen, wie eine »große Tabelle« und der dazugehörige Index auf mehrere Geräte aufgeteilt werden. Diese Technik zerlegt die Tabelle in kleinere Segmente, auf die man gleichzeitig zugreifen kann. Darüber hinaus bietet sich die Möglichkeit, die Fragmentierung zu steuern. Im dargestellten Szenario lassen sich Tabellen lesen und gleichzeitig die zugehörigen Indizes referenzieren, was die Geschwindigkeit des gesamten Datenzugriffs erhöht.


Es handelt sich hier nur um ein einfaches Beispiel. Je nach der Funktion, der Größe und der systembezogenen Probleme Ihrer Datenbank werden Sie ähnliche und vielleicht bessere Methoden finden, um den Zugriff auf die Systemressourcen zu optimieren. Im Idealfall könnte man jeder größeren Datenbankeinheit - einschließlich großer Tabellen und Indizes - eine eigene Festplatte spendieren. Dieses Vorhaben wird aber nicht zuletzt am finanziellen Aufwand scheitern.


Der DBA und Systemadministrator sollten zusammenarbeiten, um die Platzzuweisung der Datenbank abzuwägen und den auf dem Server verfügbaren Speicher zu optimieren.

Die Abstimmung einer Datenbank hängt in erster Linie vom eingesetzten Datenbanksystem ab. Offensichtlich genügt es nicht, Abfragen vorzubereiten und sie auf die Datenbank loszulassen. Andererseits nützt die beste Abstimmung der Datenbank nichts, wenn das SQL der Anwendung selbst nur ungenügend formuliert ist. Datenbankprofis spezialisieren sich in der Regel auf ein Datenbankprodukt und beschäftigen sich intensiv mit den Merkmalen und Eigenheiten dieses Produkts. Obwohl die Abstimmung von Datenbanken oft als lästige Aufgabe angesehen wird, kann sie für Leute, die wirklich etwas davon verstehen, eine sehr lukrative Beschäftigung sein.



Leistungsbremsen

Wir haben bereits einige der zahllosen Stolpersteine erwähnt, die die Leistungsbilanz einer Datenbank negativ beeinflussen. Dabei handelt es sich im allgemeinen um Engpässe, die mit der Verwaltung auf Systemebene, der Datenbankwartung und der Verarbeitung der SQL-Anweisungen zusammenhängen.


Dieser Abschnitt faßt die allgemeinen Bremsen der Systemleistung und Reaktionszeiten von Datenbanken zusammen.


Die Struktur einer SQL-Anweisung läßt sich mit verschiedenen Methoden optimieren, wobei die Reihenfolge der Verarbeitungsschritte auf dem Datenbank-Server einen wesentlichen Faktor darstellt.


Werkzeuge zur Feinabstimmung

Prüfen Sie zusammen mit Ihrem DBA oder dem Datenbankanbieter, welche Werkzeuge für die Messung und Abstimmung der Leistung vorhanden sind. Mit den entsprechenden Werkzeugen lassen sich Schwächen in der gesamten Kette des Datenzugriffs erkennen. Darüber hinaus können diese Werkzeuge auch Änderungen vorschlagen, um die Leistung einer bestimmten SQL-Anweisung zu verbessern.


Oracle bietet mit dem Befehl EXPLAIN PLAN und dem Diagnosewerkzeug TKPROF die Möglichkeit, SQL-Anweisungen zu testen. Die Ausgabe des Befehls EXPLAIN PLAN spiegelt das Verhalten des Oracle-Optimierers wider. Mit dem Werkzeug TKPROF läßt sich die Effizienz der von einer Anwendung ausgeführten SQL-Anweisungen bestimmen. Daneben stellt Oracle weitere Diagnosewerkzeuge für die Analyse von SQL-Anweisungen und der Datenbank bereit. Wenn Sie lediglich die von einer Abfrage benötigte Zeit messen möchten, können Sie mit dem Befehl SET TIMING ON von SQL*Plus arbeiten.


Der Befehl SET TIMING ON und andere SET-Befehle werden detailliert in Lektion 20 behandelt.


SQL Server von Sybase verfügt ebenfalls über Diagnosewerkzeuge für SQL-Anweisungen. Diese Optionen liegen in der Form von SET-Befehlen vor, die man in die SQL-Anweisungen einbauen kann. (Diese Befehle ähneln den SET-Befehlen von Oracle.) Gebräuchliche Befehle sind SET SHOWPLAN ON, SET STATISTICS IO ON und SET STATISTICS TIME ON. Der Befehl SET SHOWPLAN ON generiert eine Beschreibung der Verarbeitungsschritte für die Abfrage. SET STATISTICS IO ON zeigt die Anzahl der Suchläufe, die Anzahl der logischen Leseoperationen (Zugriffe auf Seiten im Cache) und die Anzahl der physikalischen Leseoperationen (Laufwerkzugriffe) für jede in der Anweisung referenzierte Tabelle an. Die Ausführungszeit der Befehle insgesamt und der einzelnen Befehlsschritte läßt sich mit SET STATISTICS TIME ON anzeigen. Auf die SET-Befehle von SQL Server geht Tag 19 ein.



Zusammenfassung

Die Leistung von SQL-Anweisungen läßt sich durch die Optimierung - oder Feinabstimmung - der Anwendung auf der einen Seite und der Datenbank auf der anderen Seite unmittelbar beeinflussen. Die beiden Seiten sind im Prinzip zwar unabhängig, aber eine Komponente kann ohne die andere nicht optimal abgestimmt werden. Der erste Schritt in Richtung Erfolg besteht darin, daß Techniker und Systemingenieure gemeinsam an der Ausgewogenheit der Ressourcen arbeiten und alle von der Datenbank gebotenen Möglichkeiten zur Leistungsverbesserung nutzen. Unterstützung bieten zahlreiche Werkzeuge, die zum Lieferumfang der Datenbanksystem gehören.


Der Anwendungsentwickler muß die Datenstruktur kennen. Hier liegt der Schlüssel zu einem optimalen Datenbankentwurf. Entwickler und Programmierer müssen wissen, wann Indizes zu verwenden sind, wann man einen weiteren Index hinzufügen muß und wann die Ausführung von Stapeljobs erlaubt ist. Stellen Sie einen Zeitplan für Stapelprozesse auf und halten Sie die Stapelverarbeitung von der täglichen Transaktionsverarbeitung getrennt.


Durch eine Feinabstimmung der Datenbank kann man die Leistung der jeweiligen Anwendung verbessern, die auf die Datenbank zugreift. Datenbank-Administratoren müssen die ständige Verfügbarkeit der Datenbank gewährleisten. Neben der sorgfältigen Feinabstimmung, die hinter den Kulissen abläuft, kann der DBA gewöhnlich kreative Vorschläge für den effizienteren Datenzugriff anbieten, wie zum Beispiel die Manipulation von Indizes oder den Neuaufbau einer SQL-Anweisung. Der DBA sollte auch mit den Werkzeugen vertraut sein, die zu den Datenbankpaketen gehören, um die Leistung zu messen und Vorschläge für die Optimierung von Anweisungen zu liefern.



Fragen & Antworten

Frage:

Welcher Leistungsgewinn ist zu erwarten, wenn ich meine SQL-Anweisungen optimiere?

Antwort:

Der erreichbare Leistungsgewinn hängt von der Größe der Tabellen, von der Indizierung der Spalten und anderen relativen Faktoren ab. In einer sehr großen Datenbank läßt sich eine komplexe Abfrage, die stundenlang läuft, auf einige Minuten reduzieren. Bei der Transaktionsverarbeitung kann die Vereinfachung von SQL-Anweisungen wichtige Sekunden für den Endbenutzer einsparen.

Frage:

Wie koordiniere ich das Laden oder Aktualisieren im Stapelbetrieb?

Antwort:

Stimmen Sie mit dem Datenbank-Administrator und natürlich mit dem Management den Zeitplan für das Laden oder Aktualisieren in Stapeln ab. Als Systemingenieur werden Sie wahrscheinlich nicht alle Vorgänge innerhalb der Datenbank kennen.

Frage:

Wie oft sollte ich meine Stapeltransaktionen mit COMMIT bestätigen?

Antwort:

Fragen Sie den DBA um Rat. Er muß in etwa wissen, wie viele Daten Sie einfügen, aktualisieren oder löschen. Die Häufigkeit der COMMIT-Anweisungen sollte auch andere Stapelprozesse berücksichtigen, die gleichzeitig mit anderen Datenbankaktivitäten auftreten.

Frage:

Sollte ich alle Tabellen auf mehrere Laufwerke aufteilen?

Antwort:

Das Aufteilen bietet nur bei großen Tabellen und/oder bei Tabellen mit regelmäßig starkem Zugriff Leistungsvorteile.


Workshop


Kontrollfragen

1. Was versteht man unter Optimieren einer SQL-Anweisung?


2. Sollte man Tabellen mit den zugehörigen Indizes auf demselben Datenträger unterbringen?


3. Warum ist die Anordnung der Bedingungen in einer SQL-Anweisung wichtig?


4. Was passiert bei einer vollständigen Tabellensuche?


5. Wie kann man eine vollständige Tabellensuche vermeiden?


6. Nennen Sie allgemeine Leistungsbremsen.



Übungen

1. Verbessern Sie die Lesbarkeit der folgenden SQL-Anweisung:


SELECT MITARBEITER.NACHNAME, MITARBEITER.VORNAME, MITARBEITER.INITIALEN,
MITARBEITER.ADRESSE, MITARBEITER.TELEFON, GEHALTSLISTE.GEHALT, GEHALTSLISTE.POSITION,
MITARBEITER.SVN, GEHALTSLISTE.EINST_DATUM FROM MITARBEITER, GEHALTSLISTE WHERE
MITARBEITER.SVN = GEHALTSLISTE.SVN AND MITARBEITER.NACHNAME LIKE 'S%' AND
GEHALTSLISTE.GEHALT > 20000;


2. Ordnen Sie die Bedingungen in der folgenden Abfrage neu an, um die Zeit für das Abrufen der Daten zu optimieren. Verwenden Sie die folgenden Statistiken (auf den Tabellen in ihrer Gesamtheit), um die Reihenfolge der Bedingungen zu bestimmen:


SELECT A.NAME, A.ADRESSE, A.STADT, A.STAAT, A.ZIP,
S.GESCHLECHT, S.PERS_STAND, S.GEHALT
FROM ADRESSEN A,
PERS_STAT_TBL S
WHERE A.NAME LIKE 'SMITH%'
AND A.STADT = 'INDIANAPOLIS'
AND S.GESCHLECHT = 'M'
AND S.GEHALT >= 30000
AND S.PERS_STAND = 'S'
AND A.PERSONAL_NR = S.PERSONAL_NR;



Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: SQL in 21 Tagen, ISBN: 3-8272-2020-3


vorheriges KapitelTop Of PageInhaltsverzeichnisIndexInfoseitenächstes Kapitel