Als PDF Downloaden!

Tipps & Tricks: September 2016
Bereich:
DBA, Tuning
Erstellung:
09/2016 KW
Versionsinfo:
9.2, 10.1, 10.2, 11.1, 11.2, 12.1
Letzte Überarbeitung:
09/2016 KW
Wir finden einen Schatz - SQLT, Teil 2
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. Teil 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 Monatstipp 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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 4
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:
1.) Ist die SQL_ID oder das PLAN_HASH_VALUE des zu analysierenden Statements bekannt? Oder steht der
Statementtext zur Verfügung?
2.) Soll/kann das Statement aus dem Memory oder dem AWR ausgelesen werden oder gebe ich das zu
analysierende Statement als Textfile mit?
3.) 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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 4
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.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 4
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 Schulung.
Ebenso gern unterstützen Sie unsere erfahrenen Consultants bei Ihnen vor-Ort oder per Remote-Support.
Quellen bzw. zum Weiterlesen:
All About the SQLT Diagnostic Tool - MOS-Note 215187.1
SQLT (SQLTXPLAIN) Frequently Asked Questions - MOS-Note 1454160.1
SQLT Main Report: Usage Suggestions - MOS-Note 1922231.1
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 4