Höchste Zeit um SQL zu optimieren

Höchste Zeit um SQL zu optimieren
Autor: Harry Flora, Embarcadero Technologies
DOAGNews Q4_2004
Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere die der
Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen und Tabellen, der Funksendung, der Mikroverfilmung oder der Vervielfältigung auf anderen Wegen und der Speicherung in
Datenverarbeitungsanlagen, bleiben, bei auch nur auszugsweiser Verwertung, vorbehalten. Eine
Vervielfältigung dieses Werkes oder von Teilen dieses Werkes ist auch im Einzelfall nur in den
Grenzen der gesetzlichen Bestimmungen des Urheberrechtes der Bundesrepublik Deutschland vom
9. September 1965 in der jeweils geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig. Zuwiderhandlungen unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.
©2004
SQL-Tuning ist ein kritischer, leider jedoch oft vernachlässigter Aspekt relationaler
Datenbank-Management-Systeme. Oracle-Datenbank-Profis sind in erster Linie
verantwortlich für Verfügbarkeit und Zuverlässigkeit des RDBMS, wenig Beachtung
findet die Optimierung der SQL-Anweisungen zur Abfrage der Datenbank.
Angesichts täglich wachsender Datenmengen und immer schnelleren Betriebsabläufen rund um die Uhr, führen ineffiziente Scripte zwangsläufig zu extrem
verlangsamten Abfragezeiten, frustrierten Anwendern und unzufriedenen Kunden.
Im weiteren Verlauf des Artikels bieten wir Ihnen praktische Tipps und Hinweise,
damit Ihr SQL so effizient wie möglich ist. Sie werden sich jetzt sicher fragen: Warum
tunen, wo doch mein SQL noch Ergebnisse liefert?
SQL wurde ursprünglich nicht entwickelt, um die komplexen Abfragen zu leisten, die
heute mit ihm durchgeführt werden, um die steigenden Anforderungen der Anwender
erfüllen zu können. Die typische Oracle E-Business-Umgebung ist heutzutage
überfrachtet mit Anwendungs-, Web-, und Datenbankservern werden. Es ist keine
komplexe prozedurale Sprache und muss daher permanent angepasst und stellt eine
gewaltige Herausforderung dar.
Parallel zu den E-Business-Technologien und den immer anspruchsvolleren SQLScripten der Entwickler sind auch die Zeilenanzahl und Komplexität eines
durchschnittlichen SQL-Scripts gestiegen. Dies stellt Datenbankprofis bei der
Verwaltung vor echte Probleme und es liegt in der Verantwortung von Unternehmen,
die daraus resultierenden Risiken ernst zu nehmen. Die einer Oracle-Infrastruktur
zugrunde liegenden Datenstrukturen und -volumen ändern sich ständig. Ihr SQL
sollte dem folgen – ebenso wie die Art der Abfrage und des Zugriffs auf die Daten.
SQL: Enormer Einfluss auf Performance
SQL-Tuning muss Teil eines iterativen und effizienten Anwendungs-, Entwicklungsund Wartungsprozesses sein. Ohne Tuning haben Unternehmen keinerlei Garantie,
dass ihre Anwendungen leistungsstark sind. Bedeutende Experten behaupten sogar,
dass 60 bis 70 Prozent der Datenbank-Performance auf gut geschriebenen SQLCode beruhen. Das ist sicher richtig, wenn alle physischen Elemente – etwa
Speicher, Plattenkapazität und Prozessorkapazität – ausreichend vorhanden sind.
Weil der SQL-Code einen derart hohen Stellenwert für die allgemeine DatenbankPerformance hat, ist es so wichtig, dass Skripte von Anfang an in der effizientesten
Weise geschrieben werden. SQL Scripte können jedoch in unterschiedlicher Weise
geschrieben werden und produzieren dennoch die gleichen Ergebnisse. Bedenkt
man dies, können Datenbankprofis frei mit unterschiedlichen Coding-Möglichkeiten
experimentieren, um zu sehen, welche am Ende schneller ist als andere. Dies kann
auf unterschiedliche Art erfolgen: durch Hinzufügen neuer Indizes zu den in SQLQueries benutzter Tabellen, durch das Umschreiben von SQL in verschiedene
Formulierungen, durch Hinzufügen von Hints zu Abfragen, um zu beeinflussen, wie
die Datenbank die jeweilige SQL-Anfrage abarbeitet, usw.
©2004
Gerade der heutige Zeitdruck führt dazu, dass jedem x-beliebigen Code nach dem
Motto „Hauptsache er funktioniert“ der Vorzug vor einem wirklich effizienten gegeben
wird. Dieses Verhalten führt schnell zu einer Kostensteigerung. Denn der x-beliebige
Code wird an anderer Stelle zu Problemen führen, die wieder teure Entwicklungsressourcen binden. Die Kosten, die entstehen, um ein neues Stück SQL zu tunen,
sind zig-mal günstiger (bezogen auf die Mann-Stunden und die AnwendungsAusfallzeit), als einen Code zu untersuchen und zu tunen, der in der Produktionsumgebung schlecht performt. Ein weiterer guter Grund, der für Tuning spricht ist die
Tatsache, dass Tuning dem Datenbankprofi verstehen hilft, wie die Oracle RDBMS
strukturiert ist und wie sie mit Queries umgeht.
Damit ein Unternehmen anspruchsvolle Queries entwickeln kann, müssen Datenbankprofis entweder externe SQL-Experten engagieren oder immer mehr ihrer
eigenen kostbaren Zeit investieren, um die Scripte zu entwickeln und zu optimieren.
Dies kann eine zeitraubende Übung darstellen und führt nicht selten dazu, dass
hocherfahrene Oracle-Datenbankprofis von ihren eigentlichen Kernaufgaben
abgezogen werden müssen.
SQL-Servicing
SQL nicht zu tunen ist ähnlich, wie den Ölwechsel beim Auto zu verpassen: Für eine
bestimmte Zeit läuft das Auto weiterhin bestens. Mit der Zeit zirkuliert jedoch mehr
und mehr Schmutz im Motor, das Auto wird stetig langsamer, bevor es dann
endgültig stehen bleibt. Wenn man in der RDBMS Anwendungen und Daten
hinzufügt oder ändert, müssen die Scripte, die die Datenbank abfragen, immer
verworrenere Wege durch zahllose Tabellen, Indizes und Anwendungen verfolgen.
Wenn diese Scripte nicht aktualisiert werden und den großen strukturellen
Änderungen nicht Rechnung tragen, werden sie langsamer und mit der Zeit immer
ineffizienter die gewünschten Daten zu sammeln. Die Frage ist aber: Wann ist der
beste Zeitpunkt für das Tuning. Die einfache Antwort lautet: Unternehmen sollten ihr
SQL fortlaufend tunen. Es gibt drei perfekte Anlässe:
– Während der Erstellung neuen Codes
Wird neuer Code geschrieben, so ist das die ideale Möglichkeit, die Effizienz und
Effektivität der existierenden SQL-Routinen zu überprüfen. Entwickler sollten ihr SQL
angesichts der Veränderungen der Datenbanken, Anwendungen und Systeme
daraufhin untersuchen, ob er noch bestmöglich effektiv arbeitet. Oft ist dies nicht der
Fall.
– Wenn existierender Code geändert wird
SQL-Tuning als Teil der fortlaufenden Anwendungswartung gehört heute bei vielen
DBAs zum Alltag. Mit dem richtigen Support können DBAs das SQL-Tuning in ihre
generellen Wartungsroutinen integrieren. Allerdings ist das Management so vieler
SQL-Scripte eine sehr arbeitsintensive manuelle Tätigkeit, die nicht von unerfahrenen Entwicklern ausgeführt werden sollte.
©2004
– Um Performance-Probleme zu beheben
Zusätzlich zu geplanten Tunings werden Entwicklerteams möglicherweise zur
Überprüfung der Scripte herangezogen, wenn es innerhalb der Live-Umgebung zu
Performance-Problemen kommt.
Verallgemeinert lässt sich sagen: Herauszufinden und zu verstehen, wie der OracleDatenbankserver SQL-Statements ausführt und welche Ressourcen in jeder Stufe
dafür erforderlich sind, hilft, Engpässe zu finden und die Anwendung zu tunen, wann
immer man es für nötig erachtet.
Queries optimieren
Wichtiger als der Zeitpunkt des Tunens ist jedoch die Frage: Wie tunen? Es gibt auf
der ganzen Welt nicht genug Experten, um all die erforderlichen manuellen SQLOptimierungen auszuführen, die für eine schnelle Query-Reaktionszeit und eine
starke Datenbank-Performance nötig sind. Angesichts ihrer komplexen Infrastrukturen haben viele Unternehmen nicht die Ressourcen, ihre SQL-Scripte manuell zu
tunen. Es gibt keinen wirklich falschen oder richtigen Weg, SQL zu schreiben, weil
die Anforderungssituationen immer unterschiedlich sind. Tuning kann daher nicht
wirklich per Buch oder Anweisung gelernt werden. Kommt professionelles Tuning nur
durch Erfahrung oder gibt es Shortcuts, etwa für Datenbankprofis mit einem
zehnjährigen Know-how?
Oracle TKProf und EXPLAIN Plan sind ein guter Start. Aber es ist aufgrund des
dabei entstehenden System-Overheads nicht immer möglich, das gesamte SQL zu
tunen. Dies unterstreicht die Wichtigkeit, Anwendung für Anwendung methodisch zu
tunen. Generell kann man sagen, dass die v$-Ansichten potenzielle Probleme
aufzeigen können. Sie bieten Echtzeit-Informationen über den Zustand der OracleInstanz – über die Statements die aktuell verarbeitet werden, über die Sessions die
auf Locks warten, usw. Diese v$-Views abzufragen, unterstützt den Anwender,
Engpässe schnell zu identifizieren.
TKProf, EXPLAIN Plan und STATPACK sind das eine. Es gibt alternative Methoden
für das SQL-Tuning, die die Arbeitsbelastung von Datenbankprofis massiv reduzieren
können. So sind im Markt einige Lösungen erhältlich, die die Entwicklung und Optimierung von SQL-Scripten sehr erleichtern und komplizierte Programmieraufgaben
sehr einfach machen. Mit solchen Tools bleibt das Schreiben von effizienten und
präzisen SQL-Scripten nicht länger eine Domäne von Elite-Experten. Mit ihrer Hilfe
sind auch diejenigen erfolgreich, die nur minimale SQL-Kenntnisse mitbringen.
Warum muss man tunen?
Wie aufgezeigt wurde, ist SQL-Tuning ein wichtiger, oft jedoch extrem vernachlässigter Aspekt des Datenbank-Managements. Ganz gleich, ob man manuelles Tuning
wählt oder eines der innovativen der automatischen SQL-Script-Generierungs- und
Tunig-Tools einsetzt: Das regelmäßige Tuning der Scripte ist ein entscheidender
Faktor, um SQL zu verfeinern und zu verbessern, damit sie der wachsenden Komplexität der Oracle-Infrastrukturen aufgrund steigender Ansprüche an die IT und an
das Unternehmen gerecht wird.
©2004
Zielbasiertes Tuning
Serielle Abfragen: Um durch Tuning eine maximale Performance bei reduzierter
Nutzung der System-Ressourcen zu erreichen, sollte ein EXPLAIN-Plan für jede der
verschiedenen Versionen der SQL-Anweisungen erstellt werden. Es ist nicht
notwendig, den Code auszuführen. Danach sollten die verschiedenen SQLVersionen verglichen werden, um dann festzulegen, welche die effizienteste ist.
Parallele Abfragen: Mit Oracle 8i oder einer neueren Version der Datenbank,
können Unternehmen die Last verteilen, indem sie die Abfragen parallel ausführen.
Parallele Abfragen sind ideal bei Queries mit hohen Datenvolumina, dem Laden von
Daten, langen Batch-Jobs, usw.
Zehn Tipps für das SQL Tuning
1. Stellen Sie sicher, dass die Tabellen analysiert wurden – das ist entscheidend
für die Pfad-Selektion
2. Führen Sie einen EXPLAIN Plan für jedes Stück SQL aus, das entwickelt
wurde – identifizieren Sie Probleme bevor sie den Rest des Teams erreichen,
geschweige denn die Produktion.
3. Setzen Sie Indizes ein, aber verwenden Sie nicht zu viele – das kann sich
negativ auf die Performance auswirken.
4. Vermeiden Sie komplette Table Scans, wenn die Ergebnisse sich über einen
Index ermitteln lassen. Bei kleinen Tabellen ist Scannen allerdings
empfehlenswert.
5. Wählen Sie die Join-Reihenfolge so, dass später in der Abfrage weniger
Zeilen an die Tabellen gejoint werden.
6. Splitten Sie komplexe Queries in mehrere einzelne. Vermeiden Sie komplexe
Business-/Anwendungslogik im SQL – die sollte im Programmcode sein.
Denken Sie daran: SQL ist keine prozedurale Sprache.
7. Minimieren Sie den Einsatz von „Distinct“, denn das führt zu einer Sortierung –
alle Zeilen müssen vor der Rückgabe der ersten Daten zwischengespeichert
werden.
8. In den meisten Fällen ist es schneller, wenn Sie „exist“ und „not exist“ statt „in“
und „not in“ wählen.
9. Verwenden Sie autotrace, um „disk reads“ und „buffer gets“ zu analysieren –
als Faustregel gilt: Sind die Werte im Vergleich zu den zurückgegebenen
Daten hoch, ist dies ein gutes Zeichen dafür, dass Tuning notwendig ist.
10. Minimieren Sie die Anzahl der Netzwerk-Roundtrips beim Abrufen der Daten –
erwägen Sie, die Daten in eine lokale Anwendung zu migrieren, das führt
schneller zu Ergebnissen.
©2004
Tuning Grundlagen
Wenn Sie sonst nichts machen, tun Sie auf jeden Fall das Folgende:
-
Validieren Sie SQL
Aktualisieren und nachvollziehen Sie die wesentlichen Objekt-Statistiken
EXPLAINen und nachvollziehen Sie den Abfragepfad
Korrigieren Sie Schwachstellen
Schreiben Sie erneut und verwenden Sie unterschiedliche Techniken und
Kombinationen
Benchmarken Sie diese
Wählen Sie die beste Option basierend auf benötigte Zeit und I/O-Verbrauch
(normalerweise)
Kontakt:
Nützliche Scripte um nachzuvollziehen, wie Ihr SQL performt
Version 9.x (zeigt neue Spalten CPU_Time und ELAPSED_Time)
SELECT SQL_TEXT ,
USERNAME ,
DISK_READS_PER_EXEC,
BUFFER_GETS ,
DISK_READS,
PARSE_CALLS ,
SORTS ,
EXECUTIONS ,
ROWS_PROCESSED ,
HIT_RATIO,
FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
CPU_TIME,
ELAPSED_TIME,
ADDRESS,
HASH_VALUE
FROM
(SELECT SQL_TEXT ,
B.USERNAME ,
ROUND((A.DISK_READS/DECODE(A.EXECUTIONS,0,1,
A.EXECUTIONS)),2)
DISK_READS_PER_EXEC,
A.DISK_READS ,
A.BUFFER_GETS ,
A.PARSE_CALLS ,
©2004
A.SORTS ,
A.EXECUTIONS ,
A.ROWS_PROCESSED ,
100 - ROUND(100 *
A.DISK_READS/GREATEST(A.BUFFER_GETS,1),2) HIT_RATIO,
A.FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
CPU_TIME,
ELAPSED_TIME,
ADDRESS,
HASH_VALUE
FROM
SYS.V_$SQLAREA A,
SYS.ALL_USERS B
WHERE A.PARSING_USER_ID=B.USER_ID AND
B.USERNAME NOT IN ('SYS','SYSTEM')
ORDER BY 3 DESC)
WHERE ROWNUM < 21
Version 8.1.x
SELECT SQL_TEXT ,
USERNAME ,
DISK_READS_PER_EXEC,
BUFFER_GETS ,
DISK_READS,
PARSE_CALLS ,
SORTS ,
EXECUTIONS ,
ROWS_PROCESSED ,
HIT_RATIO,
FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
ADDRESS,
HASH_VALUE
FROM
(SELECT SQL_TEXT ,
B.USERNAME ,
ROUND((A.DISK_READS/DECODE(A.EXECUTIONS,0,1,
A.EXECUTIONS)),2)
DISK_READS_PER_EXEC,
A.DISK_READS ,
A.BUFFER_GETS ,
©2004
A.PARSE_CALLS ,
A.SORTS ,
A.EXECUTIONS ,
A.ROWS_PROCESSED ,
100 - ROUND(100 *
A.DISK_READS/GREATEST(A.BUFFER_GETS,1),2) HIT_RATIO,
A.FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
ADDRESS,
HASH_VALUE
FROM
SYS.V_$SQLAREA A,
SYS.ALL_USERS B
WHERE A.PARSING_USER_ID=B.USER_ID AND
B.USERNAME NOT IN ('SYS','SYSTEM')
ORDER BY 3 DESC)
WHERE ROWNUM < 21
Version 7.x – 8.0.x
SELECT SQL_TEXT ,
B.USERNAME ,
ROUND((A.DISK_READS/DECODE(A.EXECUTIONS,0,1,
A.EXECUTIONS)),2)
DISK_READS_PER_EXEC,
A.DISK_READS ,
A.BUFFER_GETS ,
A.PARSE_CALLS ,
A.SORTS ,
A.EXECUTIONS ,
A.ROWS_PROCESSED ,
100 - ROUND(100 *
A.DISK_READS/GREATEST(A.BUFFER_GETS,1),2) HIT_RATIO,
A.FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
ADDRESS,
HASH_VALUE
FROM
SYS.V_$SQLAREA A,
SYS.ALL_USERS B
WHERE A.PARSING_USER_ID=B.USER_ID AND
B.USERNAME NOT IN ('SYS','SYSTEM')
ORDER BY 3 DESC
©2004
Kontakt:
Harry Flora
Embarcadero Technologies
[email protected]
©2004