View Full Version : SQL-Optimizer ist einfach blöd
Auch wenn Birgitta da anderer Meinung sein wird, ich verstehe den Optimizer schon lange nicht mehr:
Da existieren auf jeden Fall passende LF's ohne jedwede Einschränkung (SELECT/OMIT), es ist auch keine LF mit Select/Omit überhaupt vorhanden.
Der Optimizer begründet die Nichtauswahl mit Aussage 4:
4 - Die Kosten zur Verwendung dieses Zugriffspfads, die vom
Optimierungsprogramm ermittelt wurden, sind höher als die Kosten für die
ausgewählte Zugriffsmethode.
Anschließend wird ein Zugriffspfad mit genau den existierenden Schlüsselfeldern erstellt !
Andererseits wird behauptet, dass ein Zugriffspfad einer weiteren Datei mit Ursache 0 verwendet wird und prompt wird trotzdem ein neuer Zugriffspfad mit identischen Schlüsselfeldern erstellt.
Dies führt in Folge dazu, dass allein die Abfragedauer bis zum 1. Satz 110 Sekunden benötigt.
Für eine weitere Tabelle wird sogar ein Cast in der Join-Beziehung durchgeführt, was hier den Optimzer überhaupt nicht stört und in Folge der korrekte Zugriffsweg sogar verwendet wird:
left join afp1 on aefirm=p1firm and aewknr=p1wknr and zoned(case aequel when 'V' then substr(aelpnn, 1, 7) else '0' end , 7, 0)=p1afnr and aehpos=p1afhp
willkommen im Club,
... aber vielleicht fällt ja jemand noch ein Workaround ein, je nach vorliegender Konstellation (ohne Statement und Release wohl schwierig)
- Kosten für alternative Zugriffsstrategien durch order by verteuern
- where in exists und co als Join umformulieren
- left join statt inner join
- reroute zu CQE erzwingen
- mit neuem Release lange genug warten
- bei Batchabfragen mit temp Extrakten arbeiten
PS: dass da kein Problem mit CCSID bei Erstellung <> Abfrage vorliegt, nehme ich mal an!
D*B
Auch wenn Birgitta da anderer Meinung sein wird, ich verstehe den Optimizer schon lange nicht mehr:
Da existieren auf jeden Fall passende LF's ohne jedwede Einschränkung (SELECT/OMIT), es ist auch keine LF mit Select/Omit überhaupt vorhanden.
Der Optimizer begründet die Nichtauswahl mit Aussage 4:
4 - Die Kosten zur Verwendung dieses Zugriffspfads, die vom
Optimierungsprogramm ermittelt wurden, sind höher als die Kosten für die
ausgewählte Zugriffsmethode.
Anschließend wird ein Zugriffspfad mit genau den existierenden Schlüsselfeldern erstellt !
Andererseits wird behauptet, dass ein Zugriffspfad einer weiteren Datei mit Ursache 0 verwendet wird und prompt wird trotzdem ein neuer Zugriffspfad mit identischen Schlüsselfeldern erstellt.
Dies führt in Folge dazu, dass allein die Abfragedauer bis zum 1. Satz 110 Sekunden benötigt.
Für eine weitere Tabelle wird sogar ein Cast in der Join-Beziehung durchgeführt, was hier den Optimzer überhaupt nicht stört und in Folge der korrekte Zugriffsweg sogar verwendet wird:
left join afp1 on aefirm=p1firm and aewknr=p1wknr and zoned(case aequel when 'V' then substr(aelpnn, 1, 7) else '0' end , 7, 0)=p1afnr and aehpos=p1afhp
Nunja, CCSID ist nicht relevant.
Ich habe nun auch das Problem, dass dies erst nach Umstellung auf V5R4 passiert.
Bis V5R3 lief alles problemlos. Da die SQL's (per ODBC) schon lange optimiert sind (Indexe, Order-by, u.ä.) habe ich seit V5R4 plötzlich SQL0666 (Querytimout) obwohl ichden seit jeher bereits auf 32.000! gestellt hatte.
Nun liefert die AS/400 plötzlich erwartete Zeiten jenseits der 40.000 Sekunden.
Per ODBC ist allerdings nur 32767 als Maximum einstellbar.
Da die Anwendung jedoch bei vielen Clients eigentlich problemlos läuft, such ich nun auf der AS/400 selber nach Schräubchen zum Drehen.
das Problem mit CCSID ist ja, dass zwei Indexe (der vorhandene und der neu erstellte) gleich aussehen (Felder und Reihenfolge) und unterschiedliche Zugriffspfade repräsentieren können, da die CCSID bei der Erstellung sich bei Alfa Feldern in der Sortierfolge wiederfinden, dieser Effekt kann auch mit Treibereinstellungen zusammenhängen.
Ansonsten klingt das nach eine der normalen Schlimmbesserungen der SQE, manchmal kann man diese mit genügend Glanz in den Augen beim vorsichhinmurmeln von EssKuhEllKwihRihEndschinn beeinflussen, ansonsten auf ein PTF hoffen, oder einer der Workarounds (ach ja: per QAQQINI) kann man da auch den Wechsel zur CQE forcieren, aber bei V5R4 sitzt man da zwischen Baum und Borke - die alte geht nicht mehr richtig und die neue noch nicht...)
D*B
Nunja, CCSID ist nicht relevant.
Ich habe nun auch das Problem, dass dies erst nach Umstellung auf V5R4 passiert.
Bis V5R3 lief alles problemlos. Da die SQL's (per ODBC) schon lange optimiert sind (Indexe, Order-by, u.ä.) habe ich seit V5R4 plötzlich SQL0666 (Querytimout) obwohl ichden seit jeher bereits auf 32.000! gestellt hatte.
Nun liefert die AS/400 plötzlich erwartete Zeiten jenseits der 40.000 Sekunden.
Per ODBC ist allerdings nur 32767 als Maximum einstellbar.
Da die Anwendung jedoch bei vielen Clients eigentlich problemlos läuft, such ich nun auf der AS/400 selber nach Schräubchen zum Drehen.
So, ich sach ja, der Optimizer ist einfach blöd !
Ich habe nun in der QUSRSYS eine QAQQINI angelegt und einzig folgende Option eingestellt:
update qusrsys/qaqqini
set qqval='*YES'
where qqparm= 'IGNORE_DERIVED_INDEX'
Sämtliche anderen Parameter stehen auf ihrem Initialwert *DEFAULT, von denen ich nun mal annehme, dass der Optimizer diese auch annimmt, wenn die QAQQINI fehlt.
Die geschätzte Abfragezeit sank von 40.237 auf nun noch 447 Sekunden. Da mein Timeout auf 32.000 steht, ist das genug.
Die tatsächliche Abfragezeit (die rechne ich selber vom Start des Executes bis zum 1. Satz) sank von 110 Sekunden auf 13 Sekunden.
Dies ist die Beschreibung von IBM:
IGNORE_DERIVED_INDEX
*DEFAULTThe default value is the same as *NO.*YESAllow the SQE optimizer to ignore the derived index and process the query. The resulting query plan will be created without any regard to the existence of the derived index(s). The index types that are ignored include:
Keyed logical files defined with select or omit criteria and with the DYNSLT keyword omitted
Keyed logical files built over multiple physical file members (V5R2 restriction, not a restriction for V5R3)
Keyed logical files where one or more keys reference an intermediate derivation in the DDS. Exceptions to this are: 1. when the intermediate definition is defining the field in the DDS so that shows up in the logical's format and 2. RENAME of a field (these two exceptions do not make the key derived)
Keyed logical files with K *NONE specified.
Keyed logical files with Alternate Collating Sequence (ACS) specified
SQL indexes created when the sort sequence active at the time of creation requires a weighting (translation) of the key to occur. This is true when any of several non-US language IDs are specified. It also occurs if language ID shared weight is specified, even for language US.*NODo not ignore the derived index. If a derived index exists, have CQE process the query.
Keine der obigen Bedingungen scheint zuzutreffen, trotzdem arbeitet der Optimizer nun irgendwie anders.
Die Diagnosenachrichten im Joblog sind die gleichen wie vorher (Index/LF wegen Kosten nicht genutzt), empfohlene Zugriffswege, die dann auch nicht genutzt werden (habe ich probiert, Index erstellt, nun folgte die Nachricht, dass der Index auf Grund der Sortierfolge nicht genutzt wird, also Index wieder gelöscht).
Die Antwortzeiten sind nun wieder wie bei V5R3.
Vielleicht kann mir Birgitta (gerne auch andere) ja noch Erklärungen liefern.
Ach ja, das ganze läuft auf einer 525 mit 16GB Hauptspeicher, 917 G Platte (16 Disks, 47% belegt), ca. 1500 aktive Jobs bei einer CPU-Auslastung von ca. 15% (WRKSYSSTS).
Die QAQQINI-Änderung könnte bewirkt haben, dass der Access Plan, der zuvor (aus welchem Grund auch immer nicht aktualisiert oder neu erstellt wurde), bei der nächsten Ausführung neu erstellt wurde.
Mich würde ja wirklich interessieren mit welcher Query Engine die Abfragen ausgeführt werden.
Führe doch die Abfrage mit und ohne der QAQQINI-Änderung und prüfe über Visual Explain welche Engine die Abfrage ausgeführt hat.
Was Du beschrieben hast, trifft eigentlich viel mehr auf die CQE als auf die SQE zu. Bei SQE bin ich bislang selten in Situtationen gelaufen, in denen ein Index vorgeschlagen und anschließend nicht genommen wurde.
Anschließend wird ein Zugriffspfad mit genau den existierenden Schlüsselfeldern erstellt !
Andererseits wird behauptet, dass ein Zugriffspfad einer weiteren Datei mit Ursache 0 verwendet wird und prompt wird trotzdem ein neuer Zugriffspfad mit identischen Schlüsselfeldern erstellt.
Wenn außerdem ein (temporärer) Zugriffspfad erstellt und auch verwendet wurde, müssten alle folgenden Ausführungen (auch von anderen Jobs aus) wesentlich schneller sein, wenn mit der SQE gearbeitet wird. Temporäre Indices werden erst seit V5R4 von der SQE erstellt bzw. verwendet. Solche MTIs (Maintained Temporary Indexes) sind quasi permanent, d.h. die bleiben existent bis der letzte Access Plan, der diesen Zugriffspfad verwendet aus dem Plan Cache (spätestenfalls bei IPL) verschwindet und können wie ein permanenter Index von allen Jobs aus verwendet werden. Wurde mit der SQE ein temporärer Index erstellt, müsste dieser auch in der Joblog-Detail-Anzeige sichtbar sein als MTI-irgendwas.
Die Diagnosenachrichten im Joblog sind die gleichen wie vorher (Index/LF wegen Kosten nicht genutzt), empfohlene Zugriffswege, die dann auch nicht genutzt werden (habe ich probiert, Index erstellt, nun folgte die Nachricht, dass der Index auf Grund der Sortierfolge nicht genutzt wird, also Index wieder gelöscht).
Die Antwortzeiten sind nun wieder wie bei V5R3.
Dann prüfe doch mal die Sortierreihenfolge in Deinem Job, bzw. des Jobs, in dem Du den Index erstellt hast. Vielleicht wurde diese irgenwann versehentlich oder für Testzwecke geändert. Zugriffswege können nur dann verwendet werden, wenn sie die gleiche Sortierreihenfolge, wie der Job, in dem die Abfrage ausgeführt wird haben.
Wenn Du eine andere Sortierreihenfolge als HEX verwendest (also LangIdShr oder LangIdUnq), wird die Abfrage sowieso von der CQE ausgeführt, zumindest noch unter V5R4.
Vielleicht war CCSID nicht die Ursache, wohl aber die Sortierreihenfolge.
Mehr kann man, wie Dieter schon gesagt hat nicht sagen, ohne das SQL-Statement zu sehen und ohne die vorhandenen Zugriffswege zukennen.
Birgitta
Nun ja, inzwischen müsstest du mich gut genug kennen, dass ich gerade was Index, Sortierfolge und CCSID angeht relativ gut auskenne.
Im Joblog stand i.Ü. auch immer, dass der Zugriffsplan neu aufgebaut wurde.
Da ich ab und an mit den automatischen SQLPKG's (ich arbeite über ODBC), insbesonders wegen Berechtigungen Probleme habe, lege ich diese immer in die QTEMP, dann sind sie anschließend auch wieder weg.
Der SQL ist eigentlich ziemlich simpel, eine Haupttabelle und 5 Left Joins, sogar gecasteter Join (s.o.), der sogar korrekt ausgeführt wird.
Aber wie gesagt, seit Setzen der QAQQINI ist dieses Problem ja gelöst.
Was einfach mein Hauptproblem ist, ist dieser blöde SQL0666 (Zeitlimit), der überhaupt nichts mit dem späteren tatsächlichen SQL zu tun hat.
Wie ist zu erklären, dass (ich nehme mal an) die CQE auf 40.000 Sekunden kommt (stelle ich den Timeout auf NOMAX (-1)) kommt der 1. Block aber nach ca. 100 Sekunden, und die SQE 400 Sekunden annimmt und das Ergebnis in 13 Sekunden liefert ?
Diese Zeitschätzungen halten meist mehr auf, als sie tatsächlich wert sind.
Ich habe ehrlich noch nie eine annähernd korrekte Schätzung erlebt.
Gut, ich gebe zu, dass meine SQL's manchmal nicht so einfach sind.
Da ich aber mit einem BI-Programm (das ist die FTSolutions) auf vorhandene ERP's zugreife kommt natürlich eine Umstellung der fremden ERP nicht in Frage.
Nun noch kurz zum SQL:
With cQLPAU
.CommandText = "select aefirm, aewknr, aetada, aetenr, aewacd, aekurs, double(aeefwt) as aeefwt, double(aeefmg) as aeefmg, double(aeplwt) as aeplwt, double(aeplmg) as aeplmg, aelakz, aelpnn, substr(aelpnn, 1, 7) as k1aunr "
.CommandText = .CommandText & ", aefirm as k1firm, aewknr as k1wknr, aeabtl as k1abtg, aeafat as k1aart, digits(aekdnr) as k1akdn, digits(aevenr) as k1avsn, aequel, k1aref, k1rfda "
.CommandText = .CommandText & ", p1rpan, aelagr as p2lanr, aeprgr as p1prgr, teprkl as p1prkl, teprka as p1prka, aetenr as p1tenr, aevpcd as p1vpcd, aevn01 as p1vtn1, aevn02 as p1vtn2 "
.CommandText = .CommandText & ", case aequel when 'V' then kpkdin else lpsnbe end as lpsnbe, lpdfsv "
.CommandText = .CommandText & " from " & fLib & ".lpau "
.CommandText = .CommandText & " inner join " & fLib & ".teil on aefirm=tefirm and aewknr=tewknr and aetenr=tetenr"
.CommandText = .CommandText & " left join " & fLib & ".afp1 on aefirm=p1firm and aewknr=p1wknr and zoned(case aequel when 'V' then substr(aelpnn, 1, 7) else '0' end , 7, 0)=p1afnr and aehpos=p1afhp "
.CommandText = .CommandText & " left join " & fLib & ".afk1 on aefirm=k1firm and aewknr=k1wknr and zoned(case aequel when 'V' then substr(aelpnn, 1, 7) else '0' end , 7, 0)=k1afnr "
.CommandText = .CommandText & " left join " & fLib & ".lplp on aefirm=lpfirm and aewknr=lpwknr and aetenr=lptenr and aekdnr=lpkdnr and aevenr=lpvenr "
.CommandText = .CommandText & " left join " & fLib & ".kpzi on 'KP'=kpsart and aefirm=kpfirm and aewknr=kpwknr and aetenr=kptenr and aekdnr=kpkdnr "
.CommandText = .CommandText & " where aefirm=? and aewknr=? and aetada>=? "
.CommandText = .CommandText & " order by aefirm, aewknr, aetada, aetenr"
.CommandTimeout = 32000
Set .ActiveConnection = fBrain
End With
Die Variable "fLib" enthält den Namen der Lib, so dass der SQL qualifiziert ist, sämtliche Verknüpfungen verweisen auf vorhandene Indices.
Die Problemdateien sind die LPLP und KPZI für die genau passende LF's exisitieren und die auch keine LF's mit Select/Omit haben.
Interessant ist, dass auf die AFP1 und AFK1 korrekt über Index zugegriffen wird obwohl gerade hier LF's mit Select/Omit existieren.
Die Hauptdatei LPAU sowie TEIL wird auch korrekt über Index verarbeitet.
Es besteht also überhaupt kein Grund, warum gerade bei LPLP und KPZI der vorhandene Index nicht genutzt wird.
das kann auch damit zusammen hängen für wieviele Zeilen des ResultSets optimiert wird! Die ermittelten Zeiten hängen zudem von der Balance der Indexbäume und den entsprechenden Runstats zusammen (müsste frei nach Theorie mit zunehmender SQE besser werden, tut aber oft nicht). Bei mehreren Join Dateien und Optimierung für alle Sätze kommen hier schnell krasse Fehler zusammen. Optimize for 10 rows ist da ein weiterer Workaround dem Hang zu full Table scans und Indexaufbauten auszuweichen.
Was generierte Zugriffe angeht ist bei solchen Joins das vorziehen der Selektion und anschließendem Join von Temp Tables für komplexe Queries im BI Bereich gerade auf stärkeren Maschinen nach meinen Erfahrungen im Schnitt die bessere Strategie.
D*B
Was einfach mein Hauptproblem ist, ist dieser blöde SQL0666 (Zeitlimit), der überhaupt nichts mit dem späteren tatsächlichen SQL zu tun hat.
Wie ist zu erklären, dass (ich nehme mal an) die CQE auf 40.000 Sekunden kommt (stelle ich den Timeout auf NOMAX (-1)) kommt der 1. Block aber nach ca. 100 Sekunden, und die SQE 400 Sekunden annimmt und das Ergebnis in 13 Sekunden liefert ?
Diese Zeitschätzungen halten meist mehr auf, als sie tatsächlich wert sind.
Ich habe ehrlich noch nie eine annähernd korrekte Schätzung erlebt.
Gut, ich gebe zu, dass meine SQL's manchmal nicht so einfach sind.
Da ich aber mit einem BI-Programm (das ist die FTSolutions) auf vorhandene ERP's zugreife kommt natürlich eine Umstellung der fremden ERP nicht in Frage.
Hier ist der Hintergrund der, dass die Selektion ja nur auf der Haupttabelle stattfindet.
Die Joins werden nur auf Grund ihrer Verbindung halt je Satz selektiert.
In diesem Select gibts sowohl 1:1 als auch 1:N-Beziehungen, die auch durchaus gebraucht werden.
Im Zweifel sollen ja auch alle Daten selektiert werden, was hier durch das AETADA der Whereklausel bestimmt wird.
Die anderen beiden Felder AEFIRM und AEWKNR sind konstant (Mandanten-Felder).
Ich habe auch so manchmal das Gefühl, dass der Optimizer die zu erwartende Verarbeitungszeit schätzt als die reine Query-Zeit bis zum 1. Satz.
Dem Optimizer kann es doch egal sein, wie lange das Programm später braucht, schließlich kann die CPU ja durchaus andere Dinge tun wenns mal länger dauert.
Das gemeine an solchen Dingen ist eher, bis zu dem Release X gings, ab X+1 gehts plötzlich nicht mehr und man sucht wieder von vorne.
Hallo,
ein (Binary Radix Tree) Index Scan wird nur verwendet wenn max. 15-20% der Datensätze in der Tabelle selektiert werden müssen. Ist der Anteil der Datensätze größer kann eventuell ein Encoded Vector Index (EVI) werden (sofern angelegt) oder es wird ein Table Scan verwendet. EVIs können vom Optimizer bei einem Anteil von ca. 20 - 70 % der selektierten Sätze verwendet werden.
Im Gegensatz zur CQE arbeitet die SQE mit den gesammelten Statistiken, über die die tatsächliche Satz-Anzahl pro Schlüssel-Kombination ermittelt werden kann. CQE arbeitet dagegen nur mit Schätzwerten. Aus diesem Grund kann es auch sein, dass die CQE einen Index verwendet hat, während die SQE einen Table Scan vorzieht.
Schau doch mal in den permanenten Index Advice, entweder über den i Navigator oder direkt in die Datei SYSIXADV in der Bibliothek QSYS2, ob der Vorschlag nicht für einen EVI war.
In dieser Datei sieht man auch, ob ein MTI angelegt wurde.
Birgitta