Wann genau wird der Plan Cache gelöscht

[B.Hauser]

Mit Analyse der SQLs ist doch sicher die Verwendung des Index Advisors gemeint. Oder gibt es da noch etwas besseres? Einige Vorschläge, die der Index-Advisor macht, kommen mir ziemlich suspekt vor (z.B. Indizes mit mehreren Schlüsselfeldern, bei denen der erste Schlüssel der Unique Key der Datei ist!

Es gibt 2 Möglichkeiten die SQL Statements zu optimieren:
1. durch die richtigen Indices, da bieten sich Index Advisor und Index Condenser an
2. durch die Art wie ein SQL Statement codiert ist.

Für die Analyse von SQL-Statements bietet der System iNavigator neben dem Index-Advisor und Index Condesor auch noch die SQE Plan Cach-Analyse sowie die Database Monitor Jobs mit entsprechenden Auswertungen.

Der Advisor schlägt z.T. auch Indices vor, die für einen IOA (Index Only Access) verwendet werden könnten, d.h. alle für die Abfrage benötigten Informationen sind in den Schlüssel-Feldern enthalten, so dass ein Zugriff auf den Datensatz nicht notwendig ist.

Birgitta

[dschroeder]

OK, das mit dem IOA erklärt einiges. Ich habe auch eben gesehen, dass einige empfohlene Indizes mit Primärschlüssel encoded Vector Indizes sind. Das kann natürlich doch Sinn machen. Ich bin allerdings etwas vorsichtig damit, jeden Index, den der Advisor vorschlägt, als permanenten Index anzulegen. In Tabellen, in denen viel gelesen und wenig geschrieben wird, ist das sicherlich OK. Bei Tabellen mit viel Bewegung befürchte ich immer, dass die Performance durch die Pflege der Indizes beeinträchtigt wird.

Vielen Dank für die Anworten
Dieter

[andreaspr@aon.at]

Der Index-Adviser ist nebenbei auch nur als Vorschlag zu betrachten!

Es kann durchaus passieren, dass der dadurch erstellt Index gar nicht verwendet wird, da die DB meint, dass sie ihn lieber doch nicht verwenden will.

Des weiteren sollte auch festgestellt werden, von wo aus die Empfehlung kommt. Nur weil im STRSQL einige User ein paar SELECTs absetzen (die eh schon schnell laufen) muss der dafür empfohlene Index nicht wirklich notwendig sein.

Auch auf das Alter der Empfehlung achten. Eventuell wird diese gar nicht mehr benötigt!

Was die Optimierung betrifft, sollte zuerst das SQL Statement analysiert, und erst zum schluss Indice erstellt werden.

Ich habe schon abfragen korrigiert, wo im Statement eine Logische statt der Physischen verwendet wurde.
Dadurch hab ich die Dauer der Abfrage von bis zu 10 min. auf ein paar Millisekunden verbessern können.

lg Andreas

[dschroeder]

Vielen Dank für die Infos.
Ich mache es im Prinzip so, wie du es beschrieben hast: Nur aktuelle Vorschläge werden überhaupt in Erwägung gezogen. Ich schaue mir natürlich auch an, wie oft der Index angefordert wird und weshalb (war es ein normaler User oder hat ein Programmierer einfach mit SQL "rumprobiert"). Die Sache mit der Analyse der SQL-Statements ist natürlich schwierig. Einige Abfragen werden mit dynamischem SQL zusammengebaut und sehen abhängig davon, was die User selektieren, immer wieder anders aus. Außerdem haben wir einiges über user defined functions gekapselt. Da läuft dann innen eine Mischung aus RPG und embedded sql.

Gruß,
Dieter

[Fuerchau]

Eine Selektion über UDF "... where myudf(..) = xxx" erzwingt natürlich einen Tablescan.
Hier ist ggf. aber zu prüfen ob das nicht anders gelöst werden kann oder zusätzlich Felder zur Abfrage verwendet werden können.

UDF's in der Where-Klausel gehörten aus Performancesicht auch verboten.

[dschroeder]

Wir verwenden UDFs in der Regel nicht im Where bzw. dann nur, wenn weitere Selektionsbedingungen vorhanden sind, mit denen der Optimizer die Grundmenge schon mal verkleinern kann. (Ich hoffe, dass der Optimizer das auch tut, dass er also die UDF erst zum Schluss prüft).
Sorry: Eben habe ich mich auch verschrieben. Ich meinte nicht, dass wir UDFs für die Selektion nutzen, sondern UDTFs.

Danke für die Infos,
Dieter

[B.Hauser]

Sorry: Eben habe ich mich auch verschrieben. Ich meinte nicht, dass wir UDFs für die Selektion nutzen, sondern UDTFs.

Wirklich UDTFs?
Die können nämlich nur in der From-Anweisung verwendet werden.

Ansonsten schau Dir die Analyse-Tools im System iNavigator an. Da siehst Du auch welches SQL Statement ausgeführt wurde (unabhängig davon, ob es dynamisch oder als statisches SQL oder als ad hoc-Abfrage ausgeführt wurde.)

Die Analyse-Tools ermöglichen Dir auch die SQL-Statements wieder "herzuholen" und erneut auszuführen etc.

Ansonsten solltest Du einfach mal den systemweiten Index-Advisor (die Datei SYSIXADV) clearen und nach einiger Zeit, z.B. 1, 2 Wochen die die neuen Advices anschauen.

Sofern die Advices von der SQE ausgesprochen wurden, und solange kein IPL gefahren wurde, kannst Du Dir auch die SQL-Statements, die zu dem Advice geführt haben anzeigen lassen.

... ich komm' auch gerne mal zur weiteren Unterstützung vorbei

Birgitta

[dschroeder]

Hallo Birgitta,
vielen Dank für die Anmerkungen. Die UDTFs benutzen wir in der Tat für Selektionen. Natürlich kommen sie nicht in der where Klausel vor. Wir haben beispielsweise eine UDTF, der man einige Suchbegriffe übergeben kann und die einem dann die gefundenen Kunden zurückgibt. Wir machen das so, damit bestimmte aufwendigen Codierungen (wie z.B. Berechtigungsprüfung) gekapselt ist. Wenn jeder Programmierer die UDTF verwendet, um die Kunden zu lesen, kann er sicher sein, dass nur die erlaubten Kunden ermittelt werden. Wenn er mit SQL einfach selber auf die Kundentabelle geht, muss er selber daran denken, die Berechtigungsprüfung aufzurufen. Und so eine UDTF können wir eben nicht nur von der iSeries-Programmierung aus nutzen. Auch unsere Java-Kollegen oder unsere Kollegen, die ad hoc Auswertungen machen, können das nutzen.
Wir nutzen SQL (dank deiner hervorragenden Schulungen bei uns) immer stärken. Neue Sachen machen wir fast nur noch mit SQL. Deshalb wird es bei uns immer wichtiger, sich auch um die Performance bei SQL-Zugriffen Gedanken zu machen. Wir haben da aktuell noch eine Anfrage bei IBM offen. Wir müssen mal schauen, was dabei herauskommt. Wenn wir da nicht weiterkommen, sollten wir nochmal über einen Inhouse-Termin sprechen.

Gruß,
Dieter