Welcher Datenbankentwickler kennt das nicht: Eine bestimmte Prozedur oder Methode zeigt eine sehr schlechte Performance und der Verdacht liegt nahe, dass ein oder mehrere der verwendeten SQL-Statements die Probleme verursachen.
Doch was kann man da tun? Wie bekommt man ein solches Problem besser eingegrenzt? Und welche der verwendeten SQL-Statements lösen das Problem aus? Im Folgenden ein Einstieg in die Analyse problematischer SQL-Statements.
Das Thema SQL-Tuning ist ja bekanntlich ein weites Feld und Oracle bietet mit AWR, ASH und ADDM sehr hilfreiche Werkzeuge, die einem das Leben bei Performance-Problemen deutlich erleichtern. Allerdings sind diese Werkzeuge kostenpflichtig und stehen darüber hinaus nur in der Enterprise Edition zur Verfügung. Das mittlerweile etwas angestaubte Statspack (kostenfrei) kann einem einige hilfreiche Hinweise geben. Bei der Analyse konkreter Statements stößt man aber auch hiermit an Grenzen.
Für die schnelle Eingrenzung problematischer Statements arbeite ich zur Laufzeit der betroffenen Prozeduren oder Methoden meist mit ein paar einfachen Abfragen, die einem auch ohne AWR schnell weiterhelfen können.
TOP-SQL-Statements
Der Klassiker ist natürlich die Abfrage nach Top-SQL-Statements, die in der Grundform meist etwa so aussieht:
Top-SQL-Statements
Oracle PL/SQL
| 1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT * from (SELECT sql_id, child_number, elapsed_time/1000000 elapsed_time, executions, round((elapsed_time/executions) /1000000, 4) elapsed_time_per_exec, last_active_time, parsing_schema_name, module, sql_text, disk_reads, buffer_gets, rows_processed, cpu_time FROM v$sql where executions >= 1 — and parsing_schema_name = ‚<Schemaname>‘ — and last_active_time >= sysdate – 1/24 — and upper(sql_fulltext) like ‚%<BELIEBIGER STRING>%‘ ORDER BY buffer_gets desc — order by disc_reads desc — order by cpu_time desc ) WHERE rownum <=25;
|
Ich schaue hierbei oft zuerst auf die absolute Anzahl der „executions“ und den Wert in „elapsed_time_per_exec“ (durchschnittliche Laufzeit je Abfrage). Wird eine Abfrage eher selten ausgeführt, ist natürlich eine durchschnittliche Laufzeit von mehreren Sekunden weitaus weniger problematisch, als wenn diese Abfrage permanent ausgeführt wird. Durch mehrfaches Ausführen der obigen Abfrage erkennt man schnell, ob und wie stark sich die Zahl im Feld „executions“ erhöht.
Die Abfrage kann beliebig angepasst werden. Oft grenze ich sie auf bestimmte Datenbankschemen (Feld „parsing_schema_name“) ein, da die Applikationen, in denen Probleme auftauchen, meist auf nur wenige Datenbankschemen begrenzt sind. Oder auf eine „last_active_time“, die über einem bestimmten Schwellenwert liegen muss. Natürlich kann auch die Sortierreihenfolge angepasst werden, wenn man z. B. an Statements mit besonders vielen „disk_reads“ interessiert ist.
Die der Abfrage zugrunde liegende View v$sql bietet darüber hinaus zahlreiche weitere interessante Informationen zu den SQL-Statements im Shared Pool (z. B. Speicherverbrauch, Anzahl Versionen, Optimizer-Mode etc.). Im Rahmen eines SQL-Tunings lohnt sich jedenfalls ein genauer Blick hierauf.
Meist findet man mit der obigen Abfrage schnell einige Statements, bei denen sich eine genauere Analyse lohnt.
Neben der Analyse der Top-SQLs hilft oft auch eine Abfrage auf Betriebssystemebene. Unter Linux und verschiedenen Unix-Varianten steht das top-Kommando zur Verfügung, mit dem CPU-intensive Prozesse angezeigt werden können. Die Prozesse, die dem User „oracle“ gehören, sind oftmals mit SQL-Statements verknüpft, die man sich in der Oracle DB genauer anschauen kann:
SQL-Statements zu betimmen Unix-Prozess
| 1
2 3 4 5 6 7 8 9 10 11 12 |
select q.sql_id, s.sid, s.serial#, p.program, p.spid, s.username, w.event, s.last_call_et, w.seconds_in_wait, q.sql_text, q.executions from v$sql q, v$session s, v$session_wait w, v$process p where q.sql_id (+) = s.sql_id and q.address (+) = s.sql_address and q.child_number (+) = s.sql_child_number and s.sid = w.sid and s.paddr = p.addr and spid = <process id> — <–hier Unix-Process aus top eintragen!!!!!! order by s.last_call_et desc;
|
Ein weiterer Ansatz, problematische Statements zu identifizieren, ist das Tracen von ein oder mehreren Datenbank-Sessions. Hierauf möchte ich an dieser Stelle nicht tiefer eingehen, da dieses Thema wiederum ein weites Feld ist, und stattdessen auf folgende gute einführende Artikel verweisen: hier, hier und hier.
Analyse einzelner Statements
Der Schlüssel für die weitere Analyse eines Statements ist dessen SQL_ID (das erste Attribut der obigen Abfragen). So kann man sich beispielsweise die in einem Statement verwendeten Bind-Variablen anzeigen lassen:
verwendete Bind-Variablen
Oracle PL/SQL
| 1
2 3 4 5 6 7 8 9 10 |
select sql_id, child_number, name, datatype_string, value_string, CASE SYS.ANYDATA.getTypeName(value_anydata) WHEN ‚SYS.VARCHAR2‘ THEN SYS.ANYDATA.accessVarchar2(value_anydata) WHEN ‚SYS.NUMBER‘ THEN TO_CHAR(SYS.ANYDATA.accessNumber(value_anydata)) WHEN ‚SYS.DATE‘ THEN TO_CHAR(SYS.ANYDATA.accessDate(value_anydata), ‚DD.MM.YYYY HH24:MI:SS‘) END AS value_ad from V$sql_bind_capture where sql_id = ‚<SQL_ID>‘;
|
Hinweis: Die angezeigten Bind-Variablen sind nicht unbedingt die zuletzt verwendeten, da sie nicht zu jeder Ausführung abgespeichert werden.
Weitaus interessanter ist aber im Allgemeinen der Ausführungsplan zu einem Statement. Mit folgender Abfrage wird der tatsächlich verwendete Ausführungsplan angezeigt und keine neue berechnete Variante, wie man sie beispielsweise mit „explain plan for <SQL-Statement>“ erhält:
Verwendung von dbms_xplan.display_cursor
Oracle PL/SQL
| 1
2 3 |
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‚<SQL_ID>‘, 0));
|
Oftmals existieren mehrere Varianten eines Ausführungsplans zu einem SQL-Statement. Folgende Abfrage zeigt neben einigen Informationen aus v$sql alle Varianten der gespeicherten Ausführungspläne an:
Verwendung von DBMS_XPLAN.DISPLAY_CURSOR und v$sql
Oracle PL/SQL
| 1
2 3 4 5 6 7 8 9 10 11 |
SELECT s.sql_id, s.child_number, row_number() over (partition by s.sql_id, s.child_number order by ‚x‘) zaehler_je_sql, t.*, elapsed_time/1000000 elapsed_time, executions, round((elapsed_time/executions) /1000000, 4) elapsed_time_per_exec, last_active_time FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_id = ‚<SQL_ID>‘;
|
Hier wird es dann interessant. Allerlei Fragen können so beantwortet werden. Z. B.: Unterscheiden sich die verschiedenen Pläne massiv bzgl. ihrer Ausführungszeiten? Welche Varianten wurden zuletzt verwendet? Werden sie aktuell noch verwendet? Enthalten sie Full Tables Scans, obwohl man eigentlich eine Indexnutzung erwartet? Etc.
Invalidierung einzelner Statements
Ist man mit dem aktuellen Ausführungsplan oder seinen verschiedenen Varianten unzufrieden, kann man seit Oracle 11g Ausführungspläne zu einzelnen SQL-Statements aus dem Shared Pool entfernen. Folgendes Statement gibt u. a. einen „Purge Call“ aus, mit dem alle Informationen zu einen bestimmten SQL-Statement aus dem Shared Pool gelöscht werden.
Entstehung eines Purge Calls
Oracle PL/SQL
| 1
2 3 4 5 |
select SQL_ID, address, hash_value, ‚exec sys.dbms_shared_pool.purge(‚||““||address||‘ ‚||hash_value||““||‘, “C“);‘ purge_call, executions, invalidations, parse_calls from v$sqlarea WHERE SQL_ID = ‚<SQL_ID>‘;
|
Beispiel für einen generierten Purge Call:
Beispiel Purge Call
Oracle PL/SQL
| 1
2 3 |
exec sys.dbms_shared_pool.purge(‚000007FF886AA958 1602468406‘, ‚C‘);
|
Nach dem Ausführen dieses Purge Calls muss die Oracle-Datenbank den Plan zu dem SQL-Statement bei seiner nächsten Verwendung neu berechnen. Hier besteht dann eine Chance, dass ein besserer Plan berechnet wird. Ist dies nicht der Fall, empfiehlt sich vorher die Neuberechnung der Tabellenstatistiken, ein neues Design der verwendeten Indices oder natürlich das Umschreiben des SQL-Statements.
Die Verwendung des sehr empfehlenswerten SQL Tuning Advisors ist jedoch an das kostenpflichtige Oracle Tuning Pack gebunden.