Wir finden einen Schatz - SQLT, Teil 2

03.
September
2016
Veröffentlicht von: Katja Werner

Die Suche geht weiter - Performance-Analyse von SQL-Statements mit SQLT.

Schon seit Ewigkeiten gibt es ein Tool zur Performance-Analyse von SQL-Statements: ständig weiterentwickelt und auch intern eingesetzt von Oracle, kostenlos, auch ohne Diagnostic- und Tuning-Pack nutzbar und - in weiten Kreisen - unbekannt. Gründe genug, das Tool an dieser Stelle vorzustellen und einen "Performance-Tuning-Schatz" einem größeren Publikum zugänglich zu machen. <link blog-detailansicht wir-finden-einen-schatz-sqlt-teil-1.html external-link-new-window internal link in current>Opens external link in new windowTeil 1 beinhaltete die Vorstellung des Tools und erläutert die Installation. Hier nun, in Teil 2, werden die Analyse-Möglichkeiten mit SQLT beleuchtet.

Die Suche geht weiter - Performance-Analyse von SQL-Statements mit SQLT

Skripte und Aufbau der Testumgebung

Für die folgenden Analysen werden die Skripte vom <SQL_ROOT>-Verzeichnis aus ausgerufen, also dem Verzeichnis, in das SQLT nach dem Download entpackt wurde, und das sowohl die Installations- als auch Performance-Analyse und Testskripte enthält.

Unter <SQL_ROOT>/run liegen die Skripte, die zur Analyse aufgerufen werden.

Bereits in unserem <link blog-detailansicht wir-finden-einen-schatz-sqlt-teil-1.html external-link-new-window internal link in current>Opens external link in new windowMonatstipp August 2016 wurde erwähnt, dass mit SQLT Skripte zur Verfügung gestellt werden, mit denen ein Testschema angelegt werden kann. Diese Skripte befinden sich im SQLT-Root-Verzeichnis unter <SQL_ROOT>/input/sample/minidb.

Skripte mit ausgewählten SQL-Statements legten die Entwickler von SQLT freundlicherweise unter unter <SQL_ROOT>/input/sample ab. So muss man sich für einen ersten Eindruck nicht alles selbst "aus den Fingern saugen", sondern kann diese Beispiele ohne großen Aufwand verwenden. :)

Für diesen Monatstipp wird ebenfalls mit diesen Skripten gearbeitet. So können Sie es leicht "nachspielen" und die Funktionsweise von SQLT kennenlernen. Los geht's!

Zu Beginn wird das Schema QTUNE wie folgt installiert und befüllt:

cd <SQLT_ROOT>/input/sample/minidb
sqlplus sys as sysdba
-- Erstellung Testschema qtune
start create_user.sql
-- Befüllung Testschema qtune:
conn qtune/qtune
start create_order_entry_db.sql 

Der Schatz wird erarbeitet - Methoden

SQLT bietet viele Möglichkeiten zur SQL-Statement-Analyse, in der SQLT-Dokumentation werden sie "Methoden" genannt. Je nach Einsatzzweck muss die geeignete Methode (sprich: das geeignete Skript) eingesetzt werden. Für die Wahl der geeigneten Methode (sprich: des geeigneten Skripts) muss in erster Linie geklärt sein:

  • Ist die SQL_ID oder das PLAN_HASH_VALUE des zu analysierenden Statements bekannt? Oder steht der Statementtext zur Verfügung?
  • Soll/kann das Statement aus dem Memory oder dem AWR ausgelesen werden oder gebe ich das zu analysierende Statement als Textfile mit?
  • Wie umfangreich soll die Analyse sein? Soll zum Beispiel das Statement erneut ausgeführt werden oder ist unter Umständen die einfache Erstellung eines möglichen Ausführungsplanes ausreichend?

Aus der Kombination der Antworten auf diese Fragen ergibt sich, welche Methode zur Performance-Analyse mit SQLT gewählt werden sollte. Es liegt in der Verantwortung des Datenbankadministrators oder Entwicklers, die zu seiner Problemstellung passende Methode auszuwählen. Allen Methoden ist gemeinsam, dass sie nur ein Statement analysieren. Es existiert zwar eine Methode, der mehrere Statements mitgegeben werden können, aber auch hier werden diese Statements nicht gleichzeitig sondern nacheinander analysiert.

Im Folgenden sollen einige Methoden vorgestellt werden:

1. XTRACT

Eine andere Methode - XTRACT - wird mit der SQL_ID oder dem PLAN_HASH_VALUE als Parameter aufgerufen. Die SQL-Statistiken werden direkt aus dem Memory oder dem AWR abgezogen und so ist diese Methode etwas schneller als die oben genannte. Nachteil ist, dass man keinerlei Daten bekommt, wenn das Statement nicht mehr im Memory oder im AWR enthalten ist. Für Anwender ohne Diagnostic-Pack und somit ohne Zugriff auf das AWR ist dieser Nachteil noch gravierender. Aufgerufen wird XTRACT wie folgt:

sqlplus qtune/qtune
start run/sqltxtract.sql 1pzqzvnzaucy oracle

Die SQL_ID wurde zuvor aus gv$sql ermittelt.

2. XECUTE

XECUTE ist eine Methode, die das zu analysierende Statement per Textfile erhält und dieses vollständig laufen lässt, bevor die Auswertung beginnt. Der Aufruf lautet so:

sqlplus qtune/qtune
start run/sqltxecute.sql input/sample/script2.sql oracle

Alternativ, wenn der Testuser QTUNE nicht installiert wurde, aber das Beispielschema SH von Oracle auf einer Spieledatenbank vorhanden ist, kann man sich damit verbinden und das SQL-Statement im Textfile script1.sql ausführen:

sqlplus sh/sh
start run/sqltxecute.sql input/sample/script1.sql oracle

Natürlich kann jedes beliebige SQL-Statement in diesen Textdateien stehen. Wichtig ist, dass der User, mit dem SQLT aufgerufen wird - hier also "QTUNE" oder "SH" - Zugriff auf die Objekte hat sowie die Rolle SQLT_USER_ROLE verliehen bekommen hat. "oracle" ist in diesem Fall das Passwort des Users SQLTXPLAIN. Diesem User gehört das Repository, in das die Performance-Daten und Ausführungspläne "hineingesammelt" werden. Das Passwort wird benötigt, um die Daten aus dem Repository zu exportieren. Nach dem Export werden die einzelnen Files in einem Zip-File zusammengefasst.

3. XTRXEC

Diese Methode kombiniert die ersten beiden Methoden. Die Methoden werden nacheinander aufgerufen. Per XTRACT werden der Statementtext, Bindevariable aus dem Bind Peeking sowie Ausführungspläne aus dem Memory oder AWR ermittelt, anschließend wird das erhaltene Statement per XECUTE erneut prozessiert und die Performance analysiert.

sqlplus qtune/qtune
start run/sqltxtrxec.sql 1pzqzvnzaucy oracle

4. XPLAIN

XPLAIN ist die letzte Methode, die an dieser Stelle aufgeführt werden soll. Es wird verwendet, wenn schnell ein möglicher Ausführungsplan für ein Statement dargestellt werden soll. Bei der Verwendung von XPLAIN werden weder Bindevariablen noch zur Laufzeit ermittelten Statistiken berücksichtig. Es ist das Pendant zu EXPLAIN PLAN FOR im Sql*Plus mit dem Vorteil, dass die Ergebnisse im SQLT-Repository abgespeichert werden und dort später auch wieder abrufbar und vergleichbar sind.

XPLAIN sollte - ob seiner Ungenauigkeiten und des geringen Informationsgehaltes - nur eingesetzt werden, wenn XECUTE und auch XTRACT aus Performance-Gründen nicht eingesetzt werden können. Der Aufruf sieht so aus:

sqlplus sh/sh
start run/sqltxplain.sql input/sample/sql1.sql oracle

Es gibt darüberhinaus viele weitere, sehr nützliche Methoden, deren Erläuterung aber den Rahmen dieses Monatstipps sprengen würde.

Der Schatz wird gehoben - Analyse und Reports

Der Output von SQLT-Files umfasst so ziemlich alles, was sich ein Performence-tunender Datenbankadministrator, Tester oder Entwickler nur wünschen kann. Zuerst einmal: alle Daten, die zu der getunten Session gehören, werden letztlich in einem Zip-File gespeichert.

Nach dem Entpacken kann man die Datei sqlt_xxxxxx_main.html aufrufen und sich durchklicken: Zuoberst sind Auffälligkeiten und Hinweise gelistet. Dort steht zum Beispiel, ob es veraltete Statistiken gibt, ob Optimizer Parameter vom Default abweichen und ob die Statistikjobs aktiviert sind. Weitere Kapitel im Report enthalten die getracten SQL-Statements, NLS-Parameter für die Session/Instance/Datenbank, Bindevariablen, Cursor, Ausführungspläne und Laufzeiten. Es folgen Details zur Ausführung des SQLs, also SQL Statistiken, Session Statistiken, Events und - falls vorhanden - Parallelprozesse. Weiterhin finden sich Angaben zu Tabellen, deren Statistiken, Constraints, Indices und Histogrammen. Abgeschlossen wird der Report von Daten zu Objektabhängigkeiten, Tablespaces sowie DDL Statements zum Anlegen der beteiligten Objekte. Sofern das Diagnostic-Pack oder das Diagnostic- und Tuning-Pack genutzt werden kann, wird der Report durch diese zusätzlichen Informationen ergänzt.

Wem dieser Report zu erschlagend ist, hat die Möglichkeit, die Datei sqlt_xxxxxx_lite.html anzusehen. Hier gibt es die wichtigsten Informationen: Ausführungspläne des Statements, sowie Statistiken zu den Tabellen und Indices, auf die zugegriffen wurde.

Wer sich ausführlicher für die Tracefiles interessiert, greift auf die Datei sqlt_xxxxxx_trca_exxxxx.html zu.

Als wenn es noch nicht genug wäre - Tipps & Tricks

Ein weiteres interessantes Feature, das auch SQLT-intern für die Reports verwendet wird und sozusagen als "Nebenprodukt" verwendet werden kann, ist die Möglichkeit konventionell erzeugte Tracefiles zusammenzufassen. Dies ist dann nützlich, wenn ein Statement getract wird, das parallele Aktionen durchführt. Hier wird für jeden einzelnen  Parallelprozess ein separates Tracefile erstellt. SQLT erstellt daraus ein einziges Tracefile und - wenn gewünscht - wertet es dieses Tracefile auch aus.

Auch die umgekehrte Variante - das Splitten von Tracefiles - ist möglich. Wird zum Beispiel für eine Session ein SQL Trace (Event  10046) und ein Optimizer Trace (Event 10053) gleichzeitig ausgeführt, so schreibt Oracle alle Traceinformationen zu diesem Prozess in ein einziges Tracefile. Dieses kann mit SQLT-Skripte wieder in separate Files gesplittet werden und erleichtert so die Auswertung. 

Erwähnenswert ist, dass SQLT auch Statements analysieren kann, die auf einer Standby-Datenbank im Read-Only-Modus laufen. Dafür muss der Connect auf der Primary Database erfolgen, die Analyse erfolgt dann über einen Datenbanklink, der von der Primary- auf die Standby-Datenbank zeigt.

Die Analyse mit SQLT kann auch von einem Oracle-Client remote erfolgen. Voraussetzung dafür ist, dass die SQLT-Skripte auf dem Clientrechner entpackt sind und eine SQL*Net Verbindung zur Datenbank besteht. Zusätzlich muss zu Beginn in der SQL*Plus Session der Connect Identifier für die Datenbank gesetzt werden, damit die Skripte nicht mit dem Fehler "ORA-20106: SQLT parameter connect_identifier must be set when running SQLT from a remote client" abbrechen:

EXEC sqltxadmin.sqlt$a.set_sess_param('connect_identifier', '@dwh12');

Und das war erst der Anfang

SQLT bietet neben den hier vorgestellten Features noch weit mehr, die "Advanced Methods and Modules". Vielleicht haben Sie Lust bekommen, sich selbst auf Schatzsuche zu begeben? Es gibt noch viel zu entdecken.

Für weitere Information stehen wir gern zur Verfügung - zum Beispiel im Rahmen einer SQL Tuning Opens internal link in current windowSchulung. Ebenso gern unterstützen Sie unsere erfahrenen Consultants bei Ihnen Opens internal link in current windowvor-Ort oder per Remote-Support.

Quellen bzw. zum Weiterlesen:

All About the SQLT Diagnostic Tool - Opens external link in new windowMOS-Note 215187.1
SQLT (SQLTXPLAIN) Frequently Asked Questions  - Opens external link in new windowMOS-Note 1454160.1
SQLT Main Report: Usage Suggestions - Opens external link in new windowMOS-Note 1922231.1

DBA SQL Performance Tuning Analyse SQLT

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.