Ferne SQL Analyse / Performance

[pwrdwnsys]

Hallo Forum,

ich arbeite schon sehr lange auf der AS400 und kenne mich recht gut aus. Habe jetzt das Problem, das ein externes Produjt via JDBC auf eine S/20 zugreift, allerdings sind die Zugriffe sehr langsam. Es werden viele temporäre Zugriffspfade aufgebaut, so viel habe ich schon gesehen. Normalerweise starte ich bei so etwas immer den Debugger und werte das Joblog aus. Aber wie mache ich das bei den QZDASOINIT-Jobs ? Wie kriege ich hier die (sonst sehr guten) Vorschläge der AS400 für den optimalen Zugriffspfad ?

Wäre dankbar für einen kurzen Tip. Ich weiß zwar auch, das ich die Statements über einen Exit-Point in der Registrierung abfragen kann, aber das ist mir jetzt zu aufwändig. Wie immer muss es schnell gehen....

[B.Hauser]

Hallo Forum,

ich arbeite schon sehr lange auf der AS400 und kenne mich recht gut aus. Habe jetzt das Problem, das ein externes Produjt via JDBC auf eine S/20 zugreift, allerdings sind die Zugriffe sehr langsam. Es werden viele temporäre Zugriffspfade aufgebaut, so viel habe ich schon gesehen. Normalerweise starte ich bei so etwas immer den Debugger und werte das Joblog aus. Aber wie mache ich das bei den QZDASOINIT-Jobs ? Wie kriege ich hier die (sonst sehr guten) Vorschläge der AS400 für den optimalen Zugriffspfad ?

Wäre dankbar für einen kurzen Tip. Ich weiß zwar auch, das ich die Statements über einen Exit-Point in der Registrierung abfragen kann, aber das ist mir jetzt zu aufwändig. Wie immer muss es schnell gehen....

Die einfachste Methode ist über iSeries Navigator für den Job eine detaillierte Leistungs-Überwachung zu starten. Die einzelnen Statements und die benötigten Zeiten, können aufgelistet werden und über Visual Explain ausgewertet werden.
Visual Explain verfügt auch über einen Index Advisor, der die benötigten Indices vorschlägt. Die Indices können dann auch per Knopf-Druck erstellt werden.

Zusätzlich werden alle Informationen in Dateien geschrieben, die via Query oder SQL ausgewertet werden können.
Statt über iSeries Navigator kann der Database Monitor auch über den CL-Befehl STRDBMON gestartet werden.

Die folgende SQL-Abfrage ermittelt aus der DBMON-Protokoll-Datei die vorgeschlagenen Indices:

SELECT qqucnt, substr(qvqtbl, 1, 10) "Datei",                    
       substr(qvqlib, 1, 10) "Bibliothek",                       
       qqi2 "Anzahl Primary Keys",                               
       Substr(qqidxd, 1, 100) "Vorgeschlagene Schluessel",    
FROM MyLib/MyDBMON a                                         
WHERE qqrid IN (3000, 3001, 3002) and qqidxa='Y'                 
ORDER BY 3, 2, 5 

Birgitta

[NEich]

Hi Forum,

mich plagt ein ähnliches Problem... nur...
ich habe hier einige Views, die wollen unter keinen Umständen die Indices benutzen die bereits da sind. Visual Explain schlägt mir immer wieder diese Indices vor, obwohl diese längst vorhanden sind. Es handelt sich dabei sogar um völlig einfache wie z.B. Index auf dem Primary Key.

Ich habe auch schon versucht, diese komplett zu löschen und neu anzulegen, aber auch das blieb ohne Erfolg.

Ich weiss zwar, dass der Query Optimizer nicht unbedingt diese Indices benutzen muss, aber die Views sind sowas von Prozessorbelastend dass ich mich langsam frage, ob nicht irgendwas an unserer DB fehlerhaft ist. (Die Tabellen dahinter haben verhältnismässig wenige Einträge im 10.000 Bereich)

Leider gehen mir inzwischen die Ideen aus, so dass ich fast dazu übergehen möchte jemanden zu beauftragen der hier bei uns etwas optimiert, aber das ist wohl der letzte Schritt der mir so einfallen würde.

Oder gibt es noch andere Methoden Abfragen zu verbessern (JOIN-Reihenfolge, Indexzwang o.ä) ?

[B.Hauser]

Hi Forum,

mich plagt ein ähnliches Problem... nur...
ich habe hier einige Views, die wollen unter keinen Umständen die Indices benutzen die bereits da sind. Visual Explain schlägt mir immer wieder diese Indices vor, obwohl diese längst vorhanden sind. Es handelt sich dabei sogar um völlig einfache wie z.B. Index auf dem Primary Key.

Ich habe auch schon versucht, diese komplett zu löschen und neu anzulegen, aber auch das blieb ohne Erfolg.

Ich weiss zwar, dass der Query Optimizer nicht unbedingt diese Indices benutzen muss, aber die Views sind sowas von Prozessorbelastend dass ich mich langsam frage, ob nicht irgendwas an unserer DB fehlerhaft ist. (Die Tabellen dahinter haben verhältnismässig wenige Einträge im 10.000 Bereich)

Leider gehen mir inzwischen die Ideen aus, so dass ich fast dazu übergehen möchte jemanden zu beauftragen der hier bei uns etwas optimiert, aber das ist wohl der letzte Schritt der mir so einfallen würde.

Oder gibt es noch andere Methoden Abfragen zu verbessern (JOIN-Reihenfolge, Indexzwang o.ä) ?

Es ist natürlich schwer irgendetwas dazu zu sagen, ohne die Dateien die Abhängigkeiten zwischen den Dateien und die Abfagen zu kennen.

Es gibt gewisse Möglichkeiten, durch das Umschreiben der SQL-Abfragen Einfluß auf die Index-Auswahl zu nehmen. Es spielt auch eine Rolle, ob die SQL-Abrage mit der alten/Classical Query Engine (CQE) oder der neuen Standard Query Engine (SQE) ausgeführt wird, ob die Zugriffs-Pfade in DDS beschriebenen logischen Dateien oder SQL Indices gespeichert sind. u.v.m.

Den einzigen Tipp, den ich Dir geben kann, zieh Dir die Indexing Strategie von Michael Cain rein (sofern Du sie noch nicht kennst)
Indexing and statistics strategies for DB2 UDB for iSeries

Weiter Quellen sind:
Query performance and query optimization
oder
Preparing for and Tuning the V5R2 SQL Query Engine on DB2 Universal Database for iSeries

Aber selbst dann, wenn man das alles auswendig beherrscht und sich auch an alles gehalten hat, ist man vor Überraschungen nicht sicher.

Birgitta

[pwrdwnsys]

Hi Forum,

mich plagt ein ähnliches Problem... nur...
ich habe hier einige Views, die wollen unter keinen Umständen die Indices benutzen die bereits da sind. Visual Explain schlägt mir immer wieder diese Indices vor, obwohl diese längst vorhanden sind. Es handelt sich dabei sogar um völlig einfache wie z.B. Index auf dem Primary Key.

Ich habe auch schon versucht, diese komplett zu löschen und neu anzulegen, aber auch das blieb ohne Erfolg.

Ich weiss zwar, dass der Query Optimizer nicht unbedingt diese Indices benutzen muss, aber die Views sind sowas von Prozessorbelastend dass ich mich langsam frage, ob nicht irgendwas an unserer DB fehlerhaft ist. (Die Tabellen dahinter haben verhältnismässig wenige Einträge im 10.000 Bereich)

Leider gehen mir inzwischen die Ideen aus, so dass ich fast dazu übergehen möchte jemanden zu beauftragen der hier bei uns etwas optimiert, aber das ist wohl der letzte Schritt der mir so einfallen würde.

Oder gibt es noch andere Methoden Abfragen zu verbessern (JOIN-Reihenfolge, Indexzwang o.ä) ?

In der Regel liegt das an zu vielen Indexdateien. Der Optimizer der iSeries hat nur eine gewisse Zeitspanne zur Verfügung, um alle Indexdateien zu analysieren. Nach Ablauf dieser Zeit wird der bis dahin gefundene optimalste Zugriffspfad herangezogen oder ein temporärer erstellt, was dann perfomancemäßig ordentlich Zeit kostet.
So ähnlich stellte sich mein obiges Problem heute auch dar. Leider habe ich auf diese Maschine (300km weit weg) nur einen Telnet-Zugriff, der grafische Schnickschnack fällt also weg. Habe trotzdem heute ordentlich Performance gewonnen (einige Indexe gelöscht, neue erstellt). Das nächste Problem steht aber schon an (JDBC Zugriff zu langsam....)

[NEich]

@BHauser Danke für die Tips und die Links, ich hoffe die bringen mich weiter. Es handelt sich übrigens hierbei um reine SQL Tabellen/Indices mit Contraints und tadeldü, die Abfragen laufen über JDBC (welches ansonsten auch keine Schwierigkeiten macht), für fast alle Abfragen sind Views vorhanden, die so zwischen 10 und 30 Tabellen verknüpfen (nicht schimpfen, das ist nicht von mir)

@pwrdwnsys hatte icha uch schon in Betracht gezogen, jedoch sagt mir Visual Explain dass keinerlei QO Zeitüberschreitung stattgefunden hat.

[pwrdwnsys]

@BHauser Danke für die Tips und die Links, ich hoffe die bringen mich weiter. Es handelt sich übrigens hierbei um reine SQL Tabellen/Indices mit Contraints und tadeldü, die Abfragen laufen über JDBC (welches ansonsten auch keine Schwierigkeiten macht), für fast alle Abfragen sind Views vorhanden, die so zwischen 10 und 30 Tabellen verknüpfen (nicht schimpfen, das ist nicht von mir)

@pwrdwnsys hatte icha uch schon in Betracht gezogen, jedoch sagt mir Visual Explain dass keinerlei QO Zeitüberschreitung stattgefunden hat.

@NEich, das Problem das ein Zugriffspfad nicht verwendet wird hatte ich heute zufällig auch und davon gleich mehrere. Das einzige was mir dabei aufgefallen ist: alle diese nicht verwendeten Zugriffspfade verwenden Felder, deren Feldnamen länger als 10 Stellen sind. Normalerweise arbeitet die AS400 ja nur mit den 10 stelligen Feldnamen, die langen Feldnamen werden als ALIAS-Namen angelegt. Erfolgt der Zuriff nun aber über die "langen" Namen, dann kann der QO das vielleicht nicht erkennen und "vermutet" einen artfremden Zugriffspfad. Ist die einzige Erklärung die ich dafür habe. Werde das mal weiter analysieren.

Wer einen Tipp hat, wie ich das umgehen oder abstellen kann, immer her damit.

[NEich]

[...] alle diese nicht verwendeten Zugriffspfade verwenden Felder, deren Feldnamen länger als 10 Stellen sind[...]

Klingt nach nem guten Ansatz, würde einiges bei mir erklären können, denn genau diese (unverhältnismässig) performancelastigen Tabellen haben Prosatexte als Feldnamen.
Würde mich aber wundern, wenn der Optimizier das ignorieren würde :/

[pwrdwnsys]

So, lange Analyse, und im Endefeffekt einige Statements von gut 1 min Laufzeit auf unter 1s gedrückt. Das Problem sind die Statements selbst. Und hat auch nichts mit den Aliasnamen zu tun. Der Optimizer der AS400-DB2 scheint schlechter zu arbeiten als der LINUX-DB2 Optimizer. Denn der zeigt bessere Ergebnisse und verwendet auch bei gleichen Tabellen / Indizes solche.
Beispiel :

SELECT A.FLD1, A.FLD2, A.FD3, B.FLD1, B.FLD2
from TABELLE1 A, TABELLE2 B where A.FLD1 = B.FLD1
and B.FLD2 in (select C.FLD2 from TABELLE3 WHERE C.FLD3 = X and C.FLD4 = y)

Soweit sogut.Viele Tests mit Views / Indizes haben nix gebracht. Das Statement anders formuliert geht granatenschnell.

SELECT A.FLD1, A.FLD2, A.FLD3, B.FLD1, B.FLD2, C.FLD3, C.FLD4
from TABELLE3 C, TABELLE1 A, TABELLE2 B
where A.FLD1 = B.FLD1
and B.FLD2 = C.FLD2C.FLD2
where C.FLD3 = X and C.FLD4 = y

Und jetzt kommts : Wenn ich in der FROM-Klausel die Tabellenreihenfolge ändere (Tabelle C weiter hinten) dann wirds wieder wesentlich langsam.

SELECT A.FLD1, A.FLD2, A.FD3, B.FLD1, B.FLD2, C.FLD3, C.FLD4
from TABELLE1 A, TABELLE2 B, TABELLE3 C
where A.FLD1 = B.FLD1
and B.FLD2 = C.FLD2C.FLD2
where C.FLD3 = X and C.FLD4 = y

Anders ist die Datenbank nicht zur performanten Zusammenarbeit zu bewegen. Eigentlich fast logisch, denn die Ergbnismenge von C ist (in diesem Fall) die kleinste. Dabei handelt sich aber um eine Tabell mit gut 1.000.000 Datensätzen

[BenderD]

Hallo,

so nach dem Urlaub noch ein paar gesammelte Anmerkungen:
- der Query Otimizer (besser Pessimizer) ist milde ausgedrückt wirklich nicht der beste
- falls es sich um V5R3 handelt, spricht vieles für einen Bug, egal welches Phänomen zu beobachten ist.
- alles was Rekursion beinhaltet ist meist langsam und kann durch Umformulierung in einen Join (soweit möglich) schneller gemacht werden
- Feldnamen sollte egal sein
- unter 50 Indexen sollte der Pessimizer locker fertig werden, sonst ist was anderes faul.
- Indexe werden ignoriert, wenn die CCSID nicht matched
- bei JDBC gab es auch schon Probleme mit dem extended dynamic package support
- die Begeisterung über die neue Query Engine kann ich nicht teilen, da habe ich Verbesserungen nur beim parallel Database Feature gesehen und vieles andere, was schlechter geworden ist, sieht alles sehr nach Beta Version aus.
- für die Telnet Problematik: da könnte man auch den Debug aktivieren (per Driver Option? habe ich nicht im Kopf) oder per stored Procedure Aufruf, oder per STRSRVJOB - dann werden die Diagnostics ins Joblog gemalt.
- zu Visual Explain fällt mr nur ein, dass ich die Schnittmusterbogen meiner Oma auch nie verstanden habe.

mfg

Dieter Bender

[Fuerchau]

Seit der Umstellung auf V5R3 kann ich bezüglich dem Pessimzer Dieter nur zustimmen. Die geschätzten Abfragezeiten sind teilweise um Faktor 1000 - 10.000 zu hoch !!!

Da wird eine Abfrage (mit Union-Select über ca. 2 Mio Sätze) mit über 100.000 Sekunden geschätzt wobei die tatsächliche Abfrage dann nur ca. 15 Sekunden benötigt.

Es gibt allerdings kurzfristig eine Umgehung:

Wird eine LF (mit DDS und keine SQL-VIEW) mit SELECT/OMIT erstellt erzwingt man die Verwendung des alten Optimizers und siehe da, plötzlich werden die Zugriffspfade wieder korrekt verwendet und die Zeiten liegen "nur noch" um Faktor 10-100 daneben.

Allerdings konnte IBM keine Aussage treffen, ab wann der neue "Optimizer" auch LF's mit Select/Omit berücksichtigt. Es kann also mit irgendeinem PTF/Update plötzlich wieder zu pessimistischen Zeitschätzungen kommen.