SQL Select Statement - Execute dauert sehr lange

[Fuerchau]

Das deutet auf einen IPL am Wochenende hin, so dass temporäre, aber gemerkte Indizes dann wieder weg sind.
Das Löschen eine View ist eigentlich unnötig, sie kostet nichts.
Das Problem sind ggf. nicht die Verknüpfungen sondern die Where-Bedingung.
Hier entscheidet die AS/400 den Indexaufbau bzw. Tablescan.

Um den Debug einzuschalten benötigst du Berechtigung an STRDBG und nicht an STRTRC.
Also muss deine Einstellung für Debug in der Connection noch falsch sein, das deutet auch auf die Menge an Joblog-Einträgen hin.

[Robi]

? IPL am Wochenende?
äh, ja, nicht nur das. Ich schrieb:

Die Kiste fährt jede Nacht runter!

Das sollte bedeuten: jeden Tag IPL

Hmm,
unser erster versuch mit Debug brachte im Joblog die Meldung, das die Berechtigung für strtrc fehlt.
Ich frag nochmal in der Java Abteilung
Danke
Robi

[Robi]

So, nun mit debug ...

einzig diese Meldung könnte auf ein Prob. hin deuten:

Nachrichten-ID . . . . : CPI4338 Bewertung . . . . . . : 00
Nachrichtenart . . . . : Information
Sendedatum . . . . . . : 18.02.15 Sendezeit . . . . . . : 11:23:16

Nachricht . . . : 1 Zugriffspfad(e) für die Bitmap-Verarbeitung von Datei
FMD_STAT1 verwendet.
Ursache . . . . : Bitmap-Verarbeitung wurde für den Zugriff auf Sätze aus
Teildatei FMD_STAT1 der Datei FMD_STAT1 in Bibliothek BECKERF0 verwendet.
Bitmap-Verarbeitung ist eine Methode, bei der für den Zugriff auf
ausgewählte Sätze in einer Datei ein oder mehrere Zugriffspfade verwendet
werden können. Bei Bitmap-Verarbeitung wird zum Erstellen einer Bitmap die
Satzauswahl an jedem Zugriffspfad angelegt, ähnlich der
Schlüsselspaltenpositionierung. In der Bitmap sind nur die Sätze der Datei
markiert, die ausgewählt werden sollen. Wird mehr als ein Zugriffspfad
verwendet, werden die resultierenden Bitmaps mit Hilfe Boolscher Logik
gemischt. Die resultierende Bitmap wird verwendet, um den Zugriff nur auf
Weitere ...

die aus der Datei ausgewählten Sätze zu beschränken.
Bitmap-Verarbeitung wird gemeinsam mit zwei primären Zugriffsmethoden
verwendet: Zugriffsmethode nach Eingangsfolge (CPI4327 oder CPI4329) oder
Zugriffsmethode nach Schlüsselfolge (CPI4326 oder CPI4328). Die Nachricht
mit einer Beschreibung der primären Zugriffsmethode ist dieser Nachricht
vorangestellt.
Wird die Bitmap mit der Zugriffsmethode nach Schlüsselfolge verwendet,
dient die Bitmap zur Reduzierung der Anzahl der vom primären Zugriffspfad
ausgewählten Sätze, bevor die ausgewählten Sätze aus der Datei abgerufen
werden.
Wird die Bitmap mit der Zugriffmethode nach Eingangsfolge verwendet,
können bei der sequenziellen Suche in der Datei alle nicht von der Bitmap
ausgewählten Sätze der Datei übersprungen werden. Dieses ist auch als
sequenzielle Verarbeitung mit Überspringen (Skip Sequential Processing)
bekannt.
Die im folgenden aufgeführte Liste zeigt die Namen der Zugriffspfade, die
in der Bitmap-Verarbeitung verwendet werden:
TESTF/AKTENL11
Falls es sich bei Datei FMD_STAT1 in Bibliothek BECKERF0 um eine logische
Datei handelt, ist Teildatei AKTENP der physischen Datei AKTENP in
Bibliothek TESTF die eigentliche Datei, auf die zugegriffen wird.
Fehlerbeseitigung: Die Themensammlung "DB2 for i - Database Performance and
Query Optimization" der Kategorie Datenbank im IBM i Information Center
enthält weitere Hinweise zur Bitmap-Verarbeitung.

Die anderen Nachrichten, die einen Zugriffspfad empfehlen beziehen sich auf sehr kleine Dateien mit 30-40 Sätzen, die überwiegend der Textung einzelner Kennzeichen dienen.

Und eine Datei, deren Key key1 und key2 ist. Die Empfehlung lautet key2 und key1!
Diese Datei ist IMMER mit beiden Feldern verknüpft, eine Where Bedingung ist nicht auf diese Keyfelder vorhanden.

Wenn es das nächste mal 2 Stunden läuft leg ich die mal an

Ist schon komisch ...
Jetzt geht es gerade wie es soll und man hofft das es wieder langsam wird ...

Robi

[Fuerchau]

Der Optimizer ist schon manchmal eine Katastrophe!
Bei einer Verknüpfung baut dieser die Reihenfolge der Joins schon mal um.
Beispiel:
Aus
select * from filea a
inner join fileb b on a.key=b.key
wird dann gerne
select * from fileb b
inner join filea a on a.key = b.key

Je nach Satzanzahl und Auswahl ist der umgedrehte Weg aber ungünstiger da ggf. erheblich mehr Daten gelesen werden müssen.
Wenn schon umgedreht, kann man ggf. Bedingungen der Where-Klauses in die Join-Klausel legen und einen Index drüber legen, z.B.:

select *
from filea a
inner join fileb b on a.key = b.key

where b.F1 = 'Wert1'
and a.f2 = 'Wert2'

Where-Klauseln aus 2 (oder mehr Dateien) sind eher selten per Index auswählbar da immer beide Seiten verarbeitet werden müssen.
Hier bietet sich das Anhängen von teilen der Where-Klauseln an die Joinbeziehung an sowie Anlegen des entsprechenden Indexes:

select *
from filea a
inner join fileb b on b.F1 = 'Wert1' and a.key = b.key
where b.F1 = 'Wert1'
and a.f2 = 'Wert2'

oder je nach Volumen der beteiligten Dateien die Abfrage umzudrehen und den Index zu erstellen:

select *
from fileb b
inner join filea a on a.F2 = 'Wert2' and a.key = b.key

where b.F1 = 'Wert1'
and a.f2 = 'Wert2'

Ziel ist es einfach, die Anzahl der benötigten Lesezugriffe zu minimieren.
Auch hier hat sich für mich gezeigt, dass der Optimizer leider das Umdrehen selber übernimmt und dadurch ungünstiger wird.
Hier hilft das gezielt Einsetzen von "Left Join" und das Abfragen im Where per Coalesce, da der Optimizer ja sonst automatisch wieder einen "Inner Join" daraus strikt.

select *
from filea a
left join fileb b on b.F1 = 'Wert1' and a.key = b.key
where coalesce(b.F1, '') = 'Wert1'
and a.f2 = 'Wert2'

Der Optimizer will schon mal überlistet werden.

[Robi]

Moin zusammen,

Gestern ist der Aufruf (ohne Änderungen) vom Admin nach 1,5 Stunden abgebrochen worden, da es das System gebremst hat.
Ich hatte, parallel zu dem Lauf, ein paar LF gemäß Debug Empfehlung angelegt
(obwohl ich nicht wirklich einsehe auf Dateien mit max. 50 Sätzen zusätzliche LF zu legen)
Der 2. Aufruf (mit den LF) war dann wieder schnell.
Heute morgen läuft er wieder endlos, die LF Empfehlungen sind andere.
Vorgestern war (den ganzen Tag, auch der 1. Aufruf) alles schnell, nun ist es wieder 'mal so, mal so'
Wenn immer der 2 Aufruf schneller ging, ok aber selbst das ist nicht (immer) so.

Habe die View Erstellung und das letzte Posting von Baldur nun an 2 Kollegen gegeben.
Vielleicht können die noch was verschlimbessern!

Danke allen die uns unterstützt haben.
Robi

[Robi]

Nachtrag:
durch den eingeschalteten DEBUG kommt ein Spool raus : JOBSQL
In dem steht, nach jedem aufgelisteten SQL-Statement:

SQL4021 Zugriffsplan zuletzt am 19.02.15 um 15:48:46 gesichert.
SQL4020 Geschätzte Abfrageausführungszeit beträgt 0 Sekunden.
....
...das nächste Statement
SQL4021 Zugriffsplan zuletzt am 19.02.15 um 16:15:38 gesichert.
SQL4020 Geschätzte Abfrageausführungszeit beträgt 0 Sekunden.


Bleibe dabei ... am Zugriff kann es nicht liegen
Robi

[Fuerchau]

Dann würde ich dir eine Fehlermeldung an IBM empfehlen.

[Fuerchau]

Ich kann da so manche bzgl. des Optimizers schon verstehen.
Seit V7R1 laufen da halt viele SQL's langsamer.
Ich habe nun eine QAQQINI-Option ausgeschaltet:

FORCE_JOIN_ORDER *YES

Damit verhindere ich das Umbauen der Joins durch den Optimizer. Schließlcih habe ich mir bei den Joins und den On-Beziehungen was gedacht und entsprechende Indizes angelegt.
Und siehe da:
Ein SQL der mit V6R1 noch schnell war, mit V7R1 sporadisch, also wie oben nicht immer, 30 Minuten brauchte, läuft nun immer unter 1 Minute (Gesamtverarbeitung, nicht nur die Abfrage selber).
Na wer sagt's denn!!!!

[BenderD]

Ich kann da so manche bzgl. des Optimizers schon verstehen.
Seit V7R1 laufen da halt viele SQL's langsamer.
Ich habe nun eine QAQQINI-Option ausgeschaltet:

FORCE_JOIN_ORDER *YES

Damit verhindere ich das Umbauen der Joins durch den Optimizer. Schließlcih habe ich mir bei den Joins und den On-Beziehungen was gedacht und entsprechende Indizes angelegt.
Und siehe da:
Ein SQL der mit V6R1 noch schnell war, mit V7R1 sporadisch, also wie oben nicht immer, 30 Minuten brauchte, läuft nun immer unter 1 Minute (Gesamtverarbeitung, nicht nur die Abfrage selber).
Na wer sagt's denn!!!!

... bei mehrfachen beteiligten an einem join geht das Datenvolumen schon mal steil nach oben, wenn die Query engine sich für einen Crossjoin und spätere Auswertung der where clause entscheidet. Ich würde in jedem Fall erstmal dafür sorgen, dass ich primary keys und referential constraints habe und im skizzierten Szenario kriegt man die Faxen relativ sicher mit Extrakten, die man dann verjoint weg.

D*B

[Fuerchau]

Der Sch.... ist doch, dass ich die ganze Optimierungsarie bereits mit V5R3 für abgeschlossen hielt.
Bis V6R1 hielt sich die IBM ja auch daran.
Aber seit V7R1 muss ich ständig wieder dran.
Ich kann die anderen da schon verstehen, aber den Optimizer nicht.

Wenn ich schon einen Join mit F1/F2/F3 mache und ein Index in dieser Folge besteht, weiß ich nicht warum der Optimizer einen Index mit F1/F3/F2 vorschlägt. Manchmal auch nur ein einzelnes Feld, z.B. nur F3, aber im Plan dann doch wieder meinen Index nimmt.

Aber wie du schon sagst, auch kleine Dateien mit wenigen Sätzen benötigen einen vernünftigen Index.
Wenn ich 1Mio Sätze mit so einer kleinen Datei verknüpfe und kein vernünftiger Index besteht erwinge ich da schon 1Mio Mal einen Tablescan. Bei einer 100-Satzdatei können das im Zweifel halt mal 50 Zugriffe sein, macht in Summe dann 50Mio. Wenn ich dann noch mehrere davon habe...

Ich hatte da auch schon mal eine solche Abfrage, die grobgerechnet 100 Milliarden Zugriffe erfordert.
Da kann man sich dann schon mal einen Kurzurlaub gönnen.

[BenderD]

... das Problem ist zuweilen, dass der Optimizer die Selektivität von where Bedingungen nicht korrekt einschätzt und dann, je nachdem ob das gerade ein pessimistisches oder ein optimistisches Release ist, einen Hang zum Full table scan entwickelt. Letzteres lässt sich zuweilen mit optimize for 1 row oder mit einem order by nach Feldern der größten Tabelle zähmen.

D*B