Unanswered: Bad query performance but only occasionally
db2 v9 in z/OS.
I have a large query that joins several tables and views. It works fine generally finishing in less than 30 seconds.
But only for some data conditions in the WHERE clause it slows down a lot and finishes in 8 minutes.
AND ((ROLLNUM LIKE 'P15%) OR
(ROLLNUM LIKE 'P16%) OR
(ROLLNUM LIKE 'P17%))
In this case it takes 8 minutes to finish. But for any other values of ROLLNUM, e.g.
alone (ROLLNUM LIKE 'P15%)
alone (ROLLNUM LIKE 'P16%)
alone (ROLLNUM LIKE 'P17%)
or both (ROLLNUM LIKE 'P16%) OR (ROLLNUM LIKE 'P17%) etc.
or any other data value e.g.
P33%, P34%, P35, it finishes in seconds.
I have no idea why for those set of three data conditions P15, P16 and P17, it takes so long.
I have checked the number of rows in the table for these conditions and there are other data values that have far more rows in table than these three. But they finish in seconds too but not these three.
Any idea what should I be looking at to resolve this issue?
Apologies if the problem description was not very clear.
The only way to be sure is to look at the query plan. However, if you're paid by the hour, you can continue guessing.
Thanks n_i. I checked the plan_table already. As I mentioned earlier it is using indexes. What exactly would you look in query plan when it runs fast for almost all scenarios except those three data values I mentioned since you are not paid by hour, I assume.