News zu Oracle
Cursor Sharing hat zweifellos eine große Bedeutung für die Performance, insbesondere das Vermeiden des Hard Parsing. 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. Wie du Cursor-Sharing-Kandidaten in deiner Datenbank findest und welche zwei Möglichkeiten dir zur Optimierung zur Verfügung stehen, zeigt dieser DBA Tipp.
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. Du kannst Cursor Sharing an der Anwendung vorbei datenbankweit mit dem Parameter CURSOR_SHARING=FORCE erzwingen. 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 der Data-Dictionary-View v$sql. Sie führt zu jedem gecachten Statement in der Spalte FORCE_MATCHING_SIGNATURE eine Signatur seines Äquivalents bei Verwendung von CURSOR_SHARING=FORCE mit. Die Anzahl der Einträge in v$sql mit jeweils gleicher Signatur ist also ein gutes Maß dafür, wie viele Ausprägungen eines Statements im Cache liegen, nur weil unterschiedliche Literale verwendet wurden.
In unserem Beispiel würde das Ergebnis aus der v$sql ohne Cursorsharing in etwa so aussehen:
select force_matching_signature,count(*) anzahl from v$sql group by force_matching_signature order by 2; FORCE_MATCHING_SIGNATURE ANZAHL ------------------------ ---------- 1253587301725552198 2 7928100255923163774 2 17867806445150202466 3 10927028642624449365 3 5494157341192764346 3 11682584117507924582 3 17301900507670644855 3 17274913944183326537 3 0 121 8094602905134864270 10000
In unserem Testfall ist das unterste Statement mit der höchsten Ausprägung (10.000 Einträge mit der gleichen FORCE_MATCHING_SIGNATURE 8094602905134864270) vordergründig interessant. Mit einigen Samples könnten wir uns jetzt ein ungefähres Bild verschaffen, um welche Statements es sich handelt:
select sql_text from v$sql where force_matching_signature=8094602905134864270 fetch first 5 rows only; SQL_TEXT ---------------------------------------------------------------------------------------------------- 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 =9933 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 =8082 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 =2080 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 =6237 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 =7514
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.
Hier findest du weitere Infos rund um Oracle aus unserem News & Insights Bereich.
Share this article