Find the queries and indexes that cost you time

Die Meldung ist nie präzise

„Der Nachtlauf hat vier Stunden gebraucht statt vierzig Minuten." „Die Patientensuche hängt." „Gestern war es schnell." Niemand reicht Ihnen ein Statement, einen Plan und eine Zeilenzahl. Die Arbeit zerfällt deshalb in vier Schritte, und die Werkzeuge auf dieser Seite folgen ihnen: die Ursache finden, sie belegen, sie beheben und nachweisen, dass die Behebung das war, was sich geändert hat.

1. Ursache finden — das Schema prüfen, in den Cache sehen, den Plan lesen.
2. Belegen — das Statement messen und sehen, wo die Zeit hingeht.
3. Beheben — Statistiken, ein Index oder eine gelöschte Cached Query.
4. Nachweisen — noch einmal messen und vergleichen.

Schritt 1: die Ursache finden

Den Namespace auf Schemaprobleme prüfen

Ein Druck auf Start scanning führt jede Prüfung aus und füllt jeden Reiter. Die Befunde sind über alle Reiter hinweg nach Schwere sortiert, ein Fehler geht also nie unter dreißig Warnungen unter, und jeder Befund trägt eine Begründung und — wo eines erzeugt werden kann — ein vorgeschlagenes Statement zum Kopieren oder Öffnen im Editor. Der Scanner selbst führt nie etwas aus.

Aus dem Katalog

• Fremdschlüssel ohne Index. Ein referenzieller Constraint ohne Index auf der referenzierenden Spalte. Jedes Löschen in der Elterntabelle wird damit zu einem vollständigen Scan der Kindtabelle — die klassische Ursache für ein Löschen, das Minuten braucht. Als indiziert gilt ein Schlüssel nur, wenn ein Index seine Spalten als führende Spalten trägt; zusammengesetzte Schlüssel werden als Ganzes bewertet. (Diese Prüfung lieferte auf IRIS und Caché bis 4.00 überhaupt nichts. Korrigiert findet sie in der mitgelieferten AdventureWorks-Sample 40 nicht indizierte Fremdschlüssel, wo die alte keinen fand.)
• Redundante Indizes. Ein Index, dessen Spaltenliste das Präfix der Spaltenliste eines anderen Index ist. Er kostet bei jedem Insert und Update und bringt beim Lesen nichts. Eindeutige Indizes, Indizes mit eigenen Datenspalten und die strukturellen IRIS-Indizes bleiben außen vor, denn die zu verwerfen ist nicht dieselbe Frage.
• Veraltete oder fehlende Tabellenstatistiken. Tabellen, deren Optimizer-Statistiken nie erhoben wurden, älter als 90 Tage sind, durch geänderte Zeilen entwertet wurden oder aus einer zu kleinen Stichprobe stammen — dazu Tabellen mit abgeschalteter automatischer Erhebung und Indizes, die definiert, aber nie gefüllt wurden.
• Empfehlungen zum Indextyp für IRIS. Indextypen sind über JDBC unsichtbar, sie werden deshalb aus dem Klassenverzeichnis gelesen und gegen die Kardinalität der Spalte gehalten: Standardindizes auf Spalten geringer Kardinalität, die Bitmaps sein wollen; Bitmaps auf Spalten hoher Kardinalität; Bitmap-Indizes ohne Bitmap-Extent-Index; und Indizes, die der Optimizer überhaupt nicht wählen kann.

Aus den Plänen und Zählern des Servers

Seit 4.01 liest der Scanner die Ausführungspläne, die der Server ohnehin gebaut hat, und die Zähler, die er ohnehin geschrieben hat, statt aus dem Statement-Text zu schätzen. Damit wird aus einem Ratschlag eine Messung:

• eine große Tabelle, die gecachte Pläne von vorn bis hinten durchlaufen, statt einen Index zu nutzen — sortiert nach gemessener Serverzeit;
• ein Index, den kein gecachter Plan liest, der aber bei jedem Schreibvorgang gepflegt wird;
• ein eingefrorener Plan;
• ein Prädikat, für das ein Plan einen vorhandenen Index nicht nutzen kann;
• bis zu drei Vorschläge für fehlende Indizes je Tabelle, aus dem echten Abfragemuster statt aus einer allgemeinen Regel;
• eine Sortierung, die in eine temporäre Datei ausweicht.

Auf PostgreSQL, SQL Server und MariaDB/MySQL kommen die Befunde zu ungenutzten und ungelesenen Indizes ebenfalls aus den Nutzungszählern dieser Server — das ist keine InterSystems-exklusive Fähigkeit mehr.

Aus echten Daten, im Profil Deep

Die tiefste Stufe zieht Stichproben aus den Zeilen, und sie existiert, um die Fragen zu klären, die eine Schätzung nicht klären kann: ein Bitmap-Index, durch tatsächliches Zählen bestätigt oder ausgeschlossen; ein einzelner Wert, der eine Tabelle allein trägt, sodass kein Index hilft; und eine echte Abweichung zwischen der Statistik, gegen die der Optimizer plant, und den Daten selbst. Der letzte Befund wird nur gemeldet, wo die Zahlen ihn wirklich belegen — eine Stichprobe kann zeigen, dass die Statistik zu wenige Distinct-Werte behauptet, die andere Richtung braucht eine vollständige Zählung.

Die Schaltfläche Profile neben Start scanning wählt zwischen den dreien: Quick (nur Kataloge, auf Produktion unbedenklich), Standard (dazu Pläne und Zähler) und Deep (dazu Datenstichproben). Wo der Scanner eine Frage nicht sicher beantworten kann oder nichts vorfindet — ein älterer Server, eine Instanz, deren Ausführungszähler noch nicht geschrieben wurden —, sagt er das auf dem Reiter Scanned Objects, der jede geprüfte Tabelle auflistet, die als Systemobjekte ausgeschlossenen Schemata und jede Prüfung, die nicht laufen konnte, mit Grund. Er zeigt Ihnen kein sauberes Ergebnis, das er sich nicht verdient hat.

Ansehen, was IRIS gecacht hat

InterSystems IRIS plant ein Statement nicht bei jeder Ausführung neu. Beim ersten Prepare baut es einen Plan und legt ihn als Cached Query ab, geschlüsselt auf den Statement-Text ohne Literale. Jede spätere Ausführung dieses Statements benutzt den gespeicherten Plan wieder.

Deshalb ist „gestern war es schnell" eine echte Diagnose und kein Achselzucken. Der Plan entstand, als die Tabelle achttausend Zeilen hatte, und der Optimizer entschied sich völlig zu Recht für einen Scan. Heute hat die Tabelle vier Millionen Zeilen. Das Statement hat sich nicht geändert, die Daten schon, und dazwischen steht der eingefrorene Plan. Nichts im Abfragetext verrät Ihnen das — Sie müssen in den Cache sehen.

Der Cached Query Analyzer listet die Cached Queries eines Namespace mit ihrem Statement-Text, ihren Ausführungszahlen und der Zeit, die sie gekostet haben — die teuren sortieren sich also nach oben. Aus derselben Liste löschen Sie eine einzelne Cached Query oder einen ganzen Satz, womit die nächste Ausführung gegen die heutigen Statistiken neu vorbereitet.

Er liegt unter Performance > Cached Queries und unter jedem Namespace-Knoten, und er ist im Wortsinn je Namespace: Öffnen Sie ihn für drei Namespaces, bekommen Sie drei Listen, jeder Reiter mit dem zugehörigen Namespace beschriftet, jede Anzahl passend zum eigenen Raster, und Delete Cached Query wirkt auf den, den Sie gerade ansehen. Der SQL Scanner hat dafür keinen eigenen Reiter Cached Queries mehr — seine Befunde aus dem Cache waren immer Workload-Befunde, und die stehen vollständig im Reiter Findings, sortierbar nach Schwere und nach Anteil an der gemessenen Last.

Den Ausführungsplan lesen

Pläne werden im SQL-Editor selbst angezeigt, neben dem Statement, das sie erzeugt hat. Sie kopieren die Abfrage nicht in ein zweites Werkzeug, um dort zu erfahren, dass die Schleife, die Sie für indexgetrieben hielten, ein vollständiger Scan der äußeren Tabelle ist.

Schritt 2: mit Zahlen belegen

Die Kosten jedes Statements, während Sie es ausführen

Seit 4.01 kann der SQL-Editor IRIS-Ausführungsstatistiken neben Zeilenzahl und verstrichener Zeit zeigen: Global-Referenzen, ausgeführte Kommandos, Disk Reads mit ihrer Latenz und den Namen der generierten Cached-Query-Klasse. Global-Referenzen sind auf IRIS die Zahl, auf die es am meisten ankommt, weil sie sind, was die Arbeit tatsächlich kostet: Zwei Statements, die beide „1,2 Sekunden brauchen", sind nicht dasselbe Statement, wenn eines davon vierzigmal so viele Globals angefasst hat.

Der Name der Cached-Query-Klasse ist ein Link: Ein Klick öffnet diese Abfrage im Cached Query Analyzer, ein Rechtsklick bietet den generierten Klassenquelltext, den Abfrageplan, eine MONLBL-Kommandozeile oder das Löschen an. Bei einem Skript bekommen Sie die Summe und eine Zeile je Statement im Reiter Messages, was „welches Statement war das teure?" beantwortet, ohne das Skript von Hand zu halbieren. Die Zahlen wandern außerdem als sortierbare Spalte Global refs in die SQL History und in deren CSV-Export, der heutige Lauf lässt sich also mit dem der letzten Woche vergleichen. Funktioniert auf IRIS und Caché.

Das Statement mit PTools messen

Ein Plan sagt Ihnen, was der Optimizer vorhat. Messen sagt Ihnen, was tatsächlich passiert ist. SQL DATA LENS steuert die eigene PTools- und PStats-Instrumentierung von IRIS: Führen Sie das Statement unter dem Profiler aus, und Sie bekommen verarbeitete Zeilen, Global-Referenzen und Zeit je Modul statt einer Gesamtzahl, die Sie raten lässt, welcher Teil des Plans teuer war.

Das ist der Schritt, der Sie davor bewahrt, das Falsche zu optimieren. Ein Plan mit einem hässlich aussehenden Scan über eine Lookup-Tabelle mit 300 Zeilen ist nicht Ihr Problem; eine ordentliche Index-Schleife, 1,2 Millionen Mal ausgeführt, schon.

Schritt 3: beheben

Statistiken — und warum neue Statistiken nicht wirken

Der Optimizer plant gegen Tabellenstatistiken: Extent-Größe, Selektivität jedes Feldes, Blockzahlen. TUNE TABLE misst diese an den aktuellen Daten. Das lohnt sich, nachdem eine Tabelle ihr Volumen um eine Größenordnung verändert hat, nach dem ersten echten Datenladen auf einem neuen System und nach einer Migration, die Daten mit Standardstatistiken hereingeschoben hat.

Und dann kommt der Teil, über den alle stolpern. Neue Statistiken erreichen einen bereits gebauten Plan nicht. Die Cached Queries halten weiter Pläne aus den alten Zahlen, dasselbe Statement bleibt also genauso langsam, und es sieht aus, als hätte das Tunen nichts bewirkt.

Dafür gibt es %RECOMPILE_CQ. Mit dieser Option kompiliert Tune Table die Cached Queries neu, die von den gerade gemessenen Tabellen betroffen sind, damit die neuen Statistiken auch benutzt werden. Das Werkzeug bietet die Option beim Start eines Tune-Table-Laufs an, neben der Wahl zwischen einer einzelnen Tabelle und dem ganzen Schema.

Es gibt einen zweiten Grund, warum eine Tuning-Sitzung wirkungslos aussieht, und die Ansicht zur Feldselektivität benennt ihn jetzt. Neuere IRIS-Versionen halten getunte Statistiken außerhalb der Klassendefinition; die Spalte Notes sagt, woher jeder Wert stammt — eine Messung aus Tune Table oder ein in der Klassendefinition festgeschriebener Wert, der eine neuere Messung überstimmt. Der zweite Fall ist die übliche Antwort auf „ich habe getunt, und nichts hat sich geändert", und aus den Zahlen allein ist er nicht zu sehen.

Indizes

Aus den Scan-Befunden sind die beiden häufigen Korrekturen: den Index hinter einem nicht indizierten Fremdschlüssel anlegen und einen Index verwerfen, den ein anderer bereits abdeckt. Beides ist DDL, beides ist Ihre Entscheidung, und beides will aus demselben Grund wie oben anschließend eine Neukompilierung der Cached Queries.

Wie groß ist diese Tabelle eigentlich?

SELECT COUNT(*) auf einer Tabelle mit 400 Millionen Zeilen ist kein Weg, die Größe einer Tabelle zu erfahren. Es ist ein Weg, die Instanz einige Minuten zu beschäftigen und eine Zahl zu bekommen.

Stattdessen liest SQL DATA LENS die Speicherseite direkt: auf welche Globals eine Tabelle abgebildet ist, wie viel Platz diese Globals belegen und in welcher Datenbank der Speicher tatsächlich liegt — was nicht immer die Datenbank ist, die Sie annehmen, sobald Subscript-Level-Mapping im Spiel ist. Damit haben Sie die zwei Fakten, die ein Performance-Gespräch früh braucht: die echte Größe der Daten und wo sie auf der Platte liegen.

Wenn Sie eine Ebene tiefer gehen und die Global-Inhalte selbst lesen wollen, ist das der Global Browser. Für die Prozess-, Lock- und Log-Seite eines langsamen Systems — wer gerade wen blockiert — siehe Administration.

Schritt 4: nachweisen

Messen Sie dasselbe Statement erneut und vergleichen Sie es mit dem Lauf, den Sie aus Schritt 2 aufgehoben haben. Prüfen Sie, ob sich der Plan so geändert hat, wie Sie es erwartet haben, und ob die gelöschte Cached Query neu gebaut wurde. Eine Behebung, die Sie nicht vorführen können, ist eine Behebung, nach der Sie im nächsten Quartal wieder gefragt werden.

Auf Ihrem eigenen Namespace ausprobieren

Richten Sie den Scanner auf einen Namespace, den Sie ohnehin verdächtigen, und lesen Sie die Befunde — diese erste Liste reicht meist, um zu wissen, ob der Rest dieser Seite für Sie relevant ist. Der Scanner braucht eine kostenpflichtige Lizenz, und der Download enthält einen vollen 30-Tage-Test davon, ohne Registrierung. Pläne, der Cached Query Analyzer, PTools und die Ausführungsstatistiken sind in jeder Edition enthalten, mit Test oder ohne.

SQL DATA LENS für Windows herunterladen →