Oracle Datenbank-Tuning - Der Buffer Cache

Im vorigen zweiten Teil unserer Serie zum Datenbank-Tuning haben wir Ihnen den Shared Pool vorgestellte, der als Cache für eine Reihe von Informationen dient. Unter anderem haben Sie erfahren, welche Statistik-Views Ihnen zur Verfügung stehen und wie Sie die Speichergröße optimal nutzen.

In diesem Teil stellen wir Ihnen den Buffer Cache vor, in dem Oracle Tabellen, Indizies und andere relevante Daten zwischenspeichert. Wir erklären, wie der Cache arbeitet, wie Sie seine Performance messen und wie Sie mehr Leistung aus dem System herauspressen.

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-Logbuffer

Teil 5: I/O optimieren

Teil 6: Tuning automatisieren

Optimierung des Buffer Cache

Im Buffer Cache werden Datenblöcke von Segmenten gespeichert. Der Buffer Cache wird nach dem LRU-Prinzip verwaltet. Jeder Buffer hat die Größe eines Blocks. Datenblöcke können zu den folgenden Objekten gehören:

• Tabellen

• Indexen

• Cluster

• Large Objects (LOB)

• LOB-Indexen

• Rollbacksegmenten

• Temporären Segmenten

Der Buffer Cache wird gemeinsam von der LRU-Liste, einer Dirty-Liste, dem Server-Prozess des Benutzers sowie dem Database Writer (DBWn) verwaltet.

Die LRU-Liste

Auch der Buffer Cache wird nach dem LRU-Algorithmus verwaltet. Ist der Buffer Cache voll, werden Datenblöcke entfernt, die am Ende der LRU-Liste stehen. Mit diesem Prinzip bleiben die Blöcke, die zuletzt benutzt wurden, im Buffer Cache.

Wenn Daten angefordert werden, liest der Server-Prozess die Blöcke von der Festplatte und platziert sie an den Anfang der LRU-Liste. Die Buffer bleiben in der LRU-Liste, werden aber mit dem Lesen neuer Blöcke nach hinten verschoben.

Erfolgt ein Zugriff auf einen Buffer, während er in der LRU-Liste steht, dann wird dieser an den Anfang der LRU-Liste gestellt.

Der LRU-Algorithmus wird anders gehandhabt, wenn ein Full Table Scan (FTS) ausgeführt wird. Die Buffer werden sofort am Ende der LRU-Liste platziert. Damit wird verhindert, dass alle Buffer aus dem Cache entfernt werden.

Die Dirty-Liste

Es gibt die folgenden drei Buffer-Typen im Buffer Cache:

• Free. Der Buffer wird zurzeit nicht benutzt.

• Pinned. Der Buffer wird momentan von einem Server-Prozess benutzt.

• Dirty. Der Buffer wird zurzeit nicht benutzt und wurde noch nicht vom DBWn auf die Festplatte geschrieben.

Der Server-Prozess kann Free Buffer einfach überschreiben, wenn Blöcke von der Festplatte gelesen werden. Dirty Buffer können nicht überschrieben werden, bevor sie auf die Festplatte geschrieben wurden.

Dieser Mechanismus wird Dirty-Liste genannt. Die Liste registriert alle Blöcke, die verändert und nicht auf die Festplatte geschrieben wurden. Das Schreiben der Buffer auf die Festplatte übernimmt der Hintergrundprozess Database Writer (DBWn).

Server-Prozesse der Benutzer

Wenn der Server-Prozess eines Benutzers die Daten im Buffer Cache nicht vorfindet, dann liest er die Datenblöcke von der Festplatte und schreibt sie in den Buffer Cache.

Bevor die Blöcke von der Platte gelesen werden können, müssen freie Blöcke im Buffer Cache gefunden werden. Der Server-Prozess sucht einen freien Block unter Benutzung der LRU-Liste und der Dirty-Liste in der folgenden Art und Weise:

• Während der Server-Prozess die LRU-Liste nach freien Blöcken durchsucht, werden alle Dirty Buffer in die Dirty-Liste verschoben.

• Die Dirty-Liste wird dadurch immer größer. Wenn der Schwellenwert erreicht wird, schreibt der Database Writer Dirty Buffer auf die Festplatte.

• Der Database Writer kann auch dann Dirty Buffer auf die Festplatte schreiben, wenn der Schwellenwert noch nicht erreicht ist. Das passiert dann, wenn der Server-Prozess zu viele Buffer untersucht hat, ohne einen freien zu finden. In diesem Fall schreibt DWBn Dirty Buffer direkt von der LRU-Liste auf die Platte.

Wenn ein Server-Prozess einen Buffer im Cache findet, kann dieser Daten enthalten, die nicht durch ein COMMIT abgeschlossen wurden. Oracle gestattet jedoch nicht, dass ein anderer Benutzer diese Daten sieht. Die Dateninhalte vor der Änderung befinden sich im Rollback- oder Undo-Segment. Diese Segmente werden auch im Buffer Cache gespeichert.

Der Database Writer (DBWn)

Es gibt eine Reihe von Ereignissen, die den Database Writer zwingen, Buffer auf die Festplatte zu schreiben. Die Liste der Ereignisse finden Sie in folgender Tabelle:

Ereignisse, die den Database Writer anstoßen

Ereignis	Aktion des DBWn
Die Dirty-Liste erreicht ihren Schwellenwert.	DBWn schreibt Buffer aus der Dirty-Liste weg.
Die LRU-Liste wurde zu lange durchsucht, ohne einen freien Buffer zu finden.	DBWn schreibt Buffer direkt aus der LRUListe weg.
Alle drei Sekunden	DBWn schreibt Dirty Buffer von der LRUListe in die Dirty-Liste. Wird der Schwellenwert erreicht, dann werden diese Buffer auf die Festplatte geschrieben.
Checkpoint	DBWn verschiebt alle Dirty Buffer von der LRU-Liste in die Dirty-Liste und schreibt sie weg.
Tablespace im Backup-Modus	DBWn verschiebt alle Dirty Buffer der Tablespace von der LRU-Liste in die Dirty-Liste und schreibt sie weg.
Tablespace Offline Temporary	DBWn verschiebt Dirty Buffer dieser Tablespace von der LRU-Liste in die Dirty-Liste und schreibt sie weg.
Drop Segment	DBWn schreibt die Dirty Buffer dieses Segments vorher auf die Festplatte.

Die Performance des Buffer Cache messen

Der wichtigste Indikator für die Performance des Buffer Cache ist die Hit Ratio. Eine große Hit Ratio bedeutet, dass die Benutzerprozesse Daten überwiegend im Buffer Cache finden und nur selten auf die Festplatte zugreifen.

Ein Cache Miss tritt dann auf, wenn der Server-Prozess eines Benutzers die benötigten Buffer nicht im Buffer Cache findet und diese von der Festplatte lesen muss. Das View V$SYSSTAT speichert drei Statistiken, die zur Bestimmung der Hit Ratio herangezogen werden können:

• Physical Reads ist die Anzahl von Datenblöcken, die seit dem letzten Start der Instanz von der Festplatte in den Buffer Cache gelesen wurden.

• DB Block Gets ist die Anzahl aller Anforderungen für Daten, die direkt aus den Segmenten mit Ausnahme von Rollback- und Undo-Segmenten gelesen wurden.

• Consistent Gets ist die Anzahl aller Anforderungen für Daten, die aus Gründen der Lesekonsistenz aus Rollback- und Undo-Segmenten bedient wurden.

Aus diesen Werten kann die Hit Ratio bestimmt werden. Die Abfrage in folgender Tabelle ermittelt das Verhältnis zwischen Zugriffen auf den Buffer Cache und Zugriffen auf die Festplatte.

SQL> SELECT 1-(a.value / (b.value + c.value))
2 "Hit Ratio"
3 FROM v$sysstat a, v$sysstat b, v$sysstat c
4 WHERE a.name = 'physical reads'
5 AND b.name = 'db block gets'
6 AND c.name = 'consistent gets';
Hit Ratio
----------
,990704986

Eine gut optimierte OTLP-Datenbank sollte eine Buffer Cache Hit Ratio von mehr als 90 % aufweisen.

Hit Ratio des Buffer Cache

Für den Optimierungsprozess ist es wichtig, die Hit Ratio des Buffer Cache für individuelle Benutzer zu kennen. Es ist durchaus möglich, dass die allgemeine Performance der Datenbank in Ordnung ist, aber einzelne Benutzer Probleme berichten. Die Abfrage in folgendem Listing liefert die Hit Ratio pro Benutzer.

SQL> SELECT b.username, b.osuser,
2 (1-a.physical_reads / (a.block_gets+a.consistent_gets)) "Hit Ratio"
3 FROM v$sess_io a, v$session b
4 WHERE a.sid = b.sid
5 AND (a.block_gets + a.consistent_gets) != 0
6 AND b.username IS NOT NULL;
USERNAME OSUSER Hit Ratio
---------- ----------- ----------
DBSNMP oracle ,986853637
SYS oracle ,983943803
SYS Administrator 1
DBSNMP oracle ,998837278
SYS oracle ,953183521

Es gibt eine Reihe von Statistiken über Cash Misses. Mit deren Hilfe lässt sich analysieren, wie effektiv der LRU-Algorithmus arbeitet. Sie können die folgenden Werte abfragen:

• Free Buffer Inspected – Anzahl der untersuchten Buffer, bevor ein freier gefunden wurde.

• Free Buffer Waits – Anzahl von Warte-Ereignissen, während freie Buffer gesucht wurden.

• Buffer Busy Waits – Anzahl von Warte-Ereignissen, bis ein freier Buffer zur Verfügung stand.

Eine große oder ständig wachsende Anzahl von diesen Werten zeigt an, dass die Server-Prozesse zu viel Zeit verbringen, um freie Buffer im Buffer Cache zu finden. Mit Hilfe der Abfrage in folgendem Listing können Sie diese Statistikwerte abfragen.

SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name IN
4 ('free buffer inspected',
'free buffer waits','buffer busy waits');
NAME VALUE
----------------------------------- ----------
free buffer inspected 2343
buffer busy waits 1287
free buffer waits 1343

Verwenden Sie die Abfrage in folgendem Listing, um festzustellen, welche Objekte momentan wie viele Blöcke im Buffer Cache belegen.

SQL> SELECT object_name, object_type,
count(*) "Anz. Buffer"
2 FROM x$bh a, dba_objects b
3 WHERE a.obj = b.object_id
4 AND owner NOT IN ('SYS','SYSTEM')
5 GROUP BY object_name, object_type
6 ORDER BY 3 DESC;
OBJECT_NAME OBJECT_TYPE Anz. Buffer
-------------------------- ------------ -----------
SMP_VTM_DISPLAY_STATE TABLE 8
SMP_VDG_NODE_LIST TABLE 7
SMP_VDD_OPERATIONS_TABLE TABLE 7
XDB$CONFIG TABLE 6
AQ$_QS_ES_ORDERS_MQTAB_S TABLE 6
AQ$_QS_OS_ORDERS_MQTAB_S TABLE 6
AQ$_QS_CS_ORDER_STATUS_QT_S TABLE 6
AQ$_QS_OS_ORDERS_PR_MQTAB_S TABLE 6

Das ultimative Ziel zur Verbesserung der Performance lautet für den Buffer Cache, die Hit Ratio zu erhöhen. Das kann mit den folgenden Strategien erreicht werden:

• den Buffer Cache vergrößern

• verschiedene Buffer Pools verwenden

• Tabellen im Buffer Cache festnageln

• Indexe sinnvoll einsetzen

Den Buffer Cache vergrößern

Der einfachste Weg zur Verbesserung der Performance ist, den Buffer Cache zu vergrößern. Je größer der Buffer Cache ist, desto geringer ist die Wahrscheinlichkeit, dass Buffer durch den LRU-Mechanismus aus dem Buffer Cache entfernt werden.

Eine Vergrößerung des Buffer Cache erhöht nicht nur die Hit Ratio, auch die Anzahl von Warte-Ereignissen wie buffer busy waits oder free buffer waits geht zurück.

Die Hit Ratio verändert sich nicht linear zur Erhöhung der Buffer-Cache-Größe. Eine Verdoppelung des Buffer Cache führt nicht zu einer Verdoppelung der Hit Ratio.

Verschiedene Buffer Pools verwenden

Von vornherein benutzen alle Segmente denselben Buffer Pool, den so genannten Default Pool. Der Default Pool besitzt die Standard-Blockgröße der Datenbank. Ein Pool mit abweichender Blockgröße wird als Subcache bezeichnet.

Ein Subcache wird nach den Mechanismen des Default Pools verwaltet. Oracle unterscheidet die folgenden drei Pool-Typen, die unterschiedlich verwaltet werden:

• Keep Pool – Für Segmente, die permanent im Buffer Cache bleiben sollen

• Recycle Pool – Für Segmente, die nur kurzfristig im Cache behalten werden sollen

• Default Pool – Standard-Pool für alle Objekte mit Standard-Blockgröße, für die weder KEEP noch RECYCLE spezifiziert wurde

Als Administrator müssen Sie festlegen, welcher Pool für welche Segmente geeignet ist. Dazu sollten Sie die Anwendung und das Datenmodell kennen.

Datenbankobjekte des Buffer Pool

Nützliche Informationen liefert auch das View V$BH. Es wurde für den Parallel Server entwickelt, steht aber auch für Single Instance zur Verfügung. Die Abfrage in folgendem Listing gibt alle Datenbankobjekte zurück, die sich im Buffer Pool befinden. Die Liste ist absteigend nach der Buffer-Anzahl sortiert.

SQL> SELECT a.owner, a.object_name, a.object_type,
2 COUNT(*)
3 FROM dba_objects a, v$bh b
4 WHERE a.object_id = b.objd
5 AND owner NOT IN ('SYS','SYSTEM')
6 GROUP BY a.owner, a.object_name, a.object_type
7 HAVING COUNT(*) > 2
8 ORDER BY 4 DESC;
OWNER OBJECT_NAME OBJECT_TYPE COU
--------- --------------------- ------------- ---
DBSNMP MGMT_RESPONSE_CAPTURE TABLE 11
DBSNMP MGMT_RESPONSE_CONFIG TABLE 6
DBSNMP MGMT_RESPONSE_BASELINE TABLE 6

Nachdem Sie festgelegt haben, welche Objekte in welchem Pool gespeichert werden sollen, gilt es zu bestimmen, wie groß die einzelnen Pools sein sollen. Die aktuelle Größe einer Tabelle kann mit der folgenden Abfrage ermittelt werden.

SQL> SELECT owner, table_name, blocks
2 FROM dba_tables
3 WHERE owner = 'SH';
OWNER TABLE_NAME BLOCKS
------------ ----------------------- ------
SH SALES 1768
SH COSTS 206
SH TIMES 59
SH PRODUCTS 5
SH CHANNELS 4

Die Statistiken in den Views DBA_TABLES und DBA_INDEXES werden nur bereitgestellt, wenn die Objekte analysiert sind. Analysieren Sie deshalb die Tabellen und Indexe. Verwenden Sie für das Erstellen von Statistiken das Paket DBMS_STATS.

Tablespaces mit unterschiedlichen Blockgrößen

Seit Oracle9i ist es möglich, Tablespaces mit unterschiedlichen Blockgrößen in einer Datenbank zu verwenden. Vom Datenbankstandard abweichende Blockgrößen benötigen einen separaten Buffer Pool. In folgender Tabelle finden Sie eine Übersicht der Initialisierungsparameter zum Einstellen der Größen der Buffer Pools.

Initialisierungsparameter für Buffer Pools

Parameter	Bedeutung
DB_CACHE_SIZE	Größe des Default Buffer Pools in Byte. »K«, »M«, »G« kann verwendet werden für Kilo-, Mega- und Gigabyte.
DB_CACHE_nK_SIZE	Größe der Subcaches für andere Blockgrößen. »n« ist die Blockgröße und kann die Werte 2, 4, 8, 16, 32 annehmen. Für die Standard-Blockgröße der Datenbank kann kein Subcache eingerichtet werden, hierfür ist DB_CACHE_SIZE zu verwenden.
DB_KEEP_CACHE_SIZE	Größe des Keep Pools in Byte, Kilo-, Mega- oder Gigabyte. Der Keep Pool kann nur für die Standard-Blockgröße verwendet werden.
DB_RECYCLE_CACHE _SIZE	Größe des Recycle Pools in Byte, Kilo-, Mega- oder Gigabyte. Der Recycle Pool kann nur für die Standard-Blockgröße verwendet werden.

Die Parameter aus vorhergehender Tabelle können dynamisch geändert werden, das heißt, ein Neustart der Instanz ist nicht erforderlich. Folgendes Listing zeigt ein Beispiel.

SQL> SHOW PARAMETER CACHE;
NAME TYPE VALUE
------------------- ----------- -----
db_cache_size big integer 33554432
db_keep_cache_size big integer 16777216
db_recycle_cache_size big integer 0
db_16k_cache_size big integer 16777216
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
...
SQL> ALTER SYSTEM SET db_keep_cache_size=10M;
System wurde geändert.
SQL> ALTER SYSTEM SET db_recycle_cache_size=20M;
System wurde geändert.

Objekte den Buffer Pools zuordnen

Mit einer ALTER TABLE- oder einer ALTER INDEX-Anweisung können Sie, so wie in folgendem Listing, ein Objekt einem Buffer Pool zuordnen.

SQL> ALTER TABLE dwh.household_facts
2 STORAGE (BUFFER_POOL RECYCLE);
Tabelle wurde geändert.
SQL> ALTER TABLE dwh.time_dim
2 STORAGE (BUFFER_POOL KEEP);
Tabelle wurde geändert.

Mit der Abfrage in dem nächsten Listing können Sie feststellen, welches Objekt sich in welchem Pool befindet. Sie können die Pool-Zuweisungen jederzeit dynamisch verändern.

SQL> SELECT segment_name, segment_type, buffer_pool
2 FROM dba_segments
3 WHERE owner = 'DWH';
SEGMENT_NAME SEGMENT_ BUFFER_
-------------------- -------- -------
HOUSEHOLD_FACTS TABLE RECYCLE
ACCOUNT_DIM TABLE DEFAULT
BRANCH_DIM TABLE DEFAULT
HOUSEHOLD_DIM TABLE DEFAULT
PRODUCT_DIM TABLE DEFAULT
STATUS_DIM TABLE DEFAULT
TIME_DIM TABLE KEEP
ACCOUNTS_PART TABLE PA DEFAULT

Die Buffer bleiben nach Ausführung der ALTER TABLE-Anweisung zunächst im ursprünglichen Pool. Erst wenn die Blöcke das nächste Mal von der Festplatte gelesen werden, gelangen sie in den neuen Pool.

Die Performance der Buffer Pools kann mit Hilfe des Views V$BUFFER_POOL_STATISTICS überwacht werden. Die Abfrage in folgendem Listing zeigt die Hit Ratio pro Pool.

SQL> SELECT name, block_size,
2 DECODE(db_block_gets + consistent_gets,
3 0,0,1-(physical_reads / (db_block_gets +
consistent_gets))) "Hit Ratio"
4 FROM v$buffer_pool_statistics;
NAME BLOCK_SIZE Hit Ratio
-------------------- ---------- ----------
KEEP 8192 ,958333333
RECYCLE 8192 ,571428571
DEFAULT 8192 ,904970208
DEFAULT 16384 1

Wahrscheinlich haben Sie damit gerechnet, dass der Keep Pool näher bei 100 % liegt. Beachten Sie, dass es sich um eine kumulative Statistik seit dem letzten Start der Instanz handelt und die Datenblöcke mindestens einmal von der Festplatte gelesen werden müssen. Außerdem wird auch der Keep Pool nach dem LRU-Prinzip verwaltet.

Tabellen mit Buffer Pools fest verbinden

Unabhängig davon, in welchem Pool Sie ein Objekt platzieren, wird es mit einem LRU-Mechanismus verwaltet. Normalerweise werden die Buffer an den Anfang der LRU-Liste gestellt. Im Fall eines Full Table Scan werden die Buffer jedoch ans Ende der LRU-Liste platziert.

Das kann zu Optimierungsproblemen führen. Für kleine Tabellen, wie z.B. Lookup-Tabellen, plant der Cost-Based Optimizer in der Regel einen Full Table Scan. Diese Tabellen fallen dann relativ schnell wieder aus dem Buffer Pool heraus, obwohl es sinnvoller wäre, sie möglichst lange zu behalten.

Ein Weg, diesem Problem zu begegnen, ist die Benutzung von Cache Tables. Datenblöcke dieser Tabellen werden in jedem Fall an den Anfang der LRUListe gestellt. Es gibt die folgenden Möglichkeiten, Cache Tables zu implementieren:

• während die Tabelle erstellt wird

• mit einem ALTER TABLE-Befehl

• durch Optimizer Hints

Fügen Sie einfach die CACHE-Option in der CREATE TABLE-Anweisung hinzu, um eine Cache Table zu erstellen.

SQL> CREATE TABLE cache_test (
2 id NUMBER) CACHE;
Tabelle wurde angelegt.

Mit dem ALTER TABLE-Befehl können Sie die CACHE-Option ein- und ausschalten.

SQL> ALTER TABLE cache_test NOCACHE;
Tabelle wurde geändert.
SQL> ALTER TABLE cache_test CACHE;
Tabelle wurde geändert.

Auch mit einem Optimizer Hint können Sie eine Tabelle als Cache Table behandeln. Allerdings wird hierbei die Tabelle nicht permanent geändert, die CACHE-Option gilt nur für die Anweisung.

SQL> SELECT /*+ CACHE */ * FROM cache_test;

Mit der folgenden Anweisung können Sie alle Cache Tables identifizieren.

SQL> SELECT owner, table_name
2 FROM dba_tables
3 WHERE LTRIM(cache) = 'Y';
OWNER TABLE_NAME
------------ ---------------
SYS CACHE_TEST

Indexe sinnvoll einsetzen

Den wohl größten Einfluss auf die Performance des Buffer Cache haben Indexe. Wenn durch Verwendung eines Index der Explain-Plan so geändert wird, dass ein Full Table Scan vermieden wird, kommt es zwangsläufig zu viel weniger Leseoperationen von der Festplatte. (mha)