Oracle Datenbank-Tuning - Der Shared Pool

25.01.2007 von Lutz Fröhlich, Carsten Czarski und Klaus Maier

Im Shared Pool speichert Oracle wichtige Informationen zu ausgeführten SQL-Anweisungen zwischen, um die Daten später erneut nutzen zu können. Eine ungünstige Aufteilung kann verheerende Folgen für die Performance haben.

Im ersten Teil unserer Serie zum Tuning von Oracle-Datenbanken haben wir Ihnen grundlegende Fakten vorgestellt, die die Performance einer Datenbank beeinflussen, und die zur Verfügung stehenden Statistik-Tools, die bei der Fehlersuche behilflich sind.

Im vorliegenden zweiten Teil stellen wir Ihnen den Shared Pool vor, der als Cache für eine Reihe von Informationen dient. Unter anderem erfahren Sie, welche Statistik-Views Ihnen zur Verfügung stehen und wie Sie die Speichergröße optimal nutzen.

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-Logbuffer
Teil 5: I/O optimieren
Teil 6: Tuning automatisieren

Den Shared Pool optimieren

Wenn im täglichen Sprachgebrauch von der Optimierung einer Oracle-Datenbank gesprochen wird, dann ist diese Terminologie eigentlich nicht exakt. Optimiert werden sowohl die Instanz mit ihren Hauptspeicher-Objekten als auch die Datenbank mit ihren Dateien. In diesem Artikel beschäftigen wir uns also mit der Optimierung von Instanz und Datenbank.

Der Shared Pool ist der Teil der SGA, in dem SQL- und PL/SQL-Anweisungen gespeichert werden. Er wird nach dem LRU-Prinzip (Least Recently Used) verwaltet. Wenn der Shared Pool voll ist, werden die Anweisungen hinausgeworfen, die am wenigsten benutzt wurden. Wenn ein Benutzer eine SQL- oder PL/SQL-Anweisung an die Datenbank sendet, dann werden einige Aktionen durchgeführt, bevor die Anweisung selbst ausgeführt wird.

Zuerst konvertiert Oracle die Anweisung in einen numerischen Code.
Danach werden die Zeichen von einem Hash-Algorithmus verarbeitet und es wird ein Hashed Value erzeugt.
Der Benutzerprozess auf dem Server prüft dann, ob dieser Hashed Value bereits im Shared Pool existiert.
Falls der Hashed Value existiert, verwendet der Server-Prozess diese Version und führt die Anweisung aus. Existiert der Hashed Value nicht im Shared Pool, dann führt der Server-Prozess ein Parsing durch, bevor die Anweisung ausgeführt wird. Das Parsing produziert viel Mehraufwand und kostet Zeit. Die folgenden Schritte werden beim Parsing durchgeführt:

Überprüfen der Anweisung auf Syntax-Fehler
Auflösen der Objekte und Überprüfung im Datenbankkatalog, ob diese Objekte existieren
Statistiken der Objekte aus dem Datenbankkatalog lesen
Einen Ausführungsplan bestimmen und vorbereiten
Überprüfen der Objektprivilegien des Benutzers
Erstellen einer kompilierten Version der Anweisung (P-Code)

Cache Hit und Cache Miss

Dieser Parsing-Prozess kann vermieden werden, wenn sich viele Anweisungen als kompilierte Versionen im Shared Pool befinden. Das Auffinden einer kompilierten Version wird Cache Hit genannt. Cache Miss wird die Situation genannt, dass keine kompilierte Version im Shared Pool existiert und der Server-Prozess die Anweisung parsen muss.

Damit es zu einem Cache Hit kommt, müssen beide Anweisungen möglichst exakt übereinstimmen. Die kleinste Abweichung, z.B. Großbuchstaben statt Kleinbuchstaben, führt zu einem Cache Miss. Über den Initialisierungsparameter CURSOR_SHARING können Sie steuern, wann die Anweisungen als gleich angesehen werden. Die möglichen Werte sind EXACT, SIMILAR und FORCE. Ist der Parameter auf EXACT (Default) gesetzt, dann werden nur absolut identische Texte als gleiche Statements angesehen. SIMILAR gestattet Unterschiede in einigen Literalen und FORCE in allen Literalen. Andere Werte als EXACT beinhalten die Gefahr, dass unterschiedliche Befehle als gleich angesehen werden. Das Problem mit den Literalen kann umgangen werden, wenn Binde-Variablen in den Applikationen benutzt werden.

Das Optimierungsziel für den Shared Pool ist, die Anzahl von Cache Hits zu maximieren. Der Shared Pool besteht aus den folgenden drei Komponenten:

Library Cache
Data Dictionary Cache
User Global Area

Im Library Cache werden SQL- und PL/SQL-Anweisungen gespeichert. Für eine Anweisung werden die folgenden Informationen gespeichert:

der Text der Anweisung
der Hashed Value
die kompilierte Version der Anweisung (P-Code)
alle relevanten Statistiken
der Ausführungsplan

Der View V$SQLAREA

Diese Informationen können mit Hilfe des Views V$SQLAREA abgefragt werden.

SQL> SELECT sql_text, hash_value, command_type 2 FROM v$sqlarea; SQL_TEXT HASH_VALUE C ----------------------------- ----------- -- select jf.flaglength, js.siglength, jm.m 296149349 3 ethodlength, jc.ownerlength, jc.classlen gth, jm.cookiesize from triggerjavaf$ jf , triggerjavas$ js, triggerjavac$ jc, tr iggerjavam$ jm where jf.obj#=:1 and jf.o bj#=js.obj# and js.obj#=jc.obj# and jc.o bj#=jm.obj# . . .

Beachten Sie, dass das View V$SQLAREA nur die ersten 80 Zeichen der Anweisungen speichert. Die komplette Anweisung finden Sie in V$SQLTEXT.

Das View V$SQLAREA enthält auch Informationen über die Benutzung von Ressourcen. So können Sie, wie im Listing, die Anweisungen abfragen, die eine große Leseaktivität von der Festplatte aufweisen.

SQL> SELECT sql_text, disk_reads 2 FROM v$sqlarea 3 ORDER BY 2 DESC; SQL_TEXT DISK_READS ---------------------------------------- ---------- select a.schema, a.name, b.name, b.event 454 id from system.aq$_queues b, system.aq$_ queue_tables a, sys.aq$_queue_table_affi nities t where a.objno = b.table_objno and t.table_objno = b.table_objno and bi tand(b.properties, 512) = 512 and t.owne r_instance = :1 . . .

Der Enterprise Manager bietet eine Reihe von Seiten für die Überwachung des Shared Pools. Auf der Datenbankseite finden Sie unter dem Register PERFORMANCE den Link TOP SQL.

Auf der Seite TOP SQL erhalten Sie eine Gesamtübersicht und können in die Top SQL-Anweisungen verzweigen. Dort finden Sie neben dem Ausführungsplan viele Statistiken.

Data Dictionary Cache

Wenn eine SQL- oder PL/SQL-Anweisung ausgeführt wird, wird der Datenbankkatalog nach Objekten und Spaltennamen abgefragt. Es wird überprüft, ob diese Objekte existieren und ob verwendete Namen korrekt sind. Wie die Anweisung selbst, werden auch Informationen des Datenbankkatalogs in einem Cache gespeichert. Hierfür wird der Data Dictionary Cache verwendet. Auch der Data Dictionary Cache funktioniert nach dem LRUMechanismus.

Wie beim Library Cache wird die Performance des Dictionary Cache über die Hit Ratio gemessen. Die Hit Ratio kann mit Hilfe des Views V$ROWCACHE bestimmt werden.

SQL> connect sys/manager@dwhkomp as sysdba Connect durchgeführt. SQL> SELECT 1 - (SUM(getmisses)/SUM(gets)) 2 FROM v$rowcache; 1-(SUM(GETMISSES)/SUM(GETS)) ---------------------------- ,84002997

Der Shared Pool sollte vergrößert werden, wenn die Hit Ratio des Data Dictionary Cache weniger als 85 % beträgt.

User Global Area

Die User Global Area (UGA) existiert im Shared Pool nur, wenn die Shared-Server-Option benutzt wird. Dann ist die UGA eine Cache für Benutzer- und Sitzungsinformationen.

Diese Informationen müssen sich im Shared Pool befinden, weil sich in der Shared Server-Architektur mehrere Benutzer einen Server-Prozess teilen.

Jetzt stellt sich natürlich die Frage, was zu tun ist, um die Performance des Shared Pools zu verbessern. Wir empfehlen die folgenden Aktionen.

den Shared Pool vergrößern
Platz schaffen für große PL/SQL-Anweisungen
wichtige Anweisungen im Shared Pool behalten
Wiederverwendung von Programmcode fördern
einen Large Pool verwenden

Den Shared Pool vergrößern

Die Vergrößerung des Shared Pools ist der einfachste Weg, die Hit Ratio zu erhöhen und damit die Performance zu verbessern. Damit sinkt die Wahrscheinlichkeit, dass Informationen wegen Platzmangel nach dem LRUMechanismus entfernt werden.

Es tritt sehr selten auf, dass die Hit Ratio für den Library Cache gut und für den Dictionary Cache schlecht ist (oder umgekehrt). Die Hit Ratio für beide Caches verbessert oder verschlechtert sich relativ gleichmäßig.

Die Größe des Shared Pools wird durch den Initialisierungsparameter shared_pool_size festgelegt.

In Oracle 10g ist es möglich, den Parameter shared_pool_size dynamisch, das heißt ohne Neustart der Instanz zu verändern.

Platz schaffen für große SQL-Anweisungen

Wenn eine Anwendung große PL/SQL-Pakete häufig aufruft, besteht die Gefahr, dass viele andere Anweisungen nach dem LRU-Mechanismus aus dem Library Cache entfernt werden.

Dadurch wird die Hit Ratio stark abgesenkt. Um diese Problem zu vermeiden, bietet Oracle die Möglichkeit, diese großen Pakete in die Shared Pool Reserved Area auszulagern.

Die Größe wird durch den Initialisierungsparameter shared_pool_reserved_size bestimmt. Sie kann bis zu 50 % der Größe des Shared Pools betragen.

Eine Möglichkeit, die optimale Größe des Reserved Pools zu bestimmen, ist, die PL/SQL-Pakete zu überwachen. Die Abfrage in folgendem Listing zeigt die Paketnamen und ihre Größe im Library Cache an.

SQL> SELECT owner, name, sharable_mem 2 FROM v$db_object_cache 3 WHERE TYPE IN ('PACKAGE','PACKAGE BODY') 4 AND owner != 'SYS' 5 ORDER BY 3 DESC; OWNER NAME SHARABLE_MEM ------------ ----------------- ------------ WKSYS WK_ADM 93500 WKSYS WK_JOB 38808 DBSNMP MGMT_RESPONSE 24840 XDB DBMS_XDBZ0 20122 XDB DBMS_XDBZ0 15370

Die Auslastung der Reserved Area können Sie mit Hilfe des Views v$shared_pool_reserved überwachen.

SQL> SELECT free_space, avg_free_size 2 FROM v$shared_pool_reserved; FREE_SPACE AVG_FREE_SIZE ---------- ------------- 5086152 203446,08

Wichtige Anweisungen im Shared Pool behalten

Wenn es gelingt, PL/SQL-Code, der häufig von Anwendungen benutzt wird, permanent im Shared Pool zu belassen, verbessert sich die Hit Ratio und damit die Performance.

Dieses Verfahren wird als Pinning bezeichnet. Für das Pinning eines Paketes benötigen Sie das Paket DBMS_SHARED_POOL. Das Paket ist nicht standardmäßig installiert. Folgendes Listing zeigt, wie Sie dieses Paket installieren können.

SQL> CONNECT / AS SYSDBA Connect durchgeführt. SQL> @dbmspool Paket wurde erstellt. Benutzerzugriff (Grant) wurde erteilt. View wurde angelegt. Paketrumpf wurde erstellt.

Pinnen Sie ein Paket mit dem folgenden Prozedur-Aufruf.

SQL> EXEC SYS.DBMS_SHARED_POOL.KEEP('SYS.STANDARD'); PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Gepinnte Objekte können Sie über die folgende Abfrage identifizieren.

SQL> SELECT owner, name, type 2 FROM v$db_object_cache 3 WHERE kept = 'YES'; OWNER NAME TYPE ------------ ---------------------- ---------- SYS HISTGRM$ TABLE SYS java/util/Arrays$Array JAVA CLASS List SYS java/lang/ThreadGroup JAVA CLASS SYS /8dba2ab7_PolicyTableR JAVA CLASS owConsta

Wiederverwendung von Programmcode fördern

Ein weiterer Weg, die Hit Ratio im Shared Pool zu verbessern, ist, Programmcode so oft wie möglich wieder zu verwenden. Zwei Anweisungen werden nur als gleich betrachtet, wenn sie denselben Hashed Value besitzen. Bereits geringe Unterschiede im SQL-Text führen zu unterschiedlichen Hashed Values.

Die Verwendung von Binde-Variablen fördert die Wiederbenutzung von Programmcode. Für die folgenden Anweisungen werden verschiedene Hashed Values berechnet:

SELECT * FROM account_dim WHERE last_name='Miller';

SELECT * FROM account_dim WHERE last_name='Smith';

Wird jedoch eine Binde-Variable wie im folgenden Beispiel benutzt, so werden die Anweisungen als identisch betrachtet.

SELECT * FROM account_dim WHERE last_name=:name;

Binde-Variablen können aber auch negative Auswirkungen auf die Performance haben. Da sie keine echten Werte sind, sondern nur Container für Werte bilden, kann der Cost-Based Optimizer nicht immer die richtige Entscheidung treffen.

Einen Large Pool verwenden

Alle vorhergehenden Aktionen können unter Umständen nicht ausreichen, die Performance signifikant zu verbessern, z.B. wenn Sie den Recovery Manager oder den Shared-Server-Modus verwenden.

Oracle stellt dafür den Large Pool zur Verfügung. Setzen Sie den Initialisierungsparameter large_pool_size auf einen Wert größer 0, um einen Large Pool zu erzeugen. Mit der Abfrage in folgendem Listing können Sie feststellen, welche Objekte den Large Pool benutzen.

SQL> SELECT name, bytes 2 FROM v$sgastat 3 WHERE pool = 'large pool'; NAME BYTES ---------------------- --------- PX msg pool 206208 free memory 8182400

(mha)