Performance - SQL-Abfrage nach längerer Pause/IPL nicht ausführbar

[berndl]

Hallo zusammen,

wir haben ein rekursives SQL auf zwei Stücklisten-Tabellen. Innerhalb jeder Ebene wird dabei noch nach einem Merkmal einer dritten Tabelle sortiert (hierfür wird ein Sortierkriterium aufgebaut / concatiniert).

Das SQL funktioniert eigentlich super (Ausführung ca. 20 - 500 ms), auch für auf grosse Ergebnismengenn. Nur wenn eine Kleinigkeit am SQL ändert oder die Abfrage schon länger nicht mehr ausgeführt wurde(IPL?), braucht es wieder ein paar Ausführungen, bis das Teil performant läuft, das wäre auch okay.

Normalerweise werden bis zu 10 Sätze in höchsten 3 Verschachtelungen zurückgegeben. Wird nach einer Änderung / Pause aber zufällig auf einen umfangreicheren Datenbestand mit mehr als 100 Ergebnissätzen abgefragt (ca. 0.1 % der gesamten Fälle), kann eine Antwort Stunden benötigen. Der Index-Advisor macht keine Vorschläge mehr zu dem SQL.

Umgebung: V6R1, PF + LF mit DDS beschrieben, keine Indizes, SQL aus ILE-RPG abgesetzt, SQL wird von SQE akzeptiert.

Kann mir jemand einen Tipp geben, wo ich hier anfangen sollte? SQL-Performance-Optimierung hab ich bisher nur mit Anpassung der Abfragen gemacht und LFs entsprechend dem Index-Advisor erstellt (echte Indizes haben wir bisher vermieden).

Herzlichen Dank schon mal
Bernd

[Fuerchau]

Das Problem wird die Sortierung sein da du den Begriff zusammenbaust.
Kannst du da nicht mit direkten Feldern sortieren und für diese dann halt einen Index anlegen?

[berndl]

Leider geht das nicht. Je nach Mandant und Abteilung ist eine andere Sortierfolge zwingend, deswegen muss diese jeweils berechnet werden.

Da weder Firma noch Mandant direkt auf der Stückliste enthalten sind, kann ich diese auch nicht direkt dazu joinen, sondern muss den Mandant und die Abteilung noch in der Joinbedingung angeben.

[Fuerchau]

Trotzdem würde mich diese "Berechnung" interessieren.

Anstelle von

select ...
, Key1 concat key2 as sortkey
...
order by sortkey

müsste doch auch ein "order by key1, key2" genügen.
Oder fügst du noch bedingte Konstanten ein?

Wenn in der Stückliste die Schlüssel Mandant und/oder Firma fehlen, benötigst du diese ja eigentlich gar nicht für den Sort, da du diese ja über ggf. einen Subselect/CTE hinzufügst.

Somit beschränkt sich das Problem auf die Abteilung.
Auch hier ist die Frage, ob diese dann nicht auch mehr oder weniger konstant ist.

Auch ist die Art des Cursors wichtig.
Ich hoffe es ist kein scrollable (sensitive) Cursor, das verhindert eine Kopie der Daten und erzwingt ggf. einen temporären Index über den gesamten Datenbestand, was die Antwortzeit erklärt.

Kannst du den SQL mal posten?

[BenderD]

... die Problembeschreibung deutet eher darauf hin, dass ein Zugriffsplan gecached wird und bei IPL oder Nichtbenutzung aus dem cache verdrängt, bzw. der cache gelöscht wird. Bei der dann fälligen Neuberechnung dann Schätzungen für Selektivität die Planberechnung mal so, mal so ausfallen lassen.

Ich sehe da zwei Ansätze:
1. Ablage des Zugriffsplanes in einem Package forcieren durch zwischenschalten einer UDTF.
2. Überarbeitung des Datenbankdesigns - Ansatz: Vermeidung des Bastelfeldes durch mehr Redundanz (könnte eventuell auch eine zusätzliche Tabelle sein, die automatisch mitgepflegt wird)

Wobei Ansatz 2 wahrscheinlich mehr hergibt.

D*B

[berndl]

Hier mal das SQL:

WITH LISTE
(GESHID, BTLHID, PTAHID, PTAHIDALTK, PTASTUFE, VERTRETENER, EBENE, PTGPTAGRP, PTG4STEUER, SORT ) AS
(
SELECT B.GESHID, B.BTLHID, B.PTAHID, PA.PTAHIDALTK, PA.PTASTUFE, 0 AS VERTRETENER,
DEC(1) AS EBENE, PG.PTGPTAGRP, PG.PTG4STEUER,
'E1001' CONCAT 'P' CONCAT TRIM(CHAR(PA.PTASEQ+100000)) CONCAT '#'
CONCAT TRIM(CHAR(B.BTLHID + 10000)) CONCAT '.' AS SORT
FROM RIPBTL AS B
LEFT OUTER JOIN RIPBTV V ON B.GESHID=V.GESHID AND B.BTLHID=V.BTV4VBTLID
JOIN RIPPTG PG ON PG.FIRHID = 22 AND PG.GGPHID= 39 AND PG.PTAHID=B.PTAHID
JOIN RIPPTA PA ON B.PTAHID = PA.PTAHID
WHERE B.GESHID = 333661 AND V.GESHID IS NULL
UNION ALL
SELECT V.GESHID, B.BTLHID, B.PTAHID, PA.PTAHIDALTK, PA.PTASTUFE,
V.BTLHID AS VERTRETENER, X.EBENE + 1, PG.PTGPTAGRP, PG.PTG4STEUER,
TRIM(X.SORT) CONCAT 'E' CONCAT TRIM(CHAR(X.EBENE + 1001)) CONCAT 'P'
CONCAT TRIM(CHAR(PA.PTASEQ+100000)) CONCAT '#' CONCAT
TRIM(CHAR(B.BTLHID+10000)) CONCAT ';'
FROM RIPBTV V
JOIN RIPBTL B ON V.GESHID=B.GESHID AND V.BTV4VBTLID=B.BTLHID
JOIN RIPPTG PG ON PG.FIRHID = 22 AND PG.GGPHID=39 AND PG.PTAHID=B.PTAHID
JOIN RIPPTA PA ON B.PTAHID = PA.PTAHID
INNER JOIN LISTE AS X ON V.GESHID = X.GESHID AND V.BTLHID = X.BTLHID
WHERE V.GESHID=333661 AND X.EBENE <= 5
)
SELECT GESHID, BTLHID, PTAHID, PTAHIDALTK, PTASTUFE, VERTRETENER, EBENE,
PTGPTAGRP, PTG4STEUER, SORT FROM LISTE ORDER BY SORT

Grundsätzlich läuft das Teil ja ziemlich schnell. Es würde auch nicht stören, wenn es nach dem Deployment oder IPL das erste mal ein 2 oder 3 Minuten braucht. Das aber schlichtweg keine Antwort (bzw. stundenlang nichts kommt) ist auch bei 0.5 % der Fälle nicht tragbar - das müsste ich irgendwie hin bekommen.

[B.Hauser]

Ich schätze mal, dass MTIs (Maintained Temporary Indices) gebildet werden.
MTIs sind temporäre Indices, deren Aufbau solange dauert wie der Aufbau eines permanenten Indices. Sobald ein MTI aufgebaut ist, kann er von allen SQL-Abfragen, die mit der SQE arbeiten verwendet werden, bis er aus dem SQL Plan Cache verschwindet.
Ein MTI verschwindet aus dem Plan Cache, sofern dieser überläuft und der MTI der am längsten nicht verwendete Index ist, oder bei IPL. Bei IPL wird der komplette Plan Cache gecleart, d.h. alle MTI müssen in Folge neu aufgebaut werden.

Das würde zumindest das Verhalten erklären, warum die Abfrage nach IPL extrem lange dauert und anschließend wieder ratzfatz läuft.

Sofern für eine Tabelle MTIs gebildet wurden, sind diese im System i Navigator sichtbar - Rechtsclick auf Tabelle und Indices anzeigen. Wurden tatsächlich MTIs angelegt, und werden diese häufig verwendet, sollte hierfür ein permanenter Index angelegt werden (Rechtsclick auf den Index und Erstellen).
Beim nächsten IPL sollten die Performance Probleme nicht mehr auftreten, da der permanente Index nicht gelöscht wird.

P.S. Das hat nur am Rande was mit Performance zu tun, aber ich würde eine SQL-View erstellen in der die Tabellen miteinander verknüpft werden und diese im SQL-Statement verwenden.

Birgitta

[andreaspr@aon.at]

Du solltest auch ein Datenbank-Monitor drüber laufen lassen.
Dann siehst du welche Zweige am meisten verbrauchen und ob MTIs verwendet werden.
(Auch wenn die Abfrage innerhalb von 500ms durch ist)

[Fuerchau]

Anstelle des komplexen Concats würde ich doch Einzelfelder empfehlen, wobei fehlende Felder im 1. Union-Select durch konstanten definiert werden können.

Die Sortierung bzw. der Concat kann dann im Hauptselect erfolgen, da dieses ggf. nur noch auf das Ergebnis angewendet wird.

[berndl]

Vielen Dank Euch allen für die Tipps - hier mal eine Zusammenfassung:

Ich hab folgende Änderungen vorgenommen:
* Die JOIN-Orgien sind jetzt in zwei SQL Views ausgelagert.
* Im rekursiven SQL nehm ich nur noch auf die Views bezug.
* Auf das Sortierkriterium hab ich einfach mal verzichtet
* Gem. Visual-Explain wurden nur vorhandene LFs (DDS) verwendet - konnte keine MTI finden (kenne mich hier aber nicht so aus).

WITH LISTE
(GESHID, BTLHID, PTAHID, PTAHIDALTK, PTASTUFE, VERTRETENER, EBENE, PTGPTAGRP, PTG4STEUER) AS
(
SELECT GES.GESHID, GES.BTLHID, GES.PTAHID, GES.PTAHIDALTK, GES.PTASTUFE, 0 AS VERTRETENER,
DEC(1) AS EBENE, GES.PTGPTAGRP, GES.PTG4STEUER
FROM GES1 AS GES
WHERE GES.GESHID=333661 AND GES.FIRHID=22 AND GES.GGPHID =39
UNION ALL
SELECT V.GESHID, V.VERTRETER, V.PTAHID, V.PTAHIDALTK, V.PTASTUFE,
V.VERTRETENER AS VERTRETENER, X.EBENE + 1, V.PTGPTAGRP, V.PTG4STEUER
FROM BTV1 V
INNER JOIN LISTE AS X ON V.GESHID = X.GESHID AND V.vertretener = X.BTLHID
WHERE V.GESHID=333661 AND V.FIRHID=22 AND V.GGPHID =39 AND X.EBENE <= 5
)
SELECT GESHID, BTLHID, PTAHID, PTAHIDALTK, PTASTUFE, VERTRETENER, EBENE,
PTGPTAGRP, PTG4STEUER
FROM LISTE;

---
Auswirkungen:
* Die Abfrage braucht ca. 10 - 20x länger (auch nach mehrfacher Ausführung), der Cache scheint jetzt nicht mehr genutzt zu werden
* Abfragen mit mehr als 20 Sätzen kommen innerhalb einer halben Stunde nicht mehr zurück (hab dann abgebrochen)
* Gem. VisualExplain wird statt SQL-Views weiterhin auf die DDS-LFs zugegriffen, obwohl alle verwendeten Felder in den Views sind.

Aus Zeitgründen hab ich das Ganze jetzt auf Left-Outer-Joins für max. 10 Ebenen umgestellt - auch wenn das nicht ganz sauber ist. Trotzdem nochmals herzlichen Dank für all die Ideen!

Kann mir jemand eine Einführungs-Lektüre zur SQL-Optimierung auf der AS empfehlen - ich glaube, ich brauch hier noch etwas Grundwissen zu VisualExplain / PerformanceMonitor und QAQQINI. Im Moment fehlt mir hier absolut der Überblick...

merci!

[andreaspr@aon.at]

Das wird dir sicher weiter helfen können:
http://publib.boulder.ibm.com/infoce...zajq/rzajq.pdf

Eine View siehst du im Visual Explain nicht als eigenes Objekt. Eine View selbst wirkt sich auch nicht auf die Performance aus, da das Select der View hergenommen wird.
Wenn du also ein

Select * from MyView

machst, siehst du im Visual Explain nicht MyView, sondern das Select, welches sich in MyView befindet.
Es ist das gleiche, wie wenn du das Select direkt abgesetzt hättest.

Views werden eher für Übersichtlichkeit und Berechtigungen verwendet.

lg Andreas

[B.Hauser]

Auswirkungen:
* Gem. VisualExplain wird statt SQL-Views weiterhin auf die DDS-LFs zugegriffen, obwohl alle verwendeten Felder in den Views sind.

Kann mir jemand eine Einführungs-Lektüre zur SQL-Optimierung auf der AS empfehlen - ich glaube, ich brauch hier noch etwas Grundwissen zu VisualExplain / PerformanceMonitor und QAQQINI. Im Moment fehlt mir hier absolut der Überblick...

merci!

1. Sind in der Visual Explain übersicht irgendwo temporary Indices aufgeführt? Wenn ja, so sind dies die MTI
2. Sofern DDS beschriebene logische Dateien verwendet werden, erstelle zusätzlich Indices mit den gleichen Key-Feldern wie die DDS beschriebenen logischen Dateien. Indices werden aufgrund der größeren Pagesize vom Optimizer bevorzugt.
Die bestehenden DDS beschriebenen logischen Dateien sollten anschließend gelöscht und neu erstellt werden. Dann können die logischen Dateien den gleichen Zugriffspfad wie die SQL Indices verwenden und erhalten zusätzlich eine Pagesize von 64 K.

Was Performance Optimierung hier ist ein Link aus dem Information Center:
Database performance and query optimization


Das folgende Whitepaper sollte auch unbedingt gelesen werden:
IBM DB2 for i
indexing methods and strategies

Birgitta