View Full Version : SQL-Optimizer ist einfach blöd
@optimize for n rows:
hat (im Unterschied zu fetch n rows only) mit der Anzahl der tatsächlich zu liefernden Sätzen nix zu tun.
Den Effekt kann man oft beobachten, wenn man interaktives SQL per STRSQL, per OopsNerv und im Programm miteinander vergleicht (selbes Statement). STRSQL liefert meist den ersten Satz am schnellsten, im Programm wird meist auf Folgeverarbeitung optimiert (z.B.: Blockung erfordert dies oft, um effektiv zu sein!!!)
@runstats:
Runstats können ebensogut in die Irre führen, wie blanke Schätzungen.
(Beispiel: Mandantenkey vom Typ int, Mandant 1 hat 99% aller Sätze
select * from .. where mandant = :mandant) für Mandant 1 wäre full table scan optimal, für mandant2 per index. Ohne runstats kommt unabhängig vom Mandant der gleiche Zugriffspfad raus (wohl index!). Mit runstats kanns klappen, könnte aber sogar für Mandant 2 ein full table scan rauskommen, je nach vorher abgefragten Daten, was aber fatal wäre.
BTW: das wäre eigentlich ein klassischer Kandidat für EVI, wenn nicht...
@Evi:
ein reines Dummy Feature!!! Ich habe noch nie die Benutzung eines EVI erlebt!!! aber Empfehlungen beim schreiben löschen nachher neu aufbauen gefunden - lachhaft, dauert Stunden. War vielleicht mal gut gemeint (ähnlich extended dynamic package support), sollte man besser vergessen!!! Oder hat jemand wirklich ein nachvollziehbares Gegenbeispiel???
@SQE - CQE:
ich habe nix gegen den rewrite, der war absolut notwendig und ist in Summe von Vorteil, hat uns aber seit V5R2 eine ungewohnt launische Datenbank mit unzähligen Bugs beschert und ich halte es für absolut erforderlich solche Dinge öffentlich anzuprangern, damit es besser wird!!!
D*B
@Evi:
ein reines Dummy Feature!!! Ich habe noch nie die Benutzung eines EVI erlebt!!!
Ich hab's schon erlebt, wenn EVIs über einzelne Felder gelegt wrrden, können z.T. sogar mehrere EVIs zum Erstellen von Bitmaps verwendet werden.
(Stichwort: Index anding and oring, Star Join Schema und Look ahead Predicat Generation LPG)
@SQE - CQE:
ich habe nix gegen den rewrite, der war absolut notwendig und ist in Summe von Vorteil, hat uns aber seit V5R2 eine ungewohnt launische Datenbank mit unzähligen Bugs beschert und ich halte es für absolut erforderlich solche Dinge öffentlich anzuprangern, damit es besser wird!!!
Ich hab' auch nie behauptet, dass die SQE in Ordnung ist, und weiße auch immer daraufhin, dass Bugs bzw. alles was nachweislich mit SQE langsamer als mit CQE läuft IBM gemeldet werden sollte.
Nur in einem deutschen Forum anzuprangern, wie bescheiden die SQE ist (und inzwischen ist sie wirklich besser geworden) bringt m.E. nichts. Selbst wenn irgendein IBMer das Lesen sollte, ohne offiziellen Call tut sich da überhaupt nichts.
Auch wenn man auf dem kleinen Dienstweg die (inoffizielle) Bestätigung aus dem Lab hat, dass ein Bug vorliegt, muss man einen offiziellen Call starten bevor sich die ganze Maschinerie auch nur einen Milimenter bewegt. Man kann sich allenfalls auf denjenigen beziehen, der den Bug bestätigt hat.
IBM ist halt ein großer Beamtenstaat und auch nur ein großes Software-House.
Außerdem ... in den meisten Fällen in denen Leute über die SQE gejammert haben (u.a. weil sie aus solchen Foren entnommen haben, dass die neue Query Engine nur Schrott ist), hat sich herausgestellt, dass die Abfragen mit der CQE ausgeführt wurden z.B.
... weil logische Dateien in SQL-Statements angegeben wurden
...weil bei den meisten gewachsenen Datenbanken jede Menge logische Dateien mit Select/Omit-Anweisungen vorhanden sind
... weil eine Funktion verwendet wurde, die von der SQE nicht unterstützt wird usw.
Erstaunlicherweise wurden die meisten Abfragen (ca. 80-90%) schneller ausgeführt, nachdem sie auf die SQE gehievt worden waren (und nachdem entsprechende Zugriffswege erstellt wurden und die SQL-Statements umgeschrieben wurden).
Birgitta
wenn du wieder mal so ein Erlebnis hast, wäre ich dir sehr verbunden, wenn du dieses mit uns teilst...
Ich habe jedenfalls versucht mich an die Empfehlungen in Performance and Query Optimization zu halten und habe für eine Tabelle mit Mandanten Key mit einigen hundert Mio Sätzen, bei 6 Mandantenkey Ausprägungen erstellt mit SQL, kein RLA, alle SQL Statements auf Views, alles wie es sich gehört, alle selects ausgeführt mit SQE, EVIs angelegt wie ein Weltmeister, unter V5R3 und V5R4, unter mehreren PTF Ständen; das einzige was passiert ist, ist dass die Schreibperformance messbar gelitten hat. Löschen des EVIs vor schreiben und Neuaufbau danach, wie in der Reference empfohlen, lag dann beim Aufbau eines einzigen Indexes deutlich im Stundenbereich, bei einer Maschine mit parallel Database Feature, die in einer Stunde 30 Millionen commited Transactions wegpackt, das kann man nichtmal im Winter laufen lassen, wenn die Nächte länger sind.
Ach ja, und der Softwaresupport, das ist noch so ein Thema für sich, wenn man denen dann keine reproduzierbare Konstellation liefern kann, dann ist eh' Ende der Fahnenstange und den Aufwand, den man treibt steckt man dann oftmals gleich ins finden von Workarounds.
Deine Palette an Stichworten am Ende klingt erst mal beeindruckend, aber über Starschema Support und OLAP Funktionen scheint es auch sehr unterschiedliche Erfahrungswerte zu geben; nachdem ich einen halben Tag mit RANK rumlaboriert habe, um den 10. niedrigsten Preis zu finden, habe ich dann eine SQL Function geschrieben, die das in einem Bruchteil der Zeit erledigt.
D*B
Ich hab's schon erlebt, wenn EVIs über einzelne Felder gelegt wrrden, können z.T. sogar mehrere EVIs zum Erstellen von Bitmaps verwendet werden.
(Stichwort: Index anding and oring, Star Join Schema und Look ahead Predicat Generation LPG)
Birgitta
Ich sags ja, kaum dreht man an der einen Schraube läuft was anderes nicht.
Ich hatte ja in der QAQQINI den Wert 'IGNORE_DERIVED_INDEX' auf *YES gesetzt.
Dies führt nun dazu, dass so gut wie alle SQL's von der SQE durchgeführt werden.
Tja, nun stirbt ein Programm, dass schon lange (Februar 2005) läuft mit einem MCH3203-Fehler:
Nachricht . . . : Funktionsfehler X'1720' in Maschineninstruktion. Interne
Speicherauszugs-ID .
Ursache . . . . : Die Ausführung der Maschineninstruktion ist
fehlgeschlagen. Zeitmarke = 22.10.08 11:58:23, Fehlercode = X'1720',
Fehlerklasse = 0, Einheitennummer = X'0000'. Die Fehlerklasse zeigt an, wie
der Fehler entdeckt wurde:
0000 = nicht spezifizierte abnormale Bedingung
0002 = logisch ungültiger Einheitensektor
0003 = Einheitenfehler
0004 = ungültige Operation ausgeführt
Bei Fehlerklasse 0003 kennzeichnet die Einheitennummer die fehlerhafte
Einheit oder sie enthält Null, falls der Fehler im Hauptspeicher aufgetreten
ist.
Bei Fehlerklasse 0004 wurde ein nicht unterstützter Operationscode einer
MI-Instruktion verwendet.
Fehlerbeseitigung: Bei Fehlerklasse 0004 den nicht unterstützten
Operationscode der MI-Instruktion aus dem Programm entfernen. Bei allen
anderen Fehlerklassen die Problemanalyse starten (Befehl ANZPRB).
Nachrichten-ID . . . . : MCH3203 Bewertung . . . . . . : 60
Sendedatum . . . . . . : 22.10.08 Sendezeit . . . . . . : 11:58:28
Nachrichtenart . . . . : Abbruch
Von . . . . . . . . . : REMHOL_800 CCSID . . . . . . . . : 65535
Von Programm . . . . . . . . . : assert
Instruktion . . . . . . . . : 000004
An Programm . . . . . . . . . : QQQOOODBOP
An Bibliothek . . . . . . . : QSYS
An Modul . . . . . . . . . . : QQQOOOINV
An Prozedur . . . . . . . . : CALLDBMAINTFOROPENOROPTIMIZE
An Anweisung . . . . . . . . : 4139
Zusätzlich gibts einen 1419 Seiten Dump des Optimizers.
Ich habe nun in einem CLP per CHGQRYA auf eine andere, nicht modifizierte QAQQINI ungeschaltet und siehe da, der Befehl läuft wieder, da er nun von der CQE ausgeführt wird.
Der SQL ist eigentlich recht simpel und auch schnell:
c/exec sql
c+ with
c+ x1IVCL as -- alle offenen Inventuren
c+ (select distinct ivivnr from ivcl
c+ where ivfirm=:DAFIRM
c+ and ivwknr=:DAWKNR
c+ and ivlanr=:DASTLA
c+ and ivsart='KO'
c+ and ivstat<'80' -- erledigt/storniert
c+ )
c+ ,
c+ x1LGLM as -- alle Bestände aus LGST
c+ -- die nicht gesperrt sind
c+ (select cmfirm, cmwknr, cmlanr, cmlonr, cmtenr
c+ ,dec(sum(cmbest), 11, 2) as cmbest
c+ ,dec(sum(case tevpbm*tevpap -- auf palette
c+ when 0 then 1
c+ else cmbest / (tevpbm*tevpap) -- Anz.Paletten
c+ end)
c+ , 11, 5) as cmpale
c+ from lglm
c+ inner join teil
c+ on cmfirm=tefirm
c+ and cmwknr=tewknr
c+ and cmtenr=tetenr
c+ inner join LGST
c+ on cmfirm=clfirm
c+ and cmwknr=clwknr
c+ and cmlanr=cllanr
c+ and cmlonr=cllonr
c+ where cmfirm=:DAFIRM
c+ and cmwknr=:DAWKNR
c+ and cmlanr=:DASTLA
c+ and clspgr <> '09' -- Auslagerung anstehend
c+ and clakiv not in (select ivivnr from x1IVCL)
c+ and clivda < :GZDAVON -- Letzte Inventur < Grenze
c+ group by cmfirm, cmwknr, cmlanr, cmlonr, cmtenr
c+ )
c+
c+ -- Sammeln der Daten
c+
c+ select min(cllonr) -- erster Platz
c+ ,max(cllonr) -- letzter Platz
c+ ,count(cllonr) -- Anzahl Plätze
c+
c+ ,(select count(*) from LGST -- Anzahl gezählt
c+ where clfirm=:DAFIRM
c+ and clwknr=:DAWKNR
c+ and cllanr=:DASTLA
c+ and clivda >= :GZDAVON -- Letzte Inventur
c+ and abs(clfr04) <= :DAAGNR -- Mengendiff %
c+ and abs(clfr05) <= :DAKONR -- Wertdiff %
c+ )
c+
c+ ,(select count(*) from LGST -- Anzahl Differenz
c+ where clfirm=:DAFIRM
c+ and clwknr=:DAWKNR
c+ and cllanr=:DASTLA
c+ and clivda >= :GZDAVON -- Letzte Inventur
c+ and clakiv not in (select ivivnr from x1IVCL)
c+ and (abs(clfr04) > :DAAGNR -- Mengendiff %
c+ or abs(clfr05) > :DAKONR) -- Wertdiff %
c+ )
c+
c+ ,(select count(*) from LGST -- leere Plätze
c+ exception join LGLM
c+ on clfirm=cmfirm
c+ and clwknr=cmwknr
c+ and cllanr=cmlanr
c+ and cllonr=cmlonr
c+ where clfirm=:DAFIRM
c+ and clwknr=:DAWKNR
c+ and cllanr=:DASTLA
c+ and clakiv not in (select ivivnr from x1IVCL)
c+ and clivda < :GZDAVON -- Letzte Inventur
c+ )
c+
c+ ,(select count(distinct cllonr) -- Anzahl ohne Res.
c+ from LGLM
c+ inner join LGST
c+ on cmfirm=clfirm
c+ and cmwknr=clwknr
c+ and cmlanr=cllanr
c+ and cmlonr=cllonr
c+ inner join teil
c+ on cmfirm=tefirm
c+ and cmwknr=tewknr
c+ and cmtenr=tetenr
c+ where clfirm=:DAFIRM
c+ and clwknr=:DAWKNR
c+ and cllanr=:DASTLA
c+ and clakiv not in (select ivivnr from x1IVCL)
c+ and clivda < :GZDAVON -- Letzte Inventur
c+ and teresb = 0 -- keine Reservierung
c+ )
c+
c+ ,(select count(distinct cmlonr) -- Anzahl volle Palette
c+ from x1LGLM
c+ where floor(cmpale) = cmpale -- Ganzzahl
c+ )
c+
c+ ,(select count(distinct cmlonr) -- Anzahl Anbruchpalette
c+ from x1LGLM
c+ where floor(cmpale) <> cmpale -- Ganzzahl
c+ )
c+
c+ ,(select count(*) from LGST -- Anzahl für Inventur
c+ where clfirm=:DAFIRM
c+ and clwknr=:DAWKNR
c+ and cllanr=:DASTLA
c+ and clakiv not in (select ivivnr from x1IVCL)
c+ and clivda < :GZDAVON -- Letzte Inventur
c+ and clspgr <> '09' -- Auslagerung
c+ )
c+
c+ into :WKLGMI
c+ ,:WKLGMX
c+ ,:WKLGAN
c+ ,:WKLGGZ
c+ ,:WKANPD
c+ ,:WKANLE
c+ ,:WKANRS
c+ ,:WKANVP
c+ ,:WKANAP
c+ ,:WKANSU
c+ from lgst
c+ where clfirm=:DAFIRM
c+ and clwknr=:DAWKNR
c+ and cllanr=:DASTLA
Und nur wegen der SQE nun diesen SQL umzuschreiben finde ich nicht sehr amüsant.
das ist wohl ein einfacher Fall für eine Fehlermeldung.
BTW: ich habe diesen Fall mal zum Anlass genommen mir die QAQQINI defaults genauer anzuschauen => da scheinen die Entwickler selber den Fähigkeiten der SQE unterschiedlich zu trauen.
FORCE_JOIN_ORDER default *NO favorisiert SQE
IGNORE_DERIVED_INDEX default *NO favorisiert CQE
... ließe sich fortsetzen
D*B
Fehlermeldung hin oder her, das Problem ist doch, dass die IBM dann immer relativ lange benötigt ein PTF zu erstellen /Tage bis Wochen).
Wenn Anwendungen aber massiv beeinträchtigt werden, ist die Wartezeit ziemlich heftig.
Ich hege die Befürchtung, dass uns die CQE irgendwann verläßt und Betriebe dann stillstehen.
Wer kann es sich schon leisten, die gesamte Anwendung auf einem neuen Release (teilweise durch neue Hardware bedingt) mit tausenden von Funktionen und Programmen zu testen und dann ggf. auf den Einsatz zu verzichten.
Insbesonders dann, wenn die alte Hardware aus z.B. Leasing und Wartung ausläuft und man die neue nehmen muss.
Muss mich jetzt hier mal aus DAU outen.
Was ist er Unterschied zwischen SQE und CQE.
Gruß Ronald
CQE := classic query engine (hatte vor V5R1 keinen Namen)
SQE := SQL query engine; wird seit V5R1 parallel zur alten entwickelt und mit jedem PTF und Release ein wenig mehr installiert.
Die beiden existieren parallel, weil die alte (CQE) die neuen Features nicht kann und die neue die alten (noch) nicht alle kann und je nach SQL Statement muss die Arbeit von der neuen, oder der alten gemacht werden, oder der Optimizer darf würfeln welche er nimmt.
Mit anderen Worten: seit V5R1 ist die Datenbank permanent im Beta Status und der arme User (aka DAU) darfs ausbaden.
Dieter Bender
Muss mich jetzt hier mal aus DAU outen.
Was ist er Unterschied zwischen SQE und CQE.
Gruß Ronald
Wobei ich jetzt halt gemerkt habe:
IGNORE_DERIVED_INDEX = *YES zwingt zu 99,99% in die SQE.
IGNORE_DERIVED_INDEX = *NO (*DEFAULT) und LF mit Select/Omit zwingt zu 99,99% in die CQE.
Ansonsten gilt halt, bestimmte neue SQL-Funktionen sind in der CQE nicht mehr implementiert, so dass die SQE zwangsläufig verwendet werden muss.
Andersherum gilt auch, dass es in der SQE noch nicht implementierte Funktionen gibt, so dass eben doch die CQE genommen wird.
PS:
optimize for n rows:
Alle meine Versuche, die selben Antwortzeiten bzgl. des 1. Satzes wie mit STRSQL zu erreichen, sind kläglich gescheitert.
Egal ob ich ODBC oder embedded SQL verwende, den Weg, den STRSQL geht kann ich leider nicht nachvollziehen.
Ein SQL in STRSQL ist häufig sehr schnell, die Debug-Nachrichten werden untersucht, ggf. angewendet.
Spätestens im embedded SQL läuft da irgendwas anders, so dass diese Antwortzeiten einfach nicht erreichbar sind.
- das war bisher einer der elegantesten Workarounds im Problemfall an der SQE vorbei zu kommen.
- STRSQL: hast du optimize for 1 rows direkt beim select angegeben?
ansonsten wird interaktiv meist CPU schonend gearbeitet (betrifft auch embedded SQL im 5250) und oft ist da auch noch caching im Spiel und interaktiv ist immer read only und blocken ist erlaubt. Wenn du dann immer noch Laufzeitdifferenzen hast, dann wären die Diagnostics des Optimizers bezüglich der Ausführung noch interessant, am Besten per DBMON ermittelt.
D*B
PS: wobei ich immer noch glaube, dass du am leuchten in den Augen noch arbeiten musst, wenn du "s la vache qui rit engine" vor dich hin murmelst während der Optimizer den optimalen Zugriffsweg zusammenbraut.
IGNORE_DERIVED_INDEX = *NO (*DEFAULT) und LF mit Select/Omit zwingt zu 99,99% in die CQE.
PS:
optimize for n rows:
Alle meine Versuche, die selben Antwortzeiten bzgl. des 1. Satzes wie mit STRSQL zu erreichen, sind kläglich gescheitert.
Egal ob ich ODBC oder embedded SQL verwende, den Weg, den STRSQL geht kann ich leider nicht nachvollziehen.
Ein SQL in STRSQL ist häufig sehr schnell, die Debug-Nachrichten werden untersucht, ggf. angewendet.
Spätestens im embedded SQL läuft da irgendwas anders, so dass diese Antwortzeiten einfach nicht erreichbar sind.