SQLQC

Qualitätssicherung für dynamische und statische SQL

Zunehmend komplexer werdende Workloads erfordern neue Ansätze für SQL-Qualitätskontrolle und Performance-Tuning. Viele unterschiedliche SQL-Statements werden kontinuierlich durch die Systeme geschleust.  Es ist schwierig zu erkennen welche Statements Optimierungspotential haben oder ob der Ressourcenverbrauch gerechtfertigt ist.

Die meisten Anwendungen generieren SQL Statements erst kurz vor der Ausführung. Dies erfordert neue Methoden um die Statements in einer unbekannten und sich ständig verändernden Umgebung messbar und bewertbar zu machen. SQLQC steht für permanente SQL Qualitätskontrolle, er analysiert fortlaufend den aktuellen DB2-Workload und liefert Vorschläge für Verbesserungen.

Es geht nicht so sehr darum, zu erkennen, ob ein SQL-Statement zu langsam ist oder zu viel CPU verbraucht – diese Information liefern konventionelle Performance Monitore hinreichend und zuverlässig – sondern vielmehr darum, auf Anhieb zu verstehen, ob dies ein Problem ist: Wird es häufig oder selten ausgeführt? Falls es ein Problem darstellt, was ist die Ursache und wie kann es korrigiert werden? SQLQC formt aus Messdaten sinnvolle Informationen und macht konkrete Verbesserungsvorschläge. SQLQC gibt Antwort auf die häufige Frage, “Weshalb lief dieses Statement in der letzten Woche schneller, als heute?” Oder anders, “Was hat sich seitdem verändert?”.

Abbildung 1 zeigt historische Performancedaten eines bestimmten Statements. Wir haben drei Messpunkte, mit Performance relevanten Daten: CPU time, elapsed time, I/O, etc.

Abbildung 1 – History der Performancedaten

Abbildung 1 – History der Performancedaten

SQLQC stellt außerdem eine History mit Zugriffspfad relevanten Informationen bereit. Das macht die Analyse einfach, wenn es zum Beispiel darum geht herauszufinden welche Indexe vom Optimizer in der Vergangenheit genutzt wurden, was insbesondere dann nützlich ist, wenn es um die Diagnose von Performanceproblemen der dynamischen SQL geht, die mit Zugriffspfaden zu tun haben.

Figure 2 - Access path history

Figure 2 – Access path history

SQLQC steht für Qualitätskontrolle dynamischer und statischer SQL. Er vergleicht ständig Optimizer-Daten mit Messdaten prozessierter Daten und zeigt Ihnen zum Beispiel, ob die Laufzeit eines Stmt/einer Stmt-Gruppe x-mal höher war als die Optimizer-Schätzung, dass also die Statistiken (Runstats) nicht passen. Oder, dass wegen fehlender oder ungeeigneter Indizes erhebliche Diskrepanzen zwischen Examined Rows und Processed Rows bestehen.

SQLQC entdeckt ineffiziente SQL und hilft bei der Optimierung: Problematische Zustände wie RID Errors, Cache Trashing, Locking und andere werden erkannt. Ebenso wie Änderungen der Zugriffspfade, Runstats-Probleme, obsolete REORGs, usw.

SQLQC vergleicht die Kosten der abgefragten Zeilen über die Zeit. Eines der wichtigsten Kriterien dafür Kandidaten für eine Verbesserung zu finden ist die Anzahl der “getpages per processed rows”.

Diese Information offenbart Performanceprobleme, die durch Reorganisieren der untersuchten Objekte gelöst werden können. Zur gleichen Zeit werden damit Objekte identifiziert, bei denen ein Reorg nicht zu Performanceverbesserungen führen würde. Diese Information kann dazu genutzt werden unnötige Reorganisierungen zu vermeiden.

Abbildung 3 zeigt hierfür ein Beispiel: ein starker Anstieg der getpages per processed row. Dies könnte verschiedene Ursachen haben: Vielleicht ist ein Reorg überfällig, die Datenmenge massiv gewachsen, oder der Zugriffspfad hat sich geändert.

Es ist leicht herauszufinden, ob die Datenmenge gewachsen ist. Hingegen herauszufinden, ob sich der Zugriffspfad geändert hat, ist ohne angemessenes Werkzeug schon schwieriger. Die historischen Informationen der Zugriffspfade, die im SQLQC parat liegen, können diese Änderungen unmittelbar identifizieren.

Abbildung 3 – Getpages per processed row

Abbildung 3 – Getpages per processed row

SQLQCs Komponente INDEX ADVISOR hilft aktiv beim Tuning des SQL-Workloads. Auswertungen der SQL-Statements können vorgenommen werden, beispielsweise nach Top Elapsed Time, meist ausgeführte SQL oder Top CPU User. Eine History ermöglicht es die Entwicklung der einzelnen Statements über einen bestimmten Zeitraum zu verfolgen, um so periodisch auftauchende Engpässe zu erkennen. Durch eine Simulation ist es möglich Statements in der Entwicklung zu testen, bevor diese in Produktion gehen, oder sogar eigene Statements auf ihr Verhalten hin zu untersuchen.

Abbildung 4 ist ein Beispiel für eine Index Empfehlung. Der Index Advisor wird nicht nur zur Analyse der Auslastung genutzt. In diesem Fall hat er festgestellt, dass zwei Felder teil eines Index sein sollten, es aber nicht sind.

Der Benutzer kann SQL Statements direkt auf der Arbeitsfläche von SQLQC anpassen und testen und bekommt unmittelbar Hinweise zu den Indexen. Dies funktioniert für Statements, die Teil des aktuellen Workloads sind, als auch für Statements, die ad hoc vom Nutzer erstellt werden.

Abbildung 4 – Index Empfehlungen

Abbildung 4 – Index Empfehlungen

SQLQC analysiert den Workload automatisch und prüft die SQL Statements auf fehlende oder falsche Indexe:

  • Fehlende Indexe
  • Fehlende Spalten in Indexen
  • Falsche Reihenfolge der Spalten in Indexen
  • Stage 2 Attribute
  • und anderes

Abbildung 5 zeigt wie SQLQC den Zugriffspfad für ein neues Statement festlegt, z.B. Ein Statement für das keine historischen Informationen verfügbar sind. Index Empfehlungen werden im gleichen Ablauf generiert, der in Abbildung 4 gezeigt wird. Diese Informationen sind mit einem Klick verfügbar.

Bild5-768x180

Abbildung 5 – Zugriffspfad Darstellung

Alle negativen Funde können unmittelbar untersucht werden: Nutzer können virtuelle Indexe direkt in der SQLQC Arbeitsfläche anlegen und diese testen, bevor sie in Produktion gehen. Die Erstellung neuer virtueller Indexe ist sehr einfach. SQLQC macht einen Vorschlag für die Definition eines neuen Index, der vom Nutzer entweder akzeptiert oder nach eigenen Anforderungen angepasst werden kann.

Abbildung 6 zeigt den Ablauf der Erstellung eines virtuellen Index in SQLQC. Die Eingabefelder werden entweder mit von SQLQC empfohlenen Werten vorausgefüllt, oder nutzen den Standardwert, der durch die Nummer -1 angegeben wird. Alle Attribute können vor der Erstellung des virtuellen Index verändert werden.

Bild6-768x577

Abbildung 6

Der SQLQC im Vergleich zu konventionellen Monitoren

Wir wollen mit dem SQLQC keine Konkurrenz zu bestehenden Monitoren aufbauen. Die Zielsetzung ist vielmehr das fortlaufende Überwachen, Beurteilen und Verbessern der Anwendungsperformance. Das Augenmerk liegt dabei auf automatisierter Erkennung, Benachrichtigung und Anleitung bei der Problembehebung. Ein klassischer Monitor zeigt Informationen über das Verhalten eines Threads – wie lange läuft der Thread, wo verbringt er seine Zeit, also IO Waits, CPU Verbrauch, Lock Waits, usw. Zusätzlich erhält man Informationen über die ausgeführten SQL. Solange kein SQL Trace aufgesetzt wird, sieht man zwar die SQL-Statements, aber eben nur kurz “aufblitzen”. Welche SQL letztendlich die Probleme darstellen, und vor allen Dingen warum erkennt man nur nach Aufschalten zusätzlicher Traces (falls der Monitor überhaupt Auswertungen dafür vorsieht). Die Betrachtung erfolgt also ausgehend vom Thread, mit den einzelnen Verweilzeiten im Thread. Die Analyse ist manuell, fordert Einsatz und Konzentration des Experten.

SQLQC geht andere Wege

Im Mittelpunkt der Betrachtung steht das einzelne SQL. SQLQC beantwortet bspw. die Frage “Wo in meinem Workload stecken die echten Problemfälle?”. Dazu gehören zum Beispiel schwer zu findende Tablespacescans, die durch Engpässe im RidPool passieren (Riderrors). Weiteres in Stichpunkten: SQ-Statements bei denen falsche Zugriffspfade genommen werden, weil die Runstatsinformationen nicht korrekt sind, Zugriffspfadprobleme durch ineffiziente Indices, plötzliche Veränderungen von Zugriffspfaden nach Runstats, Einspielen von Wartung in DB2, etc., Vergleichsmöglichkeiten der Optimizerschätzungen mit den tatsächlichen Verbrauchsdaten über längere Zeiträume (History), Verschiebung von Sync IO auf Async IO hilft bei der Erkennung von Housekeeping Problemen, also fehlenden Reorgs; durch Gruppierung von Statements ohne Parametermarker zu Statementgruppen erkennt man endlich auch hohe CPU Verbräuche vermeintlich harmloser SQL.
Diese Beispiele zeigen die Ausrichtung von SQLQC auf automatische Erkennung problematischer SQL mit gleichzeitiger Einordnung, d.h. einfache aber klare Kriterien zeigen auf, wo, welches Tuningpotential liegt. Man greift auf vorbereitete Auswertungen zurück, die durch eine klare Klassifizierung die mögliche Lösung gleich mitbringen: Runstats, Reorg, Zugriffspfad, RidPool …

Abbildung 7 zeigt die verschiedenen Analysefunktionen im SQLQC. Jeder Bericht nutzt vordeffinierte Kriterien um die “top” Statements im ausgewählten Bericht zu finden. Die Top-Statements in einem Bericht sind typischerweise die Statements, die untersucht werden sollten.

SQLQC zeigt nicht einzelne Ausführungen eines Statements. Stattdessen bildet SQLQC Gruppen der Statements, die syntaktisch identisch sind, selbst wenn diese ohne Parameter Marker laufen. SQLQC weist jedem Statement einen einzigartigen Schlüssel zu, der es ermöglicht das Statement über die Zeit zu verfolgen.

Abbildung 7

Abbildung 7

In einem typischen DB2 Subsystem werden jede Stunde tausende von Statements ausgeführt. Die Herausforderung besteht darin die Statements zu identifizieren, die verbessert werden können. SQLQC stellt eine History aller Ausführungen der Statements zur Verfügung. Diese Informationen können für einige Berichte genutzt werden:

  • Getpage per processed row
  • Ineffiziente Suchkriterien
  • Lock time
  • RID Probleme
  • Bind Probleme, wie repeatable read
  • Fehlende Indexe
  • und viele andere

Diese Beispiele zeigen die Hauptausrichtung von SQLQC: Er erkennt und klassifiziert automatisch problematische SQL Statements. Einfache, klare Kriterien fließen in genaue Bewertungen ein und zeigen wo und welche Art von Tuningpotential besteht. Die klare Einordnung führt simultan zur möglichen Lösung: Runstats, Reorg, Zugriffspfad, RID pool, usw.

Impact Analysis

“Virtual Index” erlaubt es Änderungen an den Indexen zu simulieren ohne diese tatsächlich zu erstellen. Wer sich mit dem Thema SQL Tuning beschäftigt, kennt das Problem: ein neuer Index mag zwar die eine oder andere Query beschleunigen, doch die Wirkung auf die Gesamtheit der SQL bleibt unbekannt. Noch komplizierter wird die Antwort, wenn man das Löschen vermeintlich überflüssiger Indices untersucht. Manuell sind solche Analysen bei der Menge heutiger Workloads kaum noch machbar.

Wie wirkt sich das Löschen oder Anlegen eines Index auf das Gesamtsystem aus? Steigt oder fällt der CPU-Verbrauch meines Workloads? Diese Fragen lassen sich mit dem Index Advisor unmittelbar beantworten, ohne die Indices tatsächlich anlegen zu müssen. Die Empfehlungen des Index Advisor lassen sich sofort simulieren und so die Auswirkungen auf ein einzelnes Statement oder sogar auf die gesamte Umgebung unmittelbar feststellen.

Abbildung 8 ist ein Beispiel für die “Impact Analyse”

Die Impact Analyse simuliert das Verhalten eines bestehenden Workloads mit virtuellen oder modifizierten Indexen. SQL kann die Ergebniss für den gesamten Workload und für jedes einzelne Statement analysieren ohne irgendein reales Objekt zu ändern.

Bild8-768x364

Abbildung 8

Die Impact Analyse basiert auf virtuellen Indexen. Jede Änderung kann simuliert werden, egal ob es eine Erstellung, Änderung oder Löschung eines Index ist.

SQLQC berechnet das Setup mit dem neuen oder geänderten Index neu. Dies ist eine große Hilfe, wenn es darum geht die finalen Ergebnisse einer Indexänderung abzuschätzen. Als Beispiel kann man sich einen Index vorstellen, bei dem eine Schlüsselspalte von Position 4 auf Position 2 gemäß dem Vorschlag des Index Advisors verschoben wird. SQLQC simuliert die Auswirkungen dieser Änderungen auf den Workload und ermittelt, ob der Gesamteffekt positiv oder negativ ist.

Auf der höchsten Ebene zeigt die Impact Analyse die zu erwartenden totalen CPU-Kosten für den ausgewählten Workload an. Es ist möglich bis auf Statement Level herunter zu gehen, wo SQLQC prognostiziert, wie sich die Index-Änderung auf den CPU-Verbrauch jedes einzelnen Statements auswirkt.

SQL History

SQLQC ist ein Tuning–Werkzeug, das nicht nur den DBA bei der täglichen Arbeit unterstützt, sondern auch für Anwendungsentwickler von Interesse ist. Der Anwendungsentwickler erhält mit dem SQLQC ein Werkzeug, mit dem er täglich die Qualität seiner SQL Statements untersuchen kann. SQLQC bietet eine Sammlung aller ausgeführten SQL über einen beliebig langen Zeitraum, so wird es möglich die Entwicklung der SQL über die Zeit darzustellen (CPU, Laufzeit, IO, etc.). Veränderungen im Zugriffspfad eines SQL werden erkannt, auch wenn Monate dazwischen liegen. Die Sammlung der Daten erfolgt ohne den Einsatz kostspieliger Traces, so entsteht kein zusätzlicher Overhead. Die SQL werden zudem im Hinblick auf ihre Class 3 (Wait Zeiten) genau aufgeschlüsselt.

Die History Funktion ist in Berichten und in graphischer Form verfügbar.

Abbildung 9 zeigt ein Beispiel für einen History-Bericht. Diese Berichte können als PDF heruntergeladen und für spätere Vergleiche archiviert werden. Verschiedene Typen historischer Vergleiche sind verfügbar: CPU Verbrauch, getpage, Index-Benutzung (wie in Abbildung 9 gezeigt), und viele andere.

Abbildung 9

Abbildung 9

Die Berichte zeigen welche Indexe von DB2 verwendet wurden, während die graphischen Diagramme die Änderungen der Elapsed Time für ein beliebiges Statement über die Zeit zeigen (Abbildung 10).

Die Kombination der SQLQC Berichte in Text- und Diagrammform (Abbildung 10) stellt ein komplettes Bild der History eines Statement zur Verfügung.

Abbildung 10 (graphische Ansicht der Performancedaten)

Abbildung 10 (graphische Ansicht der Performancedaten)

Diese Informationen werden mit der History der Zugriffspfade kombiniert. Das bedeutet, dass vollständige Information verfügbar sind für:

  • Das zu analysierende Statement
  • Die historischen Daten des Statements (CPU time, elapsed time, lock , I/O, . ….)
  • Historische Informationen über den Zugriffspfad

SQL Gruppierung

SQLQC analysiert tausende von SQL Statements, und liefert durch eine Gruppierung genau die Gruppen von SQL, die ein vergleichbares Muster haben. Dadurch können auch kurze, schnelle SQL in die Optimierungsvorhaben aufgenommen werden.

Abbildung 11 stellt die Gruppierungstechnik im SQLQC dar. Statements werden normalisiert, das bedeutet, dass Konstanten entfernt werden – in diesem Fall die Sting Symbole in der WHERE Bedingung.

Der verbleibende Teil des Statements wird dazu verwendet einen Hash Key zu erstellen, der SQLQC erlaubt das Statement über die Zeit zu identifizieren. Mit anderen Worten, wenn das Statement in der Zukunft erneut ausgeführt wird, bekommt es den gleichen Hash Key, selbst wenn unterschiedliche String Symbole genutzt werden.

Abbildung 11 – Statement Gruppierung

Abbildung 11 – Statement Gruppierung

Aus dem Blickwinkel von DB2 sind dies zwei unterschiedliche Statements. Die Gruppierungsfunktion von SQLQC entfernt Konstanten und normalisiert das Statements. Dies ermöglicht SQLQC das Statement über die Zeit zu verfolgen.

Zoom Funktion

Die Zoom Funktion innerhalb der graphischen Oberfläche erleichtert die Auswertung von Relationen. Unter anderem liefert SQLQC hierbei weitere Informationen. Der Nutzer ist in der Lage ad hoc und ressourcenschonend herauszufinden, welche Tabellen von welchen SQLs aufgerufen wurden. Die graphische Workstation Komponente listet alle relevanten Informationen übersichtlich auf.

Die automatisierte und fortlaufende Überprüfung der statischen und dynamischen SQL der verschiedenen Umgebungen unterscheidet den SQLQC von konventionellen Performance-Monitoren. Das Tool erleichtert das Performance-Tuning, spart die mühsame Analyse von Performance-Traces, Runstats, Index Inspektionen usw. Komplexe Fragen und versteckte Probleme können durch wenige Mausklicks beantwortet werden. SQLQC unterstützt Datenbankadministratoren und Anwendungsprogrammierer bei der Gewährleistung der Qualität in allen Entwicklungs- und Rollout-Zyklen, sowie bei der fortlaufenden Überwachung der Produktion.

Abbildung 12 ist ein Beispiel für einen bestimmten Typ des Zoomings in SQLQC. Andere Monitore stellen üblicherweise SQL Statements, Nutzer oder Packages dar. Mit SQLQC ist es möglich Statements auf Tabelleneben zu untersuchen. Dies ermöglicht einen Einblick in Fragen wie welche Tabellen für den größten Teil des CPU Verbrauchs verantworlich sind.

Abbildung 12 - Zooming

Abbildung 12 – Zooming

Die Zoom Funktion ermöglicht dem Nutzer die Berichterstellung für beliebige Objekttypen:

  • Users
  • Tables
  • Statements
  • Packages

Dies kann mit Filtern kombiniert werden. Zum Beispiel ist es möglich herauszufinden welche Nutzer auf eine bestimmte Tabelle in einem speziellen Zeitraum zugegriffen haben und welche Statements gegen die ausgewählte Tabelle ausgeführt wurden.

Ein einfacher Klick bringt mehr Details über das ausgewählte Objekt.

  • Video