Ferne SQL Analyse / Performance

[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 :/