Optimale SQL Indexe

[manuel.marcos]

Da könnte dir das SQL Perfomance Center > Show Statements im ACS weiterhelfen. Dort kannst du dir u.a. die durschnittliche Perfomance von Statements, sowie im Detail anzeigen lassen. Außerdem kannst du dir da die Statements auch "erklären" lassen und die Empfehlungen dazu ausgeben lassen.

Gruß,
Manuel

[Fuerchau]

Das Thema heißt eher "Visual Eplain". Dies kann man vom alten iSeries Navigator, oder wenn du bereits ACS hast über SQL-Script ausführen (Menü Aktionen in 5250-Sitzung), aufrufen.
Dieser gibt dir eine Schätzung der möglichen Indizes.
Allerdings haben mir die Ausgaben einer Debugsitzung da schon oft weitergeholfen:

strdbg
strsql
select * from myview where ....

Ansehen, was da so im Joblog steht.
Allerdings: In 1% der Fälle wird der vorgeschlagene Index dann gar nicht genommen.

Indizes ist das Eine, eine qualifizierte Abfrage von Feldern ohne Cast bringt dich da auch oft weiter.
Wenn bei gewachsenen Systemen die Felder z.T. unterschiedlich definiert sind (char vs nchar, decimal vs zoned, decimal vs integer, usw.).
Statt eines simplen Joins "on a.f1 = b.f1" hilft da schon mal, das Herkunftsfeld zu casten: "on dec(a.f1, n, m) = b.f1".

[B.Hauser]

SQL Performance Optimierung ist nicht ganz so einfach auch wenn wir inzwischen im SQL Perfomance Center gute Tools haben, musst Du verstehen wie SQL arbeitet, um dann auch die richtigen Indices zu finden.

Der Index-Advisor hilft schon weiter. Wenn Du allerdings noch nie in den Advisor reingeschaut hast (was ich vermute) sind da sämtliche von beiden Query Engines empfohlene Indices seit Release V5R2M0 (bzw. seit der Einführung des Index Advisors).

Der Index-Advisor kann im übrigen auch gecleart werden. Am Besten machst Du mit ACS (Access Client Solutions) alles platt, wartest 2-3 Wochen und schaust nochmals rein.
Im Übrigen können auch über ACS die SQL-Statements, für die die Empfehlung ausgesprochen wurde (incl. der verwendeten Variablen) angezeigt und geöffnet werden (zumindest solange kein IPL gefahren wurde).

Des weiteren kannst Du Dir im Plan Cache alle Access Pläne für alle SQL Statements, die seit dem letzten IPL ausgeführt wurden anzeigen, incl. der durchschnittlichen und längsten Laufzeit anzeigen lassen. Die problematischen SQL-Statements kannst Du dann einzeln greifen, aufrufen, analysieren (mit Visual Explain), neue Indices erstellen und im Anschluss das SQL-Statement erneut ausführen.

Ansonsten solltest Du Dir mal die Indexing und Statistics Strategy von Mike Cain und Kent Milligan reinziehen.

Es gibt eigentlich nur 2 Schrauben an denen man für eine bessere SQL Perforance drehen kann:
1. an der Syntax (wird das SQL-Statement so geschrieben, kann kein Index verwendet werden, wird es anders geschrieben kann der Optimizer einen Index verwenden)

2. Erstellen der optimalen Indices.
Dabei sollte man allerdings nicht nur "normale" Binary Radix Tree Indices im Auge haben, sonder auch derived und/oder sparse Indices und natürlich auch Encoded Vector Indices.

Birgitta

[Fuerchau]

Nun ja, wenn man sich das so durchliest, sind EVI's eher kontraproduktiv, da sie für Joins nicht nutzbar sind und sehr viel Platz benötigen.
Ich bin auch mit den bisherigen Methoden ganz gut gefahren. Ich habe nur selten Queries gesehen, die nicht mit Joins umgehen müssen. Auf die Typisierung bei der Abfrage wird gar nicht eingegangen.
Da habe ich schon festgestellt, dass die automatische Anpassung (Cast) seit V6 z.T. schlechter läuft als vorher. Der Optimizer castet eher den Feldinhalt zum Abfragewert als umgekehrt, was wiederum eine Indexverwendung verhindert. Beispiel, XFeld ist vom Typ CHAR(3):

Select * from mytable where XFeld = 001

Bis V5R4:
Select * from mytable where XFeld = cast(001 as char(3))

Seit V6R1:
Select * from mytable where cast(XFeld as decimal(3, 0)) = 001

Wir haben das nur bemerkt, dass bei der damiligen Umstellung auf V6 plötzlich Queries/SQL's nicht mehr funktionierten (XFeld hatte schon mal nicht numerische Werte) oder extrem langsam wurden (keine Indexnutzung), nur weil
a) die Programmierer die Hochkommas vergessen hatten
b) die IBM den Cast umgedreht haben

Also auch hier kann man dem Optimizer helfen, wenn man Vergleiche (Join/Where) korrekt selber per Cast anpasst um Indexnutzung zu erreichen.

[B.Hauser]

@Baldur:
Wer außer Dir behauptet denn, dass EVIs sehr viel Platz benötigen?
EVIs sind im Gegenteil sehr viel kompakter als Binary Radix Tree Indices
... außerdem werden Binary Radix Tree Indexes nur verwendet, wenn max. ca. 15% der Daten einer Datei gelesen werden. EVIs decken den Bereich >15% bis ca 80% der Daten ab.
Des weiteren können in den Encoded Vector Indices Aggregat-Informationen gespeichert werden, so dass z.B. beim richtigen EVI mit den richtigen Aggregat-Informationen und der richtigen Gruppierung (Group By) die Daten direkt aus dem Symbol Table des EVI ausgelesen werden können, d.h. es ist also kein Zugriff auf eine Tabelle notwendig (EOA - Encoded Vector Index Only Access).

Außerdem ist der Query Optimizer in der Lage für eine Abfrage für ein und die selbe Datei mehrer Indices zu verwenden (Index-ANDING und Index-ORING)

Im Übrigen sollte man natürlich sauber kodieren und nicht die IBM für Schlampereien verantwortlich machen wollen.
Wenn ein Feld alphanumerisch definiert ist, sollte man in der Where-Bedinung die Konstante in Hochkommata setzen bzw. in einem Programm auch die Host-Variable entsprechend definieren, auch dann wenn die Kunden-Nr. immer nummerisch ist.

[Fuerchau]

Bzgl. der EVI's hast du ja den Link auf das Dokument mitgeteilt. Da steht immerhin drin, dass EVI's sehr viel Platz benötigen und somit u.U. ineffektiv werden können. Von alleine wäre ich da nie drauf gekommen.

Und was das Beispiel angeht, so wollte ich damit nur auf das Umdrehen des Cast hinweisen, der zur Nichtverwendung von Indizes führt, nachweislich.

[mkoni]

Vielleicht noch eine kurze Erklärung warum das derzeit für mich wichtig ist.
Wir arbeiten gerade an einem ERP Upgrade. Dadurch ändern sich die verwendeten Biliotheken (Schemas) und in vielen Fällen auch die Tabellen bzw. deren Felder.
Das führt dazu das wir so ziemlich alle WebQuery Reports, bzw. die SQL-Views die dafür verwendet werden, überarbeiten bzw. anpassen müssen. (und das sind wirklich viele)
Wenn wir schon fast alles "neu" machen dann möchte ich es soweit möglich auch verbessern.
Es gibt schon jetzt SQL-Views die für WebQuery eine zu lange laufzeit haben (führt im WebQuery zu timeouts), daher werden die Ergebnisse dieser Views in Nacht-Jobs mittels Prozeduren in Tabellen gespeichert und diese dann für die Auswertungen verwendet. Aber das lässt sich wegen untershiedlicher Anforderungen nicht immer machen.

lg michael

[BenderD]

... aus meiner praktischen Erfahrung gibt es da ein paar Grundregeln:
- Anforderungsanalyse: Was darf wie lange dauern.
- den (eigenen und maschinellen) Aufwand begrenzen auf das, was die Anforderungen nicht erreicht.
- Laufzeiten messen statt schätzen oder schätzen lassen (STRDBMON ist Dein Freund)
- bei Verbesserungsversuchen kann man sich durchaus von Empfehlungen der Query Engine leiten lassen
- nach Verbesserungsversuchen neu messen, falls es nix bringt Rückbau auf Stand vorher
Von Blindschusstherapien rate ich ab, anlegen überflüssiger Indexe kann drastische Folgen haben und freut allenfalls den Hardware Lieferanten.

D*B

[Fuerchau]

Nun ja BI ist schon immer eine Kunst, deshalb habe ich mich dieser auch verschrieben (siehe Signatur).
Was den Querytimeout angeht, so kann man diesen via QAQQINI überschreiben.
Dein bisheriges Vorgehen hat sich auch heute noch nicht geändert.
Um zu prüfen, welche Indizes tatsächlich verwendet werden, kann man leicht an Hand der geöffneten Dateien des Jobs sehen. Auch unter STRDBG (gibt auch einen QAQQINI-Eintrag dafür) muss man sich die Meldungen im Joblog ansehen. Für jede Datei gibt es 1-2 Einträge in der die Indizes mit einem Bewertungscode aufgeführt sind. Der Index mit Bewertung 0 wurde verwendet.

Im Gegensatz zu früher hat sich auch der Einsatz von 1-Feld-Indizes verbessert, da der Optimizer durchaus in der Lage ist, Indizes zu kombinieren. Also an Stelle viele Indizes mit wilden Feldkombinationen (A+B, A+C, B+C+D, ...) zu erstellen reicht es durchaus, weniger Indizes mit genau nur 1 Feld zu verwenden. Das dient dann auch der Pflegeoptimierung.
Dabei wird man dann feststellen, dass es durchaus Felder gibt, die nur weniger als 10 verschiedene Werte aufweisen und dann ein Tablescan effektiver sein kann.

Und da kommt dann nun wirklich EVI ins Spiel, da hier die Bitmap/RRN-Kombinationen extrem eine Rolle spielen. Wichtig ist bei der Erstellung die "Anzahl Distinct Values" fest zu legen um einen Rebuild zu verhindern.
Nachzulesen in Birgittas o.a. Link;-).

Nachtrag:
Und was Birgittas Hinweis angeht, bereits Aggregate in den EVI zu schieben, so ist dieser wenig zielführend. Wie man an Hand der Beschreibung sieht, wird das Aggregat zum Schlüssel gebildet.
In einer mandantenfähigen Software kommt man bei Aggregaten selten ohne Where-Klauseln aus, sowie bei Gruppierungen über mehr als 1 Feld, da sind dann die voraggregierten Werte eher sinnlos.

[mkoni]

Ich danke euch vorerst mal für eure Antworten. Ich muss mir jetzt erst mal die empfohlene Literatur durchlesen und ein paar tests mit den vorgeschlagenen tools durchführen.
Da ich auch verpflichtet worden bin meinen Urlaub abzubauen (da hat sich einiges angesammelt) könnte es ein paar Wochen dauern.
Trotzdem - Vielen Dank vorerst

[mkoni]

Hallo Fuerchau,
Mit "Visual Explain" -> "Ausführen und mit Explain bearbeiten" haben wir das bisher gemacht.
Anzeigen empfohlener Indexes aktiviert und einfach alle Indexe erstellt die empfohlen wurden.
Auch wenn es in vielen Fällen hilfreich war bezweifle ich ob das wirklich die richtige vorgehensweise ist.
Ausserdem hat es nicht immer geholfen und hat auch dazu geführt das wir jede Menge Indexe haben wo keiner genau weiß ob die wirklich etwas bringen.

lg Michael

[mkoni]

Hallo Manuel,
Das Performance Center ist mir auch schon aufgefallen. Gibts da irgendwo eine ausführliche Beschreibung dazu? Vielleicht anhand von Beispielen wie man damit richtig umgeht und wie man die ermittelten Werte auch richtig interpretiert?

lg Michael