single.php
< Beitrag von Frank Winter

Erste Hilfe bei problematischen SQL-Statements

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:

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:

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:

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:

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:

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.

Beispiel für einen generierten Purge Call:

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. Den SQL Tuning Advisor werde ich dennoch in einem meiner nächsten Beiträge detaillierter beschreiben.

Folgen
X

Folgen

E-mail : *
Kategorie: Oracle-Development | Schlagwörter: , , , , , | Kommentare: 0

Beitrag kommentieren

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.