We manually found out that the view was the problem. But we needed index advisor to find out wihich index was missing.
If I remember it right the missing index led to scanning one 80 k row table 500 times for every execution.
It was dynamic SQL, but we ran index advisor on the view itself.
------------------------------
Martin Ålund
------------------------------
Original Message:
Sent: Tue October 19, 2021 11:54 AM
From: Patrick Bossman
Subject: Information Xchange - A performance question
So it was a dynamic SQL and you saw it via the statement cache snap interface?
------------------------------
Patrick Bossman
Original Message:
Sent: Tue October 19, 2021 11:50 AM
From: Martin Ålund
Subject: Information Xchange - A performance question
We went from "not possible to run" to "execution within milliseconds". It took us about an hour to solve the problem. Most of the time spent came from figuring out how to find the problem. When we realised that we could use Data Studio we found and solved the problem in minutes.
------------------------------
Martin Ålund
Original Message:
Sent: Tue October 19, 2021 11:44 AM
From: Patrick Bossman
Subject: Information Xchange - A performance question
Nice! Resolving performance problems near and dear to my heart...
What was the performance improvement and how quickly were you able to respond?
------------------------------
Patrick Bossman
Original Message:
Sent: Tue October 19, 2021 11:39 AM
From: Martin Ålund
Subject: Information Xchange - A performance question
We accessed several tables thru a complex view. Thanks to index advisor we found out which index was missing
------------------------------
Martin Ålund
Original Message:
Sent: Tue October 19, 2021 11:35 AM
From: CALENE JANACEK
Subject: Information Xchange - A performance question
What was your most difficult query performance problem, and how did you solve it?
------------------------------
CALENE JANACEK
------------------------------
#Db2Toolsforz/OS