Oracle Database: Buffer Cache Tuning
Memory-Tuning-Methoden sind in Oracle Database meist effektiv, unabhängig von der Lizenzoption und transparent gegenüber der Anwendung umzusetzen. Dieser Artikel beschreibt die Möglichkeiten, die Buffer Cache-Nutzung zu optimieren. Für Oracle Database Versionen vor 12.1 können unterschiedliche Buffer Pool-Konfigurationen verwendet werden. Diese sind auch in Standard-Editionen verfügbar. Ab der Version 12.1 steht mit Automatic Big Table Caching eine weitere Variante zur besseren Nutzung des Buffer Caches zur Verfügung. Zusätzlich, falls die Größe des Buffer Caches die Datenbankgröße überschreitet, kann mit der Eigenschaft Full Force Database Caching die gesamte Datenbank im Buffer Cache gehalten werden.
Interessant kann die Option Automatic Big Table Caching oder Force Full Database Caching bei Kunden werden, die nicht über eine Enterprise-Edition verfügen, die aber den Arbeitsspeicher ausreichend vergrößern können. Der Artikel skizziert, welches Feature für welche Situation geeignet ist, wo sie sich unterscheiden oder auch zusammen kombiniert werden könnten.
LRU-Algorithmus und Full Table Scans
Um die Konfiguration des Buffer Caches effizient verändern zu können, ist es hilfreich, die Funktionsweise und Nutzung des Buffer Caches genauer zu kennen. Der LRU-Algorithmus spielt dabei eine zentrale Rolle.
LRU-Algorithmus
Oracle hat mit Version 7 den Least-Recently-Used (LRU)-Algorithmus eingeführt. Je häufiger ein Block referenziert wird, desto länger wird er im Buffer Cache gehalten und wird am Kopf der LRU-Liste platziert (wird auch Most-Recently-Used = MRU genannt). Erfolgt nach längerer Zeit kein Datenzugriff mehr auf einen Block, gelangt der Block zunächst an das Ende der LRU-Liste und verschwindet dann nach kurzer Zeit aus dem Buffer Cache vollständig (aged out). Der LRU-Algorithmus hat sich bewährt, da häufig gelesene Blöcke länger im Buffer Cache bleiben.
Verhalten bei Full Table Scans
Wenn ein Full Table Scan stattfindet, werden die Blöcke der Tabelle nicht am MRU-Ende oder in der Mitte zwischen MRU und LRU plaziert, sondern am Ende der LRU. Durch die Positionierung an das Ende der LRU gelangen die Blöcke frühzeitiger aus dem Cache heraus,
da davon ausgegangen wird, dass beim Full Table Scan nicht alle gelesenen Blöcke auch für längere Zeit häufiger benötigt werden.
Die CACHE-Klausel
Dieses Verhalten kann pro Tabelle beeinflusst werden durch die Verwendung der CACHE-Klausel (Storage clause) bei der Erstellung oder Änderung einer Tabelle. Mit der CACHE-Klausel werden die Datenblöcke bei einem Full Table Scan am MRU-Ende platziert, damit sie länger im Cache bleiben können. Die Zeitdauer bis zum Verlassen des Caches kann dadurch verlängert werden, wenn die Blöcke sich am MRU-Ende befinden.
Diese so markierten Tabellen bleiben nicht länger als andere Tabellen im Buffer Cache, wie die Bezeichnung "Cache" suggeriert. Die Empfehlung von Oracle lautet, dass besonders kleine lookup-Tabellen dafür geeignet seien [1].
Full Table Scans bei größeren Tabellen
Wenn die Anzahl der Blöcke einer Tabelle aber größer ist als die Größe des gesamten Buffer Caches, würde ein Full Table Scan damit fast alle häufiger gelesenen Blöcke aus dem Buffer Cache verdrängen.
Daher liest Oracle eine Tabelle nur dann komplett in den Buffer Cache, wenn die Anzahl der Blöcke nur einen kleinen prozentualen Anteil vom Buffer Cache beträgt (1-2 Prozent). Übersteigt die Blockanzahl diesen Schwellenwert, so beurteilt Oracle bei mittelgroßen Tabellen die Referenzierung aller Blöcke komplett im Buffer Cache bei einem Full Table Scan durch Betrachtung des letzten Zeitpunktes des Full Table Scans sowie der letzten Blockentfernung aus dem Buffer Cache (aging timestamp) und den frei verfügbaren Platz im Buffer Cache [2].
Direct Reads
In diesem Abschnitt werden Lesevorgänge nur im Serial Mode betrachtet. Seit der Version 11.1 werden je nach Tabellen- und Buffer Cache-Größe Full Table Scans im Direct Read Mode von Festplatte gelesen, wobei Direct Read bedeutet, dass der Buffer Cache umgangen wird. Die Blöcke der Tabelle werden von der Festplatte direkt zur Session und damit in das PGA (Progam Global Area) übertragen.
Entscheidet sich Oracle doch dafür, dass bei einem Full Table Scan die Blöcke in den Buffer Cache plaziert werden, so gelangen die Blöcke aber an das LRU-Ende, damit die häufiger gelesenen Blöcke nicht beeinflusst werden. Wie können aber nun andere Sessions bei genügend vorhandenen Memory auch bei Full Table Scans davon profitieren, dass die Segmente doch im Buffer Cache gespeichert werden und somit Plattenzugriffe gespart werden könnten?
Unterteilung des Buffer Caches
Keep Buffer Pool
Damit (soweit möglich) alle Blöcke der Tabelle in den Buffer Cache gelangen und auch länger bleiben, kann der Default Buffer Cache mit der Konfiguration und Nutzung eines Keep Pools unterteilt werden. Zunächst ist es notwendig, die Größe des Keep Buffer Pool in Bytes (K, M, G) zu spezifizieren.
Dies geschieht mit dem Parameter Db_Keep_Cache_Size. Anschließend können Tabellen/Indexe erstellt und modifiziert werden, wobei das Objekt dann mit der "Storage (Buffer_pool Keep)"-Klausel dem Keep Buffer Pool zugeordnet wird.
/* Innerhalb des Buffer Caches (Db_Cache_Size) wird ein Teil für den Keep Buffer Pool verwendet */ SQL> Alter System Set Db_Keep_Cache_Size=2G; SQL> Alter Table Tab1 Storage (Buffer_Pool Keep); SQL> Create Table Tab2 (Col1 Date,...) Tablespace Tools Storage (Buffer_Pool Keep);
Werden Tabellen und Indexe dem Keep Buffer Pool zugeordnet, die insgesamt weitaus größer sind als die spezifizierte Pool-Größe (Db_Keep_Cache_Size), gilt auch für den Keep Buffer Pool der bereits erwähnte LRU-Algorithmus (s. o.).
Recycle Buffer Pool
Der Buffer Cache kann neben dem Keep Buffer Pool noch in einen weiteren Pool-Abschnitt unterteilt werden, den Recycle Buffer Pool (Db_Recycle_Cache_Size). Dort können Segmente gespeichert werden, die nicht dem LRU-Algorithmus vom Default Buffer Cache unterworfen werden sollen. Alle Objekte im Recycle Buffer Pool werden schnellstmöglich "ausgestoßen" (aged out).
Management des Keep Pools
Daraus folgen einige Fragen, die im täglichen Datenbankbetrieb auftreten, um die Komponenten des Buffer Caches möglichst effizient zu verwalten:
- Wird der Keep Buffer Pool genutzt?
- Welche Objekte sollten dem Keep Buffer Pool zugeordnet werden?
- Wer oder welcher organisatorische Prozess sorgt dafür, dass nicht mehr verwendete Tabellen/Indexe nicht mehr dem Keep Buffer Pool zugeordnet sind?
- Ist ein Monitoring für die Nutzung eines Keep Buffer Pools nötig?
- Was sind die bestmöglichen Größen eines Keep Buffer Pools?
Monitoring Buffer Pool
Es stehen insbesondere für die Verwendung der Buffer Pools die beiden Dictionary-Views V$Buffer_Pool und V$Buffer_Pool_Statistics zur Verfügung. Die Definitionen der Buffer Pools sind dort nachvollziehbar. Der View V$Db_Cache_Advice zeigt Informationen über Nutzung oder Verbesserungsmöglichkeiten aller Komponenten des Buffer Caches.
Die Data-Dictionary-Views User/Dba_Tables und User/Dba_Indexes beinhalten den Namen der Buffer Pools, zu denen die Objekte zugeordnet sind.
SQL> select table_name,num_rows,blocks,buffer_pool ,partitioned from dba_tables where buffer_pool <> 'DEFAULT' order by 2 desc; TABLE_NAME NUM_ROWS BLOCKS BUFFER_POOL PARTITIONED ------------------------------ ---------- ---------- ----------- ----------- MATERIAL_VEHICLEBOM_ARTICLE_1 28884068 1413066 RECYCLE NO MATERIAL_VEHICLEBOM_ARTICLE_2 28850298 1411788 RECYCLE NO MENGENEINHEIT 6375778 81978 KEEP NO DOKUZUSTXTDRU 2103057 22596 KEEP NO DOKZUSATZTXT 1977079 35603 KEEP NO ARTLIEF 1898398 29083 KEEP NO
Die dynamische Nutzung des Buffer Pools und mit welchen Objekten und welchen Segmentgrößen der Buffer Cache belegt ist, muss im Zusammenhang von V$BH (Block-Header) und den Objektinformationen (DBA_TABLES.BUFFER_POOL) ermittelt werden. Im AWR-oder Statspack-Report gibt es lediglich die Informationen, dass Komponenten des Buffer Pools definiert wurden. Im Buffer Pool-Advisory-Abschnitt erscheint eine Abschätzung über eine Verbesserung oder Verschlechterung durch Vergrößerung oder Verkleinerung der jeweiligen Buffer Pool-Komponente.
Das folgende Beispiel zeigt einen Default Buffer Cache mit ca. 12 GB Allokierung an (Linke Spalte = D) und weitere Eintragungen für den Keep Buffer Pool (linke Spalte = K) zeigen die aktuelle Größe von 256 MB für den Keep Pool an.
Aus dem Buffer Pool Advisory-Beispiel ist ersichtlich, dass bei Verdoppelung des Default Buffer Caches auf ca. 24 GB die Anzahl der Plattenzugriffe (Phy Reads) von 270.288 auf 177.431 verringert werden könnte (ca. 34% Reduktion). Würde der Keep Buffer Pool auf 128 MB halbiert werden, ist mit einer fast vierfachen Anzahl von Plattenzugriffen im Vergleich zum Keep Pool von 256 MB zu rechnen.
Automatic Big Table Caching
Damit die Blöcke in den Buffer Cache gelangen und (bei genügend Speicherplatz) für weitere Sessions ohne zusätzliche I/O-Operationen nutzbar und ohne Kenntnisse darüber, welche Objekte davon betroffen sind, auch länger dort bleiben, hat Oracle seit der Version 12.1 den "Automatic Big Table Cache" eingeführt. Es ist nicht möglich, spezielle Tabellen dem Automatic Big Table Cache zuzuordnen, wie es bisher für die Verwendung des Keep Pools notwendig war.
Die Speicherung im Cache und die Verweildauer im Buffer Cache aufgrund der Popularität der Tabelle wird nicht mehr aufgrund Anzahl von Lesezugriffen einzelner Blöcke angewendet, sondern es entscheidet die Anzahl von Lesezugriffen der Tabelle insgesamt über die Aufbewahrungszeit im Buffer Cache. Der Automatic Big Table Cache kann mit dem neuen Init-Ora-Parameter DB_BIG_TABLE_CACHE_PERCENT_TARGET dynamisch spezifiziert werden. Wird dieser Parameter größer 0 gesetzt (90 ist der maximale Wert), ist es möglich, dass Tabellen mit Full Table Scan vollständig im Buffer Cache bleiben. Dieser Bereich wird Big Table Cache genannt und nimmt den prozentualen Anteil maximal vom Buffer Cache wahr, der mit diesen Parameter angegeben wird [3].
Verwendung des Automatic Big Table Cache
Das folgende Beispiel zeigt, dass 30 Prozent vom Buffer Cache für den Big Table Cache verwendet werden sollen. Sobald Full Table Scans stattgefunden haben, enthält der View V$Bt_Scan_Cache Informationen darüber, wieviele Tabellen (Object_Count) und Blöcke (Memory_Buf_Alloc) im Big Table Cache gespeichert sind.
SQL> ALTER SYSTEM SET db_big_table_cache_percent_target=30; SQL> select * from v$bt_scan_cache; BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID -------------- --------------- ------------ ---------------- --------------- -------- .299713124 30 4 52519 1000 0
Wenn nach Start-Up der Datenbank noch kein Full Table Scan stattgefunden hat, wird immer gelten:
Bt_Cache_alloc < Bt_Cache_Target.
Wird die Größe des Buffer Caches (Db_Cache_Size) stark verändert, erfolgt durch die Angabe des Parameters Db_Big_Table_Cache_Percent_Target eine dynamische Anpassung innerhalb des Buffer Caches.
Monitoring Automatic Big Table Cache
Bei der Verwendung des Keep Buffer Pools war es recht mühsam, herauszufinden, welche Tabelle mit wie vielen Blöcken im Keep Buffer Pool geladen wird und wieviel Speicherplatz im Buffer Pool eventuell noch ungenutzt ist oder um wieviel Bytes der Pool überallokiert ist.
Wie häufig die Tabellen, die dem Keep Pool zugeordnet sind, gelesen werden, ist nicht erkenntlich.
Der View V$Bt_Scan_Obj_Temps gibt für jedes Objekt im Big Table Cache übersichtlich die notwendigen Informationen. Damit fällt es wesentlich leichter, die Nutzung des Big Table Cache im Buffer Cache zu beobachten. V$Bt_Scan_Obj_Temps enthält die Objekt_Id der Tabelle, auf der mindestens ein Full Table Scan stattgefunden hat. In Feld Temperature wird ersichtlich, wie häufig die Tabelle referenziert wurde (jeder Zugriff erhöht die Temperatur um 1000). Weitere Informationen beinhalten die Anzahl gelesener Blöcke, die Gesamtgröße der Tabelle und ob die Tabelle vollständig im Cache vorhanden ist (MEM_ONLY) oder nur teilweise (MEM_PART) oder überhaupt nicht mehr im Cache vorhanden ist (DISK):
SQL> SELECT o.object_name, cached_in_mem, size_in_blks, policy, temperature FROM v$bt_scan_obj_temps bt, dba_objects o WHERE bt.dataobj#=o.object_id; OBJECT_NAME CACHED_IN_MEM SIZE_IN_BLKS POLICY TEMPERATURE -------------------------------- ------------- ------------ ---------- ----------- TAB_1G 115800 129085 MEM_PART 3000 TAB_100M 12843 12843 MEM_ONLY 4000 TAB_500M 0 65026 DISK 2000
Die Tabelle TAB_500M aus obiger Liste gelangt erst dann wieder in der Big Table Cache, wenn die Temperatur 3000 von Tabelle TAB_1G überschritten wird. Daher sind also mindestens noch zwei weitere SELECT-Statements auf TAB_500M notwendig.
Force Full Database Caching
Übersteigt die verfügbare Memory-Größe die Datenbankgröße (gemessen an Dba_Segments), kann eine weitere Option von der Version 12.1 genutzt werden: Force Full Database Caching. Um die Datenbank in den Force Full Database Caching Mode zu setzen, ist ein Re-start der Datenbank-Instanz erforderlich [4].
SQL> Startup Mount; SQL> Alter Database Force Full Database Caching; SQL> Alter Database Open; SQL> Select Force_Full_Db_Caching From V$Databbase; FORCE_FULL_DB_CACHING ---------------------- YES
Oracle geht bei dieser Einstellung davon aus, dass jedes gelesene Objekt im Buffer Cache "Platz" findet. Damit wird bei jedem Full Table Scan die gesamt Tabelle im Buffer Cache bearbeitet. Ist jedoch der Buffer Cache doch kleiner als die Datenbank, erfolgt eine Meldung im Alert-Logfile:
...
Buffer Cache Force Full DB Caching mode on when DB does not fit in cache. Turning off Force Full DB Caching advisable
...
Ist der Buffer Cache bereits vollständig mit Objekten gefüllt und wird eine weitere Tabelle neu eingelesen, werden Blöcke der Tabelle aus dem Buffer Cache wieder mit Anwendung des LRU-Algorithmus verschwinden (s. o.).
Fazit
Mit Oracle Database 12.1 stehen grundsätzlich drei Methoden zur Verfügung, bei genügend Arbeitsspeicher weitere Vorteile des Default Buffer Caches nutzen zu können:
- Keep Buffer Pool
- Automatic Big Table Caching
- Force Full Database Caching
Im Allgemeinen geht es bei der erweiterten Nutzung des Buffer Caches darum, mit Full Table Scan gelesene Tabellen aufgrund des großen Arbeitsspeichers doch allen Sessions über den Buffer Cache zur Verfügung zu stellen, ohne I/O-Operationen von der Festplatte erneut ausführen zu müssen.
Bei der Standard Memory-Konfiguration führen Full Table Scans dazu, dass Tabellen entweder mit Direct Read den Buffer Cache aussparen oder bei Nutzung des Buffer Caches nicht alle Blöcke per Default im Buffer Cache gespeichert werden können.
Der Keep Buffer Pool ist relativ statisch und kann aber für Objekte angewandt werden, deren Speicherung im Buffer Cache wichtig ist, aber die Anzahl der Zugriffe für die Tabelle derart gering ist, dass die Blöcke der Tabelle aus dem Default Buffer Cache entfernt würden.
Beim Automatic Big Table Cache wird die am häufigsten geladene Tabellen vollständig im Big Table Cache gespeichert, falls genügend Platz vorhanden ist. Eine Festlegung auf Tabellen ist nicht notwendig, während bei Nutzung des Keep Pools die Zuordnung explizit mit DDL-Statements zu erfolgen hat. Außerdem wird im Gegensatz zum LRU-Algorithmus des Keep Buffer Pools nicht die Popularität einzelner Blöcke ermittelt, sondern die Häufigkeit der Zugriffe auf die gesamte Tabelle bestimmt, welche Tabelle vollständig oder teilweise im Big-Table Cache gespeichert wird.
Das Management und Monitoring des Automatic Big Table Caching ist im Vergleich zum Keep Buffer Pool wesentlich erleichtert worden. Das Feature Automatic Big Table Caching ist wie auch die Verwendung vom Keep Buffer Pool ohne Restart der Datenbank anwendbar oder zu modifizieren.
Die Hauptnachteile des Keep Buffer Pools
- Statische Größe des Keep Buffer Pools durch Allokierung in Bytes (der Buffer Cache kann sich stark verändern) und
- Zuordnung der Objekte zum Keep Buffer Pool (die Zugriffspfade der Anwendung können sich in jedem Applikationsrelease ändern)
entfallen daher bei der Nutzung des Big-Table Cache.
Wenn Gewissheit existiert, dass der Arbeitsspeicher (RAM) größer als die Datenbankgröße im Betrag gilt, kann das Force Full Database Caching helfen, jedes gelesene Objekt im Buffer Cache zu behalten, unabhängig von der Größe und es Zugriffs des Objektes.
- AskTOM: Caching a table in Memory
- Oracle-Dokumentation: Organization of the Database Buffer Cache
- Oracle Documentation: Automatic Big Table Caching + Force Full Database Caching
- Peter Häusler: 12c New Feature: Force Full Database Caching