News
DBA-Tipp: Cursor-Sharing-Kandidaten in der Anwendung identifizieren

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

In unserem DBA-Tipp vom Februar 2017 „DBA-Tipp: Hard Parses verringern – Performance steigern“ sind wir bereits auf die Bedeutung des Cursor Sharing für die Performance, insbesondere das Vermeiden des Hard Parsing, eingegangen.

Auf den ersten Blick nicht offensichtlich, kann das Vorhalten vieler nicht-wiederverwendbarer Statements im Library Cache aber auch zur Verschwendung von Buffer Cache führen. Oracles Diagnosetools AWR und statspack speichern in ihren Repositories neben Ausführungsstatistiken auch Ausführungspläne ab. Diese werden regulär per INSERT-SELECT aus entsprechenden Data Dictionary-Views in die Repositories übertragen. Je mehr Statements sich im Shared Pool befinden, desto mehr SELECT-Statements werden ausgeführt und entsprechend Datenblöcke durch den Buffer Cache geschleust. Das verdrängt unter Umständen in großem Umfang Datenblöcke, die für die eigentliche Datenbankanwendung dort gepuffert waren und performancerelevant wären.

Zudem haben wir oft beobachtet, dass vergleichsweise große Shared Pools mit sehr vielen nicht-wiederverwendbaren Statements zu einer erhöhten Anzahl von „library cache: mutex X“ oder „library cache pin“ Wait-Events führen. Im oben genannten Artikel wurde bereits erwähnt, dass man Cursor Sharing an der Anwendung vorbei datenbankweit mit dem Parameter CURSOR_SHARING=FORCE erzwingen kann. In diesem Fall ersetzt die Datenbank von sich aus Literale in SQL-Statements durch Bindevariablen.

Nicht alle Anwendungshersteller gestatten das jedoch. Zum Teil gibt es auch Ressentiments gegen diese Einstellung, die aus sogenannten bind peeking Problemen aus Versionen vor 11.2.0.4, herrühren. Hat man entsprechenden Zugang zum Support des Anwendungsentwicklers, kann es daher von Interesse sein, ihm die konkreten Statements mitzuteilen, die wegen Verzicht auf Bindevariablen den Library Cache besonders exzessiv verschwenden. Diese können dann in puncto Bindevariablen im Code angepasst werden.

Zu Demonstrationszwecken gehen wir von einem Szenario aus, in dem ein SELECT-Statement 10.000 Mal ausgeführt wird; jedes Mal mit einer anderen ID:

SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID = 1;
SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID = 2;
...
SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID = 9999;
SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID = 10000;

NAME                                                VALUE

---------------------------------------------- ----------
execute count                                       10351
parse count (describe)                                  0
parse count (failures)                                  0
parse count (hard)                                  10037
parse count (total)                                 10200
parse time cpu                                        677
parse time elapsed                                    648

Wie unschwer zu erkennen ist, könnte dieses Statement durch Ersetzen des Literals für CUSTOMER_ID zu einem wiederverwendbaren Statement abgeändert werden. Statt 10.000 Hard Parses mit je einer Ausführung, wäre dann nur noch 1 Hard Parse erforderlich. Das geparste Statement könnte dann die nächsten 9999 Mal wiederverwendet werden und würde erheblich ressourcensparender arbeiten. Ob das über den Datenbankparameter CURSOR_SHARING=FORCE oder durch Änderung der Anwendung erfolgt, ist hierbei letztlich unerheblich.

SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID = :ID;

NAME                                                 VALUE
----------------------------------------------- ----------
execute count                                        10015
parse count (describe)                                   0
parse count (failures)                                   0
parse count (hard)                                      39
parse count (total)                                  10201
parse time cpu                                          81
parse time elapsed                                      67

Zum Auffinden solcher „tunenswerter“ Statements in einer Datenbank bedient man sich nun genau der Tatsache, dass zwar die Statement-Texte unterschiedlich sind, diese Statements jedoch den gleichen Ausführungsplan verwenden. Diese Information ist unter anderem in der View gv$sql zu finden. Je mehr Einträge in gv$sql den selben plan_hash_value aufweisen, desto wahrscheinlicher handelt es sich dabei um ein potentiell wiederverwendbares Statement, das von CURSOR_SHARING=FORCE oder der Anpassung der Anwendung profitieren kann.

 

In unserem Testfall ist natürlich das oberste Statement mit der höchsten Ausprägung (10.000 Einträge mit dem gleichen Ausführungsplan) vordergründig interessant. Aber auch die Nachfolgenden können in der Realität Kandidaten für eine Analyse sein, wenn sie einen hinreichend hohen PHV_COUNT aufweisen.

Interessiert jedes einzelne Statement innerhalb einer Gruppe potentiell wiederverwendbarer Statements (oben wurde je plan_hash_value und Schema nur je eines zufällig ausgewählt), bedient man sich folgender Query:

Fazit

Statements, die Literale statt Bindvalues verwenden, können sich durch erhöhten Parseaufwand und Verschwendung von Library und auch Buffer Cache negativ auf die Performance auswirken. Ist eine globale Lösung durch Verwendung des Datenbankparameters CURSOR_SHARING=FORCE nicht möglich, können die potentiell problematischen Statements dynamisch ermittelt und so dem Anwendungshersteller für eine gezielte Anpassung mitgeteilt werden.