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 vier Teilen haben wir uns mit Verfahren beschäftigt, die Sie direkt über die Oracle-Datenbank steuern können, um die Leistungsfähigkeit zu verbessern. Dieser Teil dreht sich um den darunter liegenden Datenträger und wie Sie ihn optimal auf den Einsatz als Datenbank-Storage vorbereiten.
|
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 |
Ein- und Ausgabe-Aktivitäten (E/A)
Die Dateien der Datenbank bestehen aus Datenblöcken. Die Blöcke beinhalten die Daten der einzelnen Segmente. Die Server-Prozesse der Benutzer lesen die Blöcke in den Buffer Cache und der Hintergrundprozess Database Writer (DBWn) schreibt sie zurück auf die Festplatte.
Sie können die E/A-Performance mit den Views V$FILESTAT und V$DATAFILE oder mit dem Performance Manager messen. Die Abfrage in folgendem Listing liefert die wichtigsten E/A-Statistiken der Datenbankdateien.
SQL> SELECT name, phyrds, phywrts,
2 avgiotim, miniotim, maxiowtm, maxiortm
3 FROM v$filestat a, v$datafile b
4 WHERE a.file# = b.file#;
NAME PHYRDS PHYWRTS AVGIOTIM MINIOTIM MAXIOWTM MAXIORTM
------------ ------ ------- -------- -------- -------- --------
system01.dbf 3140 149 0 0 2 8
undotbs01.dbf 23 84 0 0 2 4
sysaux01.dbf 574 666 0 0 4 8
users01.dbf 5 2 0 0 1 0
example01.dbf 7 2 0 0 1 2
big_ts.dbf 5 2 0 0 1 1
Ausgabe von V$FILESTAT und V$DATAFILE
Die Bedeutung der Werte ist in folgender Tabelle beschrieben.
|
Spalte |
Beschreibung |
|
|
Anzahl der physischen Lesezugriffe |
|
|
Anzahl der physischen Schreibzugriffe |
|
|
Durchschnittszeit in Millisekunden, die für E/A-Operationen gebraucht wurde |
|
|
Minimale Zeit in Millisekunden, die für E/A-Operationen gebraucht wurde |
|
|
Maximale Zeit in Millisekunden, die für Schreiboperationen gebraucht wurde |
|
|
Maximale Zeit in Millisekunden, die für Leseoperationen gebraucht wurde |
Das Beispiel in vorigem Listing zeigt, dass E/A-Operationen einigermaßen gleichmäßig verteilt sind. Die SYSTEM-Tablespace weist eine erhöhte Aktivität aus. Sollte die E/A-Aktivität der SYSTEM-Tablespace ungewöhnlich stark ansteigen, dann ist entweder der Dictionary Cache zu klein oder es befinden sich andere Benutzer-Objekte darin.
Damit die Statistiken in V$FILESTAT erzeugt werden, muss der Initialisierungsparameter timed_statistics auf true gesetzt sein.
Auch für E/A-Aktivitäten gibt es mehrere Ansätze, die Performance zu verbessern:
-
E/A-Aktivitäten ausbalancieren
-
Striping von Dateien
-
lokal verwaltete Tablespaces verwenden
-
den Wert des Parameters
db_file_multiblock_read_counterhöhen
E/A-Aktivitäten ausbalancieren
Der einfachste Weg, eine Balance der E/A-Aktivitäten zu erreichen, ist, den Richtlinien der Optimal Flexible Architecture (OFA) zu folgen und Daten und Indexe auf verschiedene Tablespaces zu verteilen. In folgender Tabelle finden Sie eine einfache Verteilungsstrategie.
|
Tablespace |
Inhalt |
|
|
Datenbankkatalog |
|
|
Standard-Tablespace für Benutzer |
|
|
Tablespace für weitere Werkzeuge und Repositories |
|
|
|
|
|
Tablespace für temporäre Segmente |
|
|
Tabellen der Applikation |
|
|
Indexe der Applikation |
Dies stellt nur eine sehr einfache Form der Aufteilung dar. Wenn mehrere Applikationen auf der Datenbank laufen, dann sollte jede Anwendung eine eigene Daten- und Index-Tablespace bekommen. Bei VLDBs und im Data-Warehouse-Umfeld ist die Aufteilung noch stärker, bis hin zu einer eigenen Tablespace pro partitionierter Tabelle.
Striping von Daten
Die Idee vom Verteilen der Dateien auf verschiedene Festplatten kann durch das Striping erweitert werden. Striping bedeutet, dass eine Tablespace über mehrere Festplatten verteilt wird. Das erhöht die Performance, da mehrere Leseköpfe gleichzeitig mit dem Lesen oder Schreiben von Daten beschäftigt sind.
In Oracle 10g wurde das Feature Automatic Storage Management (ASM) eingeführt. ASM organisiert eine dynamische Verteilung der E/A-Belastung in Abhängigkeit vom aktuellen Workload. Detaillierte Informationen zu diesem Thema finden Sie in Kapitel 4 »Die sich selbst verwaltende Datenbank
ASM benötigt eine separate Datenbank für die Verwaltung. Ob es sich lohnt, ASM einzusetzen, hängt nicht zuletzt von der Art des Workloads einer Datenbank ab. Manuelles Striping ist wichtig, wenn Sie ASM nicht einsetzen.
In folgender Abbildung sehen Sie, was Striping bewirkt. Werden Daten aus der Tabelle accounts gelesen, dann sind im günstigen Fall drei Leseköpfe mit E/A-Operationen beschäftigt. Das hat eine erhebliche Verbesserung der E/A-Performance zur Folge.
Heute werden häufig Disk-Subsysteme eingesetzt. Die Zuweisung der Festplatten erfolgt dabei über Logical Volume Manager. Auch da können Sie ein Striping vornehmen und die Tablespace-Dateien verteilen. Ein Problem ist auch, dass die Kapazitäten der Festplatten immer größer werden, was die Möglichkeiten für eine Verteilung einschränkt. Die folgenden Schritte zeigen am Beispiel der Tabelle accounts, wie ein solches Striping über mehrere Dateisysteme durchgeführt werden kann.
Striping einer Tabelle über mehrere Festplatten
1. Erstellen Sie eine Tablespace mit drei Dateien, die jeweils auf einer separaten Festplatte liegen.
SQL> CREATE TABLESPACE dat
2 DATAFILE '/u02/oradata/komp/dat01.dbf'
3 SIZE 5M;
Tablespace wurde angelegt.
SQL> ALTER TABLESPACE dat
2 ADD DATAFILE '/u03/oradata/komp/dat02.dbf'
3 SIZE 5M;
Tablespace wurde geändert.
SQL> ALTER TABLESPACE dat
2 ADD DATAFILE '/u04/oradata/komp/dat03.dbf'
3 SIZE 5M;
Tablespace wurde geändert.
2. Die Tabelle accounts wird ohne Besonderheiten erstellt unter Angabe der STORAGE-Parameter.
SQL> CREATE TABLE accounts (
2 account_id NUMBER(14),
3 account_owner NUMBER(10),
4 balance NUMBER(10,2),
5 last_balance NUMBER(10,2),
6 changed DATE)
7 STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
8 TABLESPACE dat;
Tabelle wurde angelegt.
Mit dem Anlegen der Tabelle hat Oracle das INITIAL EXTENT in der Datei /u02/oradata/komp/dat01.dbf angelegt. Normalerweise braucht sich ein Administrator nicht um das Anlegen weiterer Extents zu kümmern, da Oracle das automatisch macht, wenn die Tabelle wächst. In diesem Fall sollen jedoch die weiteren Extents nicht wahllos, sondern gezielt in den Dateien auf den anderen Festplatten angelegt werden. Mit den folgenden Anweisungen können Sie weitere Extents manuell anlegen.
SQL> ALTER TABLE accounts
2 ALLOCATE EXTENT
3 (DATAFILE '/u03/oradata/komp/dat02.dbf'
4 SIZE 1M);
Tabelle wurde geändert.
SQL> ALTER TABLE accounts
2 ALLOCATE EXTENT
3 (DATAFILE '/u04/oradata/komp/dat03.dbf'
4 SIZE 1M);
Tabelle wurde geändert.
Manuelles Striping für große Datenbanken ist sehr aufwendig. Dort bietet sich eine intelligente Verteilung der E/A-Aktivitäten über das Disk-Subsystem bzw. der Einsatz von ASM an.
Lokal verwaltete Tablespaces verwenden
Lokal verwaltete Tablespaces benutzen zur Verwaltung der Extents anstelle des Datenbankkatalogs eine Bitmap im Header einer jeden Datei. Dadurch können Extents schneller akquiriert und freigegeben werden, ohne dass auf die SYSTEM-Tablespace zugegriffen werden muss.
Lokal verwaltete Tablespaces sind in der Lage, eine große Anzahl von Extents effektiver zu verwalten und die Fragmentierung zu reduzieren.
Es hat sich jedoch gezeigt, dass für sehr große Tabellen immer noch eine bessere Performance mit vom Datenbankkatalog verwalteten Tablespaces erzielt werden kann. Falls Sie große Tabellen in lokal verwalteten Tablespaces einsetzen wollen, dann sollten Sie unbedingt die Option »Gleichförmige Zuweisung« mit großen Extents verwenden.
Den Parameter db_file_multiblock_read_count erhöhen
Dieser Initialisierungsparameter bestimmt die maximale Größe von Datenbankblöcken, die vom Server-Prozess des Benutzers gelesen werden, wenn ein Full Table Scan (FTS) ausgeführt wird. Je größer der Parameter ist, desto mehr Daten können mit einem einzigen Zugriff gelesen werden.
In Data-Warehouse- und DSS-Applikationen sollte dieser Parameter sehr hoch gesetzt werden, da Full Table Scans häufig auftreten. Für OLTP-Systeme ist er eher uninteressant, da vorwiegend Nested-Loop-Operationen mit Index-Zugriff ausgeführt werden.
Mit der Abfrage in folgendem Listing können Sie feststellen, wie viele Full Table Scans in Ihrer Datenbank ausgeführt wurden.
SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name='table scans (long tables)';
NAME VALUE
--------------------------------- --------
table scans (long tables) 121
Oracle bietet die Möglichkeit, so genannte Raw Devices zu verwenden. Auf diesen Festplatten wird kein Dateisystem angelegt. Die Oracle-Datenbank verwaltet diese Volumes selbst. Raw Devices sind eine Option der Dateiverwaltung für Real Application Clusters.
Raw Devices sind beachtlich schneller, da eine Menge an Overhead durch die Systemverwaltung wegfällt. Allerdings erreichen mittlerweile Journal File Systems (JFS) ähnliche Werte. Raw Devices haben den Nachteil, dass der Administrationsaufwand wesentlich höher ist, und zwar in allen Bereichen, bis hin zu Backup and Recovery. Denken Sie nur daran, dass Sie Dateien nicht mehr mit einem Copy-Befehl kopieren können. Für Performance-kritische Anwendungen kann es durchaus sinnvoll sein, Raw Devices zu verwenden.
Die Performance des Database-Writer-Prozesses (DBWn)
Der Database Writer ist verantwortlich für das Schreiben von Datenblöcken aus dem Buffer Cache in die Dateien der Tablespaces.
Die Views V$SYSTEM_EVENT und V$SYSSTAT liefern Informationen über die Performance des DBWn-Prozesses. Die Systemereignisse buffer busy wait und db file parallel write stellen Informationen zu Performance-Problemen zur Verfügung.
Dabei bedeutet buffer busy wait, dass auf freie Buffer im Buffer Cache gewartet werden musste. Einige dieser Ereignisse können auf uneffektive oder zu langsame Arbeit des DBWn-Prozesses beim Wegschreiben von Dirty Buffern zurückzuführen sein.
Das Ereignis db file parallel write deutet auf Probleme beim gleichzeitigen Schreiben von Blöcken hin. Die Ursache kann eine langsame Festplatte sein oder einfach die Tatsache, dass der DBWn-Prozess mit dem Schreiben nicht hinterherkommt. In folgendem Listing werden diese Ereignisse abgefragt.
SQL> SELECT event, total_waits, average_wait
2 FROM v$system_event
3 WHERE event IN
4 ('buffer busy wait','db file parallel write');
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------- ----------- ------------
db file parallel write 896 0
In diesem Beispiel sind insgesamt 896 Warte-Ereignisse für paralleles Schreiben seit dem Start der Instanz aufgetreten. Da jedoch die Wartezeiten nahe bei null liegen, gibt es keine ernsthaften Performance-Probleme, die in Zusammenhang mit dem DBWn-Prozess stehen.
Interessant ist auch das Ereignis write complete waits. Tritt es auf, dann hat der Server-Prozess darauf gewartet, dass der DBWn Blöcke aus dem Buffer Cache wegschreibt.
Hohe oder ständig steigende Werte der Ereignisse buffer busy wait oder
db file parallel write sind ein Indiz dafür, dass der Database Writer nicht effektiv arbeitet. Das hat schwerwiegende Auswirkungen auf die Performance der gesamten Datenbank.
Das View V$SYSSTAT
Weitere wichtige Statistiken für den Database Writer liefert das View V$SYSSTAT. Die Statistik redo log space requests sagt aus, dass ein Wartezustand für das Schreiben eines Redo Logs nach einem Log Switch aufgetreten ist. Da die Datenbank bei einem Log Switch einen Checkpoint ausführt, müssen beide Hintergrundprozesse, sowohl DBWn als auch LGWR, ihre Schreib-Aktivitäten beenden, bevor LGWR beginnen kann, in die nächste Online Redo Log-Datei zu schreiben.
Wenn DBWn die Datenblöcke aus dem Buffer Cache nicht schnell genug wegschreiben kann, tritt das Ereignis redo log space requests auf. Die folgende Abfrage zeigt die Statistik an.
SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name = 'redo log space requests';
NAME VALUE
------------------------------ --------
redo log space requests 12
Auch die Statistiken DBWR buffers scanned und DBWR lru scans spiegeln die Effektivität des Database Writers wider. DBWR buffers scanned liefert die Anzahl von Buffern im Buffer Cache, die untersucht wurden, um einen Dirty Buffer zu finden. Der Database Writer wird dann uneffektiv, wenn er aufgefordert wird, Dirty Buffer wegzuschreiben, und gleichzeitig die LRUListe untersucht.
In folgendem Listing wird die Anzahl der untersuchten Buffer durch die Anzahl der Abfragen der LRU-Liste geteilt. Das Ergebnis ist die Anzahl von Buffern, die bei jeder LRU-Abfrage untersucht wurden.
SQL> SELECT a.value/b.value
2 FROM v$sysstat a, v$sysstat b
3 WHERE a.name = 'DBWR buffers scanned'
4 AND b.name = 'DBWR lru scans';
A.VALUE/B.VALUE
---------------
23.2343
Auch hier stellt sich die Frage, wie der Database Writer optimiert werden kann. Es gibt zwei Initialisierungsparameter, die die Performance des Database Writers beeinflussen:
-
DBWR_IO_SLAVES -
DB_WRITER_PROCESSES
Der Parameter dbwr_io_slaves bestimmt die Anzahl von Slave-Prozessen des Database Writers, die beim Hochfahren der Instanz gestartet werden. Die Slave-Prozesse sind dem Database Writer-Prozess sehr ähnlich. Sie führen allerdings nur Schreiboperationen aus und verschieben keinen Buffer von der LRU-Liste in die Dirty-Liste.
Slave Prozesse
Das Ziel der Slave-Prozesse ist, auf Systemen, die nur synchrones E/A zulassen, einen asynchronen E/A-Modus zu simulieren. Auch der Archiver (ARCn), der Log Writer (LGWR) und der Recovery Manager können Slave-Prozesse erzeugen.
Der Standardwert für dbwr_io_slaves ist 0. Die Höchstzahl ist abhängig vom Betriebssystem.
Wenn Sie den Parameter dbwr_io_slaves auf einen Wert größer null setzen, dann werden automatisch Slave-Prozesse für Database Writer, Archiver, Log Writer und Recovery Manager gestartet. Ein einzelner Start der Slave-Prozesse ist nicht möglich.
In folgender Abbildung sehen Sie, wie der Database Writer mit seinen Slave-Prozessen asynchrones E/A simuliert.
Der Database Writer wartet bei jeder Schreibanforderung, bis das Betriebssystem die Fertigstellung signalisiert. Mit der Verwendung von Slave-Prozessen können die Schreibanforderungen parallel laufen.
Bei der Verwendung von asynchroner Ein- und Ausgabe muss der Database Writer nicht auf ein Signal vom Betriebssystem warten, sondern kann sofort den nächsten Schreibauftrag absetzen. Auf den meisten Plattformen steht asynchrones E/A nur auf Raw Devices zur Verfügung.
Slave-Prozesse helfen, die E/A-Performance zu verbessern, jedoch sind sie nicht in der Lage, alle Funktionen auszuführen. Die Verwaltung der Dirty-Liste und der LRU-Liste kann nur der Database Writer selbst durchführen.
Mit Hilfe des Initialisierungsparameters db_writer_processes können Sie zusätzliche Database Writer-Prozesse starten.
Sie können nicht gleichzeitig mehrere Database-Writer-Prozesse und Slave-Prozesse benutzen. Wenn Sie einen Wert größer null für dbwr_io_slaves angeben, hat der Wert für db_writer_processes keinen Effekt und es wird nur ein Database Writer gestartet.
Auf Systemen mit nur einer CPU ist es nicht sinnvoll, mehrere Database-Writer-Prozesse zu starten. Wenn mehrere Prozesse gegen eine CPU laufen, führt der damit verbundene Overhead eher zu einer Verschlechterung der Performance.
Segmente optimal verwalten
Seit Oracle9i existiert eine automatische Segmentverwaltung. Damit wird eine bessere Performance durch die Verwaltung über Bitmaps anstelle von Freelists erzielt. Automatische Segmentverwaltung gibt es nur für lokal verwaltete Tablespaces. Verwenden Sie lokal verwaltete Tablespaces mit automatischer Segmentverwaltung. Die anderen Optionen existieren noch aus Gründen der Abwärtskompatibilität.
Segmente wie Tabellen oder Indexe speichern ihre Daten in Blöcken. Die Standard-Blockgröße wird beim Anlegen der Datenbank festgelegt. Seit Oracle9i ist es möglich, Tablespaces mit unterschiedlichen Blockgrößen in einer Datenbank zu verwalten. Die SYSTEM-Tablespace besitzt immer die Standard-Blockgröße.
Mit der folgenden Abfrage können Sie feststellen, welche Blockgrößen die Tablespaces in Ihrer Datenbank besitzen.
SQL> SELECT tablespace_name, block_size
2 FROM dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
SYSTEM 8192
UNDOTBS1 8192
SYSAUX 8192
TEMP 8192
USERS 8192
EXAMPLE 8192
TEMP02 8192
BIG_TS 16384
Die geeignete Blockgröße für eine Datenbank ist abhängig vom Typ der Anwendung, die auf der Datenbank läuft. Das Bestimmen der richtigen Blockgröße setzt die Kenntnis der Anwendung und Erfahrung voraus.
Faustregeln zur Segment-Verwaltung
Es gibt jedoch die folgenden Faustregeln.
Für Lesezugriffe gilt:
-
Für kurze Sätze und überwiegend zufällige Lesezugriffe sind kleinere Blockgrößen besser.
-
Sind die Sätze kurz und wird überwiegend sequenziell zugegriffen, dann sind größere Blöcke besser.
-
Wenn die Sätze sehr lang sind und LOBs in großer Anzahl enthalten, dann sind größere Blöcke zu bevorzugen.
Für Schreibzugriffe gilt:
-
Für OLTP-Datenbanken mit vielen Transaktionen sollte die Blockgröße nicht zu groß gewählt werden. Im Falle eines Kompromisses ist der kleinere Wert zu bevorzugen.
-
Für ein Data Warehouse sollten eher große Blöcke verwendet werden, auch wenn das zu einer Verlangsamung im Ladeprozess führt.
Beachten Sie, dass große Blöcke die Wahrscheinlichkeit für Block Contention erhöhen und einen größeren Buffer Cache benötigen, um eine akzeptable Hit Ratio zu erreichen.
Wenn Sie sich nicht sicher sind, welche Blockgröße Sie wählen sollen, dann ist 8 Kbyte ein guter Kompromiss, der eventuell nicht optimal ist, aber andererseits auch keine Probleme verursacht.
(mha).