Oracle Datenbank-Tuning - Teil 4

15.02.2007 von Lutz Fröhlich, Carsten Czarski und Klaus Maier
In diesem Teil der Serie zum Tuning von Oracle Datenbanken betrachten wir zusätzliche Parameter und Funktionen, die die Performance der Datenbank beeinflussen.

Datenbanken sind das Herz vieler Business-Applikationen oder Websites. Der Erfolg steht und fällt mit der Geschwindigkeit, in die Datenbank Anfragen bearbeitet. Kommt die Antwort auf eine Web-Anfrage zu spät, könnte der Besucher schon wieder verschwunden sein und anderswo bestellen.

In den ersten drei Teilen haben wir uns mit Verfahren beschäftigt, die direkten Einfluss auf die Datenbank-Performance haben. Dieser Teil dreht sich um den Redo Log Buffer. Dieser enthält ein Verzeichnis aller Transaktionen und wird für eventuell notwendige Recovery-Maßnahmen benötigt. Je nachdem wie der Buffer angesteuert wird, kann das massive Auswirkungen auf die Datenbank-Performance haben.

Unsere neue Serie zu Oracle 10g basiert auf Kapitel 18 des Standardwerks „Oracle 10g“ von Fröhlich, Czarski und Maier aus dem Verlag Markt + Technik. Sie können dieses über 870 Seiten starke Buch auch in unserem Buchshop bestellen oder als eBook herunterladen.
Serie: Oracle Datenbank-Tuning

Teil 1: Grundlagen und grundsätzliche Überlegungen

Teil 2: Shared Pool konfigurieren

Teil 3: Buffer Cache optimieren

Teil 4: Der Redo Log Buffer

Teil 5: I/O optimieren

Teil 6: Tuning automatisieren

Den Redo Log Buffer optimieren

Der Redo Log Buffer ist der Cache für Online Redo Log-Dateien. Online Redo Log-Dateien speichern Roll-Forward-Informationen, die für Recovery-Prozesse benötigt werden. Alle Änderungen werden gespeichert und können bei Bedarf wiederholt werden.

Die Server-Prozesse der Benutzer schreiben Redo Log-Informationen in den Redo Log Buffer. Im Gegensatz zu den bisher beschriebenen Pools funktioniert der Redo Log Buffer nicht nach dem LRU-Mechanismus.

Der Redo Log Buffer kann als ein Trichter verstanden werden, in den Informationen hineinlaufen und der von Zeit zu Zeit geleert wird. Der Hintergrundprozess Log Writer (LGWR) ist verantwortlich für das Leeren des Redo Log Buffers. Diese Daten werden in die aktuelle Online Redo Log-Datei geschrieben. Um genügend Freiraum für neue Daten zur Verfügung zu halten, wird der LGWR in den folgenden Situationen zum Entleeren des Buffers angewiesen:

Da der LGWR bei all diesen Ereignissen aktiv wird, kommt es sehr selten zum Überlaufen des Buffers. Als Administrator können Sie nur die letzten zwei Ereignisse kontrollieren.

Größe des Redo Log Buffers festlegen

Sie können die Größe des Redo Log Buffers festlegen und damit bestimmen, wann der Füllungsgrad von einem Drittel erreicht wird. Das Festlegen der Buffer-Größe ist u.a. Gegenstand dieses Abschnitts.

Ein Checkpoint ist ein konsistenter Zustand der Datenbank. Wenn ein Checkpoint auftritt, werden die folgenden Aktivitäten durchgeführt:

Im Falle eines Instanz-Fehlers müssen also nur die Transaktionen wiederhergestellt werden, die nach dem letzten Checkpoint aufgetreten sind. Checkpoints werden angestoßen, wenn die folgenden Ereignisse auftreten:

Die Parameter log_checkpoint_interval und log_checkpoint_ timeout legen Schwellenwerte fest, bei deren Erreichen ein Checkpoint ausgeführt wird.

Der Parameter log_checkpoint_interval bestimmt die Menge von Daten, die in die Online Redo Log-Dateien geschrieben werden können, bevor ein Checkpoint ausgelöst wird. Die Angabe erfolgt in Anzahl von Betriebssystem-Blöcken. Die Blockgröße auf dem Betriebssystem (sie beträgt in der Regel 512 Byte) ist nicht identisch mit der Blockgröße der Datenbank.

Mit dem Parameter log_checkpoint_timeout wird festgelegt, wie viele Sekunden verstreichen können, bevor ein Checkpoint ausgelöst wird.

In Oracle9i wurde der Initialisierungsparameter fast_start_mttr_target zur Vorgabe der Recovery-Zeit eingeführt. Die Zeitangabe erfolgt in Sekunden und legt fest, wie viel Zeit für ein Crash Recovery zur Verfügung steht. Dieser Parameter beeinflusst die Häufigkeit des Auftretens von Checkpoints. Beachten Sie, dass log_checkpoint_interval und log_checkpoint_timeout den Parameter fast_start_mttr_target überschreiben.

Performance des Redo Log Buffers

Wenn die Datenbank im ARCHIVELOG-Modus läuft, kopiert der Hintergrundprozess ARCH die Online Redo Log-Dateien in ein Archiv. In folgender Abbildung sehen Sie die Architektur des Redo Log-Mechanismus.

Ein Weg zum Messen der Performance des Redo Log Buffers ist, die Häufigkeit und die Länge von Warte-Ereignissen der Server-Prozesse zu ermitteln. Für diese Statistiken stehen die Views V$SYSSTAT und V$SESSION_EVENT zur Verfügung.

In V$SYSSTAT werden alle relevanten Statistiken des Redo Log Buffers aufgezeichnet. Der Wert für redo entries spiegelt die Anzahl von Einträgen in den Redo Log Buffer durch Server-Prozesse seit dem Start der Instanz wider. Die Statistik redo buffer allocation retries ist die Anzahl von Wiederholungen, die der Server-Prozess gestartet hat, um Daten im Redo Log Buffer zu platzieren. Die Abfrage in folgendem Listing ermittelt die Retry Ratio des Redo Log Buffers.

SQL> SELECT a.value/b.value "Retry Ratio"
2 FROM v$sysstat a, v$sysstat b
3 WHERE a.name='redo buffer allocation retries'
4 AND b.name='redo entries';
Retry Ratio
-----------
,00009602

Im Idealfall würde der Server-Prozess nie warten, um Blöcke in den Redo Log Buffer zu schreiben. Ein starkes Anwachsen der Retry Ratio ist ein Indiz dafür, dass der Redo Log Buffer optimiert werden muss.

Die Retry Ratio des Log Buffers sollte weniger als ein % betragen. Ist sie größer, muss der Redo Log Buffer vergrößert werden.

Die Statistik redo log space requests misst, wie oft der LGWR-Prozess auf einen Log Switch warten musste.

SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name='redo log space requests';
NAME VALUE
-------------------------------------- -----
redo log space requests 6

Wartezustände durch Log Switch

Während eines Log Switch kann der LGWR-Prozess keine Daten aus dem Redo Log Buffer wegschreiben. Das kann zu Wartezuständen für den Server-Prozess führen. Damit wird ein Wiederholungsversuch ausgelöst, der sich in der Statistik niederschlägt. Eine starke Erhöhung der Statistik redo log space requests ist ein sicheres Zeichen dafür, dass die Online Redo Log-Dateien zu klein sind.

Das View V$SESSION_WAIT liefert mehr Informationen zu den Warte-Ereignissen. Die Statistik log buffer space zeigt, wie lange eine Sitzung warten musste, um einen Eintrag im Redo Log Buffer zu platzieren.

SQL> SELECT b.username, a.seconds_in_wait, a.state
2 FROM v$session_wait a, v$session b
3 WHERE a.sid = b.sid
4 AND event = 'log buffer space';
USERNAME SECONDS_IN_WAIT STATE
-------- --------------- ----------
SYSTEM 45 WAITING

Manchmal werden Checkpoints gestartet, jedoch nicht erfolgreich beendet, weil der vorher gestartete Checkpoint noch nicht abgeschlossen war. Solche Ereignisse teilt Ihnen die Statistik log file switch (checkpoint incomplete)mit.

SQL> SELECT event, total_waits, average_wait
2 FROM v$system_event
3 WHERE event LIKE 'log file switch%';
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------- ----------- ------------
log file switch (checkpoint i 6 32
ncomplete)

Jetzt wissen Sie, wie der Redo Log Buffer funktioniert und wie er überwacht werden kann. Zur Verbesserung der Performance gibt es die folgenden Methoden:

Den Redo Log Buffer vergrößern

Der einfachste Weg, die Performance des Redo Log Buffers zu verbessern, ist, ihn zu vergrößern. Je größer der Redo Log Buffer ist, desto geringer ist die Wahrscheinlichkeit, dass ein Server-Prozess keinen Platz für Redo Buffer findet. Die Größe des Redo Log Buffers wird durch den Initialisierungsparameter log_buffer festgesetzt.

Wenn Sie den Redo Log Buffer zu klein machen, setzt Oracle ihn automatisch auf einen Standardwert hoch. Dieser Standardwert ist abhängig von der Plattform.

Vergrößern Sie den Rego Log Buffer so lange, bis die Retry Ratio keine Verbesserung mehr aufweist oder Warte-Ereignisse aus den Statistiken verschwinden.

Die Effizienz von Checkpoints verbessern

Ein Checkpoint bedeutet viele E/A-Aktivitäten für die Festplatten. Wird ein Checkpoint gestartet, dann sollte er auch erfolgreich beendet werden. Wenn nicht, führt das zu einer zusätzlichen Systembelastung, ohne dass ein Vorteil für den Recovery-Prozess herausgesprungen ist.

Vergrößern Sie die Online Redo Log-Dateien, wenn die Statistiken unvollständige Checkpoints aufweisen. Dann dauert es länger, bis eine Log-Datei gefüllt wird und ein Checkpoint durch einen Log Switch ausgelöst wird.

Die Archivierung beschleunigen

Ein weiterer potenzieller Flaschenhals ist der Hintergrundprozess ARCn. Wenn LGWR in eine Redo Log-Datei schreiben will, die gerade archiviert wird, dann wartet der Prozess so lange, bis die Archivierung beendet ist.

LGWR startet automatisch weitere ARCn-Prozesse, wenn diese Situation auftritt. Sie können die Wahrscheinlichkeit verringern, dass der ARCn-Prozess einen Log Switch behindert, indem Sie das Wegschreiben ins Archiv beschleunigen oder die Anzahl der Redo Log-Dateien erhöhen.

Weniger Redo-Daten erzeugen

Eine zusätzliche Maßnahme zur Verbesserung der Redo Log Buffer-Performance ist, weniger Redo-Informationen im System zu erzeugen. Mit Hilfe der Option UNRECOVERABLE oder NOLOGGING kann der Logging-Mechanismus umgangen werden. In diesem Fall wird nur ein so genanntes Invalidation Redo in den Redo Log Buffer geschrieben. Folgendes Listing zeigt ein Beispiel.

SQL> CREATE TABLE accounts_history
2 AS SELECT * FROM accounts
3 UNRECOVERABLE;
Tabelle wurde angelegt.

Beachten Sie, dass im Fall eines Recovery diese Tabelle unter Umständen nicht wiederhergestellt werden kann, da keine Roll-Forward-Informationen existieren. Führen Sie deshalb ein Backup aus, nachdem Sie die Option UNRECOVERABLE verwendet haben.

Die Option NOLOGGING hat denselben Effekt wie UNRECOVERABLE. Allerdings kann diese Option einem Objekt permanent zugewiesen werden. Sie können das Schlüsselwort NOLOGGING beim Erstellen einer Tabelle angeben.

SQL> CREATE TABLE ... NOLOGGING;

Diese Option kann mit der ALTER TABLE-Anweisung ein- und ausgeschaltet werden. Auswirkung hat die Option NOLOGGING allerdings nur auf den SQL*Loader im Direct-Path-Modus. In allen anderen Situationen wird »normales« Redo geschrieben.

SQL> ALTER TABLE ... LOGGING;
SQL> ALTER TABLE ... NOLOGGING;

Mit der folgenden Abfrage können Sie feststellen, welche Tabellen als NOLOGGING deklariert sind.

SQL> SELECT owner, table_name
2 FROM dba_tables
3 WHERE logging = 'NO';
OWNER TABLE_NAME
------------ ------------------------
DWH DWL_LOAD_SECTION
SYSTEM HOUSEHOLD_FACTS2
DWH ACCOUNTS_HISTORY

Den Streams Pool optimieren

Der Streams Pool ist neu in Oracle 10g und wird von Buffered Queues benutzt. Buffered Queues wurden in Zusammenhang mit Oracle Streams eingeführt. Die Größe des Streams Pools wird mit dem Parameter STREAMS_POOL_SIZE festgelegt. Der Streams Pool ist Bestandteil der SGA, er muss also in den Wert für SGA_MAX_SIZE eingerechnet werden.

Falls STREAMS_POOL_SIZE auf 0 gesetzt ist, werden maximal 10% des Shared Pools für den Streams Pool benutzt. Vergrößern Sie die Größe des Pools um die folgenden Werte:

Die SQL-Abfrage in folgendem Listing liefert die aktuelle Belegung des Streams Pools.

Die Belegung des Streams Pools abfragen

SQL> SELECT * FROM v$sgastat
2 WHERE pool='streams pool';
POOL NAME BYTES
------------ ------------------------------ ----------
streams pool Sender info 2828
streams pool free memory 75476344
streams pool recov_kgqmctx 256
streams pool apply shared t 4248
streams pool kwqbsinfy:bqgc 4004
streams pool qtree_kwqpspse 36
streams pool recov_kgqbtctx 4496
streams pool spilled:kwqbls 4232
streams pool deqtree_kgqmctx 36
streams pool msgtree_kgqmctx 36
streams pool substree_kgqmctx 36
streams pool kwqbcqini:spilledovermsgs 920

(mha)