SQL Optimizer - Logische mit Select/Omit

[schatte]

Hallo Leute,

kann mir jemand sagen, warum der SQL-Optimizer unter V5R3M0/V5R4M0 logische Dateien mit Select/Omit ignoriert?

Ich würde gerne in diversen Programmen dynamische SQLs ausführen, die nur auf das PF zeigen und hätte gerne, dass der Optimizer sich dann je nach WHERE und ORDER BY-Bedingung eine passende Logische raus sucht.
Bei ein paar Test habe ich allerdings festgestellt, dass der Optimizer immer nur Logische ohne Select/Omit benutzt. Dies ist dann oft nicht sehr optimal für die Ausführungszeit. Gibt man im SQL die logische Datei explizit an, so wird diese auch verwendet. Die Anzahl an Sätzen in der ausgewählten Datei ist dabei egal. Ob viele oder wenige Sätze, das Ergebnis ist das selbe.

Bei einem Test mit STRSQL im Debugmodus wird für die Logischen mit Select/Omit folgende Ausschlussgrund genannt:
11 - Der Zugriffspfad enthält statische SELECT/OMIT-Auswahlkriterien, die
mit der Auswahl in der Abfrage nicht verträglich sind.

Kann mir jemand sagen, wie ich den Optimizer dazu bringen kann diese Dateien von sich aus zu berücksichtigen?

Die Select/Omits lassen sich leider nicht vermeiden.

Gruß
Matthias

[Fuerchau]

SQL kennt keine Tabellen mit Ausschluss (SELECT/OMIT).
Daher kann und wird er diese nie verwenden.
Die Gefahr ist sogar, dass das Vorhandensein eine LF mit SELECT/OMIT dazu führt, dass der alte Optimizer statt des neuen (angeblich besseren) verwendet wird (ob das noch so ist weiß ich nicht, aber bis V5R2 war das so).

Diese Art der LF's (dazu gehören auch Joins) sind nicht SQL-spezifisch.
SQL kennt nur Tables, Indexe und Views, die alle auf PF basieren.
Deshalb gibts in Views ja auch kein "Order By", da dies der spätere SQL ja eh ändern kann.

Für SQL ist es tatsächlich am Besten wenn ausschließlich LF's ohne Select vorhanden sind.

[B.Hauser]

Hallo,

1. Alle Abragen, in denen logische Dateien angegeben wurden oder in denen physische Dateien auf denen select/omit-Anweisungen liegen, werden von der Classic Query Engine (CQE), also der alten Query Engine ausgeführt.
Es sei denn, man hätte in der Abfrageoptionsdatei QAQQINI die Option Ignore_Derived_Index auf *YES gesetzt. Dann können die Abfragen von der neuen SQL Query Engine (SQE) verarbeitet werden, allerdings werden alle Zugriffswege von logischen Dateien mit Select/Omit-Anweisungen ignoriert.

2. Der CQE-Optimizer verwendet Zugriffswege aus logischen Dateien mit Select/Omit-Anweisungen, wenn ihm diese als geeignet erscheinen. Die übergangenen Datensätze bzw. die durch Select/Omit ausgeschlossenen Datensätze spielen dabei keine Rolle. Wäre also eine logische Datei mit gleichem Schlüssel ohne Select/Omit-Anweisung vorhanden, die der Optimizer verwendet, wäre die Ausführung genau so schnell oder langsam wie bei Verwendung der logischen Datei mit Select/Omit-Anweisungen.

Ab Release 6.1 können z.B. auch Indices mit Where-Klausel gebildet werden, die von native I/O verwendet werden können, aber auch unter 6.1. kann der Optimizer noch nicht von der reduzierten Satz-Anzahl profitieren.

3. Wird eine komplexe logische Datei in einer SQL-Anweisung angegeben, kann es sein, das der CQE-Optimizer entscheidet, die logische Datei direkt zu verwenden. (Nämlich dann, wenn die geschätzte Auflösungs- und Optimierungszeit zu lange ist.)

Alles in allem (auch wenn viele das vielleicht nicht einsehen wollen), die Entscheidungen die der Optimizer trifft sind immer besser, als die die der kleine Programmierer sich ausdenkt.

Übrigens auch die SQE ist besser als viele glauben. SQE funktioniert halt etwas anders als die CQE, d.h. Tricks, die bei der CQE funktioniert haben klappen bei der SQE nicht mehr.
Sicher, das Ding ist neu geschrieben und hat halt auch noch jede Menge Haken und Ösen. (IBM ist halt auch nur ein großes Software House). Sind Abfragen, die mit der SQE ausgeführt werden nicht mindestens genauso schnell wie wenn sid von der CQE ausgeführt werden, liegt ein Bug vor, der der IBM gemeldet werden sollte.

Bei einem Test mit STRSQL im Debugmodus wird für die Logischen mit Select/Omit folgende Ausschlussgrund genannt:
11 - Der Zugriffspfad enthält statische SELECT/OMIT-Auswahlkriterien, die
mit der Auswahl in der Abfrage nicht verträglich sind.

Das bedeutet, dass der Optimizer einen Zugriffspfad gefunden hat, mit dem er schneller an die Daten 'rankommt als mit dem Zugriffspfad in der logischen Datei mit Select/Omit. (Da der CQE-Optimizer immer nur mit Schätzwerten arbeitet, könnte das vielleicht auch nicht die optimalste Lösung sein).

Wenn Du allerdings schon im Debug-Modus bist, gibt es irgendwelche Index-Empfehlungen?
Wenn ja lege den lege eine logische Datei oder besser einen SQL-Index über die vorgeschlagenen Felder an und führe Deine Abfrage erneut aus.


Birgitta

[Fuerchau]

@Birgitta
Nun ja, wenn man da mal was an IBM meldet, bekommt man z.B. die Antwort:
"Legen Sie eine LF mit Select/Omit an um die CQE zu erzwingen." (klar, Ausnahme QAQQINI).

Allerdings wäre es manchmal wünschenswert, wenn im Joblog ein Hinweis bzgl. CQE/SQE zu finden wäre, oder übersehe ich den nur ?

Immer häufiger bekomme ich auch Indexempfehlungen, die dann anschließend doch nicht verwendet werden. Der Optimizer glaubt dann nämlich, dass der von ihm vorgeschlagene Index aus Zeitgründen nicht verwendet werden kann und schlägt den selben gleich wieder vor.

Was soll man von so einem Optimizer halten ?

[B.Hauser]

@Birgitta
Nun ja, wenn man da mal was an IBM meldet, bekommt man z.B. die Antwort:
"Legen Sie eine LF mit Select/Omit an um die CQE zu erzwingen." (klar, Ausnahme QAQQINI).

Ich kann mir eigentlich nicht vorstellen, dass die IBM ein Rerouting zur CQE empfieht (höchstens zum Vergleich!!!).

Vor 6.1 gibt es allerdings auch einfachere Möglichkeiten als eine LF mit select/omit anzulegen eine Abfrage auf die CQE zu zwingen.
So werden z.B. Abfragen mit den skalare Funktionen Upper oder Lower (vor 6.1) von der CQE ausgeführt. Wenn man also in den Where-Bestimmungen eine Dummy-Bedingung z.B. Upper('x') = 'X' hinzufügt, wird das Statement von der CQE ausgeführt.

Wir hatten auch schon Fälle, in denen das Problem mit der SQE gemeldet wurde und innerhalb von 2 Monaten (vielleicht auch keine allzu kurze Zeit) war das PTF da. Aber man kann sich ja behelfen (s.o.)

Allerdings wäre es manchmal wünschenswert, wenn im Joblog ein Hinweis bzgl. CQE/SQE zu finden wäre, oder übersehe ich den nur ?

Visual Explain hat diese Meldung (seit V5R4 mit PTF seit V5R3).
Da erhält man die Meldung welche Query Engine die Abfrage ausgeführt hat und falls es die CQE war auch noch warum es mit der CQE ausgeführt wurde.

Immer häufiger bekomme ich auch Indexempfehlungen, die dann anschließend doch nicht verwendet werden. Der Optimizer glaubt dann nämlich, dass der von ihm vorgeschlagene Index aus Zeitgründen nicht verwendet werden kann und schlägt den selben gleich wieder vor.

Was soll man von so einem Optimizer halten ?

Bei der CQE kommt das häufiger vor als bei der SQE, da die CQE nur mit Schätzwerten analysiert, während die SQE die Statistiken befragt.

Weiterhin fordert nicht nur der QueryOptimizer Zugriffswege an, sondern auch der Statistiksmanager. Die Statistiken laufen im Hintergrund und analysieren die zusammensetzung der Daten und speichern diese entsprechend ab. Je geeigneter die Zugriffswege sind, desto scheller sind die Statistiken up to date.

Index Advisor (ab V5R4) und Index Evaluator (V5R4 mit PTF ab V5R3) zeigen welche Zugriffswege wie oft empfohlen wurden, wann die letzte Empfehlung war und wann die letzte Verwendung eines Zugriffswegs durch den Query Optimizer bzw. den Statistics Manager statt gefunden hat.

Birgitta

[schatte]

Hallo,

also CQE wird auf jeden Fall schonmal benutzt, da dort eigentlich alle Dateien irgendwo eine Logische mit Omit haben. Ist aber erstmal nicht schlimm.

Schlimm ist aber, dass der Optimizer niemals die Logische mit dem Omit nehmen will, obwohl diese genau zur SQL-Abfrage passt.

Ich habe sogar extra mal eine kleinen Testfall gemacht. Ein PF mit 2 x LF. Eines mit nur Key und eines mit Key und Omit.
SQL drauf mit Abfrage auf den Key und das Omit-Feld. Und er nimmt immer die Logische ohne Omit. Obwohl man (aufgrund der großen Satzanzahl) eindeutig sehen kann, dass der Zugriff über die Omit-LF wesentlich schneller wäre.

SELECT auf das PF (Optimizer wählt die LF ohne Omit): ca. 10 Sekunden
SELECT direkt auf die LF mit Omit (Optimizer akzeptiert meinen Vorschlag): < 1 Sekunde.
=> Da hat der "kleine Programmier" wohl den besseren Vorschlag gehabt.

Wie kann das sein?

Gruß
Matthias

[BenderD]

... das ist öfter so, z.B. wenn der Mensch vor dem Bildschirm mehr Information über den Verlauf des Programmes hat als die Maschine; in diesem Fall könnte es sein, dass die Query Engine mit einbezieht, dass bei späteren Zugriffen eine geänderte where Klausel genau die Verwendung dieses Omit paths verhindern würde.

D*B

=> Da hat der "kleine Programmier" wohl den besseren Vorschlag gehabt.

Wie kann das sein?

Gruß
Matthias

[Fuerchau]

Deswegen sollte man ja an Stelle der LF mit Select/Omit eine View erstellen und den Select darauf loslassen.

Bei meinen Tests (siehe anderer Beitrag) konnte ich nun die SQE dazu bewegen sich einzuschalten.
Ich habe einfach eine QAQQINI in QUSRSYS erstellt und den SQL
update qusrsys/qaqqini
set qqval='*YES'
where qqparm= 'IGNORE_DERIVED_INDEX'
ausgeführt. Und siehe da, ich hatte keinen QueryTimeout mehr, da die pessimistische Zeitschätzung der CQE von ca. 40.000 Sekunden durch die Zeitschätzung der SQE auf 447 Sekunden sank.

Da parallel zu meinem laufenden SQL (ca. 800.000 Sätze) ein 2. Client mit dem gleichen SQL zugriff, sank die reine Queryzeit auf einmal sogar auf 0,2 Sekunden !

Hierzu passt auch die Aussage:
If a derived index exists, have CQE process the query.

Und gerade wegen der CQE steht unser Systemwert QQRYTIMLMT auf *NOMAX, da sonst die meisten AS/400-Queries sonst auf Timeout laufen würden.

Ab V6R1 soll es ja besser werden, da man dann auch berechnete Indices erstellen kann. Wenn dann ein SQL mit Where/Join/Order genau passt, wird dieser wohl auch verwendet werden.

Klar bin ich mit Kenntnis der Anwendung, Ausnutzung der DDS-Möglichkeiten in vielen Fällen mit Native-Access (SETLL/READE/...) schneller, aber SQL-Anwendungen erfordern eh ein anderes Denken und Design.

[B.Hauser]

Und siehe da, ich hatte keinen QueryTimeout mehr, da die pessimistische Zeitschätzung der CQE von ca. 40.000 Sekunden durch die Zeitschätzung der SQE auf 447 Sekunden sank.[/FONT]
Da parallel zu meinem laufenden SQL (ca. 800.000 Sätze) ein 2. Client mit dem gleichen SQL zugriff, sank die reine Queryzeit auf einmal sogar auf 0,2 Sekunden !

Wenn tatsächlich von der SQE ein temporärer Index erstellt wurde, kann dieser genauso wie der Access Plan, der im SQE Plan Cache gespeichert wird, von jedem anderen Job aus verwendet werden, zumindest bis der Access Plan aus dem Plan Cache verschwindet.

Ab V6R1 soll es ja besser werden, da man dann auch berechnete Indices erstellen kann. Wenn dann ein SQL mit Where/Join/Order genau passt, wird dieser wohl auch verwendet werden.

Vorerst können Indices mit Where-Bedingungen nur mit native I/O verwendet werden. Der SQE Query Optimizer kann z.Z. diese Erweiterungen noch nicht nutzen, d.h. es wir genau wie bei SQL-Dateien mit Select/Omit-Anweisungen mit dem kompletten Zugriffsweg gearbeitet.
... aber die Nutzung durch die SQE ist geplant. Die CQE wird diese Indices nie nutzen können.

SELECT auf das PF (Optimizer wählt die LF ohne Omit): ca. 10 Sekunden
SELECT direkt auf die LF mit Omit (Optimizer akzeptiert meinen Vorschlag): < 1 Sekunde.
=> Da hat der "kleine Programmier" wohl den besseren Vorschlag gehabt.

Nein, da war dem Optimizer die DDS-Datei zu komplex bzw. das Rewriting zu aufwändig und die Datei wurde direkt verwendet. Das kommt bei der CQE ab und zu vor.

Was man allerdings bei der CQE/SQE-Geschichte berücksichten sollte. Seit V5R3 können alle Neuerungen nur von der SQE verarbeitet werden.
z.B. Except und Intersect (V5R3), OLAP-Ranking-Funktionen und Rekursive Abfragen (V5R4), RollUp, Cube und Grouping Sets (6.1)

Birgitta