Der ORA-04031 und die fehlenden Bind-Variablen
Es passiert immer wieder: Plötzlich gibt es in einer Produktionsdatenbank den Fehler ORA-4031, der dann auftritt, wenn in der SGA zu wenig (passend großes) Memory vorhanden ist. Seitdem es ab Oracle 10g ASMM (Automatic Shared Memory Management) und seit 11g AMM (Automatic Memory Management) gibt, hat sich die Situation um einiges entschärft, aber ganz weg ist der Fehler nicht.
Zusammen mit der Fehlemeldung kommt dann noch die zusätzliche Information, welchen Teil der SGA es erwischt hat:
Large Pool, Streams Pool, Java Pool oder Shared Pool.
Oracle hat ein ganzes Bündel an Ratschlägen, was in so einem Fall zu tun ist:
-
Mehr Memory für die Instanz (bei AMM)
-
Mehr Memory für die SGA (bei ASMM)
-
den entsprechenden Pool vergrößern (ohne Memory Management) oder
-
die Mindestgrößen des entsprechenden Pools anheben (mit Memory Management)
-
den Parameter Shared_Pool_Reserved_Size anheben
-
...
Allen diesen Maßnahmen eigen ist noch ein weiteres Problem: Der Fehler ist kaum reproduzierbar, daher kann jede Gegenmaßnahme nur statistisch auf Wirksamkeit überprüft werden: Dauert es länger, bis er auftritt, dann könnte es geholfen haben. Und natürlich will auch irgend jemand eine Garantie dafür, „... dass dieser Fehler ab jetzt nie wieder auftritt
“. Ja, eh.
Genauso vielfältig wie die Gegenmaßnahmen sind auch die Ursachen. Eine davon ist sehr weit verbreitet, und dabei trifft es den Shared Pool. Der Grund sind zu viele Statements in der Applikation, die eigentlich Bind-Variablen benutzen sollten, es aber nicht tun, weil sich diese moderne Technologie (ist erst ca 30 Jahre alt) bei manchen Software-Entwicklern noch nicht herumgesprochen hat.
Was soll das auch toll für einen Effekt haben, wenn man anstatt Literale wie in
SELECT ... WHERE spalte = 10
einfach
SELECT ... WHERE spalte = :b
schreibt?
Antwort: zwei Effekte.
1. Weniger Parse-Zeit - das wissen die meisten DBAs ohnehin schon genau so lange, wie es Bind-Variablen gibt.
2. Weniger Platzverschwendung und Fragmentierung im Shared Pool (in der Shared SQL Area)
Genau Punkt 2 kann bei Spitzenlast zum ORA-4031 führen, denn jedes Statement, das ausgeführt wird, bekommt ein Stückchen Memory im Shared Pool, und da es meist zu viele sind, schlägt auch hier der Least Recently Used Algorithmus zu und schmeißt die weniger häufig benutzen Varianten regelmäßig raus. Dadurch entstehen Löcher, die nicht immer passend genutzt werden können - die Fragmentierung beginnt.
Um eine solche Situation zu erkenne, wäre es wünschenswert herauszufinden, ob es mehrfache Varianten eines Statements gibt.
Oracle schlägt folgendes Statement vor:
SELECT plan_hash_value, COUNT(*)
FROM v$sqlarea
GROUP BY plan_hash_value
HAVING COUNT(*) > 10;
Damit werden alle Statements aus der Shared SQL Area nach dem errechneten Plan gruppiert. Man geht hier davon aus, dass gleicher Execution-Plan gleichbedeutend mit gleichem Statement ist. Stimmt auch, die Wahrscheinlichkeit, dass zwei verschiedene Execution-Pläne den selben Hash haben ist verschwindend klein. Allerdings haben auch diese Statements möglicherweise den selben Execution-Plan:
SELECT last_name FROM hr.employees WHERE employee_id = 123
und
SELECT salary FROM hr.employees WHERE employee_id = 123
und
SELECT last_name,salary FROM employees WHERE employee_id = :b1
Man erwischt mehr, als man fangen möchte.
Eine etwas exaktere Möglichkeit, den Beifang auszusortieren, gibt es seit Oracle 10g. In der V$SQL gibt es zwei Spalten EXACT_MATCHING_SIGNATURE und FORCE_MATCHING_SIGNATURE. Hier klingen in den Namen die beiden Werte „EXACT“ und „FORCE“ des Parameters CURSOR_SHARING mit, und ja, Oracle verwendet intern diese Spalten, wenn der Parameter gesetzt ist. Damit wird entschieden, ob zwei Statements als gleichwertig behandelt werden sollen. Der Interessantere der beiden Parameter ist FORCE_MATCHING_SIGNATURE, denn er ignoriert die Werte von Literalen:
SELECT last_name FROM hr.employees WHERE employee_id = 123
und
select LAST_NAME from HR.EMPLOYEES where EMPLOYEE_ID = 456
haben den gleichen Wert für FORCE_MATCHING_SIGNATURE aber nicht für EXACT_MATCHING_SIGNATURE.
Jetzt ist es einfach:
SELECT force_matching_signature, COUNT(*)
FROM v$sqlarea
GROUP BY force_matching_signature
HAVING COUNT(*) > 10;
Damit werden genau die Statements gruppiert, die garantiert mittels Bind-Variablen vereinheitlicht werden könnten. Ganz perfekt ist auch dieses Ergebnis nicht, denn Kommentare in den Statements beeinflussen die Signatur, auch wenn die Statements ansonst identisch wären.
Anlässlich eines ORA-4031, bei dem wir fehlende Bind-Variablen als Ursache vermutet hatten, fanden sich in der Shared SQL-Area über 40000 (!) Varianten des selben Statements. Selbst wenn es nicht der alleinige Auslöser war - da muss der Software-Hersteller ein wenig nachbessern.